PostgreSQL:从另一个表插入 [英] PostgreSQL: insert from another table

问题描述

我正在尝试将数据从另一个表插入到一个表中，但这些表只有一列是共同的.问题是，TABLE1 的列不接受空值，因此我不能将它们留空，也无法从 TABLE2 中获取它们.

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:id、col_1(非空)、col_2(非空)、col_3(非空)

I have TABLE1: id, col_1 (not null), col_2(not null), col_3 (not null)

和 TABLE2:id、col_a、col_b、col_c

and TABLE2: id, col_a, col_b, col_c

那么如何将 id 从 TABLE2 插入到 TABLE1 并用硬编码字符串填充 col_1-3，例如data1"、data2"、data3"?

so how could I insert id from TABLE2 to TABLE1 and fill the col_1-3 with hard coded strings like "data1", "data2", "data3"?

INSERT INTO TABLE1 (id) SELECT id FROM TABLE2 WHERE col_a = "something";

将导致:

错误:col_1"列中的空值违反了非空约束

ERROR: null value in column "col_1" violates not-null constraint

推荐答案

您可以在 SELECT 中提供文字值:

You can supply literal values in the SELECT:

INSERT INTO TABLE1 (id, col_1, col_2, col_3)
SELECT id, 'data1', 'data2', 'data3'
FROM TABLE2
WHERE col_a = 'something';

选择列表可以包含任何值表达式:

但是select列表中的表达式不必引用FROM子句的表表达式中的任何列；例如，它们可以是常量算术表达式.

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.

字符串字面量当然是值表达式.

And a string literal is certainly a value expression.

这篇关于PostgreSQL:从另一个表插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！