Как добавить поле в таблицу postgresql
Если вы создали таблицы, а затем поняли, что допустили ошибку, или изменились требования вашего приложения, вы можете удалить её и создать заново. Но это будет неудобно, если таблица уже заполнена данными, или если на неё ссылаются другие объекты базы данных (например, по внешнему ключу). Поэтому PostgreSQL предоставляет набор команд для модификации таблиц. Заметьте, что это по сути отличается от изменения данных, содержащихся в таблице: здесь мы обсуждаем модификацию определения, или структуры, таблицы.
Изменять значения по умолчанию
Изменять типы столбцов
Все эти действия выполняются с помощью команды ALTER TABLE ; подробнее о ней вы можете узнать в её справке.
5.6.1. Добавление столбца
Добавить столбец вы можете так:
Новый столбец заполняется заданным для него значением по умолчанию (или значением NULL, если вы не добавите указание DEFAULT ).
Подсказка
Начиная с PostgreSQL 11, добавление столбца с постоянным значением по умолчанию более не означает, что при выполнении команды ALTER TABLE будут изменены все строки таблицы. Вместо этого установленное значение по умолчанию будет просто выдаваться при следующем обращении к строкам, а сохранится в строках при перезаписи таблицы. Благодаря этому операция ALTER TABLE и с большими таблицами выполняется очень быстро.
При этом вы можете сразу определить ограничения столбца, используя обычный синтаксис:
5.6.2. Удаление столбца
Удалить столбец можно так:
Данные, которые были в этом столбце, исчезают. Вместе со столбцом удаляются и включающие его ограничения таблицы. Однако если на столбец ссылается ограничение внешнего ключа другой таблицы, PostgreSQL не удалит это ограничение неявно. Разрешить удаление всех зависящих от этого столбца объектов можно, добавив указание CASCADE :
Общий механизм, стоящий за этим, описывается в Разделе 5.14.
5.6.3. Добавление ограничения
Для добавления ограничения используется синтаксис ограничения таблицы. Например:
Чтобы добавить ограничение NOT NULL, которое нельзя записать в виде ограничения таблицы, используйте такой синтаксис:
Ограничение проходит проверку автоматически и будет добавлено, только если ему удовлетворяют данные таблицы.
5.6.4. Удаление ограничения
Для удаления ограничения вы должны знать его имя. Если вы не присваивали ему имя, это неявно сделала система, и вы должны выяснить его. Здесь может быть полезна команда psql \d имя_таблицы (или другие программы, показывающие подробную информацию о таблицах). Зная имя, вы можете использовать команду:
Так можно удалить ограничения любых типов, кроме NOT NULL. Чтобы удалить ограничение NOT NULL, используйте команду:
(Вспомните, что у ограничений NOT NULL нет имён.)
5.6.5. Изменение значения по умолчанию
Назначить столбцу новое значение по умолчанию можно так:
Чтобы удалить значение по умолчанию, выполните:
При этом по сути значению по умолчанию просто присваивается NULL. Как следствие, ошибки не будет, если вы попытаетесь удалить значение по умолчанию, не определённое явно, так как неявно оно существует и равно NULL.
5.6.6. Изменение типа данных столбца
Чтобы преобразовать столбец в другой тип данных, используйте команду:
PostgreSQL попытается также преобразовать к новому типу значение столбца по умолчанию (если оно определено) и все связанные с этим столбцом ограничения. Но преобразование может оказаться неправильным, и тогда вы получите неожиданные результаты. Поэтому обычно лучше удалить все ограничения столбца, перед тем как менять его тип, а затем воссоздать модифицированные должным образом ограничения.
15) PostgreSQL ALTER Table
Команда ALTER TABLE используется для изменения структуры таблицы PostgreSQL. Это команда, используемая для изменения столбцов таблицы или имени таблицы.
В этом уроке вы узнаете:
Синтаксис
Вот синтаксис команды PostgreSQL ALTER TABLE:
Параметр table-name — это имя таблицы, которую нужно изменить.
Параметр action — это действие, которое необходимо выполнить, например, изменить имя столбца, изменить тип данных столбца и т. Д.
Описание
Команда ALTER TABLE изменяет определение существующей таблицы. Требуются следующие подчиненные формы:
Изменение столбца
Столбец может быть изменен несколькими способами. Такие изменения могут быть выполнены с помощью команды ALTER TABLE. Давайте обсудим это:
Добавление нового столбца
Чтобы добавить новый столбец в таблицу PostgreSQL, используется команда ALTER TABLE со следующим синтаксисом:
Имя таблицы — это имя таблицы, которую нужно изменить.
Имя нового столбца — это имя нового столбца, который нужно добавить.
Определение столбца — это тип данных нового столбца.
Смотрите таблицу Book, показанную ниже:
Таблица состоит из двух столбцов: id и name. Нам нужно добавить новый столбец в таблицу и дать ему имя автора. Просто запустите следующую команду:
После выполнения вышеуказанной команды таблица Book теперь выглядит следующим образом:
Новый столбец был успешно добавлен.
Переименование столбца таблицы
Мы можем использовать команду ALTER TABLE, чтобы изменить имя столбца. В этом случае команда используется со следующим синтаксисом:
Имя таблицы — это имя таблицы, столбец которой необходимо переименовать.
Старое имя — это старое / текущее имя столбца.
Новое имя — это новое имя столбца. Рассмотрим таблицу Book, показанную ниже:
Книга:
Нам нужно имя автора колонки для book_author. Вот команда:
После выполнения команды мы можем просмотреть структуру таблицы:
Имя столбца было успешно изменено.
Установка значения по умолчанию для столбца
Мы можем установить значение по умолчанию для столбца таким образом, чтобы даже если вы не указали значение для этого столбца во время операций INSERT, будет использоваться значение по умолчанию. В этом случае команду ALTER TABLE можно использовать со следующим синтаксисом:
Имя таблицы — это имя таблицы, столбец которой необходимо изменить.
Имя столбца — это имя, для которого должно быть установлено значение по умолчанию.
Значение является значением по умолчанию для столбца.
Рассмотрим таблицу Book, приведенную ниже:
Нам нужно установить значение по умолчанию для столбца book_author. Мы можем запустить следующую команду:
Теперь давайте вставим строку в таблицу:
Обратите внимание, что мы вставили значения только для двух столбцов, id и name. Однако для столбца book_author было использовано значение по умолчанию:
Добавление ограничения проверки
Проверочное ограничение помогает в проверке записей, которые вставляются в таблицу. Мы можем сделать это, комбинируя команду ALTER TABLE с оператором ADD CHECK. Синтаксис:
Имя таблицы — это имя таблицы, которую нужно изменить.
Выражение — это ограничение, накладываемое на столбец таблицы.
Давайте изменим столбец book_author таблицы Book так, чтобы он принимал только значения Николас и Самуил:
Теперь давайте попробуем вставить значение, отличное от Николаса или Сэмюэля, в столбец book_author таблицы Book:
Как добавить поле в таблицу postgresql
Если вы создали таблицы, а затем поняли, что допустили ошибку, или изменились требования вашего приложения, вы можете удалить её и создать заново. Но это будет неудобно, если таблица уже заполнена данными, или если на неё ссылаются другие объекты базы данных (например, по внешнему ключу). Поэтому PostgreSQL предоставляет набор команд для модификации таблиц. Заметьте, что это по сути отличается от изменения данных, содержащихся в таблице: здесь мы обсуждаем модификацию определения, или структуры, таблицы.
Изменять значения по умолчанию
Изменять типы столбцов
Все эти действия выполняются с помощью команды ALTER TABLE ; подробнее о ней вы можете узнать в её справке.
5.5.1. Добавление столбца
Добавить столбец вы можете так:
Новый столбец заполняется заданным для него значением по умолчанию (или значением NULL, если вы не добавите указание DEFAULT ).
При этом вы можете сразу определить ограничения столбца, используя обычный синтаксис:
Подсказка
5.5.2. Удаление столбца
Удалить столбец можно так:
Данные, которые были в этом столбце, исчезают. Вместе со столбцом удаляются и включающие его ограничения таблицы. Однако если на столбец ссылается ограничение внешнего ключа другой таблицы, PostgreSQL не удалит это ограничение неявно. Разрешить удаление всех зависящих от этого столбца объектов можно, добавив указание CASCADE :
Общий механизм, стоящий за этим, описывается в Разделе 5.13.
5.5.3. Добавление ограничения
Для добавления ограничения используется синтаксис ограничения таблицы. Например:
Чтобы добавить ограничение NOT NULL, которое нельзя записать в виде ограничения таблицы, используйте такой синтаксис:
Ограничение проходит проверку автоматически и будет добавлено, только если ему удовлетворяют данные таблицы.
5.5.4. Удаление ограничения
Для удаления ограничения вы должны знать его имя. Если вы не присваивали ему имя, это неявно сделала система, и вы должны выяснить его. Здесь может быть полезна команда psql \d имя_таблицы (или другие программы, показывающие подробную информацию о таблицах). Зная имя, вы можете использовать команду:
Так можно удалить ограничения любых типов, кроме NOT NULL. Чтобы удалить ограничение NOT NULL, используйте команду:
(Вспомните, что у ограничений NOT NULL нет имён.)
5.5.5. Изменение значения по умолчанию
Назначить столбцу новое значение по умолчанию можно так:
Чтобы удалить значение по умолчанию, выполните:
При этом по сути значению по умолчанию просто присваивается NULL. Как следствие, ошибки не будет, если вы попытаетесь удалить значение по умолчанию, не определённое явно, так как неявно оно существует и равно NULL.
5.5.6. Изменение типа данных столбца
Чтобы преобразовать столбец в другой тип данных, используйте команду:
PostgreSQL попытается также преобразовать к новому типу значение столбца по умолчанию (если оно определено) и все связанные с этим столбцом ограничения. Но преобразование может оказаться неправильным, и тогда вы получите неожиданные результаты. Поэтому обычно лучше удалить все ограничения столбца, перед тем как менять его тип, а затем воссоздать модифицированные должным образом ограничения.
Операции с данными
Добавление данных. Команда Insert
После INSERT INTO идет имя таблицы, затем в скобках указываются все столбцы через запятую, в которые надо добавлять данные. И в конце после слова VALUES в скобках перечисляются добавляемые значения.
Допустим, у нас в базе данных есть следующая талица:
Добавим в нее одну строку с помощью команды INSERT:
После удачного выполнения в pgAdmin в поле сообщений должно появиться сообщение «INSERT 0 1»:
ProductName: ‘Galaxy S9’
Также при вводе значений можно указать непосредственные столбцы, в которые будут добавляться значения:
Здесь значение указывается только для трех столбцов. Причем теперь значения передаются в порядке следования столбцов:
ProductName: ‘iPhone X’
Для столбца Id значение будет генерироваться автоматически базой данных, так как он представляет тип Serial. То есть к значению из последней строки будет добавляться единица.
Для остальных столбцов будет добавляться значение по умолчанию, если задан атрибут DEFAULT (например, для столбца ProductCount), значение NULL. При этом неуказанные столбцы (за исключением тех, которые имеют тип Serial) должны допускать значение NULL или иметь атрибут DEFAULT.
Если конкретные столбцы не указываются, как в первом примере, тогда мы должны передать значения для всех столбцов в таблице.
Также мы можем добавить сразу несколько строк:
В данном случае в таблицу будут добавлены три строки.
Возвращение значений
Если мы добавляем значения только для части столбцов, то мы можем не знать, какие значения будут у других столбцов. Например, какое значени получит столбец Id у товара. С помощью оператора RETURNING мы можем получить это значение:
Как добавить поле в большую таблицу PostgreSQL без блокировки?
Есть таблица вот такого вида:
Теперь в неё необходимо добавить новое поле. Так-то задача простая,
Вот только проблема: табличка уже размером в несколько десятков гигабайт и этот alter table блокирует всю запись в таблицу на длительное время.
Как можно добавить поле без простоя системы?
PS: странно, но не нашёл такого распространённого вопроса здесь
1 ответ 1
Если же у вас postgresql версии ниже 11, то всё не так просто. В указанном запросе
Поэтому внести желаемое изменение возможно, но выглядеть оно будет совсем не так, а в много действий.
Начинаем аккуратно вносить изменение
Отдельного пояснения заслуживает зачем я изменил statement_timeout в транзакции. Эта настройка ограничивает максимальное время выполнения запроса. Этот alter table всё равно требует блокировку на таблицу, хоть и на короткое время и здесь есть скрытые грабли: что если alter table не может взять блокировку из-за какой-то другой выполняемой транзакции? Например, простой insert в другой транзакции не позволит взять блокировку для изменения структуры. Но при этом запущенный alter table уже заблокирует все последующие пишущие запросы к этой таблице. Короткий statement_timeout быстро убьёт alter table и сервис продолжит работу. А попытку добавить поле можно безболезненно повторить чуть позже пока это в итоге не удастся.
Поле добавили, теперь default в данных
Длительный процесс, необходимо запросами
проставить желаемое значение в имеющихся данных таблицы. Обновлять данные необходимо кусочками (для чего в запросе оставил открытое условие), делая паузы между обновлениями, следить за отставанием репликации (если такая есть) и за процессами autovacuum. Есть несколько подходов по самому обновлению, более простой будет обновлять по первичному или любому уникальному ключу. Берём любой язык программирования или сценариев по своему вкусу и делаем что-то такое:
Во время выполнения такого скрипта можно изредка делать vacuum email_stats чтобы табличка не сильно увеличивалась в размерах. Особенно если autovacuum настроен недостаточно агрессивно и не успевает прибирать за скриптом.
Размер одного обновления и величину паузы между обновлениями нужно подбирать под профиль нагрузки конкретного сервиса. Маленькие обновления и большие паузы не будут никому мешать, но просто выполняться скрипт будет очень долго.
Пример запроса не самый эффективный, время его выполнения будет сильно плавать от пропусков id в данных и потому что данные скорей всего будут в разных страничках памяти, зато он простой и можно легко настраивать максимальный размер для одного обновления.
Ставим not null
Теперь у нас в таблице не должно быть null значений в добавленном поле, можно проставить not null.
Этот запрос к сожалению поставит блокировку на запись. Но время выполнения куда меньше чем на обновление всей таблицы с перезаписью значения по умолчанию.
С минимальной блокировкой записи придётся отказаться от родного not null свойства, зато можно добавить check ограничение с аналогичным свойством. Сначала добавляем ограничение с указанием not valid (аналогично со statement timeout для транзакции)
Затем в другой транзакции уже без statement_timeout
Проверка ограничения не заблокирует запись.
Готово
Всё, поле добавлено
