Thread: INSERT... WHERE

I have a lot of VALUES I want to INSERT.  But only a subset of them -
only those that meet a JOIN criteria involving another table.

I could INSERT them into a temp table, and then do a SELECT INTO.  But
do I need to do that?  Is there any way to do a INSERT... VALUES ...
WHERE...

On Sun, Jan 13, 2013 at 7:00 PM, Robert James <srobertjames@gmail.com> wrote:
> I have a lot of VALUES I want to INSERT.  But only a subset of them -
> only those that meet a JOIN criteria involving another table.
>
> I could INSERT them into a temp table, and then do a SELECT INTO.  But
> do I need to do that?  Is there any way to do a INSERT... VALUES ...
> WHERE...

What you're probably looking for is an insert .. select statment like so:

insert into tablea (col1, col2, col3) select colx, coly, colz from
tableb where somecondition;

2013/1/14 Robert James <srobertjames@gmail.com>:
> I have a lot of VALUES I want to INSERT.  But only a subset of them -
> only those that meet a JOIN criteria involving another table.
>
> I could INSERT them into a temp table, and then do a SELECT INTO.  But
> do I need to do that?  Is there any way to do a INSERT... VALUES ...
> WHERE...

INSERT INTO ... SELECT is what you are looking for.

Simple example:

  CREATE TABLE seltest (id INT);
  INSERT INTO seltest (id) SELECT 1;


HTH

Ian Lawrence Barwick

On 1/13/13, Ian Lawrence Barwick <barwick@gmail.com> wrote:
> 2013/1/14 Robert James <srobertjames@gmail.com>:
>> I have a lot of VALUES I want to INSERT.  But only a subset of them -
>> only those that meet a JOIN criteria involving another table.
>>
>> I could INSERT them into a temp table, and then do a SELECT INTO.  But
>> do I need to do that?  Is there any way to do a INSERT... VALUES ...
>> WHERE...
>
> INSERT INTO ... SELECT is what you are looking for.
>
> Simple example:
>
>   CREATE TABLE seltest (id INT);
>   INSERT INTO seltest (id) SELECT 1;


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 ...)

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

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?

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

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!)

On 2013.01.13 6:00 PM, Robert James wrote:
> I have a lot of VALUES I want to INSERT.  But only a subset of them -
> only those that meet a JOIN criteria involving another table.
>
> I could INSERT them into a temp table, and then do a SELECT INTO.  But
> do I need to do that?  Is there any way to do a INSERT... VALUES ...
> WHERE...

As per my reply to your "Bulk INSERT with individual failure" thread, for what
you want to do a temporary staging table (or several) with minimal constraints
is truly your best option to accomplish your goal.

Having a large amount of literals in an INSERT statement like discussed is a bad
idea.

If you're loading from a file, just use some bulk load feature that bypasses the
need for INSERT statements, into some temp file matching the format of the file.

If you're loading from a program, use a prepared single-row INSERT statement,
bundling batches of say a few thousand rows at a time into a single transaction.

Then pretend your newly populated temp table was your original source, but now
it is visible to SQL, and you can load your regular tables from the temp
table(s) much more nicely.

-- Darren Duncan

Robert James wrote
> On 1/13/13, Chris Angelico <

> rosuav@

> > wrote:
>> On Mon, Jan 14, 2013 at 3:37 PM, Robert James <

> srobertjames@

> >
>> 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?

INSERT INTO table_abc (a, b, c)

WITH values_to_insert (a, b, c) AS (
    VALUES (1,2,3), (4,5,6), (7,8,9)
)

SELECT a, b, c
FROM values_to_insert
WHERE a = 4

;

See:  http://www.postgresql.org/docs/9.2/interactive/sql-values.html
<http://www.postgresql.org/docs/9.2/interactive/sql-values.html>   for more
detail on "VALUES".  Basically it provides a way to build an on-the-fly
table and can be used wherever a normal table can be used (though usually it
takes some aliasing to get meaningful names).

David J.





--
View this message in context: http://postgresql.1045698.n5.nabble.com/INSERT-WHERE-tp5740009p5740164.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.