Question 114106 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Using timestamp function in Postgres SQL while inserting json data

I am trying to insert timestamp into json data into a column called ‘conversations’ of type "jsonb"

this is what I want the data to look like-

{
"sender": "John",
"message": "Issue",
"msgAt": "2022-11-11"}

Below is my attempt at the above,which is not the correct format for the function NOW()

    INSERT INTO ticket_data(conversations) VALUES('{"sender":"John","message":"Issue","msgAt":NOW()}');

Any help is appreciated!

Tags:

2

Answers


  1. 0 
    select ('{"sender":"John","message":"Issue",' || '"msgAt":'|| '"' || ( now()::date) ||'"' ||'}')::json;

    or use parameter.

    DO $$
DECLARE
    _date date;
BEGIN
    _date := now();
    RAISE NOTICE '%', json_build_object('sender', 'John', 'message', 'Issue', 'msgAt', _date);
END
$$;
    Login or Signup to reply.
  2. 0

    From JSON functions Table 9.47. JSON Creation Functions.

    select json_build_object('sender', 'John', 'message', 'Issue', 'msgAt', current_date);
                        json_build_object                         
------------------------------------------------------------------
 {"sender" : "John", "message" : "Issue", "msgAt" : "2022-10-23"}

    Changed now() to current_date as that was what you wanted to end up with.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search