Copying data to tables with . in the name

Kevin_Rose

I am having trouble copying data from one database to another using the COPY FROM VERTICA command.  It works fine when the table doesn't have a '.' in the name, unfortunately a lot of ours do

For example, do this on the source database:

CREATE TABLE farm."good_table" ( 
    stuff int
);
CREATE TABLE farm."evil.table" (
    stuff int
);
insert into farm."good_table" values (1);
insert into farm."good_table" values (2);
insert into farm."good_table" values (3);
insert into farm."evil.table" values (1);
insert into farm."evil.table" values (2);
insert into farm."evil.table" values (3);
commit;

Then switch to the destination database and do the following:

CREATE TABLE farm."good_table" ( 
    stuff int
);
CREATE TABLE farm."evil.table" (
    stuff int
);
CONNECT TO VERTICA pgvert1 USER ********** PASSWORD ************ ON *************,*********;
COPY farm."good_table" FROM VERTICA pgvert1.farm."good_table";

Everything works fine, but then do this:

COPY farm."evil_table" FROM VERTICA pgvert1.farm."evil_table";

And you get the error:

ERROR 4568:  Relation "farm.evil_table" does not exist

Is there anyway around this?

Thanks
Kevin

Sharon_Cutter

According to your example, your table name is "evil.table" not "evil_table" as in your COPY statement.

Kevin_Rose

Sorry, that was a typo.  My copy statement was actually:

COPY farm."evil.table" FROM VERTICA pgvert1.farm."evil.table";

And it produces:

ERROR 2983:  [pgvert1] Database "farm" does not exist

Evan_H

What if  you try the fully qualified path for the source?  Like this:
COPY pgvert1.farm."evil.table" FROM VERTICA pgvert1.farm."evil.table";

Kevin_Rose

That doesn't help

Kevin_Rose

Sorry, that didn't seem to make any difference

Ramesh_Narayana

Hi Kevin,

I understand that many of your tables have a period in the name itself like evil.table and it works in most of the commands like SELECT, regular COPY etc. as long as you enclose in quotes as you have done above.
A possible work-around for the COPY FROM to work is: -
a. Rename the source table to evil_table
b. Run the COPY FROM evil_table
c. Rename it back to evil.table

If I find more solid information on the root cause, will post it here again.