duckdb / duckdb/duckdb-postgres
INSERT OR REPLACE fails on json columns
- Dominant language
- C++
- Stars
- 372
- Forks
- 105
- Avg merge
- 10h 19m
- Merged PRs (30d)
- 18
Description
### 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
```sql
CREATE TABLE test_pg(
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
col JSON
);
```
In duckdb
```sql
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?
- [x] 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?
- [x] I agree
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.