Hi Team,
I have a dataset which contains below items. I have used integer and numeric. But Vertica is rejecting data. What is best option to store such values as integers/float and not varchar?
1,234
54.1%
commas and % in the dataset
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: commas and % in the dataset
Hi,
You can use the FILLER option of the COPY command with a regular expression function. Something like the following:
You can use the FILLER option of the COPY command with a regular expression function. Something like the following:
Code: Select all
dbadmin=> create table t (c numeric);
CREATE TABLE
dbadmin=> \! cat /home/dbadmin/t.txt
1,234
54.1%
dbadmin=> copy t(c_f filler varchar(100), c as REGEXP_REPLACE(c_f, '[^\d.-]', '')::numeric) from '/home/dbadmin/t.txt';
Rows Loaded
-------------
2
(1 row)
dbadmin=> select * from t;
c
----------------------
1234.000000000000000
54.100000000000000
(2 rows)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.