Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 039E313F0F81 for ; Sat, 14 Apr 2012 20:17:26 -0300 (ADT) Received: from nm10.bullet.mail.sp2.yahoo.com ([98.139.91.80]) by magus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1SJCDa-00027k-To for pgsql-admin@postgresql.org; Sat, 14 Apr 2012 23:17:25 +0000 Received: from [98.139.91.70] by nm10.bullet.mail.sp2.yahoo.com with NNFMP; 14 Apr 2012 23:17:08 -0000 Received: from [98.139.91.39] by tm10.bullet.mail.sp2.yahoo.com with NNFMP; 14 Apr 2012 23:17:08 -0000 Received: from [127.0.0.1] by omp1039.mail.sp2.yahoo.com with NNFMP; 14 Apr 2012 23:17:08 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 425022.30695.bm@omp1039.mail.sp2.yahoo.com Received: (qmail 92359 invoked by uid 60001); 14 Apr 2012 23:17:08 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1334445428; bh=0DBZ0OpRfqnlIC2mrarnPCD+n4dM4DQnMb9E3EWa0lo=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=Y2PPN1DxXP90k9PdsNqCrrvY3/VLZ5tsVIr6Q1zmNjTkW0qgsq0T8QvBE0TmuzqTmgqw65fldQ8zOk1AVxr/PIuqfVQONI5SnOPwSqDpe8MdlsUMJ4P/n0O2lKbyOJciMuukFu7lQsfOqQNFzfutWqsUHf+uEnif56wvaujx3Lw= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=eWJuIvgx+KT0CTbhJXmd0Ffsi6UIXKI6nT18TcI3UJKrjxkrv2AWOPReBxmW7F1+mLhboUX+M0Lc1SDOV3GCZuCrW4Wolirb/9qJP1ObWGxmM0iHWQsI5STlI08hFBCLzcf/ISjMmx3TfQQ11cE2BLYrzQaaL30khntXQu03zaA=; X-YMail-OSG: jFs8doAVM1lxFHaDanQVfunwMlvxUh214kfW8zY4RIR10ms JEgfY5ZWYrtr4jbDfyZttHmlKnoRjMszcFHVg4ciF7M5aN2d8I7H4kJx5CjF vvTKCtYWT_1c5VuGYg3iYuZ45wvZHGXlFLRADtnrae43bH7Y2co4ULlTFY2v Fi7VJtjHJyTkYgo_KdfSjeuqcA_s_CRPQxN9fuL.p.VXczIJeR2q7LeNpomf Bd8tUcYfmetkUmYwjuTacmwOkYfcxgxqZse5xy49iSNbIQUDz9mSFbKheEjY fkJwArm9wvtOHN9CDnDm4XwtkclkgNs1l6rcQThzs39.Cx7zdO5_.iJWJMMY P8rOVAyodHdVupRvqdaIm.AJSbxxPUP_bwzXFpQfu4nwTz7RslEDNAx6O2tz 1EXlAPUR07xYDlavsM.PR0y6emnDe5a2OlwXUFVcWK_4DLxksEEcJ1hhmzg- - Received: from [98.210.146.78] by web46106.mail.sp1.yahoo.com via HTTP; Sat, 14 Apr 2012 16:17:07 PDT X-Mailer: YahooMailWebService/0.8.117.340979 References: Message-ID: <1334445427.92228.YahooMailNeo@web46106.mail.sp1.yahoo.com> Date: Sat, 14 Apr 2012 16:17:07 -0700 (PDT) From: Greg Williamson Reply-To: Greg Williamson Subject: Re: Trouble w/ COPY command To: Wells Oliver , "pgsql-admin@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -1.5 (-) X-Archive-Number: 201204/72 X-Sequence-Number: 39408 Wells Oliver asked:=0A=0A=0A>=0A>I have CSV files blowing up because they h= ave double quotes around numeric values: " 1.34" yields=A0ERROR: =A0invalid= input syntax for type numeric: " 1.34"=0A>>=0A>And occasionally these are = empty, and you see invalid syntax for type numeric: " "=0A>=0A>Are there fl= ags I can pass to COPY to avoid this? There are properly quoted string valu= es elsewhere in the final, so I'm reticent to strip out double quote charac= ters or anything.=0A>=0A=0A=0AThere's no easy command line method that I kn= ow of. Perhaps if you can detect patterns some sed / awk might help.=0A=0AI= 'd probably load the CSV file into a temporary work table with the columns = that are giving you issues defined as type "text." Then use SQL to remove t= he offending quotation marks from the afflicted columns. Create new columns= of type numeric in the table, and copy the values from the text variant to= the numeric ones with the proper cast one at a time; if it blows up you go= back to resolving issues. Then copy the table using the numeric columns to= the desired destination.=0A=0AHopefully others might have better ideas ...= best o' luck!=0A=0A=0AGreg Williamson=0A