SQL 使用嵌套的 INSERT INTO？

Question

我正在使用 postgreSQL 来存储聊天记录，并且我有这个示例模式:

CREATE TABLE contacts (
  "id" BIGSERIAL PRIMARY KEY,
  "user" BIGINT NOT NULL,
  "contact" BIGINT NOT NULL,
  "savedAs" VARCHAR(36),
  CONSTRAINT user_fk FOREIGN KEY("user") REFERENCES users("id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT contact_fk FOREIGN KEY("contact") REFERENCES users("id") ON DELETE CASCADE ON UPDATE CASCADE,
  UNIQUE("user", "contact")
);

CREATE TABLE messages (
  "id" BIGSERIAL PRIMARY KEY,
  "contact" BIGINT NOT NULL,
  "direction" direction_type NOT NULL,
  "type" message_type default 'text',
  "body" VARCHAR(1000) NULL,
  "status" status_type DEFAULT 'none',
  "time" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT contact_fk FOREIGN KEY("contact") REFERENCES contacts("id") ON DELETE CASCADE
);

CREATE TABLE last_message (
  "id" BIGSERIAL PRIMARY KEY,
  "chat" BIGINT NOT NULL UNIQUE,
  "message" BIGINT NOT NULL,
  CONSTRAINT message_fk FOREIGN KEY("message") REFERENCES messages("id"),
  CONSTRAINT chat_fk FOREIGN KEY("chat") REFERENCES contacts("id") ON DELETE CASCADE
);

我想要做的是将特定聊天的最后一条消息存储在 last_message 表中。我正在考虑这样做(但没有工作):

 INSERT INTO last_message (chat, message) VALUES (
     9,
     (INSERT INTO messages (contact, direction, body) VALUES (9, 'sent', 'hello there') RETURNING id)
 )

但是我得到一个语法错误(“into”处或附近的语法错误)，所以这是我的问题，

• 上面的查询有什么问题？

• 有没有更好的方法来做到这一点？怎么办？

• 有什么可以改进的吗？

Answer 1

使用 CTE:

WITH toinsert as (
      INSERT INTO messages (contact, direction, body)
          VALUES (9, 'sent', 'hello there')
          RETURNING id
     )
INSERT INTO last_message (chat, message) 
    SELECT 9, id
    FROM toinsert;