user2331095 user2331095 - 1 month ago 14
SQL Question

How to concat string of jsonb when inserting rows in Postgres?

I am new to SQL.

I have troble when using jsonb in postgres.

Here is my sql file:

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


However, it throw error like:

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


I just want to get the following data structure:

{
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' }
]
}


How can I implement this requirement? thanks.

Answer

Try casting the content as jsonb during the insert:

INSERT INTO posts (title, user_id, content)
VALUES (
    'Learning PG',
    user_id,
   '[ { "type": "image", "image_id": "' || image_id || '" }, { "type": "text", "text": "Hello Postgres" } ]'::jsonb
);