CODEWITHSUNDEEP
postgresqlprimary-keyauto-increment
Greg Z
Greg Z

Reputation:

How to prevent primary serial primary key from being updated with number not in sequence?

CREATE TABLE u_account (
Jid serial primary key,
score int4
);

The primary key works fine (updates itself) ok when I update it like this;

INSERT INTO u_account ('score') VALUES ('122233344');

However when I insert a value like this;

INSERT INTO u_account VALUES ('122233344');

This updates the primary key;

I don't want the primary key to accept anything other than the number that is supposed to be coming next.

Someone had set it up for me before so that if I put in this code;

INSERT INTO u_account VALUES ('122233344');

it would ignore the primary key and just update score.

Please help.

Upvotes: 0

Views: 1603

Answers (3)

ollyc
ollyc

Reputation: 6103

You can use "DEFAULT" to put the correct value in the primary key field, eg:

INSERT INTO u_account VALUES (DEFAULT, '122233344');

Upvotes: 1

Peter Eisentraut
Peter Eisentraut

Reputation: 36729

You could write a trigger that substitutes the next sequence value for the jid column on every insert.

Upvotes: 0

Greg Smith
Greg Smith

Reputation: 18136

It looks like you should just reverse the order of the two fields in your table. Then if you INSERT a single column value, it will overwrite the "score" field and use the primary key serial sequence to generate a value for the other column. This example does what I think you want:

CREATE TABLE u_account (
score int4,
Jid serial primary key
);

INSERT INTO u_account VALUES ('122233344');

Upvotes: 1

Related Questions