SQL: How to ALTER a COLUMN so that it uses a PK SEQUENCE

I have a table called PaperTrailDocumentControlSetting that works as expected on my local database, but doesn’t work on our production database. I need to alter the table on the production database so that it matches the local database table.

------------------------------------
| PaperTrailDocumentControlSetting |
------------------------------------
|     id
|    etc...
------------------------------------

The error occurs when I try insert a row, the id column is being set to null because it is not set to use its sequence. e.g:

ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 163898, null, null, null, null, null, null, null, null, null). SQL state: 23502

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

The table on the local database has it’s id as:

id bigint NOT NULL DEFAULT nextval('papertraildocumentcontrolsetting_id_seq'::regclass),

The table on the production database has it’s id as:

id bigint NOT NULL,

I have tried the following SQL command to alter the column:

ALTER TABLE PaperTrailDocumentControlSetting ALTER COLUMN id bigint NOT NULL DEFAULT nextval('papertraildocumentcontrolsetting_id_seq'::regclass);

But that returns the following:

ERROR:  syntax error at or near "bigint"
LINE 1: ... PaperTrailDocumentControlSetting ALTER COLUMN id bigint NOT...
                                                             ^
SQL state: 42601
Character: 62

>Solution :

Judging from the ALTER TABLE syntax you can’t change the whole column specification in on go, but have to specify what you want to change.

I think this should work:

ALTER TABLE PaperTrailDocumentControlSetting 
   ALTER COLUMN id SET DEFAULT 
   nextval('papertraildocumentcontrolsetting_id_seq'::regclass);