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

PostgreSQL – How to handle invalid values when casting to timestamp in SELECT from jsonb data

I am trying to get data from a jsonb column and need to cast an attribute from the payload to timestamp. But the query aborts when the value is null or invalid.

This is my select statement.

Select
    (jsonb_path_query(AnchorNode, '$.TestDate')#>> '{}')::timestamp as TestDate
From (
      Select 
          jsonb_path_query_first(payload, '$.node1[*].node2[*]') as AnchorNode
      From TestTable
     ) subq1

The invalid values could be only the Date or null.

How can I change the query to handle this. There are about 7 or 8 date fields where I would need to do this

Thank you

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    It was possible to achieve above using CASE statement. It may also be done using COALESCE

    Select
    case 
        when jsonb_typeof(AnchorNode -> 'TestDate') is null then null
        when jsonb_typeof(AnchorNode -> 'TestDate') = 'string' then (AnchorNode ->> 'TestDate')::timestamp
    end TestDateTS,
From (
      Select 
          jsonb_path_query_first(payload, '$.node1[*].node2[*]') as AnchorNode
      From TestTable
) subq1

    OR

    COALESCE((AnchorNode ->> 'TestDate')::timestamp, null)  as "TestDate"

  2. 0

    Maybie try to cast text inside a function and return null on exception

    create or replace function str_to_timestamp(_date text) returns timestamp
language plpgsql AS
$$
    BEGIN
        return _date::timestamp;

        EXCEPTION WHEN OTHERS THEN
            return null::timestamp;
    END;
$$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search