How to concat string of jsonb when inserting rows in Postgres?
<p>I am new to SQL. </p>

<p>I have troble when using jsonb in postgres.</p>

<p>Here is my sql file:</p>

<pre><code>CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE Users (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  name text NOT NULL
);
CREATE TABLE Images (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  url
);
CREATE TABLE Posts (
  id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
  title text,
  user_id uuid,
  content jsonb
);

DO $$
DECLARE user_id UUID;
DECLARE image_id UUID;
BEGIN

INSERT INTO users (name) VALUES ('John') RETURNING id INTO user_id;
INSERT INTO images (url) VALUES ('http://xxx') RETURNING id INTO image_id;
INSERT INTO posts (title, user_id, content) VALUES (
  'Learning PG',
  user_id,
  '[ { "type": "image", "image_id": "' || image_id || '" }, { "type": "text", "text": "Hello Postgres" } ]'
);

END $$;
</code></pre>

<p>However, it throw error like:</p>

<pre><code>psql:test.sql:53: ERROR:  column "content" is of type jsonb but expression is of type uuid
LINE 3:   image_id
</code></pre>

<p>I just want to get the following data structure: </p>

<pre><code>{
  id: '109cf06c-17d8-4ffe-9c46-de4cc29e6353',
  title: 'Learning PG',
  user_id: 'b8aa2928-4fe1-4522-87b8-4dcdfe5cce0c',
  content: [
    { type: 'image', image_id: '7454360f-2763-480f-9207-fcdc1787db9b' },
    { type: 'text', text: 'Hello Postgres' }
  ]
}
</code></pre>

<p>How can I implement this requirement? thanks.</p>