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
– joop, Commented Dec 21, 2015 at 17:15
last_message stores the last message for a particular chat whether sent or received doesn't matter. — zolamk
– zolamk, Commented Dec 21, 2015 at 17:37
Possible duplicate of Postgres insert value from insert in other table — unutbu
– unutbu, Commented Mar 8, 2019 at 20:59

Gordon Linoff · Accepted Answer · 2015-12-21 17:02:42Z

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, 2015 at 17:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dan Bracuk · Accepted Answer · 2015-12-21 18:10:30Z

-1

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.

answered Dec 21, 2015 at 18:10

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Collectives™ on Stack Overflow

SQL using nested INSERT INTO's?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related