Обсуждение: Numeric and CSV under 8.0.1 ?
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
nullfirstname | character(25) | not nulllastname | 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 ?RegardsSteph
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
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 >
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/
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 > >