Re: [sqlite] [Importing CSV] Empty colums != NULL?

2019-11-21 Thread Winfried 
For other newbies' benefit: Yes, indeed, importing empty columns doesn't set
them to NULL.

UPDATE Shops SET "contact:postcode" = NULL WHERE "contact:postcode" = "";
UPDATE Shops SET "addr:postcode" = NULL WHERE "addr:postcode" = "";

SELECT name, IFNULL("contact:postcode","addr:postcode") zipcode from Shops
where id="251373376";
Acme;12345



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [Importing CSV] Empty colums != NULL?

2019-11-21 Thread Winfried 
Hello,

I imported data from a CSV file.

Some of the columns are empty, so I expected them to be set to NULL, but it
looks like it's not how SQLite handles them internally:

=
sqlite> .schema
CREATE TABLE Shops(
  "id" TEXT,
  "name" TEXT,
  "contact:postcode" TEXT,
  "contact:city" TEXT,
  "addr:postcode" TEXT,
  "addr:city" TEXT,
  "contact:phone" TEXT,
  "email" TEXT,
  "website" TEXT
);
=

Here's a record where "addr:postcode" is filled, but "contact:postcode" is
not:
=
sqlite> select * from Shops limit 10;
id;name;contact:postcode;contact:city;addr:postcode;addr:city;contact:phone;email;website
251373376;Acme;;;12345;My City;;;http://www.acme.com
etc.
=

But SQLite returns an empy column when using IFNULL:
=
sqlite> select name, IFNULL("contact:postcode","addr:postcode") zipcode from
Shops where id="251373376";
name;zipcode
Acme;
=

Should I use a different command when importing data or running IFNULL?

Thank you.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users