Add Autoincrement primary key to PostgreSQL table in Python

Question

I'm trying to add an autoincrement primary key to a sql table converted from a pandas data frame.

The code to convert the dataframe to sql and add the alter table command to add autoincrement primary key is below

    test.to_sql('test_sql',schema='test_schema',con=conn,if_exists='replace',index=False)

with conn:
    conn.execute("ALTER TABLE test_schema.test_sql ADD XPK_test_sql id SERIAL PRIMARY KEY;")

I'm getting the following error:

ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "SERIAL"

I checked the syntax and it seems to be correct. Where am I going wrong? Is there any other method?

Answer 1

I'm not quite sure what you're going for: does the column already exist and you want to make it a primary key, or do you want to create a new primary key column?

Add Primary Key Constraint to existing column

These are the docs for the syntax to create a constraint:

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
    action [, ... ]

...

where action is one of:

    ...
    ADD table_constraint [ NOT VALID ]
    ...


and table_constraint is:


[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

...


index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

So you need to write something like

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column_name)

to add the (existing) column column_name of the table table_name as a primary key, with the new constraint being named constraint_name.

Add new primary key column

The docs are on the same page:

ADD COLUMN [ IF NOT EXISTS ]

    This form adds a new column to the table, using the same syntax as CREATE TABLE. 

The CREATE TABLE docs are not very readable IMO, but here are the relevant parts:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ COMPRESSION compression_method ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option ... ] }
    [, ... ]
] )
...
where column_constraint is:
  ...
  PRIMARY KEY index_parameters 
  ...

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:


[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
...

So, here's the syntax that you're looking for:

ALTER TABLE table_name ADD COLUMN column_name serial PRIMARY KEY