How to use auto-increment with PostgreSQL sequence

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.

!