2

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.

1 Answer 1

1

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
);
Sign up to request clarification or add additional context in comments.

1 Comment

When concatenating strings and casting to jsonb, I had to wrap the strings with parens.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.