Insert Data Into Tables Linked by Foreign Key

Question

I am using PostgreSQL.

Customer
==================
Customer_ID | Name

Order
==============================
Order_ID | Customer_ID | Price

To insert an order, here is what I need to do usually,

For example, "John" place "1.34" priced order.

(1) Get Customer_ID from Customer table, where name is "John"
(2) If there are no Customer_ID returned (There is no John), insert "John"
(3) Get Customer_ID from Customer table, where name is "John"
(4) Insert "Customer_ID" and "1.34" into Order table.

There are 4 SQL communication with database involved for this simple operation!!!

Is there any better way, which can be achievable using 1 SQL statement?

Wayne Conrad · Accepted Answer · 2010-01-05T23:51:06.427

78

You can do it in one sql statement for existing customers, 3 statements for new ones. All you have to do is be an optimist and act as though the customer already exists:

insert into "order" (customer_id, price) values \
((select customer_id from customer where name = 'John'), 12.34);

If the customer does not exist, you'll get an sql exception which text will be something like:

null value in column "customer_id" violates not-null constraint

(providing you made customer_id non-nullable, which I'm sure you did). When that exception occurs, insert the customer into the customer table and redo the insert into the order table:

insert into customer(name) values ('John');
insert into "order" (customer_id, price) values \
((select customer_id from customer where name = 'John'), 12.34);

Unless your business is growing at a rate that will make "where to put all the money" your only real problem, most of your inserts will be for existing customers. So, most of the time, the exception won't occur and you'll be done in one statement.

edited Jan 05 '10 at 23:51

answered Jan 05 '10 at 06:12

Wayne Conrad

103,207
26
155
191

Can you modify your answer to use "RETURNING" as depesz suggested? So that we need not perform additional query [select customer_id from customer where name = 'John'] – Cheok Yan Cheng Jan 05 '10 at 08:04
That's not an optimization I'd do. Here's why: First, keeping the code simple: If the second insert into order is identical to the first, you can use the same code to do both inserts. Second: There's not likely to be a real performance gain. Since you have just done the query on customer, and then the insert, Postgres will have the row and the appropriate index in cache, so the select from customer will be lightning fast. – Wayne Conrad Jan 05 '10 at 14:51
1

what if there 2 john in table – CSK May 06 '19 at 14:43
@CSK The where clause should be a primary key or candidate key, such that it selects a unique row. – Wayne Conrad May 06 '19 at 16:03

score 4 · Answer 2 · answered Jan 04 '10 at 08:15

Not with a regular statement, no.

What you can do is wrap the functionality in a PL/pgsql function (or another language, but PL/pgsql seems to be the most appropriate for this), and then just call that function. That means it'll still be a single statement to your app.

score 1 · Answer 3 · answered Jan 04 '10 at 08:49

1

Use stored procedures.

And even assuming you would want not to use stored procedures - there is at most 3 commands to be run, not 4. Second getting id is useless, as you can do "INSERT INTO ... RETURNING".

answered Jan 04 '10 at 08:49

Do you recommence of using procedures, instead of issuing at most 3 continuous SQL command? Will using stored procedures be faster/ – Cheok Yan Cheng Jan 04 '10 at 10:22
Unlikely to be faster. I.e. the difference shouldn't be measurable. At least assuming sensible network connection. – Jan 04 '10 at 12:10

Insert Data Into Tables Linked by Foreign Key

3 Answers3

Linked