CODEWITHSUNDEEP
csvimportduckdb
Ondrej profant
Ondrej profant

Reputation: 21

DuckDB import CSV and column property (PK, UNIQUE, NOT NULL)

I'm using DuckDB. When I import a CSV, everything goes smoothly. I can set a lot of parameters (delimiter, etc.). However, I couldn't set additional column properties: PK, UNIQUE, or NOT NULL.

The ALTER TABLE command can't change PK (not implemented yet).

I also tried: SELECT Prompt FROM sniff_csv('data.csv'); and manually adding the properties: CREATE TABLE product AS SELECT * FROM read_csv('data.csv', auto_detect=false, delim=',', quote='"', escape='"', new_line='\n', skip=0, comment='', header=true, columns={'id': 'BIGINT PRIMARY KEY', 'description': 'VARCHAR UNIQUE', 'status': 'VARCHAR NOT NULL'});

It doesn't throw an error, but they don't get written to the table.

MWE:

data.csv:

id,description,status
1,"lorem ipsum",active

SQL:

SELECT Prompt FROM sniff_csv('data.csv');
CREATE TABLE product AS SELECT * FROM read_csv('data.csv', auto_detect=false, delim=',', quote='"', escape='"', new_line='\n', skip=0, comment='', header=true, columns={'id': 'BIGINT PRIMARY KEY', 'description': 'VARCHAR UNIQUE', 'status': 'VARCHAR NOT NULL'});
show product;

UPDATE: Alter table PK is fixed/implemented in new version 1.2: https://github.com/duckdb/duckdb/releases/tag/v1.2.0

Upvotes: 1

Views: 82

Answers (1)

peak
peak

Reputation: 116957

First CREATE the table with the desired structure. Then populate it using COPY.

For example:

CREATE OR REPLACE TABLE product (
  id BIGINT PRIMARY KEY, 
  description VARCHAR UNIQUE, 
  status VARCHAR NOT NULL);

COPY product from 'data.csv' WITH (
  delimiter ',', quote '"', escape '\', new_line '\n');

D show product;
┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │  null   │   key   │ default │  extra  │
│   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ id          │ BIGINT      │ NO      │ PRI     │ NULL    │ NULL    │
│ description │ VARCHAR     │ YES     │ UNI     │ NULL    │ NULL    │
│ status      │ VARCHAR     │ NO      │ NULL    │ NULL    │ NULL    │
└─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
D from product;
┌───────┬─────────────┬─────────┐
│  id   │ description │ status  │
│ int64 │   varchar   │ varchar │
├───────┼─────────────┼─────────┤
│   1   │ lorem ipsum │ active  │
└───────┴─────────────┴─────────┘
D

Upvotes: 0

Related Questions