CREATE TABLE: types and constraints

Now we learn to build a database rather than read one. Try this lesson’s commands in your own personal database: Practice → Free play — there you can create and drop any table you like.

Creating a database

CREATE DATABASE kutubxona;

To connect to the new database in PostgreSQL: \c kutubxona. To list databases: \l.

Creating a table

CREATE TABLE books (
  id       ...,                         -- primary key (see below)
  title    varchar(200) NOT NULL,
  author   varchar(100) NOT NULL,
  year     int,
  price    decimal(10, 2) DEFAULT 0,
  isbn     varchar(20) UNIQUE
);

Each column has a name, a type and, if needed, constraints.

The main types

In PostgreSQL date-and-time is timestamp, and decimals are numeric (decimal works too).

Never store money in float: it is an approximate number, and 0.1 + 0.2 can come out as 0.30000000000000004. Money is decimal.

Automatic numbers: the primary key

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

The older serial also works: id serial PRIMARY KEY.

The database numbers the rows itself: 1, 2, 3… You do not write id in an INSERT.

Constraints

A foreign key

CREATE TABLE loans (
  id       bigint PRIMARY KEY ...,
  book_id  bigint NOT NULL,
  reader   varchar(100) NOT NULL,
  taken_at date NOT NULL DEFAULT CURRENT_DATE,
  FOREIGN KEY (book_id) REFERENCES books (id)
);

The rules:

• the parent table (books) is created first;
• the foreign-key column has the same type as the parent key (bigint ↔ bigint);
• you cannot lend a book that does not exist, and you cannot delete a book that is on loan.

Seeing a table’s structure

\d books — columns, types, keys. To list tables: \dt.

Exercise

In free play, create the kutubxona database and build the books and loans tables as shown above. Then try inserting a row with a book_id that does not exist and see which error you get. For step-by-step practice, missions 2 and 4 in the Practice section cover exactly this.

Remember

• CREATE TABLE name (column type constraints, …);
• Money is decimal, a date is date, text is varchar(n).
• Write foreign keys as a separate FOREIGN KEY line, in MySQL and PostgreSQL alike.