Arrays, enums, domains and composite types

Output

$ psql -X -a -f tags.sql
-- 50,000 products, each with a category tag and one more tag.
CREATE TABLE products (id int PRIMARY KEY, name text NOT NULL, tags text[] NOT NULL);
CREATE TABLE
INSERT INTO products
SELECT n, 'product ' || n,
       ARRAY[(ARRAY['tea', 'coffee', 'snacks', 'spices', 'sweets'])[1 + n % 5],
             (ARRAY['organic', 'gift', 'vegan', 'sale', 'new', 'local', 'imported'])[1 + n % 7]]
FROM generate_series(1, 50000) AS n;
INSERT 0 50000
CREATE INDEX products_tags ON products USING gin (tags);
CREATE INDEX
VACUUM ANALYZE products;
VACUUM
-- The operators of array_ops, the default GIN operator class for arrays.
SELECT amopopr::regoperator AS operator FROM pg_amop
WHERE amopfamily = (SELECT opcfamily FROM pg_opclass WHERE opcname = 'array_ops'
                    AND opcmethod = (SELECT oid FROM pg_am WHERE amname = 'gin'))
ORDER BY amopstrategy;
       operator        
-----------------------
 &&(anyarray,anyarray)
 @>(anyarray,anyarray)
 <@(anyarray,anyarray)
 =(anyarray,anyarray)
(4 rows)

-- Products tagged both tea and organic.
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM products WHERE tags @> ARRAY['tea', 'organic'];
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  Bitmap Heap Scan on products (actual rows=1428.00 loops=1)
         Recheck Cond: (tags @> '{tea,organic}'::text[])
         Heap Blocks: exact=562
         ->  Bitmap Index Scan on products_tags (actual rows=1428.00 loops=1)
               Index Cond: (tags @> '{tea,organic}'::text[])
               Index Searches: 1
(7 rows)

-- The same question for one tag, written with = ANY.
EXPLAIN (COSTS OFF) SELECT count(*) FROM products WHERE 'tea' = ANY (tags);
                 QUERY PLAN                 
--------------------------------------------
 Aggregate
   ->  Seq Scan on products
         Filter: ('tea'::text = ANY (tags))
(3 rows)

EXPLAIN (COSTS OFF) SELECT count(*) FROM products WHERE tags @> ARRAY['tea'];
                     QUERY PLAN                      
-----------------------------------------------------
 Aggregate
   ->  Bitmap Heap Scan on products
         Recheck Cond: (tags @> '{tea}'::text[])
         ->  Bitmap Index Scan on products_tags
               Index Cond: (tags @> '{tea}'::text[])
(5 rows)

-- Every table and index of this design, and their total.
SELECT coalesce(relname, 'total') AS relation, pg_size_pretty(sum(pg_relation_size(oid))) AS size
FROM pg_class WHERE relnamespace = 'public'::regnamespace GROUP BY ROLLUP (relname) ORDER BY relname;
   relation    |  size   
---------------+---------
 products      | 4496 kB
 products_pkey | 1112 kB
 products_tags | 192 kB
 total         | 5800 kB
(4 rows)

-- The same tags as one comma-separated string per product: smaller, but searched by pattern.
CREATE TABLE products_csv AS SELECT id, name, array_to_string(tags, ',') AS tags FROM products;
SELECT 50000
SELECT pg_size_pretty(pg_relation_size('products_csv')) AS csv_table,
       'teapot,gift' LIKE '%tea%' AS teapot_matches_tea;
 csv_table | teapot_matches_tea 
-----------+--------------------
 3072 kB   | t
(1 row)

-- Renaming one tag rewrites every row that carries it.
UPDATE products SET tags = array_replace(tags, 'sale', 'offer') WHERE tags @> ARRAY['sale'];
UPDATE 7143

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 tags_session.py

Output

$ psql -X -a -f jointable.sql
-- The same 50,000 products, with their tags as rows of a join table.
CREATE TABLE products (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE
CREATE TABLE tags (id int PRIMARY KEY, name text NOT NULL UNIQUE);
CREATE TABLE
CREATE TABLE product_tags (
  product_id int NOT NULL REFERENCES products,
  tag_id int NOT NULL REFERENCES tags,
  PRIMARY KEY (product_id, tag_id));
CREATE TABLE
INSERT INTO products SELECT n, 'product ' || n FROM generate_series(1, 50000) AS n;
INSERT 0 50000
INSERT INTO tags (id, name)
SELECT id, name FROM unnest(ARRAY['tea', 'coffee', 'snacks', 'spices', 'sweets', 'organic',
                                  'gift', 'vegan', 'sale', 'new', 'local', 'imported'])
                     WITH ORDINALITY AS t(name, id);
INSERT 0 12
INSERT INTO product_tags SELECT n, 1 + n % 5 FROM generate_series(1, 50000) AS n;
INSERT 0 50000
INSERT INTO product_tags SELECT n, 6 + n % 7 FROM generate_series(1, 50000) AS n;
INSERT 0 50000
CREATE INDEX product_tags_tag ON product_tags (tag_id, product_id);
CREATE INDEX
VACUUM ANALYZE products, tags, product_tags;
VACUUM
-- Products tagged both tea and organic: one row per tag, grouped back per product.
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF)
SELECT count(*) FROM (
  SELECT pt.product_id FROM product_tags pt JOIN tags t ON t.id = pt.tag_id
  WHERE t.name IN ('tea', 'organic')
  GROUP BY pt.product_id HAVING count(*) = 2) AS both_tags;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   ->  HashAggregate (actual rows=1428.00 loops=1)
         Group Key: pt.product_id
         Filter: (count(*) = 2)
         Batches: 1  Memory Usage: 1305kB
         Rows Removed by Filter: 14286
         ->  Nested Loop (actual rows=17142.00 loops=1)
               ->  Seq Scan on tags t (actual rows=2.00 loops=1)
                     Filter: (name = ANY ('{tea,organic}'::text[]))
                     Rows Removed by Filter: 10
               ->  Index Only Scan using product_tags_tag on product_tags pt (actual rows=8571.00 loops=2)
                     Index Cond: (tag_id = t.id)
                     Heap Fetches: 0
                     Index Searches: 2
(14 rows)

-- Every table and index of this design, and their total.
SELECT coalesce(relname, 'total') AS relation, pg_size_pretty(sum(pg_relation_size(oid))) AS size
FROM pg_class WHERE relnamespace = 'public'::regnamespace GROUP BY ROLLUP (relname) ORDER BY relname;
     relation      |    size    
-------------------+------------
 product_tags      | 3544 kB
 product_tags_pkey | 3312 kB
 product_tags_tag  | 2208 kB
 products          | 2544 kB
 products_pkey     | 1112 kB
 tags              | 8192 bytes
 tags_name_key     | 16 kB
 tags_pkey         | 16 kB
 total             | 12 MB
(9 rows)

-- A tag that does not exist is refused by the foreign key.
INSERT INTO product_tags VALUES (1, 99);
psql:jointable.sql:27: ERROR:  insert or update on table "product_tags" violates foreign key constraint "product_tags_tag_id_fkey"
DETAIL:  Key (tag_id)=(99) is not present in table "tags".
-- Renaming a tag changes one row.
UPDATE tags SET name = 'offer' WHERE name = 'sale';
UPDATE 1

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 jointable_session.py

Output

$ psql -X -a -f arrayfns.sql
-- 10,000 baskets, each with its items in the order they were added.
CREATE TABLE baskets (id int PRIMARY KEY, items text[] NOT NULL);
CREATE TABLE
INSERT INTO baskets
SELECT n, CASE n % 3 WHEN 0 THEN ARRAY['tea', 'spices', 'tea', 'sugar']
                     WHEN 1 THEN ARRAY['coffee', 'sweets'] ELSE '{}' END
FROM generate_series(1, 10000) AS n;
INSERT 0 10000
VACUUM ANALYZE baskets;
VACUUM
-- A list of ids: = ANY (array) and IN (list) use the same B-tree.
EXPLAIN (COSTS OFF) SELECT id FROM baskets WHERE id = ANY (ARRAY[17, 42, 99]);
                     QUERY PLAN                     
----------------------------------------------------
 Index Only Scan using baskets_pkey on baskets
   Index Cond: (id = ANY ('{17,42,99}'::integer[]))
(2 rows)

EXPLAIN (COSTS OFF) SELECT id FROM baskets WHERE id IN (17, 42, 99);
                     QUERY PLAN                     
----------------------------------------------------
 Index Only Scan using baskets_pkey on baskets
   Index Cond: (id = ANY ('{17,42,99}'::integer[]))
(2 rows)

-- An array as numbered rows.
SELECT b.id, i.item, i.pos
FROM baskets AS b, unnest(b.items) WITH ORDINALITY AS i(item, pos) WHERE b.id = 3;
 id |  item  | pos 
----+--------+-----
  3 | tea    |   1
  3 | spices |   2
  3 | tea    |   3
  3 | sugar  |   4
(4 rows)

-- New in PostgreSQL 18: array_sort(), array_reverse(), min() and max() of arrays.
SELECT items, array_sort(items) AS sorted, array_sort(items, true) AS descending,
       array_reverse(items) AS reversed FROM baskets WHERE id = 3;
         items          |         sorted         |       descending       |        reversed        
------------------------+------------------------+------------------------+------------------------
 {tea,spices,tea,sugar} | {spices,sugar,tea,tea} | {tea,tea,sugar,spices} | {sugar,tea,spices,tea}
(1 row)

SELECT min(items), max(items) FROM baskets;
 min |          max           
-----+------------------------
 {}  | {tea,spices,tea,sugar}
(1 row)

-- An empty array has no dimensions and so no length, but its cardinality is 0.
SELECT array_ndims('{}'::text[]) AS dimensions, array_length('{}'::text[], 1) AS length,
       cardinality('{}'::text[]) AS cardinality;
 dimensions | length | cardinality 
------------+--------+-------------
            |        |           0
(1 row)

SELECT count(*) FILTER (WHERE array_length(items, 1) = 0) AS by_length,
       count(*) FILTER (WHERE cardinality(items) = 0) AS by_cardinality FROM baskets;
 by_length | by_cardinality 
-----------+----------------
         0 |           3333
(1 row)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 arrayfns_session.py

Output

$ psql -X -a -f pincode.sql
-- A PIN code: six digits, the first of them 1 to 9.
CREATE DOMAIN pin_code AS text CHECK (VALUE ~ '^[1-9][0-9]{5}$');
CREATE DOMAIN
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, pin pin_code NOT NULL);
CREATE TABLE
CREATE TABLE stores (id int PRIMARY KEY, city text NOT NULL, pin pin_code NOT NULL);
CREATE TABLE
INSERT INTO customers VALUES (1, 'Asha Rao', '411001');
INSERT 0 1
INSERT INTO stores VALUES (1, 'Pune', '011001');
psql:pincode.sql:6: ERROR:  value for domain pin_code violates check constraint "pin_code_check"
-- Test candidate values without raising an error.
SELECT v AS value, pg_input_is_valid(v, 'pin_code') AS valid
FROM (VALUES ('411001'), ('011001'), ('41100'), ('4110012'), ('41l001'), (' 411001')) AS t(v);
  value  | valid 
---------+-------
 411001  | t
 011001  | f
 41100   | f
 4110012 | f
 41l001  | f
  411001 | f
(6 rows)

SELECT message, sql_error_code FROM pg_input_error_info('011001', 'pin_code');
                               message                                | sql_error_code 
----------------------------------------------------------------------+----------------
 value for domain pin_code violates check constraint "pin_code_check" | 23514
(1 row)

-- One rule, two columns: the catalog knows every column of the domain.
SELECT table_name, column_name FROM information_schema.columns
WHERE domain_name = 'pin_code' ORDER BY table_name;
 table_name | column_name 
------------+-------------
 customers  | pin
 stores     | pin
(2 rows)

SELECT conname, pg_get_constraintdef(oid) FROM pg_constraint WHERE contypid = 'pin_code'::regtype;
    conname     |           pg_get_constraintdef            
----------------+-------------------------------------------
 pin_code_check | CHECK ((VALUE ~ '^[1-9][0-9]{5}$'::text))
(1 row)

-- Text operators work on the domain, and their results are plain text.
SELECT pg_typeof(pin) AS stored, pg_typeof(pin || '-1') AS concatenated FROM customers;
  stored  | concatenated 
----------+--------------
 pin_code | text
(1 row)

-- The domain itself allows NULL; NOT NULL belongs on each column.
SELECT NULL::pin_code IS NULL AS null_is_allowed;
 null_is_allowed 
-----------------
 t
(1 row)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 pincode_session.py

Output

$ psql -X -a -f enum.sql
-- Four statuses, in the order an order moves through them.
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');
CREATE TYPE
CREATE TABLE orders (id int PRIMARY KEY, status order_status NOT NULL);
CREATE TABLE
INSERT INTO orders VALUES (1, 'shipped'), (2, 'pending'), (3, 'cancelled'), (4, 'paid');
INSERT 0 4
-- Enum values sort in the order they were declared, not alphabetically.
SELECT id, status FROM orders ORDER BY status;
 id |  status   
----+-----------
  2 | pending
  4 | paid
  1 | shipped
  3 | cancelled
(4 rows)

SELECT id, status FROM orders WHERE status > 'paid' ORDER BY id;
 id |  status   
----+-----------
  1 | shipped
  3 | cancelled
(2 rows)

-- A new value inside a transaction cannot be used before that transaction commits.
BEGIN;
BEGIN
ALTER TYPE order_status ADD VALUE 'refunded' BEFORE 'cancelled';
ALTER TYPE
UPDATE orders SET status = 'refunded' WHERE id = 3;
psql:enum.sql:11: ERROR:  unsafe use of new value "refunded" of enum type order_status
LINE 1: UPDATE orders SET status = 'refunded' WHERE id = 3;
                                   ^
HINT:  New enum values must be committed before they can be used.
ROLLBACK;
ROLLBACK
-- Committed first, it can be used at once, and the table keeps its data file.
SELECT pg_relation_filenode('orders') AS file_before \gset
ALTER TYPE order_status ADD VALUE 'refunded' BEFORE 'cancelled';
ALTER TYPE
SELECT pg_relation_filenode('orders') = :file_before AS same_file;
 same_file 
-----------
 t
(1 row)

UPDATE orders SET status = 'refunded' WHERE id = 3;
UPDATE 1
SELECT enum_range(NULL::order_status);
                enum_range                 
-------------------------------------------
 {pending,paid,shipped,refunded,cancelled}
(1 row)

SELECT enumlabel, enumsortorder FROM pg_enum
WHERE enumtypid = 'order_status'::regtype ORDER BY enumsortorder;
 enumlabel | enumsortorder 
-----------+---------------
 pending   |             1
 paid      |             2
 shipped   |             3
 refunded  |           3.5
 cancelled |             4
(5 rows)

-- A value can be renamed, but there is no command to drop one.
ALTER TYPE order_status RENAME VALUE 'shipped' TO 'dispatched';
ALTER TYPE
ALTER TYPE order_status DROP VALUE 'refunded';
psql:enum.sql:23: ERROR:  dropping an enum value is not implemented
LINE 1: ALTER TYPE order_status DROP VALUE 'refunded';
                                ^
SELECT enum_range(NULL::order_status);
                  enum_range                  
----------------------------------------------
 {pending,paid,dispatched,refunded,cancelled}
(1 row)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 enum_session.py

Output

$ psql -X -a -f composite.sql
-- One line of an order as a composite type: an item, its price in rupees, a quantity.
CREATE TYPE order_line AS (item text, price numeric(10,2), qty int);
CREATE TYPE
CREATE TABLE orders (id int PRIMARY KEY, line order_line NOT NULL);
CREATE TABLE
INSERT INTO orders VALUES (1, ROW('Masala tea', 120.00, 2)), (2, ('Coffee, filter', 250, 1)),
                          (3, ROW('Cardamom', 90.50, 3));
INSERT 0 3
-- The whole value prints in its text form, with quotes where a field needs them.
SELECT id, line FROM orders ORDER BY id;
 id |            line             
----+-----------------------------
  1 | ("Masala tea",120.00,2)
  2 | ("Coffee, filter",250.00,1)
  3 | (Cardamom,90.50,3)
(3 rows)

-- Without parentheses, line is read as a table name.
SELECT line.price FROM orders;
psql:composite.sql:9: ERROR:  missing FROM-clause entry for table "line"
LINE 1: SELECT line.price FROM orders;
               ^
SELECT id, (line).item, (line).price * (line).qty AS total FROM orders ORDER BY id;
 id |      item      | total  
----+----------------+--------
  1 | Masala tea     | 240.00
  2 | Coffee, filter | 250.00
  3 | Cardamom       | 271.50
(3 rows)

-- A function takes the whole line as one argument.
CREATE FUNCTION line_total(l order_line) RETURNS numeric
LANGUAGE sql IMMUTABLE RETURN (l).price * (l).qty;
CREATE FUNCTION
SELECT line_total(ROW('Ginger', 40, 5)::order_line) AS ginger;
 ginger 
--------
 200.00
(1 row)

-- New in PostgreSQL 18: max() of a composite compares field by field.
SELECT max(line) FROM orders;
           max           
-------------------------
 ("Masala tea",120.00,2)
(1 row)

-- (line).* hands a client plain columns instead of one text value.
SELECT o.id, (o.line).* FROM orders AS o WHERE o.id = 2;
 id |      item      | price  | qty 
----+----------------+--------+-----
  2 | Coffee, filter | 250.00 |   1
(1 row)

Recorded with Python 3.14.8 on macOS 26 arm64. To run it yourself: mise exec python@3.14.8 -- python3 composite_session.py