{"id":5090,"date":"2013-11-19T08:04:50","date_gmt":"2013-11-19T16:04:50","guid":{"rendered":"http:\/\/www.chesnok.com\/daily\/?p=5090"},"modified":"2013-11-20T06:35:30","modified_gmt":"2013-11-20T14:35:30","slug":"everyday-postgres-insert-with-select","status":"publish","type":"post","link":"https:\/\/www.chesnok.com\/daily\/2013\/11\/19\/everyday-postgres-insert-with-select\/","title":{"rendered":"Everyday Postgres: INSERT with SELECT"},"content":{"rendered":"

This is a continuation of a series of posts about how I use Postgres everyday<\/a>.<\/em><\/p>\n

One of the most pleasant aspects of working with Postgres is coming across features that save me lots of typing. Whenever I see repetitive SQL queries, I now tend to assume there is a feature available that will help me out.<\/p>\n

One such feature is INSERT<\/code> using a SELECT<\/code>, and beyond that, using the output of a SELECT<\/code> statement in place of VALUES<\/code>.<\/p>\n

Take for example:<\/p>\n

INSERT into foo_bar (foo_id, bar_id) VALUES ((select id from foo where name = 'selena'), (select id from bar where type = 'name'));    \nINSERT into foo_bar (foo_id, bar_id) VALUES ((select id from foo where name = 'funny'), (select id from bar where type = 'name'));\nINSERT into foo_bar (foo_id, bar_id) VALUES ((select id from foo where name = 'chip'), (select id from bar where type = 'name'));\n<\/code><\/pre>\n
I think a lot of people know that this is possible. There are a few problems with it – like if the result of the WHERE<\/code> clause isn’t unique in both cases, you’d get an error. In this case, id<\/code> in both tables were surrogate keys, with both name<\/code> and type<\/code> being unique.<\/p>\n
What some people don’t realize is that you can SELECT, and then directly insert that into a table:<\/p>\n
INSERT into foo_bar (foo_id, bar_id) ( \n  SELECT foo.id, bar.id FROM foo CROSS JOIN bar \n    WHERE type = 'name' AND name IN ('selena', 'funny', 'chip') \n);\n<\/code><\/pre>\n
If the values you wanted to take from the table bar<\/code> were not all the same, the query would be considerably more complex. Given that I only am interested in a single value from bar<\/code>, and I want it joined with a series of explicitly selected values from foo<\/code>, this version of the query saves me a lot of typing.<\/p>\n
The bigger picture, however, was pointed out in the comments by Marko<\/a>:<\/p>\n
\n
VALUES is just a special type of SELECT and that INSERT writes the
\n  result of an arbitrary SELECT statement into the table. Consider:<\/p>\n
SELECT 1; vs. VALUES (1);<\/p>\n
SELECT * FROM (SELECT 1) sq; vs. SELECT * FROM (VALUES (1)) sq;<\/p>\n
INSERT INTO quix VALUES (1); vs. INSERT INTO quix SELECT 1;<\/p>\n
The reason VALUES is often used with INSERT is that many RDMBSs don’t
\n  support SELECT without a FROM clause, so using VALUES is more
\n  convenient. It’s also handy if you have a list of data you want to
\n  SELECT, e.g. VALUES (..), (..), (..);<\/p>\n<\/blockquote>\n
I may have referenced this feature a few times when breaking down functions used for reports in Socorro. It’s super convenient and saves quite a bit of typing! You can put any valid SQL query in there, including CTEs<\/a>. The documentation for INSERT<\/a> provides a few more examples.<\/p>\n","protected":false},"excerpt":{"rendered":"
This is a continuation of a series of posts about how I use Postgres everyday. One of the most pleasant aspects of working with Postgres is coming across features that save me lots of typing. Whenever I see repetitive SQL … Continue reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-5090","post","type-post","status-publish","format-standard","hentry","category-postgresql"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5090","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=5090"}],"version-history":[{"count":7,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5090\/revisions"}],"predecessor-version":[{"id":5099,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/5090\/revisions\/5099"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=5090"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=5090"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=5090"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}