Изменение данных в Postgresql
CREATE
Создать две временные таблицы:
Первая таблица, копия таблицы aircrafts из учебной базы данных Postgresql
demo=# CREATE TEMP TABLE aircrafts_tmp AS
SELECT * FROM aircrafts WITH NO DATA ;
В примере выше копируется таблица без данных, о чем говорит фраза WITH NO DATA
Проверить структуру и содержимое таблицы:
demo=# \d aircrafts_tmp
Table "pg_temp_5.aircrafts_tmp"
Column | Type | Collation | Nullable | Default
---------------+--------------+-----------+----------+---------
aircraft_code | character(3) | | |
model | text | | |
range | integer | | |
demo=# select * from aircrafts_tmp ;
aircraft_code | model | range
---------------+-------+-------
(0 rows)
ALTER
Добавить первичный ключ:
demo=# ALTER TABLE aircrafts_tmp
demo-# ADD PRIMARY KEY ( aircraft_code );
ALTER TABLE
Сделать столбец model уникальным:
demo=# ALTER TABLE aircrafts_tmp
demo-# ADD UNIQUE ( model );
ALTER TABLE
Создать аналогичным способом вторую временную таблицу:
demo=# CREATE TEMP TABLE aircrafts_log AS
demo-# SELECT * FROM aircrafts WITH NO DATA;
CREATE TABLE AS
Добавить в таблицу два столбца:
demo=# ALTER TABLE aircrafts_log
demo-# ADD COLUMN when_add timestamp;
ALTER TABLE
demo=# ALTER TABLE aircrafts_log
demo-# ADD COLUMN operation text ;
ALTER TABLE
Альтернативный способ создания временных таблиц:
CREATE TEMP TABLE aircrafts_tmp
( LIKE aircrafts INCLUDING CONSTRAINTS INCLUDING INDEXES );
Способ выше можно применять и для создания постоянных таблиц
В первой таблице будут меняться данные, а во вторую таблицу будут заноситься все изменения которые были сделаны в первой таблице
INSERT
Вставить строки из таблицы aircrafts в таблицу(временную) aircrafts_tmp:
demo=# WITH add_row AS
( INSERT INTO aircrafts_tmp
SELECT * FROM aircrafts
RETURNING *
)
INSERT INTO aircrafts_log
SELECT add_row.aircraft_code, add_row.model, add_row.range,
current_timestamp, 'INSERT'
FROM add_row ;
INSERT 0 9
В конструкции WITH add_row INSERT INTO вставляет данные полученные запросом SELECT из согласованной по порядку и кол-ву атрибутов таблицы aircrafts в таблицу aircrafts_tmp
RETURNING * возвращает вывод SELECT в конструкцию WITH
Далее во внешнем запросе с помощью INSERT INTO вставляются строки в таблицу aircrafts_log, данные для внешнего запроса берутся с помощью SELECT из временной таблицы add_row, строки которой получены с помощью конструкции WITH и оператора RETURNING *
Два недостающих столбца вставляются явно, системной функцией(текущая дата и время) и строкой(название операции)
При вставке новых строк, могут возникать ситуации, когда нарушается ограничение первичного и уникального ключей, так как вставляемые строки могут иметь значения совпадающие с теми, что уже есть в таблице
Для таких случаев предусмотрено средство ON CONFLICT, которое предусматривает два варианта действия
Первый вариант отменяет добавление новой строки
demo=# WITH add_row AS
(INSERT INTO aircrafts_tmp SELECT * FROM aircrafts RETURNING * )
INSERT INTO aircrafts_log SELECT add_row.aircraft_code, add_row.model, add_row.range,current_timestamp, 'INSERT' FROM add_row ;
ERROR: duplicate key value violates unique constraint "aircrafts_tmp_pkey"
DETAIL: Key (aircraft_code)=(773) already exists.
demo=# WITH add_row AS (INSERT INTO aircrafts_tmp SELECT * FROM aircrafts ON CONFLICT DO NOTHING RETURNING * )
INSERT INTO aircrafts_log SELECT add_row.aircraft_code, add_row.model, add_row.range,current_timestamp, 'INSERT' FROM add_row ;
INSERT 0 0
demo=# WITH add_row AS (INSERT INTO aircrafts_tmp VALUES ( 'SU9', 'Sukhoi SuperJet-100', 3000 ) ON CONFLICT DO NOTHING RETURNING * )
INSERT INTO aircrafts_log SELECT add_row.aircraft_code, add_row.model, add_row.range,current_timestamp, 'INSERT' FROM add_row ;
INSERT 0 0
Первый запрос не использует фразу ON CONFLICT и выводит ошибку о том, что такое значение для ключа уже существует
Во втором и третьем варианте используются фраза ON CONFLICT, при этом строки уже существующие не меняются и ошибок нет
Если во фразе ON CONFLICT не указывается доп. информация об атрибуте, в котором следует проверять информацию, тогда по умолчанию проверяются первичные и уникальные ключи
В следующем примере указывается столбец для проверки конфликтующих значений:
demo=# INSERT INTO aircrafts_tmp
VALUES ( 'SU9', 'Sukhoi SuperJet-100', 3000 )
ON CONFLICT ( aircraft_code ) DO NOTHING
RETURNING * ;
aircraft_code | model | range
---------------+-------+-------
(0 rows)
В примере выше будет вывод также, как это делает SELECT. Значения будут конфликтовать как по атрибуту первичного ключа так и по атрибуту model ( уникальный ключ )
Если повторить запрос так, чтобы значение ключа было уникальным, тогда вывод будет следующим:
demo=# INSERT INTO aircrafts_tmp
VALUES ( 'S90', 'Sukhoi SuperJet-100', 3000 )
ON CONFLICT ( aircraft_code ) DO NOTHING
RETURNING * ;
ERROR: duplicate key value violates unique constraint "aircrafts_tmp_model_key"
DETAIL: Key (model)=(Sukhoi SuperJet-100) already exists.
В примере выше ошибки конфликта не будет только по первичному ключу, так как он указан в DO NOTHING, однако по уникальному ключу будет ошибка, так значение уже существует и в DO NOTHING атрибут не упоминается
Второй вариант ON CONFLICT заменяет значение, для этого используется фраза DO UPDATE
demo=# INSERT INTO aircrafts_tmp
VALUES ( 'SU9', 'Sukhoi SuperJet', 3000 )
ON CONFLICT ON CONSTRAINT aircrafts_tmp_pkey
DO UPDATE SET model = excluded.model, range = excluded.range
RETURNING * ;
aircraft_code | model | range
---------------+-----------------+-------
SU9 | Sukhoi SuperJet | 3000
(1 row)
INSERT 0 1
В примере выше, значение атрибута aircraft_code уже существует, в противном случае запрос поменял бы всю строку, изменилось только значение атрибута model
Специальная таблица excluded, поддерживаемая СУБД, хранит в себе текущие значения строк, предлагаемые для вставки команды INSERT
Таким образом, если значение атрибута aircraft_code уникальное, тогда выполняется операция INSERT, иначе изменяются только те значения строки, которые отличаются от исходных, т.е. выполняется операция UPDATE
COPY
Для массового ввода строк используется команда COPY из файла или стандартного ввода или наооборот, из таблицы в файл или в стандартный вывод
COPY aircrafts_tmp FROM '/home/postgres/aircrafts.txt';
UPDATE
demo=# WITH update_row AS
(UPDATE aircrafts_tmp
SET range = range * 1.2
WHERE model ~ '^Bom'
RETURNING *
)
INSERT INTO aircrafts_log
SELECT ur.aircraft_code, ur.model, ur.range, current_timestamp, 'UPDATE'
FROM update_row ur;
INSERT 0 1
В виртуальной таблице update_row хранится строка с атрибутами и значениями полученная с помощью RETURNING после выполнения операции UPDATE. Полученные значения используются для вставки во временную таблицу aircrafts_log
Следующий пример показвает, как можно увидеть динамику продаж билетов по всем направлениям таблицы маршруты
ALTER
Создать временную таблицу, город отправления, город прибытия
demo=# CREATE TEMP TABLE tickets_directions AS
demo-# SELECT DISTINCT departure_city, arrival_city FROM routes;
SELECT 516
Добавим еще два столбца, момент времени и кол-во проданных билетов и заполним их нулевыми значениями:
demo=# ALTER TABLE tickets_directions
demo-# ADD COLUMN last_ticket_time timestamp ;
ALTER TABLE
demo=# ALTER TABLE tickets_directions
demo-# ADD COLUMN tickets_num integer DEFAULT 0;
ALTER TABLE
DISTINCT говорит что дублирование направлений не будет
Создать временную таблицу, которая является аналагом таблицы перелеты и добавить первичные ключи:
demo=# CREATE TEMP TABLE ticket_flights_tmp AS
SELECT * FROM ticket_flights WITH NO DATA;
ALTER TABLE ticket_flights_tmp
ADD PRIMARY KEY ( ticket_no, flight_id );
CREATE TABLE AS
ALTER TABLE
demo=#
Добавить конструкцию, которая будет добавлять новую запись о продаже билетов и вести подсчет о продажах в таблице tickets_directions
demo=# WITH sell_ticket AS
( INSERT INTO ticket_flights_tmp
( ticket_no, flight_id, fare_conditions, amount )
VALUES ( '1234567890123', 30829, 'Economy', 12800 )
RETURNING *
)
UPDATE tickets_directions td
SET last_ticket_time = current_timestamp, tickets_num = tickets_num + 1
WHERE ( td.departure_city, arrival_city ) =
( SELECT departure_city, arrival_city
FROM flights_v
WHERE flight_id = ( SELECT flight_id FROM sell_ticket )
Из добавленной строки в таблицу ticket_flights_tmp берется только значение атрибута flight_id для соответсвия условию, исходя из этого соответствия с помощью подзапроса, добавляются атрибуты города отправления и прибытия из таблицы flights_v
Значения для двух других атрибутов устанавливаются явно ( время и счетчик + 1 )
DELETE
demo=# WITH delete_row AS
(
DELETE FROM aircraft_tmp
WHERE model ~ '^Бом'
RETURNING *
)
INSERT INTO aircrafts_log
SELECT dr.aircraft_code, dr.model, dr.range,
current_timestamp, 'DELETE'
FROM delete_row dr ;
ALTER DATABASE
https://postgrespro.ru/docs/postgresql/16/sql-alterdatabase
Изменяет атрибуты базы данных
ALLOW_CONNECTIONS разр_подключения
CONNECTION LIMIT предел_подключений
IS_TEMPLATE это_шаблон
Переименовать текущую базу данных нельзя. (Если вам нужно сделать это, сначала подключитесь к другой базе.)
ALTER DATABASE имя RENAME TO новое_имя
Чтобы сменить владельца базы, необходимо иметь право SET ROLE для новой роли-владельца, и кроме того, иметь право CREATEDB
ALTER DATABASE имя OWNER TO { новый_владелец | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER DATABASE имя SET TABLESPACE новое_табл_пространство
ALTER DATABASE имя REFRESH COLLATION VERSION
ALTER DATABASE имя SET параметр_конфигурации { TO | = } { значение | DEFAULT }
ALTER DATABASE имя SET параметр_конфигурации FROM CURRENT
ALTER DATABASE имя RESET параметр_конфигурации
ALTER DATABASE имя RESET ALL
Возможно связать параметры сеанса не с базой данных, а с определённой ролью; см. ALTER ROLE. В случае конфликта параметры на уровне роли переопределяют параметры на уровне базы данных
-- Отключение сканирования индекса по умолчанию в базе данных test:
ALTER DATABASE test SET enable_indexscan TO off;
Источники
Связанные темы
Оптимизация запросов в Postgresql
Установка и настройка MS SQL Server 2008 на Windows Server 2008 r
Системный каталог в Postgresql
Табличные пространства в Postgresql
Определение данных в Postgresql
Изменение временной зоны в Postgresql
Основные команды psql и sql Postgresql
Методы формирования соединений наборов строк
Агрегирование и группировка в Postgresql