How to use auto-increment with PostgreSQL sequence

21 July,2020 by Jack Vamvas

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 (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to use auto-increment with PostgreSQL sequence


dba-ninja.com