如何从 PostgreSQL 中的另一个表插入值？

How to insert values from another table in PostgreSQL?

提问by mark

I have a table which references other tables:

我有一个引用其他表的表：

CREATE TABLE scratch
(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  rep_id INT NOT NULL REFERENCES reps,
  term_id INT REFERENCES terms
);
CREATE TABLE reps (
  id    SERIAL PRIMARY KEY,
  rep   TEXT NOT NULL UNIQUE
);
CREATE TABLE terms (
  id    SERIAL PRIMARY KEY,
  terms TEXT NOT NULL UNIQUE
);

I wish to add a new record to scratch given the name, the repand the termsvalues, i.e. I have neither corresponding rep_idnor term_id.

我希望在给定name，rep和terms值的情况下添加一个新记录，即我既没有相应的rep_id也没有term_id。

Right now the only idea that I have is:

现在我唯一的想法是：

insert into scratch (name, rep_id, term_id)
             values ('aaa', (select id from reps where rep='Dracula' limit 1), (select id from terms where terms='prepaid' limit 1));

My problem is this. I am trying to use the parameterized query API (from node using the node-postgres package), where an insert query looks like this:

我的问题是这个。我正在尝试使用参数化查询 API（来自使用 node-postgres 包的节点），其中插入查询如下所示：

insert into scratch (name, rep_id, term_id) values (, , );

and then an array of values for $1, $2 and $3 is passed as a separate argument. At the end, when I am comfortable with the parameterized queries the idea is to promote them to prepared statements to utilize the most efficient and safest way to query the database.

然后将 $1、$2 和 $3 的值数组作为单独的参数传递。最后，当我对参数化查询感到满意时，我的想法是将它们提升为准备好的语句，以利用最有效和最安全的方式来查询数据库。

However, I am puzzled how can I do this with my example, where different tables have to be subqueried.

但是，我很困惑如何在我的示例中执行此操作，其中必须对不同的表进行子查询。

P.S. I am using PostgreSQL 9.2 and have no problem with a PostgreSQL specific solution.

PS 我正在使用 PostgreSQL 9.2 并且对 PostgreSQL 特定的解决方案没有问题。

EDIT 1

编辑 1

C:\Users\markk>psql -U postgres
psql (9.2.4)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \c dummy
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
You are now connected to database "dummy" as user "postgres".
dummy=# DROP TABLE scratch;
DROP TABLE
dummy=# CREATE TABLE scratch
dummy-# (
dummy(#   id SERIAL NOT NULL PRIMARY KEY,
dummy(#   name text NOT NULL UNIQUE,
dummy(#   rep_id integer NOT NULL,
dummy(#   term_id integer
dummy(# );
NOTICE:  CREATE TABLE will create implicit sequence "scratch_id_seq" for serial column "scratch.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "scratch_pkey" for table "scratch"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "scratch_name_key" for table "scratch"
CREATE TABLE
dummy=# DEALLOCATE insert_scratch;
ERROR:  prepared statement "insert_scratch" does not exist
dummy=# PREPARE insert_scratch (text, text, text) AS
dummy-# INSERT INTO scratch (name, rep_id, term_id)
dummy-# SELECT , r.id, t.id
dummy-# FROM reps r, terms t
dummy-# WHERE r.rep =  AND t.terms = 
dummy-# RETURNING id, name,  rep,  terms;
PREPARE
dummy=# DEALLOCATE insert_scratch2;
ERROR:  prepared statement "insert_scratch2" does not exist
dummy=# PREPARE insert_scratch2 (text, text, text) AS
dummy-# INSERT INTO scratch (name, rep_id, term_id)
dummy-#              VALUES (, (SELECT id FROM reps WHERE rep= LIMIT 1), (SELECT id FROM terms WHERE terms= LIMIT 1))
dummy-# RETURNING id, name,  rep,  terms;
PREPARE
dummy=# EXECUTE insert_scratch ('abc', 'Snowhite', '');
 id | name | rep | terms
----+------+-----+-------
(0 rows)


INSERT 0 0
dummy=# EXECUTE insert_scratch2 ('abc', 'Snowhite', '');
 id | name |   rep    | terms
----+------+----------+-------
  1 | abc  | Snowhite |
(1 row)


INSERT 0 1
dummy=# EXECUTE insert_scratch ('abcd', 'Snowhite', '30 days');
 id | name |   rep    |  terms
----+------+----------+---------
  2 | abcd | Snowhite | 30 days
(1 row)


INSERT 0 1
dummy=# EXECUTE insert_scratch2 ('abcd2', 'Snowhite', '30 days');
 id | name  |   rep    |  terms
----+-------+----------+---------
  3 | abcd2 | Snowhite | 30 days
(1 row)


INSERT 0 1
dummy=#

EDIT 2

编辑 2

We can utilize the fact that rep_idis required, even though terms_idis optional and use the following version of INSERT-SELECT:

我们可以利用rep_id必需的事实，即使terms_id是可选的，并使用以下版本的 INSERT-SELECT：

PREPARE insert_scratch (text, text, text) AS
INSERT INTO scratch (name, rep_id, term_id)
SELECT , r.id, t.id
FROM reps r
LEFT JOIN terms t ON t.terms = 
WHERE r.rep = 
RETURNING id, name,  rep,  terms;

This version, however, has two problems:

但是这个版本有两个问题：

1. No distinction is made between a missing termsvalue (i.e. '') and an invalid termsvalue (i.e. a non empty value missing from the terms table entirely). Both are treated as missing terms. (But the INSERT with two subqueries suffers from the same problem)
2. The version depends on the fact that the repis required. But what if rep_idwas optional too?

1. 缺失terms值（即''）和无效terms值（即术语表中完全缺失的非空值）之间没有区别。两者都被视为缺失项。（但是带有两个子查询的 INSERT 遇到了同样的问题）
2. 版本取决于rep需要的事实。但如果rep_id也是可选的呢？

EDIT 3

编辑 3

Found the solution for the item 2 - eliminating dependency on rep being required. Plus using the WHERE statement has the problem that the sql does not fail if the rep is invalid - it just inserts 0 rows, whereas I want to fail explicitly in this case. My solution is simply using a dummy one row CTE:

找到了第 2 项的解决方案 - 消除了对所需代表的依赖。另外，使用 WHERE 语句的问题是，如果 rep 无效，sql 不会失败 - 它只插入 0 行，而我想在这种情况下显式失败。我的解决方案只是使用一个虚拟的单行 CTE：

PREPARE insert_scratch (text, text, text) AS
WITH stub(x) AS (VALUES (0))
INSERT INTO scratch (name, rep_id, term_id)
SELECT , r.id, t.id
FROM stub
LEFT JOIN terms t ON t.terms = 
LEFT JOIN reps r ON r.rep = 
RETURNING id, name, rep_id, term_id;

If rep is missing or invalid, this sql will try to insert NULL into the rep_id field and since the field is NOT NULLan error would be raised - precisely what I need. And if further I decide to make rep optional - no problem, the same SQL works for that too.

如果 rep 丢失或无效，此 sql 将尝试将 NULL 插入到 rep_id 字段中，并且由于该字段是NOT NULL一个错误，因此将引发错误 - 正是我需要的。如果我进一步决定让 rep 可选——没问题，同样的 SQL 也适用于此。

回答by joop

INSERT into scratch (name, rep_id, term_id)
SELECT 'aaa'
        , r.id 
        , t.id
FROM reps r , terms t -- essentially a cross join
WHERE r.rep = 'Dracula'
  AND t.terms = 'prepaid'
        ;

Notes:

笔记：

• You don't need the ugly LIMITs, since r.rep and t.terms are unique (candidate keys)
• you could replace the FROM a, bby a FROM a CROSS JOIN b
• the scratchtable will probably need an UNIQUEconstraint on (rep_id, term_it)(the nullability of term_idis questionable)

• 你不需要丑陋的LIMITs，因为 r.rep 和 t.terms 是唯一的（候选键）
• 你可以用FROM a, b一个代替FROM a CROSS JOIN b
• 该scratch表可能需要一个UNIQUE约束on (rep_id, term_it)（可空性是term_id有问题的）

UPDATE: the same as prepared query as found in the Documentation

更新：与文档中的准备查询相同

PREPARE hoppa (text, text,text) AS
        INSERT into scratch (name, rep_id, term_id)
        SELECT  , r.id , t.id
        FROM reps r , terms t -- essentially a cross join
        WHERE r.rep = 
        AND t.terms = 
        ;
EXECUTE hoppa ('bbb', 'Dracula' , 'prepaid' );

SELECT * FROM scratch;

UPDATE2: test data

UPDATE2：测试数据

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE reps ( id    SERIAL PRIMARY KEY, rep   TEXT NOT NULL UNIQUE);
CREATE TABLE terms ( id    SERIAL PRIMARY KEY, terms TEXT NOT NULL UNIQUE);
CREATE TABLE scratch ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, rep_id INT NOT NULL REFERENCES reps, term_id INT REFERENCES terms);

INSERT INTO  reps(rep) VALUES( 'Dracula' );
INSERT INTO  terms(terms) VALUES( 'prepaid' );

Results:

结果：

NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table tmp.reps
drop cascades to table tmp.terms
drop cascades to table tmp.scratch
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
PREPARE
INSERT 0 1
 id | name | rep_id | term_id 
----+------+--------+---------
  1 | aaa  |      1 |       1
  2 | bbb  |      1 |       1
(2 rows)