Re: INSERT... WHERE

On Tue, Jan 15, 2013 at 5:26 AM, Robert James <srobertjames@gmail.com> wrote:

On 1/13/13, Chris Angelico <rosuav@gmail.com> wrote:

On Mon, Jan 14, 2013 at 3:37 PM, Robert James <srobertjames@gmail.com>
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

    select * from seltest;
can be simply written as
    table seltest;


Cheers,
Gavin
(Who is running away smartly,
as no likes a smart alec!)