SQL PostgreSQL：从另一个表插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6898520/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: insert from another table
提问by Seerumi
I'm trying to insert data to a table from another table and the tables have only one column in common. The problem is, that the TABLE1 has columns that won't accept null values so I can't leave them empty and I can't get them from the TABLE2.
我正在尝试将数据从另一个表插入到一个表中，并且这些表只有一列是共同的。问题是，TABLE1 的列不接受空值，因此我不能将它们留空，也无法从 TABLE2 中获取它们。
I have TABLE1: id, col_1 (not null), col_2(not null), col_3 (not null)
我有 TABLE1: id, col_1 (not null), col_2(not null), col_3 (not null)
and TABLE2: id, col_a, col_b, col_c
和表2：id、col_a、col_b、col_c
so how could I insert id from TABLE2 to TABLE1 and fill the col_1-3 with hard coded strings like "data1", "data2", "data3"?
那么如何将 id 从 TABLE2 插入到 TABLE1 并用硬编码字符串填充 col_1-3 ，例如“data1”、“data2”、“data3”？
INSERT INTO TABLE1 (id) SELECT id FROM TABLE2 WHERE col_a = "something";
will result in:
将导致：
ERROR: null value in column "col_1" violates not-null constraint
错误：“col_1”列中的空值违反了非空约束
回答by mu is too short
Just supply literal values in the SELECT:
只需在 SELECT 中提供文字值：
INSERT INTO TABLE1 (id, col_1, col_2, col_3)
SELECT id, 'data1', 'data2', 'data3'
FROM TABLE2
WHERE col_a = 'something';
A select list can contain any value expression:
选择列表可以包含任何值表达式：
But the expressions in the select list do not have to reference any columns in the table expression of the FROM clause; they can be constant arithmetic expressions, for instance.
但是选择列表中的表达式不必引用FROM 子句的表表达式中的任何列；例如，它们可以是常量算术表达式。
And a string literal is certainly a value expression.
字符串文字当然是一个值表达式。
回答by Scott Marlowe
You could use coalesce:
您可以使用合并：
insert into destination select coalesce(field1,'somedata'),... from source;
回答by Tom Hood
Very late answer, but I think my answer is more straight forward for specific use cases where users want to simply insert (copy) data from table A into table B:
很晚的答案，但我认为我的答案对于用户想要简单地将数据从表 A 插入（复制）到表 B 的特定用例更直接：
INSERT INTO table_b (col1, col2, col3, col4, col5, col6)
SELECT col1, 'str_val', int_val, col4, col5, col6
FROM table_a
回答by kris2k
For referential integtity :
对于引用完整性：
insert into main_tbl (col1, ref1, ref2, createdby)
values ('col1_val',
(select ref1 from ref1_tbl where lookup_val = 'lookup1'),
(select ref2 from ref2_tbl where lookup_val = 'lookup2'),
'init-load'
);
