COPY from SELECT query result ? (in postgresql)
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 |开发者_JAVA技巧 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.
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'
精彩评论