DuckDB drop column: no column named that way

Question

Why DuckDB tells me there is no column named that way when I try to drop a column?

D DESCRIBE oa_pub;
┌──────────────┬─────────────┬─────────┬─────────┬──────────────────────────┬─────────┐
│ column_name  │ column_type │  null   │   key   │         default          │  extra  │
│   varchar    │   varchar   │ varchar │ varchar │         varchar          │ varchar │
├──────────────┼─────────────┼─────────┼─────────┼──────────────────────────┼─────────┤
│ id           │ INTEGER     │ NO      │ PRI     │ nextval('oa_pub_id_seq') │ NULL    │
│ project_id   │ INTEGER     │ NO      │ UNI     │ NULL                     │ NULL    │
│ oa_author_id │ VARCHAR     │ NO      │ UNI     │ NULL                     │ NULL    │
│ oa_pub_id    │ VARCHAR     │ NO      │ UNI     │ NULL                     │ NULL    │
│ oa_pub_json  │ JSON        │ YES     │ NULL    │ NULL                     │ NULL    │
│ oa_pub_id_2  │ VARCHAR     │ YES     │ NULL    │ NULL                     │ NULL    │
└──────────────┴─────────────┴─────────┴─────────┴──────────────────────────┴─────────┘
D ALTER TABLE oa_pub DROP COLUMN oa_pub_id;
Catalog Error:
table "oa_pub" does not have a column named oa_pub_id

Is it possible the oa_pub_id column name contains trailing whitespace? e.g. SET colname = ' oa_pub_id '; — Dai
– Dai, Commented Jun 16 at 18:06
@Dai that came to me also but it's not that. See my answer. But thank you very much for the help indeed! — robertspierre
– robertspierre, Commented Jun 16 at 18:09

robertspierre · Accepted Answer · 2025-06-16 18:08:20Z

Although the error message is cryptic, the problem was that oa_pub_id is part of a unique constraint. See the key column of the table returned by DESCRIBE.

To reproduce:

D CREATE TABLE test2 (id INT PRIMARY KEY, name TEXT, surname TEXT, age INT, UNIQUE(surname, age));
D DESCRIBE test2;
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ id          │ INTEGER     │ NO      │ PRI     │ NULL    │ NULL    │
│ name        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ surname     │ VARCHAR     │ YES     │ UNI     │ NULL    │ NULL    │
│ age         │ INTEGER     │ YES     │ UNI     │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
D ALTER TABLE test2 DROP COLUMN surname;
Catalog Error:
table "test2" does not have a column named "surname"

Collectives™ on Stack Overflow

DuckDB drop column: no column named that way

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related