Postgres: INSERT INTO using Sequence on column

Question

Given that I have the following create syntax for postgres

CREATE TABLE CusGoals(
  biRecipientId BIGINT Default 0, 
  iEnvironment SMALLINT Default Null, 
  iGoal01 SMALLINT Default Null, 
  iGoal02 SMALLINT Default Null, 
  iGoal03 SMALLINT Default Null, 
  iGoal04 SMALLINT Default Null, 
  iGoal05 SMALLINT Default Null, 
  iGoal06 SMALLINT Default Null, 
  iGoal07 SMALLINT Default Null, 
  iGoal08 SMALLINT Default Null, 
  iGoal09 SMALLINT Default Null, 
  iGoal10 SMALLINT Default Null, 
  iGoal11 SMALLINT Default Null, 
  iGoal12 SMALLINT Default Null, 
  iGoal13 SMALLINT Default Null, 
  iGoal14 SMALLINT Default Null, 
  iGoal15 SMALLINT Default Null, 
  iGoal16 SMALLINT Default Null, 
  iGoal17 SMALLINT Default Null, 
  iGoalsId INTEGER Default 0, 
  iHealthWellness SMALLINT Default Null, 
  iInclusion SMALLINT Default Null, 
  iPeople SMALLINT Default Null, 
  iPlanet SMALLINT Default Null, 
  iResponsibleConsumption SMALLINT Default Null, 
  iSustainableInfrastructure SMALLINT Default Null, 
  tsCreated TIMESTAMPTZ, 
  tsLastModified TIMESTAMPTZ
);

CREATE UNIQUE INDEX CusGoals_id ON CusGoals(iGoalsId);      
INSERT INTO CusGoals (iGoalsId) VALUES (0);

-- Log: Creating sequence 'auto_cusgoals_seq' for 'cus:goals'.

SELECT CreateSequenceIfNecessary('auto_cusgoals_seq', '1000', 'cache 30');

If I have the following insert statement

INSERT INTO CusGoals (iGoal01,iGoal04,iGoal14,iPlanet,iPeople,iInclusion,iResponsibleConsumption) VALUES (2,3,4,44,56,5,4)

How can I use the auto_cusgoals_seq on the iGoalsId to update correctly as I am getting the following error.

PGS-220000 PostgreSQL error: ERROR: duplicate key value violates unique constraint "cusgoals_id" DETAIL: Key (igoalsid)=(0) already exists. WDB-200001 SQL statement 'INSERT INTO CusGoals (iGoal01,iGoal04,iGoal14,iPlanet,iPeople,iInclusion,iResponsibleConsumption) VALUES (2,3,4,44,56,5,4)' could not be executed.

JGH · Accepted Answer · 2021-07-02 18:09:34Z

1

The new sequence must be bumped to the current max value.

You can reset it using

SELECT setval('auto_cusgoals_seq', max(igoalsid)) FROM CusGoals;

answered Jul 2, 2021 at 18:09

JGH

18.2k5 gold badges41 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Garcia Over a year ago

Thanks, i got it working like this also; ` var sqlSyntax = "INSERT INTO CusGoals (igoalsid, "+stringColumns+") VALUES (nextval('auto_cusgoals_seq'),"+stringValues+")";`

Ruivens · Accepted Answer · 2021-07-02 18:15:56Z

1

Assuming that biRecipientId is your primary key, just alter the data format from bigint to serial.

https://www.postgresqltutorial.com/postgresql-serial/

answered Jul 2, 2021 at 18:15

Ruivens

113 bronze badges

1 Comment

David Garcia Over a year ago

my application does not allow such configuration on the schema creation and I dont have access to the db directly.

Collectives™ on Stack Overflow

Postgres: INSERT INTO using Sequence on column

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related