This topic describes how to use DML to insert, update, and delete data in AnalyticDB for PostgreSQL.

Insert data

Note If you want to insert a large amount of data, we recommend that you use an external table or execute a COPY statement to obtain better performance than that offered by an INSERT statement.

You can execute the INSERT statement to insert one or more rows into a table. Syntax:

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

For more information, visit INSERT.

Examples

Execute the following statement to insert a row into a table:

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

Execute the following statement to insert multiple rows into a table:

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

Execute the following statement to insert data into a table by using a scalar expression:

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

Update data

You can execute the UPDATE statement to update 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 ]

For more information, visit UPDATE.

Constraints

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

Example

Execute the following statement to change the rows in which the value in the price column is 5 to 10:

UPDATE products SET price = 10 WHERE price = 5;

Delete data

You can execute the DELETE statement to delete one or more rows from a table. Syntax:

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

For more information, visit DELETE.

Constraints

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

Examples

Execute the following statement to delete all rows in which the value in the price column is 10:

DELETE FROM products WHERE price = 10;

Execute the following statement to delete all rows from a table:

DELETE FROM products;

Truncate data

You can execute the TRUNCATE statement to quickly remove all rows from a table. The truncate operation does not truncate tables that are inherited from the table and the rewrite rules of the ON DELETE clause because the operation does not scan the table and only truncates rows in the table. Syntax:

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

For more information, visit TRUNCATE.

Example

Execute the following statement to remove all rows from a table named mytable:

TRUNCATE mytable;