Re-2: Problem with SqlState=23505 when inserting rows - Mailing list pgsql-general

From Werner Kuhnle
Subject Re-2: Problem with SqlState=23505 when inserting rows
Date
Msg-id DIIE.00000D9E0000CD8A@kuhnle.com
Whole thread Raw
In response to Re: Problem with SqlState=23505 when inserting rows  (Michael Lewis <mlewis@entrata.com>)
Responses Re: Re-2: Problem with SqlState=23505 when inserting rows
List pgsql-general
 
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:
 
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®
Re: Problem with SqlState=23505 when inserting rows 15. Januar 2020, 17:51 Uhr
VonMichael Lewis
AnWerner Kuhnle
CcPostgreSQL 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 ) );

pgsql-general by date:

Previous
From: Rob Sargent
Date:
Subject: Re: OID out of range
Next
From: İlyas Derse
Date:
Subject: Is there a GoTo ?