INSERT... WHERE

[Robert James]

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


--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Scott Marlowe]

On Sun, Jan 13, 2013 at 7:00 PM, Robert James <srober...@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;

[Ian Lawrence Barwick]

2013/1/14 Robert James <srober...@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

[Robert James]

On 1/13/13, Ian Lawrence Barwick <bar...@gmail.com> wrote:
> 2013/1/14 Robert James <srober...@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 ...)

[Chris Angelico]

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

[Robert James]

On 1/13/13, Chris Angelico <ros...@gmail.com> wrote:
> On Mon, Jan 14, 2013 at 3:37 PM, Robert James <srober...@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?

[Chris Angelico]

On Tue, Jan 15, 2013 at 5:26 AM, Robert James <srober...@gmail.com> wrote:
> On 1/13/13, Chris Angelico <ros...@gmail.com> wrote:
>> On Mon, Jan 14, 2013 at 3:37 PM, Robert James <srober...@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

[Gavin Flower]

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


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

[Abel Abraham Camarillo Ojeda]

insert into seltest (id, a, b) select a from (values (1, 2, 3), (4, 5, 6), ...) as t(a, b, c) join t2 on = ...;

[Darren Duncan]

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

[Serge Fonville]

Hi,

Based on my understanding of the problem, would this be possible to solve with a MERGE statement?

HTH

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl

Convince Microsoft!

They need to add TRUNCATE PARTITION in SQL Server

https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table

2013/1/15 Darren Duncan <dar...@darrenduncan.net>

[Serge Fonville]

Hmm, nvm :-(

PostgreSQL does not yet support MERGE...

Kind regards/met vriendelijke groet,

Serge Fonville

http://www.sergefonville.nl

Convince Microsoft!

They need to add TRUNCATE PARTITION in SQL Server

https://connect.microsoft.com/SQLServer/feedback/details/417926/truncate-partition-of-partitioned-table

2013/1/15 Serge Fonville <serge.f...@gmail.com>

[David Johnston]

Robert James wrote
> On 1/13/13, Chris Angelico &lt;

> rosuav@

> &gt; wrote:
>> On Mon, Jan 14, 2013 at 3:37 PM, Robert James &lt;

> srobertjames@

> &gt;

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