Insert into … values ( SELECT … FROM … ) in postgresql?

Question

I am working with Postgresql database. I have one database which is - db1 and I have one table inside this database which is App1.

I need to make a select query against this App1 table which is in db1 and then whatever results I am getting back, I need to insert them in App2 table as it is which is in another database db2.

Below is my query which I am running against App1 table which is in db1 -

select col1, col2 from App1 limit 5

Now is there any way I can use Insert statement along with above SELECT statement which can insert into App2 table for me automatically which is in db2?

Something along this line -

Insert into … values ( SELECT … FROM … )

Is this possible to do in Postgresql as both the tables are in different database?

really? is it so very hard to look at the postgres docs? postgresql.org/docs/8.1/static/sql-insert.html — Marc B
– Marc B, Commented Mar 3, 2014 at 4:49
@MarcB: But both of the tables are in different database so this will still work? — AKIWEB
– AKIWEB, Commented Mar 3, 2014 at 4:50
as long as the user you're running this under has the appropriate rights, postgres won't care. — Marc B
– Marc B, Commented Mar 3, 2014 at 4:51
select db1.table1.field, db2.table2.otherfield is perfectly legitimate SQL in pretty much any sql database worth using. — Marc B
– Marc B, Commented Mar 3, 2014 at 4:51
@MarcB: please don't link to outdated versions (you can use current instead of the version number in the URL) — user330315
– user330315, Commented Mar 3, 2014 at 7:04

Craig Ringer · Accepted Answer · 2014-03-03 05:05:19Z

3

To do this between databases you must use the foreign data wrapper postgres_fdw or use dblink. See the documentation. PostgreSQL doesn't support cross-database SELECT.

Often, if you find yourself wanting to do this, you should be using separate schemas in a single database instead.

BTW, it's generally:

INSERT INTO ... SELECT ...

i.e. there's no subquery, no parentheses. That's because the VALUES clause is actually a standalone statement too:

INSERT INTO ... VALUES ...

observe:

regress=> VALUES (1,2), (2,3);
 column1 | column2 
---------+---------
       1 |       2
       2 |       3
(2 rows)

edited Mar 3, 2014 at 5:05

answered Mar 3, 2014 at 4:57

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

AKIWEB Over a year ago

Thanks for suggestion.. So what should I do for my use case? Should I get the data from db1 and put it in response object and then iterate the object and then insert into db2?

Craig Ringer Over a year ago

You can do it via the client side as you describe, or you can use dblink or postgres_fdw, as I linked to above, to do it via an INSERT INTO ... SELECT query

Collectives™ on Stack Overflow

Insert into … values ( SELECT … FROM … ) in postgresql?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related