This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold
--------------------------------------------------------------------------- Stephen Frost wrote: > Greetings, > > Please find attached a minor patch to remove the constraints that a > user can't include the delimiter or quote characters in a 'NULL AS' > string when importing CSV files. > > This allows a user to explicitly request that NULL conversion happen > on fields which are quoted. As the quote character is being allowed > to be in the 'NULL AS' string now, there's no reason to exclude the > delimiter character from being seen in that string as well, though > unless quoted using the CSV quote character it won't ever be matched. > > An example of the usage: > > sfrost*=# \copy billing_data from ~/BillingSamplePricerFile.csv > with csv header quote as '"' null as '""' > > This is no contrived example, it's an issue I ran into earlier today > when I got a file which had (for reasons unknown to me and not easily > changed upstream): > > "1","V","WASHDCABC12","","120033"... > > Both of the ending columns shown are integer fields, the "" here being > used to indicate a NULL value. > > Without the patch, an ERROR occurs: > > sfrost=> \copy billing_data from ~/BillingSamplePricerFile.csv > with csv header quote as '"' > ERROR: invalid input syntax for integer: "" > > And there's no way to get it to import with COPY CSV mode. The > patch adds this ability without affecting existing usage or changing > the syntax. Even with the patch an ERROR occurs with the default > treatment of CSV files: > > sfrost=# \copy billing_data from ~/BillingSamplePricerFile.csv > with csv header quote as '"' > ERROR: invalid input syntax for integer: "" > > Which would be expected. If the file is modified to remove the ""s > for NULL columns, it imports just fine with the syntax above. > > It'd be really nice to have this included. > > Thanks! > > Stephen [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate