Tip: View the SQL query behind psql commands

If you want to view the SQL query used to construct the information returned from a psql command (which will help you learn the underlying information schema) then type \set ECHO_HIDDEN

$ psql test
psql (9.4.1)
Type "help" for help.

test=# \set ECHO_HIDDEN
test=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

      List of relations
Schema | Name | Type  | Owner
--------+------+-------+--------
public | temp | table | andrew
(1 row)

Unique constraint across two rows in PostgreSQL

I recently had a requirement where I needed an account to have zero, one or two actions associated with it. One could be a single action and the other could be one of many repeating types. I didn’t want two single actions and I didn’t want two or more types of repeating actions. To solve this I used two partial indexes to split the data set and apply a unique constraint to each set.

CREATE TABLE accounts (
  id   integer NOT NULL,
  name text    NOT NULL
);

CREATE TABLE actions (
  id          integer NOT NULL,
  account_id  integer NOT NULL,
  repeat_type text    NOT NULL DEFAULT 'none'
);

INSERT INTO accounts (id, name) VALUES (1, 'Test 1'), (2, 'Test 2');

If I create a unique index on actions(account_id) then I will only be able to have a single action per account.

CREATE UNIQUE INDEX idx_unique_accounts ON actions(account_id);

INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'none');
-- INSERT 0 1
INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'weekly');
-- ERROR:  duplicate key value violates unique constraint "idx_unique_accounts"
-- DETAIL:  Key (account_id)=(1) already exists.

DROP INDEX idx_unique_accounts;

The solution is to create two partial indexes, one for the single action and one for the repeating action.

TRUNCATE TABLE actions;
CREATE UNIQUE INDEX idx_unique_single_actions    ON actions(account_id) WHERE (repeat_type = 'none');
CREATE UNIQUE INDEX idx_unique_repeating_actions ON actions(account_id) WHERE (repeat_type != 'none');

INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'none');
-- INSERT 0 1
INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'weekly');
-- INSERT 0 1

Now inserting another single action will result in an error.

INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'none');
-- ERROR:  duplicate key value violates unique constraint "idx_unique_single_actions"
-- DETAIL:  Key (account_id)=(1) already exists.

Or inserting another repeating action, even of a different repeat type, will result in an error.

(sql) INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'monthly'); -- ERROR: duplicate key value violates unique constraint "idx_unique_repeating_actions" -- DETAIL: Key (account_id)=(1) already exists.