Skip to main content

Copying data to tables with . in the name

  • April 1, 2014
  • 6 replies
  • 29 views
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

    6 replies

    Sharon_Cutter
    Forum|alt.badge.img
    • Sharon_Cutter
    • Participating Frequently
    • April 1, 2014
    According to your example, your table name is "evil.table" not "evil_table" as in your COPY statement.
    Kevin_Rose
    • Kevin_Rose
    • Author
    • New Participant
    • April 1, 2014
    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
    Forum|alt.badge.img
    • Evan_H
    • Participating Frequently
    • April 1, 2014
    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
    • Kevin_Rose
    • Author
    • New Participant
    • April 1, 2014
    That doesn't help :(
    Kevin_Rose
    • Kevin_Rose
    • Author
    • New Participant
    • April 1, 2014
    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.


    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement