Andrew Dunstan wrote: > Again, I think this will break that property. But if that's what it > takes to be able to import to a table with NOT NULL in at least some > cases I could live with it. Just. But in the general case it won't work. > Say you are importing into a table with the following defn: (a text, b > text not null, c int). then the line 'x,,' will fail on b if '' is null, > and will fail on c if '' is empty string. And yet this sort of output is > exactly what is to be expected from a spreadsheet.
I thought about this case. I think the big objection to the original idea was trying to tie the CSV behavior to specific types. With a type-agnostic system like PostgreSQL, doing that will certainly fail to catch all cases and be a maintenance nightmare. However, your idea of testing pg_attribute.attnotnull is acceptable, I think. It isn't type-specific. One way of solving the above issue is to allow comma-comma to promote to a zero-length string if the column is NOT NULL. One idea would be to print a warning the first time a NOT NULL column sees a comma-comma, and state that it will be changed to a zero-length string. You could use STRICT to disable that and throw an error. I don't see why this has to be type-specific. If a zero-length string doesn't match an int column, you throw an error. If you see comma-comma for an INT NOT NULL column, you have to throw an error. There is no proper value for the column. You might also want to do that anyway in some cases even if the column isn't NOT NULL. This is where your FORCE col1, col2 came from, where you can specify which columns should be NULL and which zero-length strings. I guess the issue is can we get a single specification for the entire table, or do we need per-column specifications? Basically, we are trying to over-load the CSV system to store null information by using the distinction of whether a column has quote-quote or nothing. Let's look at your example again: CREATE TABLE test (col1 TEXT NOT NULL, col2 INT) and you have a CSV input file of: , If we say both are NULL, it fails, and if we say neither is NULL, it fails. Do we need control for each column? What if we go with preferring NULL for comma-comma, and then print warnings for NOT NULL columns and try the promote. If you want comma-comma to be zero-length string, you can create the column with NOT NULL, load the file, then ALTER TABLE to allow NULL's again. Basically, the NOT NULL specification on the column is the COPY CSV control method, rather than having it be in COPY. -- Bruce Momjian | http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org