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.

我有表1：
ID，COL_1（NOT NULL），COL_2（NOT NULL），col_3（NOT NULL）

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和填充硬codeD字符串，如数据1，数据2，数据3？

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空值违反非空约束

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

推荐答案

刚刚在SELECT提供文字值：

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

一个选择列表中可以包含任何值前pression ：

A select list can contain any value expression:

不过选择列表中的前pressions不必引用的FROM子句表前pression的列;他们可以不断的算术EX pressions，例如。

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.

和一个字符串文字肯定是一个前值pression。

And a string literal is certainly a value expression.

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