SQL using nested INSERT INTO's?

Question

I am using postgreSQL for storing chat logs, and I have this sample schema:

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

What I want to do, is store the last message for a particular chat in the last_message table. I was thinking of doing it like this(but not working):

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

But I get a syntax error(syntax error at or near "into"), so here are my questions,

what is wrong with the above query?
is there a better a way to do this? how?
is there anything that can be improved?

Note: your `last_message` has a natural key problem. last_message for/from whom ? (that would probably need to be unique) — joop, Dec 21 '15 at 17:15
`last_message` stores the last message for a particular chat whether sent or received doesn't matter. — zola, Dec 21 '15 at 17:37
Possible duplicate of [Postgres insert value from insert in other table](https://stackoverflow.com/questions/46318925/postgres-insert-value-from-insert-in-other-table) — unutbu, Mar 08 '19 at 20:59

score 1 · Answer 1 · answered Dec 21 '15 at 17:02

1

Use a 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;

answered Dec 21 '15 at 17:02

Gordon Linoff

1,242,037
58
646
786

score -1 · Answer 2 · answered Dec 21 '15 at 18:10

Step 1

drop table last_message

You can get the last message like this:

select contact, direction, body
from messages 
join 
(select chat, max(time) maxTime
from messages
group by chat
) temp on messages.chat = temp.chat
and time = maxTime

You can create a view or whatever with this logic, whatever your requirements happent to be.

SQL using nested INSERT INTO's?

2 Answers2