Инструкция ALTER TABLE

В SQL существует также инструкция ALTER TABLE (изменить таблицу), которая позволяет добавлять, удалять и модифицировать столбцы и ограничения. Синтаксиса ALTER TABLE имеет вид:

ALTER TABLE [ ONLY ] имя_таблицы [ * ] <действие> [,... ]

где (ограничиваясь лишь стандартными элементами)

<действие>::= {

-- добавить столбец

ADD [ COLUMN ] столбецтип [ограничение_столбца [... ] ]

-- удалить столбец

| DROP [ COLUMN ] [ IF EXISTS ] столбец [ RESTRICT | CASCADE ]

-- изменить тип столбца

| ALTER [ COLUMN ] столбец [ SET DATA ] TYPE тип

-- изменить значение по умолчанию

| ALTER [ COLUMN ] столбец SET DEFAULT выражение

| ALTER [ COLUMN ] столбец DROP DEFAULT

-- изменить ограничение NOT NULL

| ALTER [ COLUMN ] столбец { SET | DROP } NOT NULL

-- переименовать столбец

| RENAME [ COLUMN ] имя_столбца TO новое_имя

-- добавить/удалить ограничение таблицы

| ADD ограничение таблицы

| DROP CONSTRAINT [ IF EXISTS ] ограничение[ RESTRICT | CASCADE ] }

Однако модификация таблицы с помощью ALTER TABLE может изменить права доступа других пользователей к этой таблице, поэтому часто бывает проще просто создать новую таблицу с необходимыми изменениями при создании, и переписать в нее данные из старой таблицы.

Примеры:

/* cоздаем новую таблицу Доставка */

CREATE TABLE "Доставка"

("Заказ" integer,

"Курьер" character varying(40) DEFAULT ‘foo’,

"Дата_время" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,

“Доставлен” boolean DEFAULT FALSE);

/* добавляем первичный ключ*/,

ALTER TABLE "Доставка" ADD CONSTRAINT "pk_доставка"
PRIMARY KEY ("Заказ");

/* добавляем внешний ключ для ссылки на Заказы*/,

ALTER TABLE "Доставка" ADD CONSTRAINT "fk_доставка"
FOREIGN KEY ("Заказы") REFERENCES "Заказы" ("Код_заказа")
MATCH SIMPLE ON UPDATE CASCADE ON DELETE SET NULL;

3. Индексы

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

PostgreSQL поддерживает четыре типа индексов: B-tree, hash, GiST, GIN. По умолчанию используется B-tree. Обычно при ведении ограничений первичного ключа и UNIQUE соответствующий индекс создается автоматически, но для первичного это делается неявно (его нельзя удалить как обычные индексы), В PostgreSQL создать индекс указанного типа для одного или нескольких полей таблицы можно с помощью инструкции (упрощенный вид):

CREATE [ UNIQUE ] INDEX [ индекс ]
ON таблица [ USING тип_индекса ]

({ столбец | выражение } [ [ASC] | DESC] [,...])

Если имя индекса вводится, оно не может быть использовано для чего-то другого в базе данных (любым пользователем). Однажды созданный, индекс будет невидим пользователю – SQL сам решает, использовать его или нет.

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

При выборе типа индекса следует учитывать, что создать UNIQUE-индекс и указать порядок сортировки (ASC/DESC) можно только для индексов типа B-tree. Индексировать по нескольким полям можно только при выборе индексов B-tree, GiST, GIN.

Любой индекс может быть кластерным (CLUSTERED). Это означает, что на его основе можно выполнить кластеризацию таблицы, т.е. переупорядочивание физических страниц данных в соответствии с логическим порядком данного индекса (что естественно увеличивает число дисковых операций ввода-вывода). Поэтому для каждой таблицы может быть создан только один кластерный индекс, тогда как не кластерных индексов может быть несколько (практически оптимальным количеством является 2-6 не кластерных индексов на таблицу). Кластеризация дает преимущество в том случае, если большую часть данных, которые выбираются из таблицы, составляют группы индексированных данных, а не случайно выбранные отдельные данные, т.к. такие группы располагаются на одной и той же физической странице (или последовательных страницах). Но необходимо учитывать, что При выполнении кластеризации таблицы все операции, в том числе чтение, с этой таблицей блокируются.

Для кластеризации в PostgreSQL используется инструкция

CLUSTER [VERBOSE] [ таблица [ USING индекс ] ]

где VERBOSE позволяет вывести сообщение о кластеризации каждой таблицы.

Упорядоченность данных, возникшая в результате кластеризации, не распространяется на данные, которые изменяются после проведения кластеризации, поэтому при обновлении данных кластеризацию необходимо повторить. Для этого достаточно ввести CLUSTER таблица. Если ввести CLUSTER без параметров, рекластеризации будут подвергнуты все таблицы базы даннях, для которых ранее были созданы кластерные индексы, поэтому такие индексы создаются обычно для тех таблиц, данные в которых изменяются редко.

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

Чтобы удалить индекс, надо знать его имя или его OID. Для удаления индекса по имени используется инструкция:

DROP INDEX имя_индекса

Индекс можно удалить непосредственно из системной таблицы pg_index, зная его OID. Удаление индекса не влияет на содержание полей.

Примеры:

CREATE UNIQUE INDEX “_ui_” ON "Доставка" ("Заказ", "Курьер", “Дата_время”);

CLUSTER VERBOSE "Доставка заказов" USING “_ui_”;

4. Представления

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

Представление создается с помощью инструкции CREATE VIEW, синтаксис которой имеет следующий вид:

CREATE [OR REPLACE] [TEMP | TEMPORARY] VIEW имя_представления
[ (столбец [,...]) ] AS запрос

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

При использовании CREATE OR REPLACE, если представление с таким же именем уже существует в данной схеме, оно будет переопределено.

С помощью TEMP или TEMPORARY создаются временные представления. Если представление определяется на основе только временных таблиц, оно автоматически становится временным, независимо от того, указано ли TEMP/TEMPORARY при его создании.

Синтаксис инструкции удаления представления подобен синтаксису удаления базовых таблиц:

DROP VIEW имя_представления