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

Re: allow CSV quote in NULL

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: allow CSV quote in NULL
Date: 2007-09-26 08:38:54
Message-ID: 200709260838.l8Q8csB11970@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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  <bruce(at)momjian(dot)us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-09-26 08:52:35
Subject: Re: [HACKERS] Include Lists for Text Search
Previous:From: Bruce MomjianDate: 2007-09-26 08:37:33
Subject: Re: pgcrypto & strong ciphers limitation

pgsql-patches by date

Next:From: Bruce MomjianDate: 2007-09-26 08:48:11
Subject: Re: strpos() && KMP
Previous:From: Bruce MomjianDate: 2007-09-26 08:36:30
Subject: Re: Reviewing new index types (was Re: [PATCHES] Updated bitmap indexpatch)

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