INSERT OR REPLACE fails on json columns

[ceymard]

What happens?

Using INSERT OR REPLACE with a json column in duckdb to a json column in postgres fails.

Which would be I guess because when creating the udpate table, it somehow creates col as VARCHAR and not JSON, which would be, if I had to guess, because src/postgres_utils.cpp function PostgresUtils::TypeToString replies VARCHAR for json.

Which leads me to ask ; why does this extension pass through an update table ? Why doesn't it use the ON CONFLICT clause on an identified primary key or unique column, failing if it doesn't detect any ?

To Reproduce

In postgres

CREATE TABLE test_pg(
   id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, 
  col JSON
);

In duckdb

CREATE TABLE test_ddb(id bigint, col JSON);
INSERT INTO test_ddb values (1, '{"A": 1}');

Then try to uses OR REPLACE

ATTACH ... AS pg (type postgres);
INSERT OR REPLACE into pg.public.test_pg by name select * from test_ddb;

Which leads to :

Failed to execute query "UPDATE "public"."test_pg" SET "col" = "update_data_fce5d0da-c0e4-4936-bd74-2b66a3fc0e93"."col" FROM "update_data_fce5d0da-c0e4-4936-bd74-2b66a3fc0e93" WHERE "test_pg".ctid=__page_id_string::TID": ERROR:  column "col" is of type json but expression is of type character varying

OS:

linux

PostgreSQL Version:

17

DuckDB Version:

latest

DuckDB Client:

latest

Full Name:

Christophe Eymard

Affiliation:

Salesway

Have you tried this on the latest main branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[NielsKorschinsky]

I have the same issue, with JSONB in Postgres.
In my case I've tried using an INSERT statement - which worked fine.
However, as soon as there are any "ON CONFLICT UPDATE" cases being hit, it fails.

I tried to re-write it into a merge statement, which had the same issue.
it seems like in the background both statements are re-written into a postgres UPDATE statement.

I also tried casing the column in duckdb into VARCHAR and JSON, both are not helping.

Invalid Error:
Failed to execute query "UPDATE "public"."MY_TABLE" SET "value" = "update_data_f3618a91-8ee6-4de8-8b86-46ae5a58ffde"."value", "deleted" = "update_data_f3618a91-8ee6-4de8-8b86-46ae5a58ffde"."deleted" FROM "update_data_f3618a91-8ee6-4de8-8b86-46ae5a58ffde" WHERE "MY_TABLE".ctid=__page_id_string::TID": ERROR:  column "value" is of type jsonb but expression is of type character varying
LINE 1: ...E "public"."MY_TABLE" SET "value" = "update_da...
                                                             ^
HINT:  You will need to rewrite or cast the expression.