Как изменить данные в таблице postgresql
Перейти к содержимому

Как изменить данные в таблице postgresql

  • автор:

Как изменить данные в таблице postgresql

Для обновления данных в базе данных PostgreSQL применяется команда UPDATE . Она имеет следующий общий формальный синтаксис:

UPDATE имя_таблицы SET столбец1 = значение1, столбец2 = значение2, . столбецN = значениеN [WHERE условие_обновления]

Например, увеличим у всех товаров цену на 3000:

UPDATE Products SET Price = Price + 3000;

UPDATE и обновление данных в PostgreSQL

В данном случае обновление касается всех строк. С помощью выражения WHERE можно с помощью условию конкретизировать обновляемые строки — если строка соответствует условию, то она будет обновляться. Например, изменим название производителя с «Samsung» на «Samsung Inc.»:

UPDATE Products SET Manufacturer = 'Samsung Inc.' WHERE Manufacturer = 'Samsung';

UPDATE and WHERE in PostgreSQL

Также можно обновлять сразу несколько столбцов:

UPDATE Products SET Manufacturer = 'Samsung', ProductCount = ProductCount + 3 WHERE Manufacturer = 'Samsung Inc.';

Как изменить данные в таблице postgresql

UPDATE — изменить строки таблицы

Синтаксис

[ WITH [ RECURSIVE ] запрос_WITH [, . ] ] UPDATE [ ONLY ] имя_таблицы [ * ] [ [ AS ] псевдоним ] SET < имя_столбца = < выражение | DEFAULT > | ( имя_столбца [, . ] ) = ( < выражение | DEFAULT > [, . ] ) | ( имя_столбца [, . ] ) = ( вложенный_SELECT ) > [, . ] [ FROM элемент_FROM [, . ] ] [ WHERE условие | WHERE CURRENT OF имя_курсора ] [ RETURNING * | выражение_результата [ [ AS ] имя_результата ] [, . ] ]

Описание

UPDATE изменяет значения указанных столбцов во всех строках, удовлетворяющих условию. В предложении SET должны указываться только те столбцы, которые будут изменены; столбцы, не изменяемые явно, сохраняют свои предыдущие значения.

Изменить строки в таблице, используя информацию из других таблиц в базе данных, можно двумя способами: применяя вложенные запросы или указав дополнительные таблицы в предложении FROM . Выбор предпочитаемого варианта зависит от конкретных обстоятельств.

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

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

Параметры

запрос_WITH

Предложение WITH позволяет задать один или несколько подзапросов, на которые затем можно ссылаться по имени в запросе UPDATE . Подробнее об этом см. Раздел 7.8 и SELECT . имя_таблицы

Имя таблицы (возможно, дополненное схемой), строки которой будут изменены. Если перед именем таблицы добавлено ONLY , соответствующие строки изменяются только в указанной таблице. Без ONLY строки будут также изменены во всех таблицах, унаследованных от указанной. При желании, после имени таблицы можно указать * , чтобы явно обозначить, что операция затрагивает все дочерние таблицы. псевдоним

Альтернативное имя целевой таблицы. Когда указывается это имя, оно полностью скрывает фактическое имя таблицы. Например, в запросе UPDATE foo AS f дополнительные компоненты оператора UPDATE должны обращаться к целевой таблице по имени f , а не foo . имя_столбца

Имя столбца в таблице имя_таблицы . Имя столбца при необходимости может быть дополнено именем вложенного поля или индексом массива. Имя таблицы добавлять к имени целевого столбца не нужно — например, запись UPDATE table_name SET table_name.col = 1 ошибочна. выражение

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

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

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

Табличное выражение, позволяющее обращаться в условии WHERE и выражениях новых данных к столбцам других таблиц. В нём используется тот же синтаксис, что и в предложении Предложение FROM оператора SELECT ; например, вы можете определить псевдоним для таблицы. Имя целевой таблицы повторять в предложении FROM нужно, только если вы хотите определить замкнутое соединение (в этом случае для данного имени должен определяться псевдоним). условие

Выражение, возвращающее значение типа boolean . Изменены будут только те стоки, для которых это выражение возвращает true . имя_курсора

Имя курсора, который будет использоваться в условии WHERE CURRENT OF . С таким условием будет изменена строка, выбранная из этого курсора последней. Курсор должен образовываться запросом, не применяющим группировку, к целевой таблице команды UPDATE . Заметьте, что WHERE CURRENT OF нельзя задать вместе с логическим условием. За дополнительными сведениями об использовании курсоров с WHERE CURRENT OF обратитесь к DECLARE . выражение_результата

Выражение, которое будет вычисляться и возвращаться командой UPDATE после изменения каждой строки. В этом выражении можно использовать имена любых столбцов таблицы имя_таблицы или таблиц, перечисленных в списке FROM . Чтобы получить все столбцы, достаточно написать * . имя_результата

Имя, назначаемое возвращаемому столбцу.

Выводимая информация

В случае успешного завершения, UPDATE возвращает метку команды в виде

UPDATE число 

Здесь число обозначает количество изменённых строк, включая те подлежащие изменению строки, значения в которых не были изменены. Заметьте, что это число может быть меньше количества строк, удовлетворяющих условию , когда изменения отменяются триггером BEFORE UPDATE . Если число равно 0, данный запрос не изменил ни одной строки (это не считается ошибкой).

Если команда UPDATE содержит предложение RETURNING , её результат будет похож на результат оператора SELECT (с теми же столбцами и значениями, что содержатся в списке RETURNING ), полученный для строк, изменённых этой командой.

Замечания

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

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

Примеры

Изменение слова Drama на Dramatic в столбце kind таблицы films :

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

Изменение значений температуры и сброс уровня осадков к значению по умолчанию в одной строке таблицы weather :

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03';

Выполнение той же операции с получением изменённых записей:

UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03' RETURNING temp_lo, temp_hi, prcp;

Такое же изменение с применением альтернативного синтаксиса со списком столбцов:

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT) WHERE city = 'San Francisco' AND date = '2003-07-03';

Увеличение счётчика продаж для менеджера, занимающегося компанией Acme Corporation, с применением предложения FROM :

UPDATE employees SET sales_count = sales_count + 1 FROM accounts WHERE accounts.name = 'Acme Corporation' AND employees.id = accounts.sales_person;

Выполнение той же операции, с вложенным запросом в предложении WHERE :

UPDATE employees SET sales_count = sales_count + 1 WHERE (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

Изменение имени контакта в таблице счетов (это должно быть имя назначенного менеджера по продажам):

UPDATE accounts SET (contact_first_name, contact_last_name) = (SELECT first_name, last_name FROM salesmen WHERE salesmen.id = accounts.sales_id);

Подобный результат можно получить, применив соединение:

UPDATE accounts SET contact_first_name = first_name, contact_last_name = last_name FROM salesmen WHERE salesmen.id = accounts.sales_id;

Однако если salesmen . id — не уникальный ключ, второй запрос может давать непредсказуемые результаты, тогда как первый запрос гарантированно выдаст ошибку, если найдётся несколько записей с одним id . Кроме того, если соответствующая запись accounts . sales_id не найдётся, первый запрос запишет в поля имени NULL, а второй вовсе не изменит строку.

Обновление статистики в сводной таблице в соответствии с текущими данными:

UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) = (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d WHERE d.group_id = s.group_id);

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

BEGIN; -- другие операции SAVEPOINT sp1; INSERT INTO wines VALUES('Chateau Lafite 2003', '24'); -- Предполагая, что здесь возникает ошибка из-за нарушения уникальности ключа, -- мы выполняем следующие команды: ROLLBACK TO sp1; UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003'; -- Продолжение других операций и в завершение. COMMIT;

Изменение столбца kind таблицы films в строке, на которой в данный момент находится курсор c_films :

UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;

Совместимость

Эта команда соответствует стандарту SQL , за исключением предложений FROM и RETURNING , которые являются расширениями PostgreSQL , как и возможность применять WITH с UPDATE .

В некоторых других СУБД также поддерживается дополнительное предложение FROM , но предполагается, что целевая таблица должна ещё раз упоминаться в этом предложении. PostgreSQL воспринимает предложение FROM не так, поэтому будьте внимательны, портируя приложения, которые используют это расширение языка.

Согласно стандарту, исходным значением для вложенного списка имён столбцов в скобках может быть любое выражение, выдающее строку с нужным количеством столбцов. PostgreSQL принимает в качестве этого значения только список выражений в скобках или вложенный SELECT . Изменяемое значение отдельного столбца можно обозначить словом DEFAULT в случае со списком выражений, но не внутри вложенного SELECT .

Пред. Наверх След.
UNLISTEN Начало VACUUM

Как поменять тип данных в таблице PostgresSQL?

У меня в таблице есть атрибут char(5000) , я хочу поменять его на тип text . Возможно ли это и если да то как?

Отслеживать
задан 2 мая 2017 в 0:51
5,267 11 11 золотых знаков 56 56 серебряных знаков 115 115 бронзовых знаков

alter table table_name alter colunm colunm_name type text не работает? Тогда 1) добавить новое поле, 2) скопировать значения из старого в новое, 3) убедиться, что скопировано верно, 4) удалить старое поле, 5) переименовать новое в старое

2 мая 2017 в 2:35

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Предложенный Sergey вариант должен работать:

alter table table_name alter colunm colunm_name type text 

Отслеживать
ответ дан 2 мая 2017 в 6:55
126 4 4 бронзовых знака

    Важное на Мете
Похожие

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Дизайн сайта / логотип © 2023 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2023.10.27.43697

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

PostgreSQL — Изменение таблиц

Когда вы создаете таблицу и понимаете, что допустили ошибку, или изменились требования приложения, вы можете удалить таблицу и создать ее заново. Но это не удобный вариант, если таблица уже заполнена данными или если на таблицу ссылаются другие объекты базы данных (например, ограничение внешнего ключа). Поэтому PostgreSQL предоставляет семейство команд для внесения изменений в существующие таблицы. Обратите внимание, что это концептуально отличается от изменения данных, содержащихся в таблице: здесь нас интересует изменение определения или структуры таблицы.

  • Добавить столбцы
  • Удалить столбцы
  • Добавить ограничения
  • Удалить ограничения
  • Изменить значения по умолчанию
  • Изменить типы данных столбца
  • Переименовать столбцы
  • Переименовать таблицы

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

Добавление столбца

Чтобы добавить столбец, используйте команду, например:

ALTER TABLE products ADD COLUMN description text; 

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

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

Однако, если значение по умолчанию изменчиво (например, clock_timestamp() ), каждая строка должна быть обновлена ​​значением, рассчитанным во время ALTER TABLE выполнения. Чтобы избежать потенциально длительной операции обновления, особенно если вы все равно собираетесь заполнить столбец в основном значениями, отличными от значений по умолчанию, может быть предпочтительнее добавить столбец без значения по умолчанию, вставить правильные значения с помощью UPDATE , а затем добавить любое желаемое значение по умолчанию, как описано ниже.

Вы также можете одновременно определить ограничения для столбца, используя обычный синтаксис:

ALTER TABLE products ADD COLUMN description text CHECK (description <> ''); 

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

Удаление столбца

Чтобы удалить столбец, используйте команду, например:

ALTER TABLE products DROP COLUMN description; 

Какие бы данные ни были в столбце, они исчезают. Ограничения таблицы, включающие столбец, также отбрасываются. Однако, если на столбец ссылается ограничение внешнего ключа другой таблицы, PostgreSQL не будет молча отбрасывать это ограничение. Вы можете разрешить удаление всего, что зависит от столбца, добавив CASCADE :

ALTER TABLE products DROP COLUMN description CASCADE;

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

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

ALTER TABLE products ADD CHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups; 

Чтобы добавить ненулевое ограничение, которое нельзя записать как табличное ограничение, используйте следующий синтаксис:

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; 

Ограничение будет проверено немедленно, поэтому данные таблицы должны удовлетворять ограничению, прежде чем их можно будет добавить.

Удаление ограничения

Чтобы удалить ограничение, вам нужно знать его имя. Если вы дали ему имя, то это легко. В противном случае система присвоила сгенерированное имя, которое вам необходимо узнать. Здесь может помочь команда psql ; другие интерфейсы также могут предоставлять способ проверки деталей таблицы. Затем команда: \d tablename

ALTER TABLE products DROP CONSTRAINT some_name; 

(Если вы имеете дело со сгенерированным именем ограничения, например $2 , не забывайте, что вам нужно будет заключить его в двойные кавычки, чтобы сделать его допустимым идентификатором.)

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

Это работает одинаково для всех типов ограничений, кроме ненулевых ограничений. Чтобы удалить ненулевое ограничение, используйте:

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL; 

(Напомним, что у ненулевых ограничений нет имен.)

Изменение значения столбца по умолчанию

Чтобы установить новое значение по умолчанию для столбца, используйте команду, например:

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; 

Обратите внимание, что это не влияет на существующие строки в таблице, а просто изменяет значение по умолчанию для будущих INSERT команд.

Чтобы удалить любое значение по умолчанию, используйте:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT; 

Фактически это то же самое, что установить по умолчанию значение null. Как следствие, не будет ошибкой отбрасывать значение по умолчанию там, где оно не было определено, поскольку значение по умолчанию неявно является нулевым значением.

Изменение типа данных столбца

Чтобы преобразовать столбец в другой тип данных, используйте команду, например:

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2); 

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

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

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

Чтобы переименовать столбец:

ALTER TABLE products RENAME COLUMN product_no TO product_number; 

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

Чтобы переименовать таблицу:

ALTER TABLE products RENAME TO items;

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *