Can we use 2 delimiters in COPY command?

[jagadeesh]

Hi,

Is it possible to use 2 delimiters in COPY command?

when i use GZIP DELIMITER '~|' its throwing error "ERROR 2730: COPY delimiter must be a single character"

i have PIPELINE, COMMA, '~','^' coming from my source data so i am planning to use combination.

[BoMBaY]

Using a Custom Column Option DELIMITER

This example, redefines the default delimiter through the COLUMN OPTION parameter.

Create a simple table.

Code: Select all

CREATE TABLE t(

pk INT,

col1 VARCHAR(10),

col2 VARCHAR(10),

col3 VARCHAR(10),

col4 TIMESTAMP);

Use the COLUMN OPTION parameter to change the col1 default delimiter to a tilde (~).

Code: Select all

=> COPY t COLUMN OPTION(col1 DELIMITER '~') FROM STDIN NO COMMIT;

>> 1|ee~gg|yy|1999-12-12

>> \.

=> SELECT * FROM t;

pk | col1 | col2 | col3 | col4

----+------+------+------+---------------------

1 | ee | gg | yy | 1999-12-12 00:00:00

(1 row)

Hope this helps.

[jagadeesh]

ok, but whats my scenario is

I have file with '|' as delimiter and i also have data like this

col1|col2
1|App,le
2|Oran~ge
3|What the|hell is this

3rd row 2nd column value has '|'
how to solve this?

[JimKnicely]

You can enclose the text in your source file with quotes and let Vertica know in the COPY command.

Example:

Code: Select all

dbadmin=> \! cat /home/dbadmin/jim.txt
1|"App,le"
2|"Oran~ge"
3|"What the|hell is this"
dbadmin=> copy test from '/home/dbadmin/jim.txt' enclosed by '"';
 Rows Loaded 
-------------
           3
(1 row)

dbadmin=> select * from test order by 1;
 col1 |         col2          
------+-----------------------
    1 | App,le
    2 | Oran~ge
    3 | What the|hell is this
(3 rows)

[id10t]

Hi!

You don't need a combination, just use in non-printed ASCII char as delimiter, for example '\033' (escape char - hard to believe that you have it in text ):

Code: Select all

daniel@synapse:/tmp$ vsql -c "drop table if exists foo cascade"
DROP TABLE
daniel@synapse:/tmp$ vsql -c "create table foo (id int, text varchar)"
CREATE TABLE
daniel@synapse:/tmp$ echo -e "1\033foo" | vsql -c "copy foo from stdin delimiter e'\033'"
daniel@synapse:/tmp$ vsql -c "select * from foo"
 id | text 
----+------
  1 | foo
(1 row)

Also, you can use in '\n' (new line as delimiter) and for record separator use in combination of chars (its possible). For example, look at COPY statements in DataCollector folder - there fields separator is a new line and record separator is a combination '\n.\n' (new line, dot,new line)