Getting "NOT NULL constraint violated" for a column with a DEFAULT value
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?
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
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
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
On Mon, Oct 24, 2016 at 10:58:28PM +0200, Eyal Rozenberg wrote:
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 Simply remove the 'NOT NULL' constraint. If this is not possible you will need to load your data into a temporary table without this contraint.
Niels
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
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Niels Nes, Manager ITF, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: https://www.cwi.nl/people/niels e-mail: Niels.Nes@cwi.nl
On 24-10-16 23:06, Niels Nes wrote:
On Mon, Oct 24, 2016 at 10:58:28PM +0200, Eyal Rozenberg wrote:
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
Simply remove the 'NOT NULL' constraint. If this is not possible you will need to load your data into a temporary table without this constraint.
Feature Request: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6107 Eyal
participants (3)
-
Eyal Rozenberg -
Niels Nes -
Stefan Manegold