COPY from SELECT query result ? (in postgresql)

Question

In postgresql, I need to insert data to, say table B from sql query that get query from table A and table C. This sample is the best that I can get:

SELECT (SELECT bic FROM bank where name='Bank Foo'), curr_id FROM currency where alpha_id = 'AUD' OR alpha_id ='NZD' OR alpha_id ='SGD';

The result is something like this:

 ?column? | curr_id
----------+---------
 xyz      |      9
 xyz      |     66
 xyz      |      4

My question are :
1) how to make the result more prettier, instead of ?column? the field should show 'bic'?
2) To insert data to table B, I think I just use COPY but I have no idea how to get data from a query statement like above. Is it possible? Any better suggestion are welcomed.
(Usually I use COPY from csv file, ok I know you guys can say just copy paste the result to csv file and COPY but that means I don't learn something new :)

Thank you in advance.

nos · Accepted Answer · 2010-11-14 13:28:21Z

4

Give the column an alias, SELECT (SELECT bic FROM bank where name='Bank Foo') bic, curr_id ...
INSERT INTO can take a query. e.g. INSERT INTO B SELECT (SELECT bic FROM bank where name='Bank Foo'), curr_id FROM currency where alpha_id = 'AUD' OR alpha_id ='NZD' OR alpha_id ='SGD'

answered Nov 14, 2010 at 13:28

nos

231k60 gold badges436 silver badges516 bronze badges

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

Collectives™ on Stack Overflow

COPY from SELECT query result ? (in postgresql)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related