INSERT inserts one or more rows into a table.


  • 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


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


insert_statement ::=

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

schema_name ::= identifier

table_name ::= identifier

column_name ::= identifier





Table to insert data into

( column_name [, ...] )

A comma separated list of column names that specifies the destination columns of the insert.


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, …)`.


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';


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)


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