I`m building a database with Postgres 9.3 as a backend, having 3 tables:
table1 (user_id, username, name, surname, emp_date)
table2 (pass_id, user_id, password)
table3 (user_dt_id, user_id, adress, city, phone)
As can be seen table2
and table3
are child tables of table1
.
I can extract the user_id
of a newly inserted row in table1
(parent):
INSERT INTO "table1" (default,'johnee','john','smith',default) RETURNING userid;
I need to insert the newly extracted id (from table1
) into user_id
columns of table2
and table3
along with other data unique for those tables. Basically 3 X INSERT ...
How do I do that?
PostgreSQL INSERT Multiple Rows First, specify the name of the table that you want to insert data after the INSERT INTO keywords. Second, list the required columns or all columns of the table in parentheses that follow the table name. Third, supply a comma-separated list of rows after the VALUES keyword.
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
When using Postgres if you do need writes exceeding 10,000s of INSERT s per second we turn to the Postgres COPY utility for bulk loading. COPY is capable of handling 100,000s of writes per second. Even without a sustained high write throughput COPY can be handy to quickly ingest a very large set of data.
PostgreSQL used the OID internally as a primary key for its system tables. Typically, the INSERT statement returns OID with value 0. The count is the number of rows that the INSERT statement inserted successfully.
Use data-modifying CTEs to chain your three INSERTs. Something like this:
WITH ins1 AS (
INSERT INTO table1 (username, name, surname)
VALUES ('johnee','john','smith')
RETURNING user_id
)
, ins2 AS (
INSERT INTO table2 (user_id, password)
SELECT ins1.user_id, 'secret'
FROM ins1 -- nothing to return here
)
INSERT INTO table3 (user_id, adress, city, phone)
SELECT ins1.user_id, ...
FROM ins1
RETURNING user_id;
It's typically best to add a column definition list for INSERT
s (except for special cases). Else, if the table structure changes, your code might break in surprising ways.
I omitted columns where you would just enter DEFAULT
. Defaults are inserted automatically. Shorter, same result.
The final, optional RETURNING
returns the resulting user_id
- obviously from a sequence or some other default. It's actually the user_id
from table3
, but that's the same unless you have some triggers or other magic interfering.
More about data-modifying (a.k.a. "writable") CTEs:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With