duckdb / duckdb/duckdb-postgres

INSERT OR REPLACE fails on json columns

Open
#390 1 comment 0 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.