INSERT INTO ​

INSERT adds rows to a table. Values can come from literal expressions, a SELECT, column defaults, a registered input format or files supplied by the native client.

An explicit column list maps input values to the named columns. Omitted columns receive their SQL DEFAULT expression or the applicable default behavior. Declared types and constraints validate every inserted value.

Syntax ​

INSERT [OR ABORT | OR FAIL | OR IGNORE | OR REPLACE | OR ROLLBACK]
INTO table [(column, ...)]
VALUES (value, ...) [, ...]
[ON CONFLICT ...]
[RETURNING expression, ...]

INSERT INTO table [(column, ...)] SELECT ...
INSERT INTO table DEFAULT VALUES

INSERT INTO table [(column, ...)] [SETTINGS ...] FORMAT name
<data>

INSERT INTO table [(column, ...)]
FROM INFILE 'path' [COMPRESSION 'type'] [SETTINGS ...] [FORMAT name]

Examples ​

Inserting selected columns ​

CREATE TABLE products
(
    id INTEGER PRIMARY KEY,
    name String NOT NULL,
    price Decimal(18, 2) DEFAULT '0.00'
);
INSERT INTO products(id, name) VALUES (1, 'Notebook'), (2, 'Pen');

SELECT id, name, price
FROM products
ORDER BY id
FORMAT csv;

1,"Notebook",0.00
2,"Pen",0.00

Inserting a query result ​

CREATE TABLE archived_products AS SELECT * FROM products WHERE 0;
INSERT INTO archived_products
SELECT * FROM products WHERE id = 1;

SELECT id, name
FROM archived_products
FORMAT csv;

1,"Notebook"

Updating a conflicting row ​

excluded exposes the proposed inserted row inside DO UPDATE:

INSERT INTO products(id, name, price)
VALUES (1, 'Notebook', '12.50')
ON CONFLICT(id) DO UPDATE SET price = excluded.price
RETURNING id, price
FORMAT csv;

1,12.50

ON CONFLICT ... DO NOTHING skips a conflicting row. Multiple conflict clauses and the listed OR conflict policies are supported.

Formatted input ​

INSERT ... FORMAT decodes input rows with a registered format. Place SETTINGS before the format name: the remaining request bytes are data, including any semicolons. Send later SQL in a separate request.

CREATE TABLE imported_orders(id UInt32, amount UInt32);

INSERT INTO imported_orders FORMAT csv
1,100
2,60

SELECT id, amount
FROM imported_orders
ORDER BY id
FORMAT csv;

1,100
2,60

Formatted input is the last statement in its request and does not accept RETURNING or ON CONFLICT clauses. The OR conflict policies still apply. Omitted format fields can use implicit type defaults according to the selected format and settings.

FROM INFILE reads files on the native client's machine and supplies their decoded rows to the server. See bulk inserts and the format reference for supported sources and options.