postgresql 从JSON中提取值用于insert：error列的类型为timestamp，没有时区，但表达式的类型为int

az31mfrm 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(144)

我试图将从JSON中提取的日期插入到表中，但出现错误
代码如下：

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"

}');

字符串
现在这一切都已经定义好了，我试图将JSON中的数据插入到临时表Channel_Info中，如下所示：

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

型
但得到以下错误：

[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

型
现在我尝试了以下方法：

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

型
但出现以下错误：

[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

型

postgresql

来源：https://stackoverflow.com/questions/77438516/extracting-values-from-json-for-insert-error-column-is-of-type-timestamp-withou

2条答案

按热度按时间

svdrlsy41#

您根本不需要强制转换，从c::json ->> 'BeginningDate'返回的字符串将在INSERT时自动进行强制转换，如下所示：

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

字符串
于是：
c::json ->> 'BeginningDate' as BeginningDate个
会起作用的。
您的问题是c::json::timestamp without time zone尝试转换整个JSON对象：

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

}'

型
作为timestamp without time zone而不是仅仅"BeginningDate"的值"2023-05-20T11:53:39"。

赞(0）回复(0）举报 2023-11-18

pftdvrlh2#

您需要将日期列从文本（操作符->>将JSON元素作为文本返回）转换为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的

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 从JSON中提取值用于insert：error列的类型为timestamp，没有时区，但表达式的类型为int

2条答案

相关问题

热门标签

最新问答