This topic describes how to use DML to insert, update, and delete data in AnalyticDB for PostgreSQL.
Insert data
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}
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 ]
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 ]
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]
Example
Execute the following statement to remove all rows from a table named mytable:
TRUNCATE mytable;