Use DML to insert update delete and truncate data - AnalyticDB

AnalyticDB for PostgreSQL supports INSERT, UPDATE, DELETE, and TRUNCATE statements to manage rows in your tables. This topic covers the syntax, limitations, and examples for each operation.

Insert data

Note

For large-scale data loads, use an external table or a COPY statement instead of INSERT for better performance.

Use the INSERT statement to add one or more rows to a table.

Syntax

INSERT INTO table [( column [, ...] )]
   {DEFAULT VALUES | VALUES ( {expression | DEFAULT} [, ...] )
   [, ...] | query}

Examples

All examples use a products table with columns product_no, name, and price.

Insert a single row:

INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

Insert multiple rows in one statement:

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

Insert rows from a query result:

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2016-05-07';

Update data

Use the UPDATE statement to modify one or more rows in a table.

Syntax

UPDATE [ONLY] table [[AS] alias]
   SET {column = {expression | DEFAULT} |
   (column [, ...]) = ({expression | DEFAULT} [, ...])} [, ...]
   [FROM fromlist]
   [WHERE condition | WHERE CURRENT OF cursor_name]

Limitations

The column defined as the distribution key cannot be updated.
The column defined as the partition key cannot be updated.
STABLE and VOLATILE functions are not allowed.
RETURNING clauses are not allowed.

Example

Set the price to 10 for all rows where the current price is 5:

UPDATE products SET price = 10 WHERE price = 5;

Delete data

Use the DELETE statement to remove one or more rows from a table.

Syntax

DELETE FROM [ONLY] table [[AS] alias]
      [USING usinglist]
      [WHERE condition | WHERE CURRENT OF cursor_name]

Limitations

STABLE and VOLATILE functions are not allowed.
RETURNING clauses are not allowed.

Examples

Delete all rows where the price is 10:

DELETE FROM products WHERE price = 10;

Delete all rows from the table:

DELETE FROM products;

Truncate data

Use the TRUNCATE statement to quickly remove all rows from a table without scanning it row by row.

Important

TRUNCATE does not apply the rewrite rules of ON DELETE clauses and does not cascade to tables that inherit from the truncated table. Use DELETE if you need these behaviors.

Syntax

TRUNCATE [TABLE] name [, ...] [CASCADE | RESTRICT]

Example

Remove all rows from a table named mytable:

TRUNCATE mytable;