Create A Primary Key In Postgreg
November - 2021
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
andBIGSERIAL
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