On Tue, Jan 15, 2013 at 5:26 AM, Robert James <srobertjames@xxxxxxxxx> wrote: > On 1/13/13, Chris Angelico <rosuav@xxxxxxxxx> wrote: >> On Mon, Jan 14, 2013 at 3:37 PM, Robert James <srobertjames@xxxxxxxxx> >> wrote: >>> Thanks. But how do I do that where I have many literals? Something like: >>> >>> INSERT INTO seltest (id, a, b) SELECT (1,2,3),(4,5,6),(7,8,9) WHERE b >>> IN (SELECT ...) >> >> You can use WITH clauses in crazy ways with PostgreSQL. I haven't >> actually tried it, but you should be able to put your VALUES behind a >> WITH, then SELECT from that WHERE blah blah, and INSERT that SELECT. >> >> As they say, knock yourself out! :) >> >> ChrisA > > > I don't quite follow - could you please elaborate? Here's something that I just tried: postgres=# create table seltest (id int,a int,b int); CREATE TABLE postgres=# with v(id,a,b) as (values (1,2,3),(4,5,6),(7,8,9)) insert into seltest select * from v where b>4; INSERT 0 2 postgres=# select * from seltest; id | a | b ----+---+--- 4 | 5 | 6 7 | 8 | 9 (2 rows) Effectively, the values() statement is given a name (and a set of column names), and can then be selected from like any other table or CTE. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
