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)

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.

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.

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.

When creating the new table- both come up with the error: "Type serial does not exist"

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.

Postgresql

Hi Scott,

you are asking about which DB?