21 July,2020 by Rambler
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.
!
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |