Automatic Update of table field in PostgreSQL!

[anulu]

Hi,

I have a question concerning table fields that have been declared to be incremented by one automatically on every INSERT.

When I try to insert a row in a table, I have to specifically provide the number even on the field that's supposed to be incremented automatically.

This is a bummer, since I don't want to add extra steps to take the max value of that column and add one to that number in the next INSERT statement.

The main reason why I don't want to do the extra steps is not the work, but because it can lead to errors in cases where two users have accessed the same max number within a short amount of time, and then they will both try to insert the same number which will lead to fealure (if the key is unique).

Anyway, sorry I took so long. Does anyone have an example (preferably with PHP) where you don't have to provide the number for a field that's supposed to be incremented by one automatically.

Thanks for your help.

 

[ceco]

in newer versions of postgresql you can define that field as SERIAL and you can ommit it at all in the insert statement - it will be set automaticly to the next number

alternativly you can create sequence

CREATE SEQUENCE sequence_name;

and then in the INSERT statement you type

INSERT INTO table_name (fields_name ...) VALUES (nextval('sequence_name') .....);

this way can be simplified by defining default value of the field

ALTER TABLE talbe_name ALTER field_name SET DEFAULT nextval('sequence_name');

thus you can ommit the field in the insert statement and it will behave as if the field is defined as serial

 

[anulu]

Excellent response. Thank you so much for your help!