Анализ структуры таблицы

Команда \d (с указанием имени таблицы) предназначена для вывода структуры таблицы и ее ограничений, если они имеются. В листинге 4.7 приведены выходные данные команды \d для таблицы books, созданной в предыдущем разделе.

Обратите внимание: каждая строка результата описывает одно из полей (то есть столбцов) таблицы; таким образом, таблица фактически разворачивается па 90°. Такое представление выбрано для наглядности, поскольку многие таблицы содержат большое количество полей, не помещающихся на экране (или на печатной странице) по горизонтали. В книге этот формат будет использоваться при описании структуры таблиц.

COPY TO

Синтаксис команды COPY FROM практически аналогичен синтаксису команды, предназначенной для экспортирования данных в файл, просто ключевое слово FROM заменяется ключевым словом ТО. Кроме того, ключевое слово stdin заменяется ключевым словом stdout, если выходные данные вместо файла направляются в стандартный вывод (например, на экран в psql). В листинге 4.22 приведен пример экспортирования таблицы books в ASCII-файл.

Добавление данных командами INSERT и COPY

После создания таблицы с заданной структурой наступает следующий этап — заполнение таблицы данными. В PostgreSQL имеются три общих способа заполнения таблиц данными:

вставка новых группированных данных командой INSERT INTO; вставка существующих данных из другой таблицы командой INSERT INTO в сочетании с командой SELECT; вставка данных из внешнего файла командой COPY (или \copy).

Добавление ограничений

После создания таблицы сохраняются некоторые возможности добавления ограничении. В PostgreSQL 7.1.x команда ALTER TABLE с секцией ADD CONSTRAINT позволяет определять для полей существующих таблиц только ограничения внешнего ключа и проверки. Команда создания новых ограничений имеет следующий синтаксис:

ALTER TABLE таблице

ADD CONSTRAINT имя_ограничения определение

Синтаксис определения зависит от типа ограничения. В листинге 4.12 продемонстрирован синтаксис создания ограничения внешнего ключа для таблицы editions (связанной с полем id таблицы books) и ограничения проверки для поля type.

Другие возможности SQL

В этой главе были представлены фундаментальные концепции применения языка SQL в PostgreSQL. В частности, были рассмотрены вопросы создания таблиц и выполнения с ними различных операций, включая выборку и модификацию данных. В главе 5 будут подробно описаны функции и операторы PostgreSQL, многие из которых уже встречались в этой главе.

Двоичный формат

Команда COPY также позволяет выполнять операции ввода и вывода с данными в двоичном формате. Если команда COPY FROM содержит ключевое слово BINARY, входной файл должен быть создан командой COPY TO в двоичном формате PostgreSQL. Двоичные файлы загружаются быстрее ASCII-файлов, но в отличие от последних их нельзя читать и редактировать в простых текстовых редакторах.

В листинге 4.21 приведена команда COPY, предназначенная для вставки записей из двоичного файла в таблицу subjects базы данных booktown.

Группировка записей

Секция GROUP BY представляет чрезвычайно мощную концепцию SQL — агрегирование. На практике агрегирование запросов SQL приводит к тому, что все записи с одинаковыми значениями выражения, заданного в секции GROUP BY, группируются в одну агрегатную запись. Выражение GROUP BY может быть простым полем таблицы, но оно также может представлять собой произвольную операцию с полем. При перечислении нескольких полей или выражений, разделенных запятыми, группировка записей осуществляется лишь при совпадении записей по всем критериям.

Чтобы эффективно пользоваться агрегированием, необходимо понимать, что все целевые поля, участвующие в агрегирующем запросе, но не указанные в секции GROUP BY, доступны лишь при выборке через агрегатную функцию. Агрегатная функция получает имя поля (или выражение, в котором участвует хотя бы одно имя поля), представляющее несколько значений (например, в нескольких сгруппированных записях), выполняет с ними некоторую операцию и возвращает одно значение.

Самые распространенные агрегатные функции:

count () — возвращает количество записей в наборе; тах () — возвращает максимальное значение в наборе; min () — возвращает минимальное значение в наборе.

Агрегатные функции работают только с записями итогового набора, поэтому они выполняются после обработки всех условных объединений и секций WHERE.

Предположим, вы хотите вывести количество книг, хранящихся в базе данных booktown для каждого издательства. Название издательства связывается с названиями опубликованных книг простым объединением таблиц editions и publishers, однако подсчитывать записи вручную весьма утомительно, а при очень больших объемах итоговых наборов — вообще неприемлемо.

В листинге 4.40 выполняется стандартное объединение двух таблиц базы данных booktown, но в нем присутствуют два новых элемента: вызов функции count () и секция GROUP BY.

Использование таблиц

Таблицы являются основными блоками хранения данных в базе. Перед любыми операциями создания, выборки или модификации записей необходимо сначала создать таблицу, в которой эти записи будут храниться.

В этом разделе рассматриваются процедуры создания, модификации и удаления таблиц командами CREATE TABLE, ALTER TABLE n DROP TABLE SQL (вопросам создания баз данных посвящена глава 9).

Конструкции CASE

Чтобы программа SQL могла принимать простейшие решения, не прибегая к процедурным языкам, в PostgreSQL поддерживаются конструкции CASE, предусмотренные стандартом SQL Ключевые слова SQL CASE, WHEN, THEN и END позволяют выполнять простые условные преобразования записей.

Вся конструкция CASE включается в целевой список команды SELECT. По умолчанию итоговому полю конструкции CASE присваивается имя case, но ему можно назначить синоним, как любому обычному полю. Общий синтаксис конструкции CASE в списке целей команды SELECT выглядит следующим образом:

CASE WHEN условие! THEN результат! WHEN условие2 THEN результат2

[ ... ]

[ ELSE результат_по_умопчанию END [ AS синоним ]

Конструкция CASE-WHEN-THEN-ELSE отчасти напоминает условные команды f-then-else в традиционных языках программирования (листинг 4.50). Условия секций WHEN должны возвращать логический результат.

Если условие в секции WHEN выполняется, результат соответствующей секции THEN возвращается в поле итогового набора. Если ни одно условие не выполнено, можно задать значение по умолчанию в секции ELSE. Если при отсутствии секции ELSE результат остается неопределенным, возвращается NULL.

Копирование данных из внешних файлов командой COPY

В PostgreSQL поддерживается и такая полезная возможность, как прямое импортирование данных в таблицу из внешних файлов командой COPY. Файл, содержащий входные данные команды COPY, хранится либо в стандартном текстовом формате ASCII с ограничением полей специальным символом-разделителем, либо в двоичном формате таблиц PostgreSQL. В ASCII-файлах в качестве разделителя обычно используется символ табуляции или запятая. При импортировании данных из ASCII-файла каждая строка файла интерпретируется как отдельная запись данных, а каждый компонент строки — как значение соответствующего поля записи.

Команда COPY FROM работает значительно быстрее обычной команды INSERT, поскольку данные передаются прямо в приемную таблицу за одну транзакцию. С другой стороны, к формату исходного файла предъявляются чрезвычайно жесткие требования, поэтому ошибка всего в одной строке приводит к сбою всей команды COPY.

Синтаксис команды COPY FROM:

COPY [ BINARY ] таблица [ WITH 0IDS ]
FROM { 'имя_файла' \ stdin }
[ [USING] DELIMITERS 'разделитель' ]
[ WITH NULL AS 'строка_nulГ ]

Ниже поясняется смысл параметров команды.

BINARY. Признак импортирования входных данных из двоичного файла, ранее созданного командой COPY TO. таблица. Имя таблицы, в которую импортируются данные. WITH OIDS. Из первой строки файла загружаются значения всех идентификаторов OID импортируемой таблицы. FROM { 'имя_файла' \ stdin }. Источник, из которого PostgreSQL получает входные данные — файл с заданным именем либо стандартный ввод (stdin). [ USING ] DELIMITERS 'разделитель'. Символ, используемый в качестве разделителя при разборе входных данных. Не используется для файлов, выведенных в двоичном формате PostgreSQL. WITH NULL AS ' строка_null. Заданная строка должна интерпретироваться как значение NULL. He используется для файлов, выведенных в двоичном формате PostgreSQL.

При подготовке к импортированию файла проследите за тем, чтобы этот файл был доступен для чтения процессом postmaster (то есть пользователем, запустившим PostgreSQL). Кроме того, разрешены только абсолютные имена файлов; при попытке передать относительное имя происходит ошибка.

При работе с входными файлами в формате ASCII в секции DELIMITERS передается символ, используемый в качестве разделителя значений полей в строках файла. Если разделитель не указан, PostgreSQL считает, что значения разделяются символом табуляции. Необязательная секция WITH NULL определяет формат, в котором передаются значения NULL. Если секция отсутствует, PostgreSQL интерпретирует последовательность \N как NULL (например, пустые поля исходного файла по умолчанию интерпретируются как пустые строковые константы, а не как NULL).

Если данные вводятся вручную или передаются терминалу другой программой, в качестве источника в секции FROM можно указать стандартный ввод (stdin). При получении данных из стандартного ввода входной поток должен завершаться последовательностью \. (обратная косая черта плюс точка), за которой немедленно следует символ новой строки.

В листинге 4.19 приведено содержимое файла, выведенного PostgreSQL в формате ASCII. Поля разделяются запятыми, а для представления NULL используется строка \null.B файле сохранены данные из таблицы subjects базы данных booktown.

Назначение системного пути для psql

Листинг 4.1. Назначение системного пути для psql

[user@host user]$ psql

bash: psql: command not found

[user@host user]$ echo $PATH

/b1n:/usr/bin:/usr/local/bin:/usr/bin/Xll:/usr/XHR6/tnn

[user@host user]$ export PATH=$PATH:/usr/local/pgsql/bin

[user@host user]$ psql testdb

Welcome to psql. the PostgreSQL interactive terminal.

Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit

testdb=#

После настройки переменной PATH интерактивный терминал PostgreSQL запускается командой psql, за которой следует имя базы данных.

ВНИМАНИЕ

После выхода из psql изменения переменной среды будут потеряны. Чтобы изменения переменной PATH сохранялись после выхода из программы, команда настройки PATH включается в стартовый сценарий командного интерпретатора (например, ~/.bash_profile).

относящийся к интерпретатору

Листинг 4.1, относящийся к интерпретатору bash, иллюстрирует процедуру назначения системного пути для клиента psql.

Получение списка команд psql booktown=# \?

Листинг 4.2. Получение списка команд psql booktown=# \?

\а toggle between unaligned and aligned mode

\c[onnect] [dbname|- [user]]

connect to new database (currently 'booktown')

\C <title>
table title

\copy... perform SQL COPY with data stream to the client machine

\copyright show PostgreSQL usage and distribution terms

\d <table>
describe table (or view, index, sequence)

\d{t|i|s|v} list tables/indices/sequences/views

\d{p|S|l} list permissions/system tables/lobjects

\da list aggregates

\dd [object] list comment for table, type, function, or operator

\df list functions

\do list operators

\dT list data types

\e [file] edit the current query buffer or [file] with external editor

\echo <text>
write text to stdout

\encoding <encoding>
set client encoding

\f <sep>
change field separator

\g [file] send query to backend (and results in [file] or (pipe)

\h [cmd] help on syntax of sql commands. * for all commands

\H toggle HTML mode (currently off)

\i<file>
read and execute queries from <file>

\l list all databases

\lo_export. \lo_import, \lo_list. \lo_unlink

Targe object operations

\o [file] send all query results to [file], or (pipe

\p show the content of the current query buffer

\pset <opt>
set table output <opt>
= {format|border|expanded|fieldsep|

\q quit psql

\qecho <text>
write text to query output stream (see \o) .

\r reset (clear) the query buffer

\s [file] print history or save it in [file] \set <var>
<value>
set internal variable

\t show only rows (currently off)

\T <tags>
HTML table tags

\unset <var>
unset (delete) internal variable

\w <file>
write current query buffer to a <file>

\x toggle expanded output (currently off)

\z list table access permissions

\! [cmd] shell escape or command

Ввод команд в psql

Листинг 4.3. Ввод команд в psql

testdb=# SELECT * FROM employees

testdb-# WHERE firstname = 'Michael';

Запрос из листинга 4.3 возвращает записи обо всех работниках с именем «Michael» из таблицы empl oyees. Деление по строкам использовано лишь для удобства чтения. Запрос передается для обработки только после ввода завершающего символа «точка с запятой». Если в предыдущей строке присутствует символ, требующий парного завершителя (например, круглой скобки или кавычки), этот символ включается в приглашение следующей строки. Например, если начать команду CREATE TABLE с открывающей круглой скобкой и перейти на другую строку, то приглашение будет выглядеть так, как показано в листинге 4.4.

Включение открывающих

Листинг 4.4. Включение открывающих символов в приглашение psql

testdb=# CREATE TABLE employees (

testdb(#

Ввод команды продолжается. Открывающая круглая скобка в приглашении psql напоминает о том, что в команду необходимо включить закрывающую круглую скобку.

Присваивание значения переменной EDITOR

Листинг 4.5. Присваивание значения переменной EDITOR

# set EDITOR='joe'
$ export EDITOR

Команда \е также может применяться для сохранения текущего содержимого буфера в файле. При вводе команды \е клиент psql запускает редактор и загружает содержимое буфера запроса, словно оно было прочитано из файла. Выполните все необходимые операции, сохраните результат в файле командой Save и вернитесь в psql. Команда \е создает временный файл с расширением .tmp; чтобы сохранить запрос в обычном файле, воспользуйтесь командой Save As и введите имя файла.

Создание таблицы books

Листинг 4.6. Создание таблицы books

booktown=# CREATE TABLE books (

booktown(# id integer UNIQUE.

booktown(# title text NOT NULL,

booktown(# authoMd integer,

booktowntf subjected integer,

booktown(# CONSTRAINT books_id_pkey PRIMARY KEY (id));

NOTICE: CREATE TABLE/PRIMARY KEY

will create implicit index 'books_id_pkey' for table

'books'

CREATE

После выполнения команды CREATE выводится сообщение с информацией об успешном создании таблицы. Если вы получите сообщение об ошибке, проверьте правильность расстановки знаков препинания и написания всех ключевых слов. Если сообщения вообще нет, вероятно, в команде осталась незакрытая круглая скобка, апостроф или другой парный символ.

Кроме того, в сообщении NOTICE говорится о том, что при создании описанной таблицы был построен скрытый индекс books_id_pkey.

Выходные данные команды \d

Листинг 4.7. Выходные данные команды \d

booktown=# \d books

Table "books" Attribute | Type | Modifier

id integer | not null

title text I not null

authorjd j integer j subjectjd | integer |

Index: books_id_pkey

Ниже приведены более подробные описания полей и атрибутов, встречающихся в листинге 4.7.

Id. В поле Id хранится числовой код, уникальный для каждой книги. Поле id определяется с типом Integer и для него устанавливаются следующие ограничения: UNIQUE. Ограничение гарантирует уникальность значений поля. В общем случае поле с ограничением уникальности может содержать NULL, но попытки вставки дубликатов завершаются неудачей. Поле Id также используется в качестве первичного ключа. PRIMARY KEY. Хотя в выходных данных команды \d об этом не упоминается, из исходной команды CREATE TABLE видно, что поле id также назначено первичным ключом таблицы. Установка ограничения первичного ключа для поля также неявно подразумевает установку ограничений NOT NULL и UNIQUE. NOT NULL. Автоматически устанавливается при назначении ограничения PRIMARY KEY. Ограничение гарантирует, что поле id всегда содержит значение, отличное от NULL. Поле никогда не остается пустым, и любые попытки вставки псевдозначения NULL завершаются неудачей. title. Поле title содержит символьные данные типа text. Тип text обладает большей гибкостью по сравнению с varchar и хорошо подходит для данного поля, поскольку не требует задания максимального количества символов. Для поля titl e установлено ограничение NOT NULL; это означает, что поле всегда содержит значение, отличное от NULL. authorjd. Поле author_id содержит значения типа integer и используется для связи с таблицей authors. Ограничения для этого поля не устанавливаются, поскольку в таблице могут встречаться книги, написанные неизвестным автором, что исключает ограничение NOT NULL. С другой стороны, один автор может написать несколько книг, поэтому ограничение UNIQUE также не подходит. subject_id. Поле subject_id аналогично полю author_id— оно тоже содержит значения типа integer и используется для установки связи с таблицей subjects. Ограничения для этого поля также отсутствуют, поскольку некоторые книги не принадлежат ни к одной категории, а категории, как правило, содержат более одной книги.

Структура таблицы может изменяться после ее создания, но возможности мо-(ификаций ограничены. Например, к их числу относится переименование табли-(ы, переименование существующих и добавление новых полей. PostgreSQL 7.1.x ie позволяет удалять поля из таблиц, поэтому структуру таблицы следует тща-•ельно продумать перед ее созданием.

Добавление поля

Листинг 4.8. Добавление поля

booktown=# ALTER TABLE books

booktown-# ADD publication date;

ALTER

booktown=# \d books

Table "books"

Attribute | Type | Modifier

id integer | not null

title text I not null

authorjd j integer |

subjectjd integer |

publication date j Index: books_id_pkey

показывает что в таблице

Листинг 4.8 показывает, что в таблице books появилось новое поле с именем pub! I cation и типом date. Кроме того, он дает типичный пример плохой координации планирования между разработчиками: в базе данных booktown из нашего примера дата публикации уже хранится в таблице editions, поэтому включать его в таблицу books не нужно. Изменение структуры таблиц после подобных ошибок рассматривается ниже в подразделе «Реструктуризация таблиц».

Изменение значений по умолчанию

Листинг 4.9. Изменение значений по умолчанию

Doktown=# ALTER TABLE books

3oktOwn-# ALTER COLUMN id

x>
ktown-# SET DEFAULT nextvalС books.ids'):

JER

}oktown=# \d books

TABLE "books" Attribute | Type | Modifier

id integer not null default nextval('books.ids'::text)

1tle | text not null

juthorjd | integer ;ubject_id | integer j idex: books_id_pkey

)oktown=# ALTER TABLE books )oktown-# ALTER id

)oktown-# DROP DEFAULT;

JER

joktown=# \d books

TABLE "books" Attribute | Type | Modifier

id | integer | not null

;itle | text not null

iuthor_id j integer ;ubject_id j integer idex: books_id_pkey

Переименование таблицы

Листинг 4.10. Переименование таблицы

oktown=# ALTER TABLE books RENAME TO literature;

TER

oktown=# ALTER TABLE literature RENAME TO books;

TER

Переименование поля

Листинг 4.11. Переименование поля

booktown=# \d daily_inventory

Table "daily_inventory"

Attribute | Type | Modifier

isbn | text

in_stock | boolean |

booktown=# ALTER TABLE daily_inventory

booktown-# RENAME COLUMN in_stock TO is_in_stock;

ALTER

booktown=# ALTER TABLE daily_inventory

booktown-l RENAME COLUMN is_in_stock TO is_stocked;

ALTER

Создание новых ограничений

Листинг 4.12. Создание новых ограничений в существующей таблице

booktown=# ALTER TABLE editions

booktown-# ADD CONSTRAINT foreign_book

booktown-# FOREIGN KEY (book_id) REFERENCES books (id);

NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)

for FOREIGN KEY check(s)

CREATE

booktown=# ALTER TABLE editions

booktown-# ADD CONSTRAINT hard_or_paper_back

booktown-# CHECK (type = 'p' OR type = 'h'):

ALTER

Установка ограничения внешнего ключа приводит к тому, что любое значение book_i d в таблице edi ti ons также должно существовать в таблице books. Кроме того, вследствие установленного ограничения проверки поле type в таблице editions может содержать только значения р или Ь.

Примечание 2
Примечание 2

Ограничение уникальности также неявно устанавливается при создании уникального индекса командой CREATE INDEX (см. раздел «Индексы» в главе 7).

Дополнительная информация об ограничениях, их назначении и синтаксисе приведена в главе 7.

Смена владельца таблицы

Листинг 4.13. Смена владельца таблицы

booktown=# ALTER TABLE employees booktown-# OWNER TO corwin;

ALTER

Примечание 3
Примечание 3

Смена владельца таблицы может осуществляться либо текущим владельцем, либо суперпользователем PostgreSQL.

Реструктуризация

Листинг 4.14. Реструктуризация таблицы командой CREATE TABLE AS

booktown=# \d books

Table "books" Attribute | Type | Modifier

id | integer | not null

title text I not null

authorjd | integer |

subjectjd integer j

publication j date | Index: books_id_pkey

booktown=# CREATE TABLE new_books

booktown-# (id, title. authorjd, subjectjd)

booktown-# AS SELECT id, title, authorjd, subjectjd

booktown-f FROM books;

SELECT

booktown=# ALTER TABLE books RENAME TO old_books;

ALTER

booktown=# ALTER TABLE new_books RENAME TO books;

ALTER

booktown=# \d books

Table "books" Attribute | Type | Modifier

id | integer |

title | text |

authorjd j integer j

subjectjd | integer |

booktown=# DROP TABLE books;

DROP

ВНИМАНИЕ

В PostgreSQL 7.1.x присутствие в команде SELECT необязательного списка полей, заключенного в круглые скобки, исключает использование специального символа *. Недостаток должен быть исправлен в PostgreSQL 7.2.

Реструктуризация

Листинг 4.15. Реструктуризация таблицы командами CREATE TABLE и INSERT INTO

booktownHf CREATE TABLE new_books (

booktown(# id integer UNIQUE,

booktown(# title text NOT NULL.

booktown(# authorjd integer.

booktown(# subjectjd integer.

booktown(# CONSTRAINT books_id_pkey PRIMARY КЕУ (id)

booktown(# ):

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index ' books Jd_pkey'

for table 'new_books'

CREATE

booktown=# INSERT INTO new_books

booktown-# SELECT id, title, author_id, subjectjd

booktown-f FROM books;

INSERT 0 12

booktown=# ALTER TABLE books RENAME TO old_books;

ALTER

booktown=# ALTER TABLE new_books RENAME TO books:

ALTER

booktown=# \d books

Table "books" Attribute | Type | Modifier

id | integer | not null

title ( text I not null

authorjd integer j

subjectjd integer | Index: booksjd_pkey

За дополнительной информацией об использовании команды INSERT INTO с командой SELECT обращайтесь к подразделу «Вставка данных из других таблиц командой SELECT» раздела «Добавление данных командами INSERT и COPY». Команда SELECT рассматривается в разделе «Выборка данных командой SELECT».

Вставка новой записи в таблицу books

Листинг 4.16. Вставка новой записи в таблицу books

booktown=# INSERT INTO books (Id, title, author_id, subject_id)

booktown-# VALUES (41472, 'Practical PostgreSQL', 1212, 4);

INSERT 3574037 1

Команда SQL, приведенная в листинге 4.16, вставляет новую запись с кодом (id) 41472, названием «Practical PostgreSQL», кодом автора 1212 и кодом темы 4. Обратите внимание на завершающее сообщение, начинающееся со слова INSERT, — оно указывает на то, что операция вставки была выполнена успешно. Первое число после INSERT является идентификатором объекта (OID) созданной записи, а второе число обозначает количество созданных записей (в нашем примере,— 1).

В приведенном примере необязательный список полей совпадает с порядком следования полей в структуре таблицы (слева направо). В данном случае этот список можно опустить, поскольку команда INSERT предполагает, что значения присваиваются в естественном порядке следования полей таблицы. Поля в списке можно переставить, но в этом случае порядок значений в секции VALUES тоже должен измениться, как показано в листинге 4.17.

Изменение порядка перечисления полей

Листинг 4.17. Изменение порядка перечисления полей

booktown=# INSERT INTO books (subjected, author_id, id, title)

booktown-# VALUES (4, 7805, 41473, 'Programming Python');

INSERT 3574041 1

Вставка данных из другой таблицы

Листинг 4.18. Вставка данных из другой таблицы

booktown-# INSERT INTO books (id, title, author_id, subject_id)

booktown-# SELECT nextval('book_ids', title, author_id, subject_id

booktown-# FROM book_queue WHERE approved;

INSERT 0 2

В приведенном примере запрос SELECT, включенный в команду INSERT INTO, переносит две записи из таблицы book_queue в таблицу books. В этом контексте допускается использование любой синтаксически правильной команды SELECT. В нашем примере в выборку включается результат вызова функции nextval () для последовательности bookj ds, за которым следуют значения полей title, author_id и subject_id из таблицы book_queue.

На этот раз команда создает сразу несколько новых записей, поэтому в сообщении об успешном выполнении операции вместо значения OID, которое выводилось бы при вставке одной записи, выводится 0. Второе число, как и в случае с обычной командой INSERT INTO, равно количеству созданных записей (в данном случае — 2).

Пример копируемого ASCIIфайла

Листинг 4.19. Пример копируемого ASCII-файла

1.Business.Productivity Ave

2.Children's Books,Kids Ct

3.Classics.Academic Rd

4,Computers,Productivity Ave

5,Cooking.Creativity St

12.Religion.\null

8.Hi story.Academic Rd

9.Horror.Black Raven Dr

10.Mystery.Black Raven Dr

11.Poetry.Sunset Dr

13.Romance.Main St

14.Science.Productivity Ave

15.Science Fiction.Main St

0.Arts.Creativity St

6.Drama.Main St

7.Entertainment.Main St

Следующая команда (листинг 4.20) импортирует содержимое файла /tmp/ subjects.sql в таблицу subjects базы данных booktown.

Копирование ASCIIфайла

Листинг 4.20. Копирование ASCII-файла

booktown=# COPY subjects FROM '/tmp/subjects.sql'

booktown-# USING DELIMITERS '.' WITH NULL AS '\null;

COPY

Копирование двоичного файла

Листинг 4.21. Копирование двоичного файла

booktown=# COPY BINARY subjects FROM '/tmp/subjects.sql';

COPY

Экспортирование таблицы books в файл ASCII

Листинг 4.22. Экспортирование таблицы books в файл ASCII

booktown=# COPY books TO 'filename';

COPY

Выборка всех записей из таблицы books

Листинг 4.23. Выборка всех записей из таблицы books

300ktown=# SELECT * FROM books;

id | title | author_id | subject_id

7808 | The Shining | 4156 | 9

4513 | Dune | 1866 | 15

4267 | 2001: A Space Odyssey | 2001 | 15

1608 | The Cat in the Hat | 1809 | 2

1590 | Bartholomew and the Oobleck | 1809 | 2

25908 | Franklin in the Dark | 15990 | 2

1501 | Goodnight Moon | 2031 | 2

190 | Little Women | 16 | 6

1234 | The Velveteen Rabbit | 25041 | 3

2038 | Dynamic Anatomy | 1644 | 0

156 | The Tell-Tale Heart | 115 | 9

41472 | Practical PostgreSQL | 1212 | 4

41473 | Programming Python | 7805 | 4

41477 | Learning Python 7805 | 4

41478 | Perl Cookbook | 7806 | 4

(15 rows)

Изменение порядка следования полей при выборке

Листинг 4.24. Изменение порядка следования полей при выборке

booktown=# SELECT Id, author_id, title, id

booktown-# FROM books;

id | authorjd | title | id

7808 | 4156 | The Shining | 7808

4513 | 1866 | Dune | 4513

4267 | 2001 | 2001: A Space Odyssey | 4267

1608 | 1809 | The Cat in the Hat | 1608

1590 | 1809 | Bartholomew and the Oobleck | 1590

25908 | 15990 | Franklin in the Dark | 25908

1501 | 2031 | Goodnight Moon | 1501

190 | 16 | Little Women | 190

1234 | 25041 | The Velveteen Rabbit | 1234

2038 | 1644 | Dynamic Anatomy | 2038

156 | 115 | The Tell-Tale Heart | 156

41472 | 1212 | Practical PostgreSQL | 41472

41473 | 7805 | Programming Python | 41473

41477 | 7805 | Learning Python | 41477

41478 | 7806 | Perl Cookbook | 41478

(15 rows)

Как нетрудно убедиться, команды SELECT в листингах 4.23 и 4.24 возвращают одинаковые наборы данных. Во втором наборе используется другой порядок следования полей (поле subject_id отсутствует, а поле id встречается дважды), что связано с явным перечислением полей после ключевого слова SELECT.

Выражения и константы

Листинг 4.25. Выражения и константы

testdb=# SELECT 2+2,

testdb-# pi (),

testdb-# 'PostgreSQL is more than a calculator!';

?column?| pi | ?column?

4 | 3.14159265358979 | PostgreSQL

is more than a calculator!

(1 row)

Для каждой цели в списке может задаваться необязательная секция AS, которая назначает синоним (новое произвольное имя) для каждого поля в итоговом наборе. Имена синонимов подчиняются тем же правилам, что и имена обычных идентификаторов (в частности, они могут содержать внутренние пробелы или совпадать с ключевыми словами при условии заключения их в апострофы и т. д.)

Назначение синонима не влияет на исходное поле и действует лишь в контексте итогового набора, возвращаемого запросом. Секция AS особенно удобна при «выборке» выражений и констант, поскольку синонимы позволяют уточнить смысл неочевидных выражений или констант. В листинге 4.26 приведены те же результаты, что и в листинге 4.25, но с другими названиями полей.

Секция AS с выражениями и константами

Листинг 4.26. Секция AS с выражениями и константами

booktown=# SELECT 2 + 2 AS "2 plus 2",

booktown-# pi() AS "the pi fnction",

booktown-# 'PostgreSQL is more than a calculator!' AS comments;

2 plus 2 | the pi function | comments

4 | 3.14159265358979 | PostgreSQL is more than a calculator!

(1 row)

Выборка из нескольких таблиц

Листинг 4.27. Выборка из нескольких таблиц

booktown=# SELECT books.id, title, authors_id, last_name

booktown-# FROM books, authors

booktown-# WHERE books.authored = authors.id;

id | title | id | last_name

190 | Little Women | 16 | Alcott

156 | The Tell-Tale Heart | 115 | Рое

41472 | Practical PostgreSQL | 1212 | Worsley

2038 | Dynamic Anatomy | 1644 | Hogarth

1608 | The Cat in the Hat | 1809 | Geisel

1590 |Bartholomew and the Oobleck | 1809 | Geisel

4513 | Dune | 1866 | Herbert

4267 | 2001: A Space Odyssey | 2001 | Clarke

1501 | Goodnight Moon | 2031 | Brown

7808 | The Shining | 4156 | King

41473 |Programming Python | 7805 | Lutz

41477 |Learning Python | 7805 | Lutz

41478 | Perl Cookbook | 7806 | Christiansen

25908 | Franklin in the Dark | 15990 | Bourgeois

1234 | The Velveteen Rabbit | 25041 | Bianco

(15 rows)

При использовании ссылок на имена полей, относящихся к разным источникам, может возникнуть неоднозначность. Предположим, команда SELECT получает исходные данные из таблиц books и authors. В каждой из этих таблиц имеется поле с именем id. Без дополнительных уточнений PostgreSQL не сможет определить, к какой таблице относится ссылка на поле i d в следующей команде:

booktown=# SELECT id FROM books, authors

ERROR: Column reference "id" is ambiguous

Для предотвращения неоднозначности в «полные» имена столбцов включается имя таблицы. При этом используется специальный синтаксис, называемый точечной записью (название связано с тем, что имя таблицы отделяется от имени поля точкой). Например, books .id означает поле id таблицы books.

Точечная запись обязательна только при наличии неоднозначности между наборами данных. Как показано в листинге 4.27, ссылка может состоять только из имени поля — при условии, что это имя уникально во всех наборах данных, перечисленных в секции FROM. В приведенном примере поле title присутствует только в таблице books, а поле last_name входит только в таблицу authors, поэтому на их имена можно ссылаться без уточнения.

Если в качестве источника используется итоговый набор, созданный подзапросом, то весь подзапрос заключается в круглые скобки. По наличию круглых скобок PostgreSQL узнает о том, что команда SELECT интерпретируется как подзапрос, и выполняет ее перед выполнением внешней команды SELECT.

В листинге 4.28 приведен несколько необычный запрос, который производит выборку значений всех полей (*) таблицы books с использованием подзапроса. Затем из полученного набора «выбирается» строковая константа test и значение поля id.

Выборка из подзапроса

Листинг 4.28. Выборка из подзапроса

booktown=# SELECT 'test' AS test, id

booktown-# FROM (SELECT * FROM books)

booktown-# AS example_sub_query;

test | id

test | 7808

test | 4513

test | 4267

test | 1608

test | 1590

test | 25908

test | 1501

test | 190

test | 1234

test | 2038

test | 156

test | 41472

test | 41473

test | 41477

test | 41478

(15 rows)

Странность запроса в листинге 4.28 заключается в том, что общий результат принципиально не отличается от прямой выборки из таблицы books, поскольку результат подзапроса идентичен ее содержимому. Строковая константа из одной команды SELECT комбинируется с данными из набора, полученного другой командой SELECT. Более реалистичный пример использования подзапросов приведен ниже в разделе «Подзапросы», но сначала необходимо лучше разобраться в том, как работает команда SELECT.

Примечание 1
Примечание 1

Необязательное ключевое слово ONLY перед именем таблицы, которая является базовой по отношению к другим таблицам, означает, что выборка из производных таблиц не выполняется (наследование рассматривается в главе 7).

Определение синонимов для источников данных

Листинг 4.29. Определение синонимов для источников данных

booktown=# SELECT b.id, title, a.id, last_name

booktown-# FROM books AS b, authors AS a

booktown-# WHERE b.author_id = a.id;

id | title | id lastjname

190 | Little Women | 16 | Alcott

156 | The Tell-Tale Heart | 115 | Рое

41472 | Practical PostgreSQL | 1212 | Worsley

2038 | Dynamic Anatomy | 1644 | Hogarth

1608 | The Cat in the Hat | 1809 | Geisel

1590 | Bartholomew and the Oobleck | 1809 | Geisel

4513 | Dune | 1866 | Herbert

4267 | 2001: A Space Odyssey | 2001 | Clarke

1501 | Goodnight Moon | 2031 | Brown

7808 | The Shining | 4156 | King

41473 | Programming Python | 7805 | Lutz

41477 | Learning Python | 7805 | Lutz

41478 | Perl Cookbook | 7806 | Christiansen

25908 | Franklin in the Dark 15990 | Bourgeois

1234 | The Velveteen Rabbit | 25041 | Bianco

(15 rows)

Синонимы можно назначать не только для источников данных в секции FROM, но и для отдельных полей внутри этих источников. Для этого за синонимом источника данных приводится список синонимов полей, разделенный запятыми и заключенный в круглые скобки. Список синонимов полей представляет собой последовательность идентификаторов, перечисленных в порядке следования полей в структуре таблицы (слева направо).

Список синонимов полей не обязан содержать данные обо всех полях. К тем полям, для которых не были заданы синонимы, можно обращаться по обычным именам. Если единственное поле, которому требуется назначить синоним, находится после других полей, вам придется включить в список все предшествующие поля (синоним поля может совпадать с его именем). В противном случае PostgreSQL не сможет определить, какому полю назначается синоним, и решит, что речь идет о первом поле таблицы.

Примечание 2
Примечание 2

Ключевое слово AS не является обязательным. Если оно отсутствует в команде, PostgreSQL считает, что все идентификаторы после ключевого слова FROM являются синонимами.

В листинге 4.30 приведен запрос из листинга 4.29, в котором полям id обеих таблиц назначаются уникальные идентификаторы, что позволяет ссылаться на них непосредственно (то есть без применения точечной записи). Синтаксис остается прежним, но на этот раз синоним назначается только полю id таблицы books, в результате ссылка на поле id таблицы authors становится однозначной.

Определение синонимов для полей

Листинг 4.30. Определение синонимов для полей

booktown=# SELECT the_book_id, title, id, last_name

booktown-# FROM books AS b (the_book_id), authors

booktown-# WHERE author_id = id;

id | title id | last_name

190 | Little Women | 16 | Alcott

156 | The Tell-Tale Heart | 115 Рое

41472 | Practical PostgreSQL | 1212 | Worsley

2038 | Dynamic Anatomy | 1644 | Hogarth

1608 | The Cat in the Hat | 1809 | Geisel

1590 | Bartholomew and the Oobleck | 1809 | Geisel

4513 | Dune | 1866 | Herbert

4267 | 2001: A Space Odyssey | 2001 | Clarke

1501 | Goodnight Moon | 2031 | Brown

7808 | The Shining | 4156 | King

41473 | Programming Python | 7805 | Lutz

41477 | Learning Python | 7805 | Lutz

41478 | Perl Cookbook | 7806 | Christiansen

25908 | Franklin in the Dark | 15990 | Bourgeois

1234 | The Velveteen Rabbit | 25041 | Bianco

(15 rows)

Ключевое слово DISTINCT

Листинг 4.31. Ключевое слово DISTINCT

booktown=# SELECT DISTINCT author_id

booktown-# FROM books;

author_id

115

1212

1644

1809

1866

2001

2031

4156

7805

7806

15990

25041

(13 rows)

booktown=# SELECT DISTINCT ON (author_id)

booktown-# author_id. title

booktown-# FROM books;

author_id | ntitle

16 | Little Women

115 | The Tell-Tale Heart

1212 | Practical PostgreSQL

1644 | Dynamic Anatomy

1809 | The Cat in the Hat

1866 | Dune

2001 | 2001: A Space Odyssey

2031 | Goodnight Moon

4156 | The Shining

7805 | Programming Python

7806 | Perl Cookbook

15990 |Franklin in the Dark

25041 | The Velveteen Rabbit

(13 rows)

Первый запрос в листинге 4.31 возвращает только 13 записей из таблицы books, хотя таблица содержит 15 записей. Два автора, написавшие по две книги, вошли в итоговый набор лишь в одном экземпляре.

Во втором запросе использована другая форма DISTINCT с явным перечислением полей (или выражений), проверяемых на наличие дубликатов. В этом случае запрос также возвращает 13 записей, поскольку секция ON указывает, что дубликаты проверяются по значению поля author_i d. Без секции ON запрос верн)и бы все 15 записей, поскольку по умолчанию PostgreSQL проверяет полное совпадение всех полей.

В общем случае PostgreSQL выбирает записи, исключаемые из итогового набора при наличии секции ON, по своему усмотрению. Если в запрос вместе с DISTINCT входит секция ORDER BY, вы можете самостоятельно задать порядок выборки полей так, чтобы нужные записи оказались в начале. Сортировка записей рассматривается в подразделе «Сортировка записей».

Если вместо исключения всех дубликатов достаточно сгруппировать записи с повторяющимися значениями некоторого критерия, воспользуйтесь секцией GROUP BY, описанной в подразделе «Группировка записей».

Простая секция WHERE

Листинг 4.32. Простая секция WHERE

booktown=# SELECT * FROM books

booktown-# WHERE subject_id = 4;

id | title | author_id | subject_id

41472 | Practical PostgreSQL | 1212 | 4

41473 | Programming Python | 7805 | 4

41477 | Learning PostgreSQ L | 7805 | 4

41478 | Perl Cookbook | 7806 | 4

(4 rows)

Запрос из листинга 4.32 возвращает только те записи, у которых поле subject_id совпадает с целочисленной константой 4. Итоговый набор содержит всего 4 записи книг о компьютерах вместо 15 записей, приведенных в листинге 4.23.

Секция WHERE может содержать несколько условий, объединенных логическими операторами (например, AND или OR) и возвращающими одно логическое значение. Допустим, вас интересуют все записи для книг о компьютерах, которые, кроме того, что они о компьютерах, написаны Марком Лутцем. Запрос уточняется объединением двух условий при помощи логического оператора AND. Возможен и другой вариант — например, поиск всех книг, посвященных компьютерным технологиям или искусству; в этом случае два условия объединяются логическим оператором OR. В листинге 4.33 продемонстрированы оба сценария с ключевыми словами AND и OR.

Объединение условий в секции WHERE

Листинг 4.33. Объединение условий в секции WHERE

booktown=# SELECT title FROM books

booktown-# WHERE subject_id = 4

booktown-# AND author_id = 7805;

title

Programming Python

Learning Python

(2 rows)

booktown=# SELECT title FROM books

booktown-# WHERE subject_id = 4

booktown-# AND author_id = 0;

title

Dynamic Anatomy

Practical PostgreSQL

Programming Python

Learning Python

Perl Cookbook

(5 rows)

Первая команда SELECT в листинге 4.33 содержит два условия, объединенных логическим оператором AND. Первое условие проверяет, что книга посвящена компьютерным технологиям (поле subject_id равно 4), а второе — что автором книги является Марк Лутц (поле author_id равно 7805). Объединение условий уменьшает объем итогового набора — в него входят всего две записи, удовлетворяющие обоим условиям.

Во второй команде SELECT в листинге 4.33 прежнее первое условие (книги по компьютерной тематике) объединяется со вторым условием: книги по искусству (поле subject_id равно 0). В результате объем итогового набора увеличивается до пяти записей, каждая из которых удовлетворяет хотя бы одному из этих условий.

Количество условий, объединяемых в секции WHERE, не ограничено, хотя при наличии двух и более условий обычно выполняется группировка при помощи круглых скобок, наглядно демонстрирующая логическую связь между условиями.