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.