A developer contacted me to to tell me they were experiencing problems with enabling an auto-increment on a PostgreSQL table.He wanted to know if the privileges assigned to his user credentials had the right level of permission to create a sequence.
I reviewed the privileges and identified some missing details:
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschema1 TO user1;
As a test I logged on as the user’s credentials and completed some simple validation tasks to confirm they could complete a CREATE SEQUENCE and add to a table.
Step 1 : Create a simple SEQUENCE
CREATE SEQUENCE myschema1.jvtest_seq;
and then check we can view the next value
SELECT nextval(‘jvtest_seq’);
nextval
———
1
Step 2 : Create a table and include the SEQUENCE
CREATE TABLE myschema1.jvtest2 (myKet int DEFAULT NEXTVAL(‘jvtest_seq’) NOT NULL, myvalue char(10));
–some test data
INSERT INTO myschema1.jvtest2(myvalue)values(‘test2’);
select * from myschema1.jvtest2;
myket | myvalue
——-+————
3 | test1
4 | test2
5 | test2
This is a very straightforward implementation of PostgreSQL SEQUENCE. There are many more considerations about whether you should use and how to use PostgreSQL SEQUENCE – read the PostgreSQL version documentation carefully.
!
Author: Jack Vamvas
Leave a Reply