pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNumeric and CSV under 8.0.1 ?
5+ messages / 4 participants
[nested] [flat]
* Numeric and CSV under 8.0.1 ?
@ 2005-04-08 16:51 Stef <stef@ummon.com>
0 siblings, 2 replies; 5+ messages in thread
From: Stef @ 2005-04-08 16:51 UTC (permalink / raw)
To: pgsql-sql
Hello Everyone,
Currently, here at work, I am doing the whole
'advocacy' part of postgreSQL. It's not really hard to
do, as the other database's are MySQL and Sybase ;)
There is obviously a whole spat of data munging
going on in the background, and I noticed that psql in
8.0.1 now accepts CSV ! Great.
Except, it doesn't really appear to be 100% there.
Numeric's wrapped in '...' don't want to appear to go in.
Is this a 'known problem' ?
Table "public.visitor_main"
Column | Type | Modifiers
------------------+----------------------+------------------------------
iuserid | numeric(12,0) | not null
firstname | character(25) | not null
lastname | character(25) | not null
Sample Data line
'3236','Alonzo','Peter'
ERROR: invalid input syntax for type numeric: "'3236'"
CONTEXT: COPY visitor_main, line 1, column iuserid: "'3236'"
Thoughts ?
Regards
Steph
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numeric and CSV under 8.0.1 ?
@ 2005-04-08 17:56 Jim Johannsen <jjsa@gvtc.com>
parent: Stef <stef@ummon.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jim Johannsen @ 2005-04-08 17:56 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Stef:
Why is iuserid numeric? Are you going to do any math on the field?
If not, change it to varchar. In the long run you'll be happier.
Stef wrote:
>Hello Everyone,
> Currently, here at work, I am doing the whole
>'advocacy' part of postgreSQL. It's not really hard to
>do, as the other database's are MySQL and Sybase ;)
>
> There is obviously a whole spat of data munging
>going on in the background, and I noticed that psql in
>8.0.1 now accepts CSV ! Great.
>
> Except, it doesn't really appear to be 100% there.
>Numeric's wrapped in '...' don't want to appear to go in.
>Is this a 'known problem' ?
>
> Table "public.visitor_main"
> Column | Type | Modifiers
>------------------+----------------------+------------------------------
> iuserid | numeric(12,0) | not null
> firstname | character(25) | not null
> lastname | character(25) | not null
>
>
>Sample Data line
>'3236','Alonzo','Peter'
>
>ERROR: invalid input syntax for type numeric: "'3236'"
>CONTEXT: COPY visitor_main, line 1, column iuserid: "'3236'"
>
>
> Thoughts ?
> Regards
> Steph
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numeric and CSV under 8.0.1 ?
@ 2005-04-08 18:12 Keith Worthington <keithw@narrowpathinc.com>
parent: Stef <stef@ummon.com>
1 sibling, 1 reply; 5+ messages in thread
From: Keith Worthington @ 2005-04-08 18:12 UTC (permalink / raw)
To: Stef <stef@ummon.com>; pgsql-sql
On Fri, 8 Apr 2005 12:51:47 -0400, Stef wrote
> Hello Everyone,
> Currently, here at work, I am doing the whole
> 'advocacy' part of postgreSQL. It's not really hard to
> do, as the other database's are MySQL and Sybase ;)
>
> There is obviously a whole spat of data munging
> going on in the background, and I noticed that psql in
> 8.0.1 now accepts CSV ! Great.
>
> Except, it doesn't really appear to be 100% there.
> Numeric's wrapped in '...' don't want to appear to go in.
> Is this a 'known problem' ?
>
> Table "public.visitor_main"
> Column | Type | Modifiers
>
> ------------------+----------------------+------------------------------
> iuserid | numeric(12,0) | not null
> firstname | character(25) | not null
> lastname | character(25) | not null
>
> Sample Data line
> '3236','Alonzo','Peter'
>
> ERROR: invalid input syntax for type numeric: "'3236'"
> CONTEXT: COPY visitor_main, line 1, column iuserid: "'3236'"
>
> Thoughts ?
> Regards
> Steph
Steph,
'3236' is a string not a numeric. As I see it (novice that I am) you have
three choices. 1) Write an external program (gawk, sed, etc.) to remove the
quotes around that field. 2) Import the data into an intermediate table and
then using an after trigger move and manipulate the data using CAST. 3)
Import the data into your table using a BEFORE trigger and manipulate the data
using CAST.
HTH
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numeric and CSV under 8.0.1 ?
@ 2005-04-08 18:25 Stef <stef@ummon.com>
parent: Keith Worthington <keithw@narrowpathinc.com>
0 siblings, 1 reply; 5+ messages in thread
From: Stef @ 2005-04-08 18:25 UTC (permalink / raw)
To: Keith Worthington <keithw@narrowpathinc.com>; +Cc: pgsql-sql
Hello Keith,
well, I can understand that 3265 appears to be a
string, but, I was under the impression that -everything-
in a CSV format file could have ' ' around it? Is this not
the case ?
Sorry if I am being completely insane here :)
Steph
On Fri, Apr 08, 2005 at 02:12:11PM -0400, Keith Worthington wrote:
> On Fri, 8 Apr 2005 12:51:47 -0400, Stef wrote
> > Hello Everyone,
> > Currently, here at work, I am doing the whole
> > 'advocacy' part of postgreSQL. It's not really hard to
> > do, as the other database's are MySQL and Sybase ;)
> >
> > There is obviously a whole spat of data munging
> > going on in the background, and I noticed that psql in
> > 8.0.1 now accepts CSV ! Great.
> >
> > Except, it doesn't really appear to be 100% there.
> > Numeric's wrapped in '...' don't want to appear to go in.
> > Is this a 'known problem' ?
> >
> > Table "public.visitor_main"
> > Column | Type | Modifiers
> >
> > ------------------+----------------------+------------------------------
> > iuserid | numeric(12,0) | not null
> > firstname | character(25) | not null
> > lastname | character(25) | not null
> >
> > Sample Data line
> > '3236','Alonzo','Peter'
> >
> > ERROR: invalid input syntax for type numeric: "'3236'"
> > CONTEXT: COPY visitor_main, line 1, column iuserid: "'3236'"
> >
> > Thoughts ?
> > Regards
> > Steph
>
> Steph,
>
> '3236' is a string not a numeric. As I see it (novice that I am) you have
> three choices. 1) Write an external program (gawk, sed, etc.) to remove the
> quotes around that field. 2) Import the data into an intermediate table and
> then using an after trigger move and manipulate the data using CAST. 3)
> Import the data into your table using a BEFORE trigger and manipulate the data
> using CAST.
>
> HTH
>
> Kind Regards,
> Keith
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Numeric and CSV under 8.0.1 ?
@ 2005-04-08 18:39 Michael Fuhr <mike@fuhr.org>
parent: Stef <stef@ummon.com>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-04-08 18:39 UTC (permalink / raw)
To: Stef <stef@ummon.com>; +Cc: Keith Worthington <keithw@narrowpathinc.com>; pgsql-sql
On Fri, Apr 08, 2005 at 02:25:13PM -0400, Stef wrote:
>
> well, I can understand that 3265 appears to be a
> string, but, I was under the impression that -everything-
> in a CSV format file could have ' ' around it? Is this not
> the case ?
See the documentation for COPY -- the default quote character for
CSV is a double quote, but you can change it with QUOTE.
http://www.postgresql.org/docs/8.0/interactive/sql-copy.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-04-08 18:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-08 16:51 Numeric and CSV under 8.0.1 ? Stef <stef@ummon.com>
2005-04-08 17:56 ` Jim Johannsen <jjsa@gvtc.com>
2005-04-08 18:12 ` Keith Worthington <keithw@narrowpathinc.com>
2005-04-08 18:25 ` Stef <stef@ummon.com>
2005-04-08 18:39 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox