CREATE TABLE: Types and Constraints That Matter

So far you have read tables someone else built. Now you build one. A CREATE TABLE statement is a promise: this table has these columns, each column holds this kind of value, and these rules always hold. The database keeps the promise for you, on every INSERT and UPDATE, from every program that ever writes to it. That is worth more than any check in application code, because application code gets bypassed. A script, a second app or a person typing in psql all skip your checks and none of them skips the database's.

A table, line by line

You will add a table to the shop for discount coupons.

CREATE TABLE coupons (
  coupon_id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code        text NOT NULL UNIQUE,
  percent_off integer NOT NULL CHECK (percent_off BETWEEN 1 AND 100),
  active      boolean NOT NULL DEFAULT true,
  expires     date
);

CREATE TABLE

Each line is a column name, then a type, then any rules. CREATE TABLE printed back means the table exists. Read \d coupons to see what the database remembers.

\d coupons

                           Table "public.coupons"
   Column    |  Type   | Collation | Nullable |           Default
-------------+---------+-----------+----------+------------------------------
 coupon_id   | integer |           | not null | generated always as identity
 code        | text    |           | not null |
 percent_off | integer |           | not null |
 active      | boolean |           | not null | true
 expires     | date    |           |          |
Indexes:
    "coupons_pkey" PRIMARY KEY, btree (coupon_id)
    "coupons_code_key" UNIQUE CONSTRAINT, btree (code)
Check constraints:
    "coupons_percent_off_check" CHECK (percent_off >= 1 AND percent_off <= 100)

Types that matter

Pick the type that matches what the value is, not what is easy. These cover most beginner tables.

Money is the one people get wrong. Never store prices in real or double precision. Those are binary floating point, and binary cannot hold 0.1 exactly.

SELECT 0.1::double precision + 0.2::double precision = 0.3::double precision AS float_equal,
       0.1::numeric + 0.2::numeric = 0.3::numeric AS numeric_equal;

 float_equal | numeric_equal
-------------+---------------
 f           | t
(1 row)

The float comparison is false. The numeric one is true. A rounding error that small turns into a report that is off by a cent, and then into an argument.

text is PostgreSQL's own name for an unlimited string. The standard calls the limited type character varying(n), and other databases differ in what they offer. GENERATED ... AS IDENTITY is standard SQL for an automatic number. MySQL calls the same thing AUTO_INCREMENT and SQL Server uses IDENTITY(1,1).

Constraints: let the database say no

A constraint is a rule on a column or table. There are five you will use all the time.

• PRIMARY KEY makes the column unique and not null. It is how you point at one row.
• NOT NULL says a value is required.
• UNIQUE says no two rows can share the value.
• CHECK (...) says a condition must be true for every row.
• DEFAULT fills in a value when the INSERT leaves the column out. It is not a rule, but it belongs here.

Insert one good row first, using the defaults.

INSERT INTO coupons (code, percent_off) VALUES ('WELCOME10', 10);

INSERT 0 1

SELECT coupon_id, code, percent_off, active, expires FROM coupons;

 coupon_id |   code    | percent_off | active | expires
-----------+-----------+-------------+--------+---------
         1 | WELCOME10 |          10 | t      |
(1 row)

active became t and expires stayed empty because you did not supply them. Now break each rule on purpose and read what the database says.

INSERT INTO coupons (code, percent_off) VALUES (NULL, 15);

ERROR:  null value in column "code" of relation "coupons" violates not-null constraint
DETAIL:  Failing row contains (2, null, 15, t, null).

INSERT INTO coupons (code, percent_off) VALUES ('WELCOME10', 15);

ERROR:  duplicate key value violates unique constraint "coupons_code_key"
DETAIL:  Key (code)=(WELCOME10) already exists.

INSERT INTO coupons (code, percent_off) VALUES ('BIGSALE', 150);

ERROR:  new row for relation "coupons" violates check constraint "coupons_percent_off_check"
DETAIL:  Failing row contains (4, BIGSALE, 150, t, null).

Three different rules, three clear messages. Each names the constraint and shows the row that was refused, so the error tells you what to fix. Nothing was inserted.

The failed inserts still used up identity numbers. The next good row will not get the next number in a row.

INSERT INTO coupons (code, percent_off) VALUES ('SPRING15', 15) RETURNING coupon_id, code;

 coupon_id |   code
-----------+----------
         5 | SPRING15
(1 row)

INSERT 0 1

Ids 2, 3 and 4 went to the failed attempts, so this one is 5. Identity numbers are unique, not gapless. Do not use them to count rows, and do not "fix" the gaps.

Common mistakes

• Using text for everything, including dates and numbers, so the database cannot reject nonsense or sort correctly.
• Storing money in a floating point type.
• Leaving out NOT NULL because "it will always be filled in".
• Using a person's name or email as the primary key instead of a number that never changes.
• Dropping and recreating a table when ALTER TABLE would do. Dropping deletes the data.

If you skip this, your tables accept anything and your queries spend their time cleaning up. Which rule from this lesson would have stopped the worst bad row you have seen in a spreadsheet?