Изменение данных

CREATE TEMP TABLE aircrafts_tmp AS
  SELECT * FROM aircrafts WITH NO DATA;

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

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

Вот пример способа их задать

CREATE TEMP TABLE aircrafts_tmp
( LIKE aircrafts INCLUDING CONSTRAINTS INCLUDING INDEXES );

 предложения RETURNING, которое можно задать для команд INSERT, UPDATE и DELETE,чтобы вернуть как результат запроса модицицированные строки

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

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;

Дальше автор рассматривает разные про вариации этого запроса для UPDATE и DELETE

пример

WITH min_ranges AS
( SELECT aircraft_code,
         rank() OVER (
           PARTITION BY left( model, 6 )
           ORDER BY range
         ) AS rank
    FROM aircrafts_tmp
    WHERE model ~ '^Airbus' OR model ~ '^Boeing'
)
DELETE FROM aircrafts_tmp a
  USING min_ranges mr
  WHERE a.aircraft_code = mr.aircraft_code
    AND mr.rank = 1
  RETURNING *;

Тут неявное объединение таблиц происходит с помощью ключевого слова USING. Оно позволяет вам объединить таблицы по столбцу без использования явного оператора JOIN.

Postgres, ключевое слово USING используется в команде JOIN, чтобы указать, по каким столбцам происходит объединение двух таблиц. Давай рассмотрим пример:

Предположим, у нас есть две таблицы: users и orders. В таблице users у нас есть столбец user_id, а в таблице orders есть столбец user_id, который связывает заказы с пользователями. Мы хотим объединить эти таблицы по столбцу user_id.

Вот как это выглядит с использованием USING:

SELECT *
FROM users
JOIN orders USING (user_id);

Этот запрос объединяет таблицы users и orders по столбцу user_id. USING (user_id) говорит базе данных, что мы хотим объединить таблицы по столбцу user_id.

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

команда TRUNCATE и DELETE

Вот как они отличаются:

1. TRUNCATE:
2. Быстрее: TRUNCATE обычно выполняется намного быстрее, чем DELETE, потому что он не выполняет запись логов для каждой удаленной строки, а просто освобождает место в таблице.
3. Не логирует каждую операцию: Поскольку TRUNCATE не логирует каждую операцию, он не может быть откатан в случае ошибки.
4. Не триггерит каскадное удаление и триггеры: TRUNCATE не запускает триггеры и каскадное удаление (если таковые настроены на таблицу), в отличие от DELETE.
5. DELETE:
6. Более гибкий: DELETE более гибкий и может быть использован с условиями для удаления определенных строк.
7. Логирует каждую операцию: Каждая операция DELETE логируется, что означает, что она может быть откатана в случае ошибки или если это необходимо для восстановления данных.
8. Триггерит каскадное удаление и триггеры: DELETE может запускать триггеры и каскадное удаление, что может быть полезно в некоторых сценариях.

Таким образом, если вы хотите быстро очистить таблицу без возможности восстановления данных и без выполнения триггеров, то TRUNCATE может быть предпочтительным выбором. Однако, если вам нужно удалить только определенные строки, выполнить триггеры или каскадное удаление, то следует использовать DELETE.

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 *; 

Быстрый способ сделать UPSERT

Значения берутся из специальной таблицы excluded, которая поддерживается самой СУБД. В этой таблице хранятся все строки, предлагаемые для вставки в рамках текущей команды INSERT. Вот это значение — excluded.model. Значение столбца range также будет обновлено, но его новое зна- чение — excluded.range — совпадает со старым.