Let's say we have a simple table called person:
The most basic insert involves inserting all values in the table:
If you want to insert only specific columns, you need to explicitly indicate which columns:
Note that if any constraints exist on the table , such as NOT NULL, you will be required to include those columns in either case.
INSERT data and RETURING values
If you are inserting data into a table with an auto increment column and if you want to get the value of the auto increment column.
Say you have a table called
If you want to insert data into
my_table and get the id of that row:
Above query will return the id of the row where the new record was inserted.
Insert data using COPY
COPY is PostgreSQL's bulk-insert mechanism. It's a convenient way to transfer data between files and tables, but it's also far faster than
INSERT when adding more than a few thousand rows at a time.
Let's begin by creating sample data file.
And we need a two column table into which this data can be imported into.
Now the actual copy operation, this will create six records in the table.
Instead of using a file on disk, can insert data from
Also you can copy data from a table to file as below:
For more details on COPY you can check here
Insert from select
You can insert data in a table as the result of a select statement:
Note that the projection of the select must match the columns required for the insert. In this case, the
tmp_person table has the same columns as
Inserting multiple rows
You can insert multiple rows in the database at the same time:
SELECT data into file.
You can COPY table and paste it into a file.
UPSERT - INSERT ... ON CONFLICT DO UPDATE...
since version 9.5 postgres offers
UPSERT functionality with
Say you have a table called my_table, created in several previous examples. We insert a row, returning PK value of inserted row:
Now if we try to insert row with existing unique key it will raise an exception:
Upsert functionality offers ability to insert it anyway, solving the conflict:
This modified text is an extract of the original Stack Overflow Documentation created by following contributors
and released under CC BY-SA 3.0