Пример создания таблицы с разными типами столбцов и ограничениями целостности:
=> CREATE TABLE people( id serial PRIMARY KEY, name varchar(100) NOT NULL, employee boolean DEFAULT true, CONSTRAINT uname UNIQUE(name) );
CREATE TABLE
Синтаксис соответствуют стандарту. Можно использовать различные типы данных и значения по умолчанию, указывать ограничения целостности как на уровне отдельных полей, так и на уровне таблицы.
Создадим аналогичную, но временную таблицу. Для этого воспользуемся специальным синтаксисом LIKE:
=> BEGIN;
BEGIN
=> CREATE TEMPORARY TABLE people_temp(LIKE people INCLUDING ALL) ON COMMIT DELETE ROWS;
CREATE TABLE
=> INSERT INTO people_temp(name) VALUES ('Иванов');
INSERT 0 1
=> COMMIT;
COMMIT
Что произошло при фиксации изменений?
Таблица пуста, потому что была объявлена как ON COMMIT DELETE ROWS:
=> SELECT * FROM people_temp;
id | name | employee ----+------+---------- (0 rows)
В любом случае временные таблицы живут не дольше, чем сеанс. В другом сеансе нашей таблицы не существует:
=> SELECT * FROM people_temp;
ERROR: relation "people_temp" does not exist
LINE 1: SELECT * FROM people_temp;
^
В нашей таблице поле id имеет тип serial, что означает целое число с автоувеличением.
=> INSERT INTO people(name, employee) VALUES ('Петров', false);
INSERT 0 1
=> SELECT * FROM people;
Какое значение будет иметь поле id у этой записи?
id | name | employee ----+--------+---------- 2 | Петров | f (1 row)
Два, потому что первое значение было взято для временной таблицы из той же последовательности:
=> \d people
Table "public.people"
Column | Type | Modifiers
----------+------------------------+-----------------------------------------------------
id | integer | not null default nextval('people_id_seq'::regclass)
name | character varying(100) | not null
employee | boolean | default true
Indexes:
"people_pkey" PRIMARY KEY, btree (id)
"uname" UNIQUE CONSTRAINT, btree (name)
Изменим последовательность так, чтобы она использовала кэширование (это может дать выигрыш, если последовательность активно используется одновременно многими сеансами):
=> ALTER SEQUENCE people_id_seq CACHE 10;
ALTER SEQUENCE
К последовательности можно при необходимости обращаться и непосредственно:
=> SELECT nextval('people_id_seq');
nextval
---------
3
(1 row)
=> SELECT nextval('people_id_seq');
Какое значение получит второй сеанс?
nextval
---------
13
(1 row)
Тринадцать, потому что значения от 3 до 12 закэшированы первым сеансом:
=> SELECT nextval('people_id_seq');
nextval
---------
4
(1 row)
Создадим большую таблицу:
=> CREATE TABLE bigtable(id serial, t text);
CREATE TABLE
=> INSERT INTO bigtable SELECT s.id, 'This is line #' || s.id FROM generate_series(1,1000000) AS s(id);
INSERT 0 1000000
=> SELECT count(*) FROM bigtable;
count --------- 1000000 (1 row)
Сколько времени занимает поиск одного значения в такой таблице?
=> \timing on
Timing is on.
=> SELECT * FROM bigtable WHERE id = 42;
id | t ----+------------------ 42 | This is line #42 (1 row) Time: 104,595 ms
=> \timing off
Timing is off.
Если попросить оптимизатор показать план запроса, мы увидим в нем Seq Scan - последовательное сканирование всей таблицы в поисках нужного значения (Filter):
=> EXPLAIN (COSTS OFF) SELECT * FROM bigtable WHERE id = 42;
QUERY PLAN
----------------------
Seq Scan on bigtable
Filter: (id = 42)
(2 rows)
Теперь проиндексируем таблицу:
=> CREATE UNIQUE INDEX bigtable_id ON bigtable(id);
CREATE INDEX
=> \timing on
Timing is on.
=> SELECT * FROM bigtable WHERE id = 42;
id | t ----+------------------ 42 | This is line #42 (1 row) Time: 0,410 ms
=> \timing off
Timing is off.
Невооруженным взглядом видно, что время уменьшилось.
А в плане запроса появился индекс:
=> EXPLAIN (COSTS OFF) SELECT * FROM bigtable WHERE id = 42;
QUERY PLAN
------------------------------------------
Index Scan using bigtable_id on bigtable
Index Cond: (id = 42)
(2 rows)
Однако индекс не является универсальным средством увеличения производительности. Обычно индекс очень полезен, если из таблицы требуется выбрать небольшую долю всех имеющихся строк. Если нужно прочитать много данных, индекс будет только мешать, и оптимизатор это понимает:
=> EXPLAIN (COSTS OFF) SELECT * FROM bigtable;
QUERY PLAN
----------------------
Seq Scan on bigtable
(1 row)
Кроме того, надо учитывать накладные расходы на обновление индексов при изменении таблицы и занимаемое ими место на диске.
Простой пример функции на SQL:
=> CREATE FUNCTION random_text(len integer) RETURNS text AS $$ SELECT string_agg( chr( trunc(65+random()*26)::integer ), '' ) FROM generate_series(1,len); $$ LANGUAGE sql;
CREATE FUNCTION
Теперь функцию можно использовать в запросах:
=> SELECT random_text(10);
random_text ------------- EJJOGXGMWV (1 row)
Продемонстрируем триггер для сохранения времени последнего изменения строк.
Добавим поле в таблицу people:
=> ALTER TABLE people ADD COLUMN last_update timestamp;
ALTER TABLE
Создадим функцию, которая будут использоваться в триггере:
=> CREATE FUNCTION set_last_update() RETURNS TRIGGER AS $$ BEGIN new.last_update := current_timestamp; RETURN new; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
И собственно триггер:
=> CREATE TRIGGER people_last_update BEFORE UPDATE OR INSERT ON people FOR EACH ROW EXECUTE PROCEDURE set_last_update();
CREATE TRIGGER
Проверим работоспособность вставки:
=> INSERT INTO people(id, name) VALUES (3, 'Сидоров');
INSERT 0 1
=> SELECT * FROM people;
id | name | employee | last_update ----+---------+----------+---------------------------- 2 | Петров | f | 3 | Сидоров | t | 2017-07-22 23:39:03.416569 (2 rows)
И обновления:
=> UPDATE people SET name = 'Петров-Водкин' WHERE id = 2;
UPDATE 1
=> SELECT * FROM people;
id | name | employee | last_update ----+---------------+----------+---------------------------- 3 | Сидоров | t | 2017-07-22 23:39:03.416569 2 | Петров-Водкин | f | 2017-07-22 23:39:03.457738 (2 rows)
Создадим представление для отображения работников:
=> CREATE VIEW employees AS SELECT id, name, last_update FROM people WHERE employee;
CREATE VIEW
Теперь имя представления можно использовать практически так же, как и таблицу:
=> SELECT * FROM employees;
id | name | last_update ----+---------+---------------------------- 3 | Сидоров | 2017-07-22 23:39:03.416569 (1 row)
В простом случае с представлением будут работать и другие операции, например:
=> UPDATE employees SET name = initcap(name);
UPDATE 1
С помощью триггеров можно сделать так, чтобы и в сложных случаях для представлений работали вставка, обновление и удаление строк.
При планировании запроса представление "разворачивается" до базовых таблиц:
=> EXPLAIN (COSTS OFF) SELECT * FROM employees;
QUERY PLAN
--------------------
Seq Scan on people
Filter: employee
(2 rows)
Конец демонстрации.