Обсуждение: Problem with SqlState=23505 when inserting rows

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
providedby 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.

Regards

Werner

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
providedby 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@aklaver.com

Original Message processed by david® Re: Problem with SqlState=23505 when inserting rows 15. Januar 2020, 17:51 Uhr Von Michael Lewis An Werner Kuhnle Cc PostgreSQL General

 

On Wed, Jan 15, 2020 at 9:41 AM Werner Kuhnle <wek@kuhnle.com> wrote:
 

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.

 

You'll need to run something like the below to set the next value to the max current value. You'll just have to figure out the name of the sequence that is automatically created whether you use the pseudo type serial, or the newer IDENTITY option. Both are implemented with a sequence.
 

 

--set sequence to max ID on a table

select setval( 'table_name_id_seq', ( select max(id) + 1 from table_name ) );

On 1/16/20 12:14 AM, Werner Kuhnle wrote:
> Thanks Michael und Adrian for your answers.
> I think that
> "
> 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.
> "
> is a correct description of what happens.
> For avoiding the need of an additional command for updating the sequence
> before every insert statement:

You don't have to update the sequence before every INSERT. For each 
SERIAL column do this one time:

1) Find current max(id) for the column.

2) Update the the sequence:

select setval('seq_name', max_id);

 From then on the sequence will automatically increment the id with 
values that are greater then those in the table and you will not get the 
unique violation errors. The caveat is that if you manually supply an id 
value then all bets are off.

For more information on sequences see:

https://www.postgresql.org/docs/12/sql-createsequence.html

For SERIAL see:

https://www.postgresql.org/docs/12/datatype-numeric.html#DATATYPE-SERIAL

If you want more control over what is accepted for the value then you 
can use an IDENTITY column:

https://www.postgresql.org/docs/12/sql-createtable.html

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]


As to below, no.

> Is there a way to specify the desired behaviour
> (that PG always provides conflict-free id values, eg. max(id)+1 when
> id values are not given explicitly in the INSERT statement)
> already whend defining(!) the table in the CREATE TABLE statement ?


> 
>     Original Message processed by david® <https://david.tobit.software>
> 
>     *Re: Problem with SqlState=23505 when inserting rows*15. Januar
>     2020, 17:51 Uhr
>     *Von*     Michael Lewis <mailto:mlewis@entrata.com>
>     *An*     Werner Kuhnle <mailto:wek@kuhnle.com>
>     *Cc*     PostgreSQL General <mailto:pgsql-general@lists.postgresql.org>
> 
> 
> 
> 
>     On Wed, Jan 15, 2020 at 9:41 AM Werner Kuhnle <wek@kuhnle.com
>     <mailto:wek@kuhnle.com>> wrote:
> 
>         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.
> 
> 
>     You'll need to run something like the below to set the next value to
>     the max current value. You'll just have to figure out the name of
>     the sequence that is automatically created whether you use the
>     pseudo type serial, or the newer IDENTITY option. Both are
>     implemented with a sequence.
> 
>     --set sequence to max ID on a table
>     select setval( 'table_name_id_seq', ( select max(id) + 1 from
>     table_name ) );
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com