CODEWITHSUNDEEP
postgresqlprimary-keypsql
some1
some1

Reputation: 1737

Is a 'serial' column automatically a primary key in PostgreSQL?

I've created the following table in Postgres...

create table printq (
   model varchar(20),
   session integer,
   timestamp timestamp DEFAULT now(),
   id serial);

It seems to do exactly what I need it to... it auto-increments the id column, when I clear the table using truncate "RESTART IDENTITY" it resets the sequence (which is why I rebuilt the table in the first place -- the id column used to not restart upon truncation)

Anyway, when I do a \d on the table, I don't see anything about a primary key.

Table "public.printq"
  Column   |            Type             |                      Modifiers                      
-----------+-----------------------------+-----------------------------------------------------
 model     | character varying(20)       | 
 session   | integer                     | 
 timestamp | timestamp without time zone | default now()
 id        | integer                     | not null default nextval('printq_id_seq'::regclass)

Three questions:

Upvotes: 12

Views: 4824

Answers (1)

Egor Rogov
Egor Rogov

Reputation: 5398

  1. No, use id serial primary key for that.
  2. Well, a table doesn't actually "need" a primary key. It can live without PK, it can live without an autoincrementing field, it can contain duplicate rows all right. But in relational theory (on top of which SQL is built) each relation (i. e. table) is a set (in mathematical sense) of rows. So duplicate rows are not allowed, they are simply not possible in sets. And hence each relation has a field (or several fields) which has unique values for all the relation. Such field can be used to uniquely identify a row, and one of possible unique keys is called a primary key. Such a key is usually very useful for identifying the rows, that's why tables are supposed to have them. But technically they are not required.
  3. Yes, it will.

Upvotes: 15

Related Questions