admin 管理员组

文章数量: 1087139

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;

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;
  • csv
  • import
  • duckdb
Share Improve this question asked Jan 22 at 10:12 Ondrej profantOndrej profant 212 bronze badges 2
  • From here read_csv: columns Column names and types, as a struct .... There is no option to include constraints. That is something you will have to do afterwards. – Adrian Klaver Commented Jan 22 at 16:58
  • Better to do it before, as in the solution given below. – peak Commented Jan 23 at 7:56
Add a comment  | 

1 Answer 1

Reset to default 0

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

本文标签： DuckDB import CSV and column property (PK unique NOT NULL)Stack Overflow

版权声明：本文标题：DuckDB import CSV and column property (PK, UNIQUE, NOT NULL) - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://roclinux.cn/p/1738572162a1990490.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。