BruceDB has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to import a file into a table in Postgres. I'm unable to use COPY as I'm not a superuser. Thus, I'm using \copy. Based on the little I'm able to find online I came up with the following code:<\p> 
...
my $data_file = "data.tsv";

my $s2 = "\copy (" . $tableName . ") FROM '" . $data_file . "'";
$dbh->do($s2);
...

[download]

However, when I run the script I get the following error:

DBD::Pg::db do failed: ERROR:  syntax error at or near ""
LINE 1: py (testingtable) FROM 'data.tsv'
        ^ at ./dbcopy.pl line 48.

[download]

What is the correct syntax?

Thx!

Replies are listed 'Best First'.
Re: Correct syntax for using \copy to import a file into a Postgres db
by bichonfrise74 (Vicar) on Nov 12, 2009 at 05:02 UTC
    I'm not familiar with the syntax of Postgresql, but I think you just need to do something like this. 
    my $s2 = "\\copy (" ....

    [download]
    Or what you can do is to print $s2 and see if the syntax is valid in Postgresql.
[reply]
[d/l]

      thx,

      i tried \\c but that also wasn't working.

      your second idea was a good one. i printed $s2 and entered it directly into psql.

      that, however, revealed another issue:

      btest-> \copy (testingtable) FROM 'data.tsv'
data.tsv: No such file or directory

      [download]

      now i have to figure out what directory i'm in and what directory the file is in. according to some documentation, i should be able to get the first by entering in \!pwd, however, psql is telling me that is an invalid command.

[reply]
[d/l]
        Hi did u fixed the error? If yes please suggest me with some hint. I'm facing the same error even when giving the correct file path
[reply]

Back to Seekers of Perl Wisdom