This topic describes how to insert, update, delete, and truncate data in AnalyticDB for PostgreSQL.
Insert data
INSERT
statement inserts one or more rows into a table. Syntax: INSERT INTO table [( column [, ...] )]
{DEFAULT VALUES | VALUES ( {expression | DEFAULT} [, ...] )
[, ...] | query}
For more information, visit INSERT.
Examples:
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
INSERT INTO products (product_no, name, price) VALUES
(1, 'Cheese', 9.99),
(2, 'Bread', 1.99),
(3, 'Milk', 2.99);
INSERT INTO films SELECT * FROM tmp_films WHERE date_prod <
'2016-05-07';
Update data
UPDATE
statement updates 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.
Limits:
- 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:
UPDATE products SET price = 10 WHERE price = 5;
Delete data
DELETE
statement deletes 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.
Limits:
- STABLE and VOLATILE functions are not allowed.
- RETURNING clauses are not allowed.
Examples:
DELETE FROM products WHERE price = 10;
DELETE FROM products;
Truncate data
TRUNCATE
statement clears a table. Syntax: TRUNCATE [TABLE] name [, ...] [CASCADE | RESTRICT]
For more information, visit TRUNCAT.
Example:
In the following example, the mytable table is cleared by using a TRUNCATE statement. The TRUNCATE statement does not scan the mytable table. Therefore, it does not process the child tables of the mytable table or the rewrite rules specified by ON DELETE clauses.
TRUNCATE mytable;