Читаем вместе. PostgreSQL 16 изнутри. Стр. 45 — 49

-- Сразу подготовка - посмотрим ограничения и индексы с помощью подготовленного оператора, понадобится чуть позже

PREPARE constrs (text) as
SELECT pgc.conname AS constraint_name,
       ccu.column_name,
           CASE WHEN contype = 'c' THEN 'check'
                        WHEN contype = 'f' THEN 'foreign key'
                        WHEN contype = 'p' THEN 'primary key'
                        WHEN contype = 'u' THEN 'unique'
                        WHEN contype = 't' THEN 'trigger'
                        WHEN contype = 'x' THEN 'exclusion'
           END constraint,
       pg_get_constraintdef(pgc.oid)
FROM pg_constraint pgc
         JOIN pg_namespace nsp ON nsp.oid = pgc.connamespace
         JOIN pg_class  cls ON pgc.conrelid = cls.oid
         left JOIN information_schema.constraint_column_usage ccu
                   ON pgc.conname = ccu.constraint_name
                       AND nsp.nspname = ccu.constraint_schema
WHERE table_name = $1
union all
SELECT 'null_not_null' constraint_name, attname column_name, 
	CASE WHEN attnotnull = 't' THEN 'not null' ELSE 'null' END constraint, 
	CASE WHEN attnotnull = 't' THEN 'NOT NULL' ELSE 'NULL' END pg_get_constraintdef 
FROM pg_attribute 
  WHERE attrelid = $1::regclass 
  AND attname NOT IN ('tableoid', 'cmax', 'cmin', 'xmax','xmin', 'ctid');

-- Ограничения на уровне столбцов
CREATE TABLE for_constraints (id integer CONSTRAINT pk_for_constraints PRIMARY KEY,
							  mark numeric (1) CONSTRAINT def_fc DEFAULT 5,
							  CONSTRAINT check_mark CHECK (mark >= 3 AND mark <= 5),
							  book_num numeric (5) CONSTRAINT unique_book_num UNIQUE NOT NULL
							 );
-- PRIMARY KEY
INSERT INTO for_constraints VALUES (1,5,1);
INSERT INTO for_constraints VALUES (1,5,2);
-- DEFAULT

SELECT * FROM for_constraints;
-- CHECK
INSERT INTO for_constraints VALUES (2, 4, 2);
INSERT INTO for_constraints VALUES (3, 8, 3);
-- UNIQUE
INSERT INTO for_constraints VALUES (4, 4, 2);
-- FOREIGN KEY
CREATE TABLE for_fk (id_fk integer REFERENCES for_constraints (id));
INSERT INTO for_fk VALUES (1);
INSERT INTO for_fk VALUES (11);
-- Посмотрим ограничения и индексы
EXECUTE constrs ('for_constraints');

-- Другой вариант - ограничения на уровне таблицы
CREATE TABLE for_constraints2 (id integer,
							   mark numeric(1) DEFAULT 5,
							   book_num numeric (5) NOT NULL,
							   CONSTRAINT pk_for_constraints2 PRIMARY KEY (id),
							   CONSTRAINT check_mark2 CHECK (mark >= 3 AND mark <= 5),
							   CONSTRAINT unique_book_num2 UNIQUE (book_num)
							  );
-- PRIMARY KEY
INSERT INTO for_constraints2 VALUES (1,5,1);
INSERT INTO for_constraints2 VALUES (1,5,2);
-- DEFAULT
INSERT INTO for_constraints2 (id, book_num) VALUES (1,1);
SELECT * FROM for_constraints2;
-- CHECK
INSERT INTO for_constraints2 VALUES (2, 4, 2);
INSERT INTO for_constraints2 VALUES (3, 8, 3);
-- UNIQUE
INSERT INTO for_constraints2 VALUES (4, 4, 2);
-- FOREIGN KEY
CREATE TABLE for_fk2 (id_fk integer,
					  CONSTRAINT fk_for_constraints2 FOREIGN KEY (id_fk) REFERENCES for_constraints2
					 );
INSERT INTO for_fk2 VALUES (1);
INSERT INTO for_fk2 VALUES (11);
-- Посмотрим ограничения и индексы
EXECUTE constrs ('for_constraints2');


-- Очистка лишнего
DROP TABLE for_constraints CASCADE;
DROP TABLE for_constraints2 CASCADE;
DROP TABLE for_fk CASCADE;
DROP TABLE for_fk2 CASCADE;
DEALLOCATE constrs;