In that case, how can I load an integer (not a varchar) field from a CSV where some lines are missing that field is occasionally missing? I was mistakenly assuming that if I interpreted the empty string as null, and specified a default value, I could essentially interpret the missing values as the default. Is there another way to achieve the same? Eyal On 24-10-16 17:15, Stefan Manegold wrote:
The default is (only) used, if you provide (insert) no other value, but not is you explicitly provide (insert) a NULL value (see below). With copy into you always provide for each record you insert values for all columns, possibly indeed NULL values.
sql>create table x (a int, b int not null default 0); operation successful (9.079ms) sql>insert into x (a) values (1); 1 affected row (6.517ms) sql>select * from x; +------+------+ | a | b | +======+======+ | 1 | 0 | +------+------+ 1 tuple (1.532ms) sql>insert into x (a,b) values (1,NULL); INSERT INTO: NOT NULL constraint violated for column x.b sql>select * from x; +------+------+ | a | b | +======+======+ | 1 | 0 | +------+------+ 1 tuple (1.634ms) sql>
Best, Stefan
----- On Oct 24, 2016, at 4:26 PM, Eyal Rozenberg E.Rozenberg@cwi.nl wrote:
I have a column defined as follows:
"div5tailnum" VARCHAR(10) NOT NULL DEFAULT ''
(that's the output of \d mytable), or if you like:
Div5TailNum VARCHAR(10) NOT NULL DEFAULT ''
(that's from the CREATE TABLE command). I'm trying to load data from a CSV file using the following SQL:
COPY OFFSET 2 INTO mytable FROM file.csv USING DELIMITERS ',','\n','"' NULL AS '';
but I get:
INSERT INTO: NOT NULL constraint violated for column ontime.div5tailnum
shouldn't that be impossible, seeing how I've provided a default? _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list