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