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

Question

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

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

我希望根据name、rep 和terms 值添加一条新记录，即我没有对应的< strong>rep_id 或 term_id。

现在我唯一的想法是:

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

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

insert into scratch (name, rep_id, term_id) values ($1, $2, $3);

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

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

附言我正在使用 PostgreSQL 9.2，并且没有遇到 PostgreSQL 特定解决方案的问题。

编辑 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 $1, r.id, t.id
dummy-# FROM reps r, terms t
dummy-# WHERE r.rep = $2 AND t.terms = $3
dummy-# RETURNING id, name, $2 rep, $3 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 ($1, (SELECT id FROM reps WHERE rep=$2 LIMIT 1), (SELECT id FROM terms WHERE terms=$3 LIMIT 1))
dummy-# RETURNING id, name, $2 rep, $3 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=#

编辑 2

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

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

然而，这个版本有两个问题:

1. 不区分缺失的 terms 值(即 '')和无效的 terms 值(即 terms 表中完全缺失的非空值)。两者都被视为缺失项。 (但是有两个子查询的 INSERT 也有同样的问题)
2. 版本取决于需要rep 的事实。但是，如果 rep_id 也是可选的呢？

编辑 3

找到第 2 项的解决方案 - 消除对 rep 的依赖。另外，使用 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 $1, r.id, t.id
FROM stub
LEFT JOIN terms t ON t.terms = $3
LEFT JOIN reps r ON r.rep = $2
RETURNING id, name, rep_id, term_id;

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

Answer 1

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

注意事项:

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

更新:与准备好的查询相同as found in the Documentation

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

SELECT * FROM scratch;

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

结果:

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)