On 1/15/20 8:40 AM, Werner Kuhnle wrote:
Hi,
I'm new to pg and want to port an application and database from ms sql to pg v12.
Together with my application a database containing initial standard data
which is needed by the application is also installed.
This is done via execution of SQL scripts.
Every table of the db has an id column and id column values are also contained in the SQL script
which is necessary for consistence.
In MS SQL id columns were defined as
id INT IDENTITY PRIMARY KEY
In PG id columns are defined as
id SERIAL PRIMARY KEY
Importing the SQL script for initial standard data with PG Admin 4 works without any problem.
But afterwards I get error messages with SqlState=23505 when inserting new rows into that databases by my application.
My application does not provide id values when doing INSERT INTO statements so that new id values are automatically provided by the database.
This worked in MS SQL DB without any problems.
But in PG there seem to be conflicts between the row id values of the rows that were
initially imported and the row id values which are automatically provided by the database
if values for id column are missing in INSERT INTO
When automatically providing id values PG seems to ignore the already existing id values.
I've tried to using the newer definition:
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
but that does not solve the problem.
Is there a way to define id columns to that when the database provides values,
it recognizes already existing values avoiding conflicts.
Any hint is highly appreciated.
My guess is that the SERIAL was defined for the column, then the data
was added with id values, but the counter for the sequence behind the
SERIAL was not updated to a value greater the the last id added. To verify:
1) In psql do \d some_table and look for the sequence name in the
default column e.g:
production=# \d order_header
Table
"public.order_header"
Column | Type | Collation | Nullable |
Default
--------------+--------------------------------+-----------+----------+------------------------------------------------------
order_no | integer | | not null |
nextval('order_header_order_no_seq'::text::regclass)
So 'order_header_order_no_seq'
2) Then:
production=# select * from order_header_order_no_seq;
last_value | log_cnt | is_called
------------+---------+-----------
252 | 0 | t
(1 row)
3) Then in your case:
select max(id) from some_table
Postgres also has UPSERT:
https://www.postgresql.org/docs/12/sql-insert.html
Look for
ON CONFLICT Clause
Though I don't think this is what you need at this point.
Regards
Werner
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
