Skip site navigation (1) Skip section navigation (2)

allow CSV quote in NULL

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-patches(at)postgresql(dot)org
Subject: allow CSV quote in NULL
Date: 2007-07-27 04:36:54
Message-ID: 20070727043653.GD4887@tamriel.snowman.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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: pgsql.csv.diff
Description: text/x-diff (2.1 KB)

Responses

pgsql-hackers by date

Next:From: Stephen FrostDate: 2007-07-27 04:45:31
Subject: Building CVS problem?
Previous:From: Tom LaneDate: 2007-07-27 04:30:55
Subject: Re: stats_block_level

pgsql-patches by date

Next:From: Stephen FrostDate: 2007-07-27 04:50:28
Subject: psql \COPY accepts multiple NULL AS
Previous:From: Hiroshi SaitoDate: 2007-07-27 04:33:54
Subject: Re: [PATCHES] patch win32.mak of libpq

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group