How to add an auto-incrementing primary key to an existing table, in PostgreSQL?

by

(Updated – Thanks to the people who commented)

Modern Versions of PostgreSQL

Suppose you have a table named test1, to which you want to add an auto-incrementing, primary-key id (surrogate) column. The following command should be sufficient in recent versions of PostgreSQL:

   ALTER TABLE test1 ADD COLUMN id SERIAL PRIMARY KEY;

Older Versions of PostgreSQL

In old versions of PostgreSQL (prior to 8.x?) you had to do all the dirty work. The following sequence of commands should do the trick:

  ALTER TABLE test1 ADD COLUMN id INTEGER;
  CREATE SEQUENCE test_id_seq OWNED BY test1.id;
  ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq');
  UPDATE test1 SET id = nextval('test_id_seq');

Again, in recent versions of Postgres this is roughly equivalent to the single command above.

More Related Contents:

  1. Changing primary key int type to serial
  2. How to reset postgres’ primary key sequence when it falls out of sync?
  3. How to convert primary key from integer to serial?
  4. What’s the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?
  5. serial in postgres is being increased even though I added on conflict do nothing
  6. Why can I create a table with PRIMARY KEY on a nullable column?
  7. postgres autoincrement not updated on explicit id inserts
  8. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  9. Reset PostgreSQL primary key to 1
  10. Calculating and saving space in PostgreSQL
  11. The forgotten assignment operator “=” and the commonplace “:=”
  12. Passing user id to PostgreSQL triggers
  13. Do nullable columns occupy additional space in PostgreSQL?
  14. how to emulate “insert ignore” and “on duplicate key update” (sql merge) with postgresql?
  15. How do I efficiently select the previous non-null value?
  16. Postgresql : Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections [duplicate]
  17. Dynamic SQL (EXECUTE) as condition for IF statement
  18. Disable PostgreSQL foreign key checks for migrations
  19. uWSGI, Flask, sqlalchemy, and postgres: SSL error: decryption failed or bad record mac
  20. Prevent recursive trigger in PostgreSQL
  21. Can’t connect to Postgresql on port 5432
  22. Spark Dataframes UPSERT to Postgres Table
  23. PostgreSQL Error: Relation already exists
  24. ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user
  25. How do you create a random string that’s suitable for a session ID in PostgreSQL?
  26. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  27. Update timestamp when row is updated in PostgreSQL
  28. Multicolumn index on 3 fields with heterogenous data types
  29. How to add a conditional unique index on PostgreSQL
  30. Select from a table variable

Leave a Comment