Ключевые слова:database, postgresql, sql, (найти похожие документы)
From: Олег Бартунов <http://www.sai.msu.su/~megera/>;
Date: Mon, 16 Dec 2007 14:31:37 +0000 (UTC)
Subject: Использование PostgreSQL
Оригинал: http://www.sai.msu.su/~megera/oddmuse/index.cgi/pgsql_tutorial
Предполагается знание основ реляционных баз данных и наличие навыков
работы с ними. Лекция рассчитана на 4 занятия по 45 минут.
Интерфейсы PostgreSQL
* Основные клиентские интерфейсы
- libpq - работа с базой данных из языка 'C'
- libpqxx,libpq++ - C++
- DBD::Pg - perl интерфейс
- psqlODBC - ODBC драйвер
- pgjdbc - JDBC драйвер
- Npgsql - .Net интерфейс
- pgtcl,pgtclng - tcl
- PyGreSQL, pygres - python
- dbd_pg - ruby
- psql - коммандная строка
- pgbash - доступ из командной оболочки
- php
* Программирование на стороне сервера - процедурные языки
- встроенные интерфейсы - sql,C
- pl/pgsql - sql
- pl/perl - perl
- pl/tcl - tcl
- pl/python - python
- plr - R
* Пример приложения на языке perl
* Особенности использования кавычек в pl/pgsql
Примеры использования
Краткое изложение основных команд и примеры их использования.
* Использование schema
- Как псевдо-БД (аналогия с файловой системой, где схема - это
директория)
- Для программистов это возможность тестирования своих
приложений, для этого надо подумать о схемах как о юниксовом
PATH, когда сво девелоперская директория ~bin ставится перед
системной /usr/bin
- Если несколько приложений используют одни и те же объекты в
одной БД, то логично выделить их в отдельную схему.
* Создание таблиц, индексов, первичные и внешние ключи, ограничения
- with/without OID Умолчание задается в postgresql.conf (
#default_with_oids = true ). OID дают возможность найти
последнюю вставленную строчку
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)
Примечание: "name" != "Name" Двойные кавычки используются для названий таблиц, колонок.
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;
^
Примечание: create table "reserved word" - используйте двойные кавычки
test=# create table "limit" (a int);
CREATE TABLE
Примечание: COMMENT ON {TABLE,FUNCTION,,,,} tblname IS '....'
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 | |
использование NULL
Anything <operation> NULL returns NULL
test=# select 1||NULL, 1>NULL, 1+NULL,1~NULL;
?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
| | |
* select (null=null) is null;
см.transform_null_equals in postgresql.conf (false)
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)
* coalesce(ARG1,VAL1,VAL2,...) - задание значений для NULL-ed
аргументов
test=# select coalesce(NULL,'значение по умолчанию');
coalesce
-----------------------
значение по умолчанию
* nullif, IS NOT NULL, IS NULL,
* count(*) vs count(some_field_which_could_ne_NULL))
* NULL in subselects (надо следить, чтобы результат subselect не
содержал NULL, пример:
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
Сделать NULL при сортировке впереди
select coll from mytable order by col IS NULL DESC, COL;
Виртуальные таблицы (представления) Пример: Показ I/O статистики (stats_* в postgresql.conf):
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
* индексы создавать нельзя, используются индексы ориг. таблиц
* Разделение прав
* постоянство запроса при изменении таблиц
* Обновление, rule system
* materialized views
Триггеры и правила.
Правила для переписывания запроса, триггер для реакции на изменение
данных (per row), Правила срабатывают *до* выполнения запроса (per statement)
и в результате могут измениться много записей.
Правильное использование правил - для обновления views.
* Наследование
Наследование таблиц позволяет создавать базовый объект и дочерние,
которые помимо своих атрибутов будут наследовать атрибуты базовых
объектов. При этом наследуются DEFAULTS и CONSTRAINTS, включая SERIAL.
* наследование+index и partitioning
Очень заманчиво использовать наследование таблиц для эффективной
работы с очень большей таблицей поделив ее на несколько частей.
psql test -c "create table a ( i int primary key);"
psql test -c "create table a1() inherits(a);"
psql test -c "create table a2() inherits(a);"
psql test -c "create table a3() 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 a from stdin;"
for ((i=2001;i<4000;i++)) do echo $i; done| psql test -c "copy a1 from stdin;"
for ((i=4001;i<6000;i++)) do echo $i; done| psql test -c "copy a2 from stdin;"
for ((i=6001;i<8000;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;
* Использование индексов:
+ многоатрибутные индексы
+ функциональные индексы
+ частичные индексы
* Использование LIMIT
+ first N results
SELECT * FROM companies ORDER BY co_id LIMIT 5;
* last N results
-- неправильный (обратный) порядок
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 и
триггерах, см. мануал
* Использование команды explain для оптимизации запросов
* Работа с иерархическими данными (ltree)
* Организация полнотекстового поиска (tsearch2)
* Работа с массивами (intarray)
* Поиск с ошибками (pg_trgm)
