Data manipulation

Andrey Aksenov

Contents

Insert data
- Bulk data insertion
Update data
Delete data
Return data from modified rows

This topic covers how to insert, update, and delete table data in Greengage DB.

Prerequisites

To follow the examples in this topic, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql. Create a new database and connect to it:

CREATE DATABASE marketplace;
\c marketplace

Then create the empty products table:

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

Create the stg_products table and populate it with data:

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

Use the INSERT command to add rows to a table. You must provide the table name and a value for each column. Optionally, you can specify the column names in any order. If you omit the column names, provide the values in the same order as the table’s columns, separated by commas.

To insert a new row while specifying the column names and their values, use the following command:

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

This command specifies only the values to insert:

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

You can insert multiple rows in a single command:

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

Usually, the INSERT command uses literal values (constants) for the new row. However, you can also insert the result of a SELECT query. For example, to insert rows from the stg_products table where the category is Clothing, use the following command:

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

NOTE

To insert data into a partitioned table, you typically target either the root partitioned table or one of its leaf child partitions. Learn more in Table partitioning overview.

To insert large amounts of data efficiently, use external tables or the COPY command. These methods are more performant than single-row INSERT statements for large datasets. For more information on bulk data loading, see:

Append-optimized tables are specifically designed for bulk loading, and single-row INSERT statements are not recommended for them.

NOTE

Greengage DB allows a maximum of 127 concurrent INSERT transactions into a single append-optimized table.

The UPDATE command modifies rows in a table. You can update individual rows, a subset of rows, or all rows. Each column can be updated independently without affecting other columns.

To perform an update, you must specify:

the table name and the columns to update;
the new values for the columns;
one or more conditions that determine which rows to update.

The following example increases the stock of a specific product by 5:

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

The following example updates both the price and stock of a specific product:

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

The following example increases the price of all products in the Home category by 10%:

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

NOTE

When using UPDATE in Greengage DB, be aware of the following restrictions:

While GPORCA supports updates to distribution key columns, the Postgres planner does not.
If mirroring is enabled, you cannot use STABLE or VOLATILE functions in an UPDATE statement.
Partitioning columns cannot be updated.

The DELETE command removes rows from a table. Use a WHERE clause to delete only the rows that match specific criteria. If no WHERE clause is specified, all rows in the table are deleted.

The following example deletes all products in the Clothing category:

DELETE
FROM products
WHERE category_name = 'Clothing';

The following example deletes all rows from the products table:

DELETE
FROM products;

The following example removes all rows from the stg_products table using TRUNCATE:

TRUNCATE TABLE stg_products;

NOTE

Unlike DELETE, TRUNCATE does not scan the table, so it does not process inherited tables or any ON DELETE rules. The command affects only the rows in the specified table.

Sample tables

Drop the existing products and stg_products tables:

DROP TABLE products;
DROP TABLE stg_products;

Create the products table with the id column of type SERIAL:

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

Insert data:

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;

Sometimes you need to see the data affected by a command as it is being modified. The INSERT, UPDATE, and DELETE commands support an optional RETURNING clause for this purpose.

The RETURNING clause can include the same expressions as a SELECT statement. It can reference column names from the command’s target table or use expressions based on those columns. Common shorthand is RETURNING *, which returns all columns of the target table in their defined order.

The following example inserts multiple rows into the products table and returns the automatically assigned id and the name of each new row:

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;

The result looks as follows:

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

When using RETURNING with UPDATE, the returned data reflects the new values of the modified rows:

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

The result should look like this:

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

The following example deletes a row from the stg_products table and returns the id and name of the deleted row:

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

The result is:

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

NOTE

The DELETE … RETURNING clause is not supported for append-optimized tables.

User-defined functions

SELECT command overview

Data manipulation

Insert data

Bulk data insertion

Update data

Delete data

Return data from modified rows