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