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

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

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

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

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

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

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

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

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?

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

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&gt; 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.

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

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

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

On 15/01/13 09:43, Chris Angelico wrote:

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 Sun, Jan 13, 2013 at 10:37 PM, Robert James <srobertjames@gmail.com>wrote:

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

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

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

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

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

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 <darren@darrenduncan.net>

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.fonville@gmail.com>