Модификация данных

Андрей Аксенов

Contents

Вставка данных
- Массовая вставка данных
Изменение данных
Удаление данных
Возврат данных из измененных строк

В этой статье описывается, как вставлять, изменять и удалять данные таблиц в Greengage DB.

Предварительные требования

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Создайте новую базу данных и подключитесь к ней:

CREATE DATABASE marketplace;
\c marketplace

Затем создайте пустую таблицу products:

CREATE TABLE products
(
    id            INT,
    name          TEXT,
    category_name TEXT,
    price         NUMERIC(8, 2),
    stock         INT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

Создайте таблицу stg_products и заполните ее данными:

CREATE TABLE stg_products
(
    LIKE products
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

INSERT INTO stg_products (id, name, category_name, price, stock)
VALUES (1, 'Laptop', 'Electronics', 1200.00, 15),
       (2, 'Headphones', 'Electronics', 150.00, 40),
       (3, 'Smartphone', 'Electronics', 800.00, 30),
       (4, 'Coffee Maker', 'Home', 85.00, 20),
       (5, 'Desk Chair', 'Home', 200.00, 10),
       (6, 'Blender', 'Home', 55.00, 25),
       (7, 'T-Shirt', 'Clothing', 25.00, 100),
       (8, 'Jacket', 'Clothing', 60.00, 50);

Используйте команду INSERT для добавления строк в таблицу. Необходимо указать имя таблицы и значения для каждого столбца. Дополнительно можно задать имена столбцов в произвольном порядке. Если имена столбцов не указаны, значения нужно перечислить в том же порядке, в каком они определены в таблице, разделяя их запятыми.

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

INSERT INTO products (id, name, category_name, price, stock)
VALUES (1, 'Laptop', 'Electronics', 1200.00, 15);

Эта команда задает только значения для вставки:

INSERT INTO products
VALUES (2, 'Headphones', 'Electronics', 150.00, 40);

Вы можете вставить несколько строк с помощью одной команды:

INSERT INTO products (id, name, category_name, price, stock)
VALUES (3, 'Smartphone', 'Electronics', 800.00, 30),
       (4, 'Coffee Maker', 'Home', 85.00, 20),
       (5, 'Desk Chair', 'Home', 200.00, 10),
       (6, 'Blender', 'Home', 55.00, 25);

Обычно команда INSERT использует константные значения для создания новой строки. Однако вы также можете вставлять данные, полученные в результате запроса SELECT. Например, чтобы вставить строки из таблицы stg_products, относящиеся к категории Clothing, используйте следующую команду:

INSERT INTO products (id, name, category_name, price, stock)
SELECT *
FROM stg_products
WHERE category_name = 'Clothing';

ПРИМЕЧАНИЕ

В партиционированных таблицах данные обычно вставляются либо в родительскую таблицу, либо в одну из ее конечных дочерних партиций. Узнайте больше в разделе Как работает партиционирование.

Для эффективной вставки больших объемов данных используйте внешние таблицы или команду COPY. Эти методы более производительны, чем операторы INSERT для отдельных строк при загрузке больших наборов данных. Подробнее о массовой загрузке данных смотрите в следующих статьях:

Оптимизированные для добавления (AO) таблицы предназначены для массовой загрузки данных, поэтому использование одиночных команд INSERT для них не рекомендуется.

ПРИМЕЧАНИЕ

Greengage DB поддерживает не более 127 одновременных транзакций INSERT для одной оптимизированной для добавления таблицы.

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

Для изменения данных необходимо указать:

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

Следующий пример увеличивает складской остаток указанного товара на 5:

UPDATE products
SET stock = stock + 5
WHERE id = 2;

Следующий пример изменяет и цену, и остаток конкретного товара:

UPDATE products
SET price = 850.00,
    stock = 25
WHERE name = 'Smartphone';

Следующий пример увеличивает цену всех товаров в категории Home на 10%:

UPDATE products
SET price = price * 1.1
WHERE category_name = 'Home';

ПРИМЕЧАНИЕ

При использовании UPDATE в Greengage DB учитывайте следующие ограничения:

Планировщик GPORCA поддерживает изменение столбцов ключа распределения, в то время как планировщик Postgres — нет.
Если включено зеркалирование, в команде UPDATE нельзя использовать функции со свойством STABLE или VOLATILE.
Столбцы партиционирования не могут быть изменены.

Команда DELETE удаляет строки из таблицы. Используйте выражение WHERE для удаления только тех строк, которые соответствуют определенным критериям. Если выражение WHERE не указано, удаляются все строки таблицы.

Следующий пример удаляет все товары в категории Clothing:

DELETE
FROM products
WHERE category_name = 'Clothing';

Следующий пример удаляет все строки из таблицы products:

DELETE
FROM products;

Следующий пример удаляет все строки из таблицы stg_products с помощью команды TRUNCATE:

TRUNCATE TABLE stg_products;

ПРИМЕЧАНИЕ

В отличие от DELETE, команда TRUNCATE не выполняет сканирование таблицы, поэтому она не обрабатывает дочерние таблицы и правила ON DELETE. Команда затрагивает только строки в указанной таблице.

Тестовые таблицы

Удалите существующие таблицы products и stg_products:

DROP TABLE products;
DROP TABLE stg_products;

Создайте таблицу products, в которой столбец id имеет тип SERIAL:

CREATE TABLE products
(
    id            SERIAL,
    name          TEXT,
    category_name TEXT,
    price         NUMERIC(8, 2),
    stock         INT
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

Добавьте данные:

INSERT INTO products (name, category_name, price, stock)
VALUES ('Laptop', 'Electronics', 1200.00, 15),
       ('Headphones', 'Electronics', 150.00, 40),
       ('Smartphone', 'Electronics', 800.00, 30)
RETURNING id, name;

Иногда возникает необходимость видеть данные, изменяемые командой в процессе ее выполнения. Команды INSERT, UPDATE и DELETE поддерживают необязательное выражение RETURNING для этой цели.

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

Следующий пример вставляет несколько строк в таблицу products и возвращает автоматически назначенный id и name для каждой новой строки:

INSERT INTO products (name, category_name, price, stock)
VALUES ('Laptop', 'Electronics', 1200.00, 15),
       ('Headphones', 'Electronics', 150.00, 40),
       ('Smartphone', 'Electronics', 800.00, 30)
RETURNING id, name;

Результат выглядит следующим образом:

 id |    name
----+------------
  1 | Laptop
  2 | Headphones
  3 | Smartphone
(3 rows)

При использовании RETURNING с UPDATE возвращаемые данные отражают новые значения измененных строк:

UPDATE products
SET price = price * 1.1
WHERE price >= 500
RETURNING name, price AS new_price;

Результат должен выглядеть так:

    name    | new_price
------------+-----------
 Laptop     |   1320.00
 Smartphone |    880.00
(2 rows)

Следующий пример удаляет строку из таблицы stg_products и возвращает id и name удаленной строки:

DELETE
FROM stg_products
WHERE id = 3
RETURNING id, name;

Результат:

 id |    name
----+------------
  3 | Smartphone
(1 row)

ПРИМЕЧАНИЕ

Выражение DELETE … RETURNING не поддерживается для оптимизированных для добавления (AO) таблиц.

Пользовательские функции

Обзор команды SELECT

Модификация данных

Вставка данных

Массовая вставка данных

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

Удаление данных

Возврат данных из измененных строк