1. Scott Severance
  2. Valentina Studio
  3. Tuesday, January 21 2020, 03:20 PM
  4.  Subscribe via email
Very new to SQL and I need some assistance on how to perform an auto-increment function to my primary keys.

I have tables created with primary keys in place. Need to know the next steps on how to take a sequence and apply it to each table and key.
Comment
There are no comments made yet.
Ruslan Zasukhin Accepted Answer
Hi Scott,

you are asking about which DB?
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 1
Scott Severance Accepted Answer
Postgresql
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 2
Sergey Pashkov Accepted Answer
Hello Scott,

For an existing table - create a new sequence, change the default value of the PK field, change the owner of this sequence to the PK field:


CREATE SEQUENCE "public"."sequence1"
INCREMENT 1
MINVALUE 0
START 0;

ALTER TABLE "public"."table1" ALTER COLUMN "field_pk" SET DEFAULT nextval('sequence1'::regclass);

ALTER SEQUENCE "public"."sequence1"
OWNED BY "public"."table1"."field_pk";


Each table must have its own sequence.

For a new table, it is simpler - just set Serial/Bigserial type for the primary key field.
References
  1. https://www.postgresqltutorial.com/postgresql-serial/
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 3
Scott Severance Accepted Answer
When creating the new table- both come up with the error: "Type serial does not exist"
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 4
Sergey Pashkov Accepted Answer
Need to see a complete query.

Here's how I did it:

-- CREATE TABLE "table_new" ------------------------------------
CREATE TABLE "public"."table_new" (
"field_pk" Serial NOT NULL,
"field_data" Character Varying( 2044 ) NOT NULL );
;
-- -------------------------------------------------------------


Or in the Valentina Studio Create Table dialog.
Attachments (1)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 5
Scott Severance Accepted Answer
Tried the Valentina studio option- I can create a table and make a field the primary key and set it to serial or bigserial, but as soon as it is created. The identifyer type changes to interger not serial. Then it provides the error as before. I will try the SQL option- thanks you your help.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 6
Sergey Pashkov Accepted Answer
That is correct, for Serial/Bigserial types PostgreSQL creates Int/Bigint field along with related sequence.
For existing field we can’t set Serial/Bigserial and the error is shown.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 7
Scott Severance Accepted Answer
Very good- yes I see the creation of the PK with serial set automatically creates that default value and increment and works like it should. Thank you.

nextval('"Table_Table_Id_Key_seq"'::regclass)
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Studio
  3. # 8
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.