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

dsolimano
  • 8,870
  • 3
  • 48
  • 63

3 Answers3

1

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');
Greg Smith
  • 16,965
  • 1
  • 34
  • 27
1

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

INSERT INTO u_account VALUES (DEFAULT, '122233344');
ollyc
  • 5,589
  • 1
  • 17
  • 8
0

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

Peter Eisentraut
  • 35,221
  • 12
  • 85
  • 90