CODEWITHSUNDEEP
postgresql
David Garcia
David Garcia

Reputation: 2696

Postgres: INSERT INTO using Sequence on column

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.

Upvotes: 0

Views: 863

Answers (2)

Ruivens
Ruivens

Reputation: 11

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

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

Upvotes: 1

JGH
JGH

Reputation: 17846

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;

Upvotes: 1

Related Questions