Data Types

PostgreSQL's type system goes well beyond the basics — auto-incrementing IDs, arrays as a real column type, and a UUID type are all built in, not bolted on.

SERIAL: auto-incrementing primary keys

SERIAL isn't technically its own type — it's shorthand that creates an INTEGER column backed by a sequence that auto-generates the next value on insert:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  price NUMERIC(10,2)
);

INSERT INTO products (name, price) VALUES ('Notebook', 4.50), ('Pen', 1.20);
SELECT * FROM products;

 id |   name   | price 
----+----------+-------
  1 | Notebook |  4.50
  2 | Pen      |  1.20
(2 rows)

Neither INSERT mentioned id at all — the underlying sequence handed out 1 and 2 automatically. For a bigger table expecting to outgrow a 32-bit integer's ~2.1 billion ceiling, BIGSERIAL does the same thing backed by a 64-bit BIGINT.

TEXT vs VARCHAR(n)

Unlike most databases, PostgreSQL's TEXT and VARCHAR have no performance difference — VARCHAR(n) only adds a length check, nothing more. Most experienced Postgres users default to plain TEXT and add a CHECK constraint if they genuinely need to cap a length, rather than reaching for VARCHAR(n) out of habit carried over from other databases.

Arrays as a real column type

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  tags TEXT[]
);

INSERT INTO articles (title, tags) VALUES ('Postgres Basics', ARRAY['sql', 'database', 'postgres']);
SELECT title, tags FROM articles WHERE 'postgres' = ANY(tags);

      title       |          tags           
-------------------+--------------------------
 Postgres Basics   | {sql,database,postgres}
(1 row)

tags TEXT[] stores a whole array of text values in one column. ANY(tags) checks whether a value appears anywhere in that array — here, whether 'postgres' is one of the tags.

UUID

CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_email TEXT
);

INSERT INTO sessions (user_email) VALUES ('jamie@example.com');
SELECT * FROM sessions;

                  id                  |     user_email      
---------------------------------------+----------------------
 3f2a91c4-8b1d-4e6a-9c3f-1a2b3c4d5e6f | jamie@example.com
(1 row)