How to insert json data into postgres database table

Question

I m a beginner and trying to insert JSON values into the database using a tutorial

I have created the table using the following command

CREATE TABLE table_name( id character varying(50),
                         data json NOT NULL,
                         active boolean NOT NULL,
                         created_at timestamp with time zone NOT NULL,
                         updated_at timestamp with time zone NOT NULL,
                         CONSTRAINT table_name_pkey PRIMARY KEY (id)
                       );

The table is created with table_name.

Now I am trying to insert the values into the database:

INSERT INTO table_name
SELECT id,data,active,created_at,updated_at
FROM json_populate_record (NULL::table_name,
     '{
        "id": "1",
        "data":{
                "key":"value"
                },
         "active":true,
         "created_at": SELECT NOW(),
         "updated_at": SELECT NOW()
       }'
  );

It throws the following error

ERROR: Invalid input syntax for type JSON '{

Could anyone help me to resolve and insert the JSON values into the DB?

Why is id a varchar if you obviously using an integer value for it? — user330315
– user330315, Commented Jul 16, 2021 at 7:50

score 29 · Accepted Answer · 2023-03-10 08:27:49Z

29

You can't include arbitrary SQL commands inside a JSON string. From a JSON "perspective" SELECT NOW() is an invalid value because it lacks the double quotes. But even if you used "select now()" that would be executed as a SQL query and replaced with the current timestamp) .

But I don't understand why you are wrapping this into a jsonb_populate_record. The better solution (at least in my opinion) would be:

INSERT INTO table_name (id, data, active, created_at, updated_dat)
VALUES ('1', '{"key": "value"}', true, now(), now());

If you really want to complicate things, you need to use string concatenation:

SELECT id,data,active,created_at,updated_at
FROM json_populate_record (NULL::table_name,
     format('{
        "id": "1",
        "data":{
                "key":"value"
                },
         "active":true,
         "created_at": "%s", 
         "updated_at": "%s"
       }', now(), now())::json
  );

edited Mar 10, 2023 at 8:27

answered Jul 16, 2021 at 7:49

user330315

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

3 Comments

DRV Over a year ago

The first code worked for me but please edit id value as 1 instead of "1" @a_horse_with_no_name

user330315 Over a year ago

Your id column is defined as varchar so you need to pass it as a string. In a JSON value a string is written with as "1" and in SQL a string is written as '1'

Sumax Over a year ago

Please edit your SQL query, close VALUES ('1',.......); ; you missed the terminating ).

Collectives™ on Stack Overflow

How to insert json data into postgres database table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related