INSERT¶

INSERT inserts one or more rows into a table.

Tip

To bulk load data into existing tables, the COPY FROM command performs better than INSERT.
To load Parquet or ORC files, see CREATE FOREIGN TABLE

Permissions¶

The role must have the INSERT permission to the destination table.

Syntax¶

insert_statement ::=

 INSERT INTO [schema_name.]table_name
     [ ( column_name [, ... ] ) ]
 query ;

schema_name ::= identifier

table_name ::= identifier

column_name ::= identifier

Elements¶

Parameter	Description
`[schema_name.]table_name`	Table to insert data into
`( column_name [, ...] )`	A comma separated list of column names that specifies the destination columns of the insert.
`query`	A SELECT or VALUES statement. Each value must match the data type of its destination column. The values must also match the order of the table or columns if specified with ` (column_name, …)`.

Examples¶

Inserting a single row¶

INSERT INTO cool_animals VALUES (5, 'fox', 15);

Inserting into rows with default values¶

The id column is an IDENTITY column, and has a default, so it can be omitted.

INSERT INTO cool_animals(name, weight) VALUES ('fox', 15);

Changing column order¶

INSERT INTO cool_animals(name, weight, id) VALUES ('possum', 7, 6);

Inserting multiple rows¶

INSERT INTO cool_animals(name, weight) VALUES ('koala', 20), ('lemur', 6), ('kiwi', 3);

Import data from other tables¶

INSERT can be used to insert data obtained from queries on other tables, including foreign tables.

For example,

farm=> SELECT name, weight FROM all_animals
.      WHERE region = 'Australia';

name     | weight
---------+-------
Kangaroo | 120
Koala    | 20
Wombat   | 60
Platypus | 5
Wallaby  | 35
Echidna  | 8
Dingo    | 25

INSERT INTO cool_animals(name,weight)
  SELECT name, weight FROM all_animals
  WHERE region = 'Australia';

Warning

The SELECT statement decrypts information by default. When executing INSERT INTO TABLE AS SELECT, encrypted information will appear as clear text in the newly created table.

Inserting data with positional placeholders¶

When preparing an INSERT statement for loading data over the network (for example, from a Python or Java application, use positional placeholders.

Example using Python:

data = [["Kangaroo", 120], ["Koala", 20], ["Platypus", 5]]
data_len = len(data)

insert_stmt = 'INSERT INTO cool_animals (name, weight) VALUES (?, ?)'
con.executemany(insert_stmt, data)

Note

The executemany method is used only for parametrized statements like INSERT. Running multiple SELECT queries or other statements this way is not supported.