# Databases : PostgreSQL : Data ## INSERT ```sql INSERT INTO <table> [( <column1>, ... )] -- if column list omitted, defaults to all columns in definition order { DEFAULT VALUES | VALUES ( <row1_value1>, ... ) } [ ON CONFLICT DO NOTHING DO UPDATE SET ... ] [ RETURNING { * | <exp> [ [ AS ] <alias> ] } ] ``` **Result** ``` INSERT 0 6 # the second is num rows inserted (ignore first number) ``` ## UPDATE ```sql UPDATE TABLE <table> SET <column1> = <exp1>, ... [WHERE <exp>] -- update from another table (ANSI) UPDATE TABLE <table1> SET <column> = ( SELECT <column> FROM <table2> WHERE <table1>.<column> = <table2>.<column> ) WHERE EXISTS ( SELECT <column> FROM <table2> WHERE <table1>.<column> = <table2>.<column> ) -- update from another table (ANSI) UPDATE <table1> SET <column> = <table2>.<column> FROM <table2> WHERE <table1>.<column> = <table2>.<column> ``` Returns num rows modified. #### Performance PostgreSQL creates a new version of a row each time a value is updated. Therefore, adding a column and filling it with values temporarily doubles the table size. **Trick:** Copy table and add populated column during operation, then rename tables. ```sql CREATE TABLE table2 AS SELECT *, <exp> as <new_column> FROM table1 ALTER TABLE table1 RENAME TO prev ALTER TABLE table2 RENAME TO table1 ``` ## DELETE ```sql DELETE FROM <table> [USING <table2>] [WHERE <exp>] TRUNCATE <table> ``` ## RETURNING Returns specified columns for modified rows. ```sql INSERT|UPDATE|DELETE ... RETURNING * INSERT|UPDATE|DELETE ... RETURNING <column1>, ... ``` ## Working With Files ```sql COPY <table> TO <path> WITH ( FORMAT CSV, HEADER, DELIMETER '|' ) COPY <table> FROM <path> ... ```