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

Postgresql – Extracting values from JSON for insert: error column is of type timestamp without time zone but expression is of type int

I am trying to insert dates extracted from JSON into a table but getting errors

My code is as follows:

create  table tmp (
  c TEXT
);

CREATE TEMP TABLE Channel_Info (
   ID                   int NOT NULL,
   ChannelID            INT,
   DeviceChannelID      UUID,
   SerialNumber         VARCHAR(10),
   DownloadTableName    VARCHAR(100),
   ReadingTrimDate      timestamp without time zone,
   BeginningDate        timestamp without time zone,
   EndDate              timestamp without time zone

);

insert into tmp values
('{
                        "SerialNumber":"940860",
                        "ChannelIDs":[17,13,11,12,14],
                        "BeginningDate":"2023-05-20T11:53:39",
                        "EndDate":"2023-05-22T09:04:04"

}');

Now this is all defined , i am attempting to insert data from my JSON into the
temporary table Channel_Info as follows:

INSERT INTO Channel_Info(ID,ChannelID,SerialNumber,BeginningDate,EndDate)
select
       row_number() over (order by ChannelID::text::int ) as ID,
       ChannelID::text::int,
        c::json ->> 'SerialNumber' as SerialNumber,
        c::json ->> 'BeginningDate' as BeginningDate,
         c::json ->> 'EndDate' as EndDate
from tmp
CROSS JOIN LATERAL json_array_elements(c::json -> 'ChannelIDs') as ChannelID

But get the following error:

[2023-11-07 13:12:18] [42804] ERROR: column "beginningdate" is of type timestamp without time zone but expression is of type text
[2023-11-07 13:12:18] Hint: You will need to rewrite or cast the expression.
[2023-11-07 13:12:18] Position: 241

Now i tried the following:

INSERT INTO Channel_Info(ID,ChannelID,SerialNumber,BeginningDate,EndDate)
select
       row_number() over (order by ChannelID::text::int ) as ID,
       ChannelID::text::int,
        c::json ->> 'SerialNumber' as SerialNumber,
        c::json::timestamp without time zone ->> 'BeginningDate' as BeginningDate,
        c::json::timestamp without time zone  ->> 'EndDate' as EndDate
from tmp
CROSS JOIN LATERAL json_array_elements(c::json -> 'ChannelIDs') as ChannelID

but got the following error :

[2023-11-07 13:15:08] [42846] ERROR: cannot cast type json to timestamp without time zone
[2023-11-07 13:15:08] Position: 173
Tags:

2

Answers


  1. 0

    You need to cast date columns from text (operator ->> return JSON element as text) to timestamp :

    INSERT INTO Channel_Info(ID, ChannelID, SerialNumber, BeginningDate, EndDate)
select
       row_number() over (order by ChannelID::text::int ) as ID,
       ChannelID::text::int,
        c::json ->> 'SerialNumber' as SerialNumber,
        (c::json ->> 'BeginningDate')::timestamp as BeginningDate,
        (c::json ->> 'EndDate')::timestamp as EndDate
from tmp
CROSS JOIN LATERAL json_array_elements(c::json -> 'ChannelIDs') as ChannelID

    Demo here

    Login or Signup to reply.
  2. 0

    You shouldn’t need the cast at all, the string returned from c::json ->> 'BeginningDate' will be automatically cast on INSERT per this example:

    Table "public.dt_test"
 Column  |            Type             | Collation | Nullable | Default 
---------+-----------------------------+-----------+----------+---------
 id      | integer                     |           |          | 
 ts_fld  | timestamp without time zone |           |          | 
 tsz_fld | timestamp with time zone    |           |          |

insert into dt_test values(1, '2023-05-20T11:53:39', '2023-05-20T11:53:39');

select * from dt_test ;
 id |       ts_fld        |        tsz_fld         
----+---------------------+------------------------
  1 | 2023-05-20 11:53:39 | 2023-05-20 11:53:39-07

    So:

    c::json ->> 'BeginningDate' as BeginningDate

    will work.

    Your issue was c::json::timestamp without time zone which was trying to cast the entire JSON object:

    '{
                        "SerialNumber":"940860",
                        "ChannelIDs":[17,13,11,12,14],
                        "BeginningDate":"2023-05-20T11:53:39",
                        "EndDate":"2023-05-22T09:04:04"

}'

    as timestamp without time zone instead of just the "BeginningDate" value "2023-05-20T11:53:39".

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

Back To Top
Search