INSERT INTO statement
Overview
The INSERT INTO
statement adds new rows to an existing table using a SELECT
statement or explicitly stating input values.
Syntax
The basic syntax for INSERT INTO
is as follows:
INSERT INTO table_name[(columns_order)] VALUES (value 1), (value 2), ... (value n);
or
INSERT INTO table_name[(columns_order)] select_statement;
Where:
-
table_name
: The table name. -
(columns_order)
: Optional column order in the table. -
select_statement
: ASELECT
statement that provides the data to insert. For example,SELECT (value 1), (value 2), ... (value n);
.
Examples
Case #1: Basic Usage
Let’s create a distance table.
CREATE TABLE distance_table (distance INT, unit TEXT);
We’ll then insert values representing different distance measurements.
INSERT INTO distance_table (distance, unit) VALUES
(2000, 'kilometers'),
(1000, 'meters'),
(5, 'miles');
Display the table using the query below.
SELECT * FROM distance_table;
You’ll get the following output.
distance | unit
----------+------------
2000 | kilometers
1000 | meters
5 | miles
Case #2: Switching Column Orders
In this example, we create a weight
table with columns kilo
and gram
. Then, we add data using the default column order (kilo
, gram
).
CREATE TABLE weight(kilo INT, gram INT);
INSERT INTO weight SELECT 45, 52;
Next, we insert data with a switched column order (gram
, kilo
).
INSERT INTO weight(gram, kilo) SELECT 45, 52;
Let’s see what’s on the table.
SELECT * FROM weight;
The output displays the first row with data from the default column order and the second row with reversed data from the switched column order.
kilo | gram
------+------
45 | 52
52 | 45
Case #3: Inserting with a NULL Column
In this case, we only insert data into a gram
column while leaving the kilo
column as NULL.
CREATE TABLE weight(kilo INT, gram INT);
INSERT INTO weight(gram) SELECT 45;
Display the table.
SELECT * FROM weight;
The output shows the first column (kilo
) as NULL.
kilo | gram
------+------
| 45
Case #4: Error Handling - Too Many Values
In this case, an error occurs when attempting to insert more values than the specified columns in the table.
CREATE TABLE weight(kilo INT, gram INT);
INSERT INTO weight SELECT 45, 52, 30;
The error result indicates that the table weight
has only 2 columns.
ERROR: INSERT has more expressions than target columns
Case #5: Error Handling - Inserting NULL into a Not-Nullable Column
In this example, you insert data into a gram
column and a NULL value into a kilo
column.
CREATE TABLE weight(kilo INT, gram INT);
INSERT INTO weight(gram) SELECT 30;
You will get an error result as you try to input data only in the gram
column, leaving the kilo
column empty, where there is a NOT NULL constraint.
ERROR: null value in column "kilo" of relation "weight" violates not-null constraint