4

This query gets syntax error:

var querydb = `INSERT INTO msg_conversations VALUES (DEFAULT, ${user_id}, null, null, null, null, '{"key": "value", "unique": "' || uuid_generate_v4() || '"}'::jsonb, to_timestamp('05 Dec 2000', 'DD Mon YYYY'), to_timestamp('05 Dec 2000', 'DD Mon YYYY') )`

db.sequelize.query(querydb)
Dharman
  • 30,962
  • 25
  • 85
  • 135
KitKit
  • 8,549
  • 12
  • 56
  • 82

1 Answers1

8

first convert to text then convert to jsonb

train this tips by select statemant:

select  ('{"key": "value", "unique": "asdas'||to_timestamp( '05 Dec 2000', 'DD Mon YYYY' )||'"}' ::TEXT)::jsonb

|| only use for text type

then for your problem :

var querydb = `INSERT INTO msg_conversations VALUES (DEFAULT, ${user_id}, null, null, null, null, ('{"key": "value", "unique": "' || uuid_generate_v4() || '"}'::TEXT)::jsonb, to_timestamp('05 Dec 2000', 'DD Mon YYYY'), to_timestamp('05 Dec 2000', 'DD Mon YYYY') )`
PersianMan
  • 924
  • 1
  • 12
  • 29
  • Thank you. I'll pick you the best answer. Do you know beside from `uuid_generate_v4`, there is any solution to create a Unique Indexes or Unique ID for JSONB field, bro? – KitKit Oct 04 '18 at 08:33
  • 1
    https://stackoverflow.com/questions/19530736/how-can-i-generate-a-unique-string-per-record-in-a-table-in-postgres – PersianMan Oct 04 '18 at 09:24
  • in postgres or js? – PersianMan Oct 04 '18 at 09:25
  • 1
    There is no point in using `::TEXT`; you are casting text to text. You seem to be basing this on posts like [Merging Concatenating JSON(B) columns in query](https://stackoverflow.com/q/30101603), where the input is a jsonb column. – Martijn Pieters Sep 13 '21 at 11:17
  • @MartijnPieters The problem is not having parentheses. castings are only for educational purposes , to show output of each part – PersianMan Oct 14 '21 at 15:10