Changing primary key int type to serial

by

Converting an int to a serial more or less only means adding a sequence default to the value, so to make it a serial;

  • Pick a starting value for the serial, greater than any existing value in the table
    SELECT MAX(id)+1 FROM mytable

  • Create a sequence for the serial (tablename_columnname_seq is a good name)
    CREATE SEQUENCE test_id_seq MINVALUE 3 (assuming you want to start at 3)

  • Alter the default of the column to use the sequence
    ALTER TABLE test ALTER id SET DEFAULT nextval('test_id_seq')

  • Alter the sequence to be owned by the table/column;
    ALTER SEQUENCE test_id_seq OWNED BY test.id

A very simple SQLfiddle demo.

And as always, make a habit of running a full backup before running altering SQL queries from random people on the Internet 😉

More Related Contents:

  1. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  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. What is PostgreSQL equivalent to autoincrement?
  11. How do I specify a password to ‘psql’ non-interactively?
  12. INSERT with dynamic table name in trigger function
  13. psql: FATAL: Ident authentication failed for user “postgres”
  14. What is the format for the PostgreSQL connection string / URL?
  15. Go and IN clause in Postgres
  16. Where does PostgreSQL store the database?
  17. Grant all on a specific schema in the db to a group role in PostgreSQL
  18. Can I make a plpgsql function return an integer without using a variable?
  19. Is there AUTO INCREMENT in SQLite?
  20. MySQL: #1075 – Incorrect table definition; autoincrement vs another key?
  21. How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
  22. Oracle SQL Developer and PostgreSQL
  23. PostgreSQL: Why psql can’t connect to server?
  24. Can I use return value of INSERT…RETURNING in another INSERT?
  25. Changing a postgres containers server port in Docker Compose
  26. no pg_hba.conf entry for host
  27. PostgreSQL authentication method 10 not supported
  28. How to delete an enum type value in postgres?
  29. Why unsigned integer is not available in PostgreSQL?
  30. now() default values are all showing same timestamp

Leave a Comment