1. Scott Severance
  2. Valentina Studio
  3. Dienstag, Januar 21 2020, 03:20 PM
  4.  Abonnieren via E-Mail
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.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Scott,

you are asking about which DB?
Kommentar
There are no comments made yet.
Scott Severance Akzeptierte Antwort
Postgresql
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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.
Referenzen
  1. https://www.postgresqltutorial.com/postgresql-serial/
Kommentar
There are no comments made yet.
Scott Severance Akzeptierte Antwort
When creating the new table- both come up with the error: "Type serial does not exist"
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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.
Anhänge
Kommentar
There are no comments made yet.
Scott Severance Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
Sergey Pashkov Akzeptierte Antwort
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.
Kommentar
There are no comments made yet.
Scott Severance Akzeptierte Antwort
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)
Kommentar
There are no comments made yet.
  • Seite :
  • 1


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

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories