从选择查询结果中复制?(在postgresql)

时间:2021-09-15 23:06:57

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:

在postgresql中,我需要插入数据,比如sql查询中的表B,从表A和表c中获取查询。这个例子是我能得到的最好的:

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';

选择(在银行中选择bic(名称=' bank Foo'),在货币中选择curr_id (alpha_id =' AUD' OR alpha_id ='NZD'或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 :)

我的问题是:1)如何让结果更漂亮,而不是?这个字段应该显示" bic " ?2)为了向表B插入数据,我认为我只是使用了COPY,但是我不知道如何从上面的查询语句中获取数据。是可能的吗?欢迎任何更好的建议。(通常我用的是csv文件的拷贝,好吧,我知道你们可以把结果复制粘贴到csv文件中,但这意味着我没有学到新的东西:)

Thank you in advance.

提前谢谢你。

1 个解决方案

#1


4  

  1. Give the column an alias, SELECT (SELECT bic FROM bank where name='Bank Foo') bic, curr_id ...

    给列一个别名,选择(从bank中选择bic,其中name=' bank Foo') bic, curr_id…

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

    插入可以接受查询。例如,插入B SELECT(从银行中选择bic,其中name=' bank Foo'),从货币中选择curr_id,其中alpha_id =' AUD' OR alpha_id ='NZD'或alpha_id ='SGD'

#1


4  

  1. Give the column an alias, SELECT (SELECT bic FROM bank where name='Bank Foo') bic, curr_id ...

    给列一个别名,选择(从bank中选择bic,其中name=' bank Foo') bic, curr_id…

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

    插入可以接受查询。例如,插入B SELECT(从银行中选择bic,其中name=' bank Foo'),从货币中选择curr_id,其中alpha_id =' AUD' OR alpha_id ='NZD'或alpha_id ='SGD'