Create A Primary Key In Postgreg

Use SERIAL with PRIMARY KEY to create a unique primary key for a table. For example, here's the SQL to make a table with an id column as the primary key:

CREATE TABLE schema_name.table_name (
    id SERIAL PRIMARY KEY,
    another_column INT
);

Notes

  • Using SERIAL provides an auto incrementing number up to 2,147,483,647 that takes 4 bytes per row
  • There's also SMALLSERIAL and BIGSERIAL which go up to 32,767 with 2 bytes and 9,223,372,036,854,775,807 with 8 bytes, respectively

Further Reading

  • You don't need to bother with it unless you're interested in the details, but the different versions of SERIAL aren't really types. They're a "notational convenience". Check out the docs if you're interested in the specifics. Otherwise, you can just ignore it.
  • Another thing you probably won't have to mess with is an alternate method by making an IDENTITY column. You can't link to it directly but search for GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] on this page for details that also tie into CREATE SEQUENCE

Note, if you try to create it with an integer, you'll get something like this as an error:

null value in column "id" of relation "example_table" violates not-null constraint