2

I have table with id and name where id is the primary key with bigint as the datatype. Now I want to alter the column, where id as the primary key with auto incremented value.

My Table:

 CREATE TABLE test(
  testid bigint NOT NULL,
  testname character varying(255),
   CONSTRAINT test_pkey PRIMARY KEY (testid)
);

Alter query:

ALTER TABLE test ALTER COLUMN testid BIGSERIAL PRIMARY KEY;

After executing the query I'm getting the following error,

ERROR:  syntax error at or near "BIGSERIAL"
LINE 1: ALTER TABLE test ALTER COLUMN testid BIGSERIAL P...
                                                         ^

********** Error **********

ERROR: syntax error at or near "BIGSERIAL"
SQL state: 42601
Character: 50
FuzzyTree
  • 32,014
  • 3
  • 54
  • 85
Vinod
  • 2,263
  • 9
  • 55
  • 104

2 Answers2

0

you can try this command

ALTER TABLE your_table ALTER COLUMN key_column TYPE BIGSERIAL PRIMARY KEY;

i hope it will be helpful for you

Umar Ali
  • 404
  • 4
  • 14
0
--This will drop the primary key temporarily
ALTER TABLE test
drop CONSTRAINT test_pkey


--change data type
ALTER TABLE test
ALTER COLUMN testid BigInt


--add primary key
ALTER TABLE test
ADD CONSTRAINT test_pkey PRIMARY KEY (testid )
Shajeer Puzhakkal
  • 141
  • 3
  • 13