Auto increment primary key

To define a primary key that auto increments in PostgreSQL you create the table row using the SERIAL type with the PRIMARY KEY constraint, like this:

CREATE TABLE cars (
  id    SERIAL PRIMARY KEY,
  brand VARCHAR(30) NOT NULL,
  model VARCHAR(30) NOT NULL,
  year  CHAR(4) NOT NULL
);

In MySQL / MariaDB this is equivalent to using AUTO_INCREMENT:

CREATE TABLE cars (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  brand VARCHAR(30) NOT NULL,
  model VARCHAR(30) NOT NULL,
  year  CHAR(4) NOT NULL
);

Resetting the serial sequence

When testing a table with a SERIAL field, this number will grow even if you remove all items in the table (like you’d do during testing), so you might insert a value and its id is 15 for example.

To restart the sequence from 1, run the SQL query:

ALTER SEQUENCE TABLENAME_id_seq RESTART

Replace TABLENAME with your table name, like:

ALTER SEQUENCE users_id_seq RESTART

Using TablePlus you have the option to reset the sequence when you truncate a table to remove its content.

Fixing “relation does not exist” error

If you have a PostgreSQL database and a table named Car for example and you try doing

SELECT * FROM Car

you’ll see an error saying

Query 1 ERROR: ERROR:  relation "car" does not exist
LINE 1: SELECT * FROM Car

One issue might be the table actually does not exist.

But if it does, this error appears because PostgreSQL raises errors on tables with mixed cases.

Use this syntax instead:

SELECT * FROM "Car"

Wrapping the table name in double quotes preserves the exact casing.

Lessons in this unit:

0:	Introduction
1:	Introduction to PostgreSQL
2:	How to install PostgreSQL on macOS
3:	How to create a PostgreSQL database
4:	Navigating Databases in PostgreSQL
5:	PostgreSQL User Permissions
6:	▶︎ Tables and Data Types
7:	Where to host a PostgreSQL database
8:	PostgreSQL vs MySQL
9:	Troubleshooting PostgreSQL
10:	Vercel Postgres, no transactions?
11:	Set up Lucia Auth for local Postgres DB vs Vercel Postgres
12:	Connect to Postgres local vs Vercel Postgres with Kysely