Документ взят из кэша поисковой машины. Адрес
оригинального документа
: http://www.sai.msu.su/~megera/wiki/pgsql_tutorial
Дата изменения: Unknown Дата индексирования: Sun Apr 10 18:28:21 2016 Кодировка: UTF-8 Поисковые слова: п п р р р р р р р п п п п п п п |
Предполагается знание основ реляционных баз данных и наличие навыков работы с ними. Лекция рассчитана на 4 занятия по 45 минут.
Краткое изложение основных команд и примеры их использования.
test=# create table with_oid (a integer) with oids; CREATE TABLE test=# insert into with_oid values(1); INSERT 6350719 1 test=# insert into with_oid values(2); INSERT 6350720 1 test=# select a from with_oid where oid=(select max(oid) from with_oid); a --- 2 (1 row)
test=# select count(temp."X") from temp; ERROR: column temp.X does not exist test=# select count(temp.X) from temp; count ------- 9
Одинарные кавчычки используются только для строк !
test=# select count(temp.'X') from temp; ERROR: parse error at or near "'X'" at character 19 LINE 1: select count(temp.'X') from temp; ^
test=# create table "limit" (a int); CREATE TABLE
test=# COMMENT ON TABLE temp is 'тестовая таблица'; test=# \dt+ temp List of relations Schema | Name | Type | Owner | Description --------+------+-------+--------+------------------ public | temp | table | megera | тестовая таблица test=# COMMENT ON COLUMN temp.x is 'целочисленный атрибут'; COMMENT test=# \d+ temp Table "public.temp" Column | Type | Modifiers | Description --------+---------+-----------+----------------------- x | integer | | целочисленный атрибут y | integer | |
test=# select 1||NULL, 1>NULL, 1+NULL,1~NULL; ?column? | ?column? | ?column? | ?column? ----------+----------+----------+---------- | | |
test=# set transform_null_equals to on; SET test=# select NULL=NULL; ?column? ---------- t (1 row) test=# set transform_null_equals to off; SET test=# select NULL=NULL; ?column? ---------- (1 row)
test=# select coalesce(NULL,'значение по умолчанию'); coalesce ----------------------- значение по умолчанию
WHERE co_id NOT IN (SELECT dy_company FROM diary) WHERE co_id NOT IN (1, 2, null, 3...) WHERE NOT (co_id=1 OR co_id=2 OR co_id=null OR co_id=3...) WHERE NOT (null) WHERE null
select coll from mytable order by coll IS NULL DESC, coll;
create or replace view iostat as select blk.relname as relname,blk.heap_blks_read || ':' || blk.heap_blks_hit as heap_blk, blk.idx_blks_read || ':' || blk.idx_blks_hit as idx_blk, tpl.seq_scan || ':' || tpl.seq_tup_read as seq_tup, tpl.idx_scan || ':' || tpl.idx_tup_fetch as idx_tup from pg_statio_user_tables blk JOIN pg_stat_user_tables tpl USING (relname); discovery-test=# select * from iostat where relname='message_parts'; relname | heap_blk | idx_blk | seq_tup | idx_tup ---------------+----------+---------+---------+--------- message_parts | 0:2256 | 0:6834 | 0:0 | 2:3640
Кстати, для очистки статистики, можно использовать
select pg_stat_reset();
Правила для переписывания запроса, триггер для реакции на изменение данных (per row), Правила срабатывают *до* выполнения запроса (per statement) и в результате могут измениться много записей. Правильное использование правил - для обновления views.
Наследование таблиц позволяет создавать базовый объект и дочерние, которые помимо своих атрибутов будут наследовать атрибуты базовых объектов. При этом наследуются DEFAULTS и CONSTRAINTS, включая SERIAL.
Очень заманчиво использовать наследование таблиц для эффективной работы с очень большей таблицей поделив ее на несколько частей.
psql test -c "create table a ( i int primary key);" psql test -c "create table a1(check (i >=0 and i<=2000)) inherits(a);" psql test -c "create table a2(check (i >=2001 and i<=4000)) inherits(a);" psql test -c "create table a3(check (i >=4001 and i<=6000)) inherits(a);" #create index a_idx on a(i); psql test -c "create index a1_idx on a1(i);" psql test -c "create index a2_idx on a2(i);" psql test -c "create index a3_idx on a3(i);" for ((i=0;i<2000;i++)) do echo $i; done| psql test -c "copy a1 from stdin;" for ((i=2001;i<4000;i++)) do echo $i; done| psql test -c "copy a2 from stdin;" for ((i=4001;i<6000;i++)) do echo $i; done| psql test -c "copy a3 from stdin;" 1. use inheritance, faster explain analyze select 1 from a where i <10; 2.use union explain analyze select 1 from only a where i <10 union select 1 from a1 where i <10 union select 1 from a2 where i <10 union select 1 from a3 where i <10;
BEGIN; UPDATE very_big_table set .......; SAVEPOINT update_ok; -- do some bad things ROLLBACK TO SAVEPOINT update_ok; -- destroy SAVEPOINT -- RELEASE SAVEPOINT update_ok; COMMIT;
CREATE TABLESPACE big_storage OWNER robot LOCATION '/db1/big_storage'; CREATE TABLE very_big_table (.......) TABLESPACE big_storage;
SELECT * FROM companies ORDER BY co_id LIMIT 5;
-- неправильный (обратный) порядок SELECT * FROM companies ORDER BY co_id DESC LIMIT 5; -- поправим дело SELECT * from ( select * FROM companies ORDER BY co_id DESC LIMIT 5) as foo order by co_id;)
Обычно для этого используют
DELETE from table_name;
однако, при этом сканируются все записи, что для очень больших таблиц очень накладно, поэтому рекомендуется команда TRUNCATE (not in SQL standard)
TRUNCATE very_big_table;
Есть несколько ограничений по ее использованию в contraints и триггерах, см. мануал
create type val_cid as ( value float, cid integer); create table t1 (gid integer, z val_cid); insert into t1 values(1,(23.65,4)); insert into t1 values(2,(20.8,2)); test=# select * from t1 where (z).cid=2; gid | z -----+---------- 1 | (20.8,2) (1 row)
Если определить операции сравнения, то можно и индекс построить.
Часто бывает, что нужно заменить каждый элемент массива его связкой из другой таблицы. PostgreSQL позволяет это сделать элегантно.
Пример: таблица survey(id integer, name text)
sn=# select name from survey where id = any( '{1,2,3}'); name ------ AB AM AR (3 rows)
Воспользуемся функцией array
sn=# select array( select name from survey where id = any( '{1,2,3}')) as survey; survey ------------ {AB,AM,AR} (1 row)
Последний штрих - превратим массив в строку
sn=# select array_to_string (array( select name from survey where id = any( '{1,2,3}')), ',') as survey; survey ---------- AB,AM,AR (1 row)