1. Scott Severance
  2. Valentina Studio
  3. 火, 1月 21 2020, 03:20 PM
  4.  メールで購読
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.
コメント
There are no comments made yet.
Scott Severance 承諾済みの回答
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)
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
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.
コメント
There are no comments made yet.
Scott Severance 承諾済みの回答
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.
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
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.
添付ファイル
コメント
There are no comments made yet.
Scott Severance 承諾済みの回答
When creating the new table- both come up with the error: "Type serial does not exist"
コメント
There are no comments made yet.
Sergey Pashkov 承諾済みの回答
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.
参照
  1. https://www.postgresqltutorial.com/postgresql-serial/
コメント
There are no comments made yet.
Scott Severance 承諾済みの回答
Postgresql
コメント
There are no comments made yet.
Ruslan Zasukhin 承諾済みの回答
Hi Scott,

you are asking about which DB?
コメント
There are no comments made yet.
  • ページ :
  • 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