Thread: using the "copy from" command to load tables from a txt flat file

Hi out there,

I'm a first-time user of the "Copy ... From..." command, and I'm trying
to load a table from a text flat file.

http://www.postgresql.org/docs/7.4/static/sql-copy.html

I don't know if I'm using the command correctly.
Question:  I run this command as a superuser, and the "copy from"
command is run like a SQL command on the command line, correct?
I get the following error.  Any suggestions would be greatly
appreciated.

[mknepper@barney datafiles]$ psql -U postgres medispan
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

medispan=# COPY mmw_ade_com FROM
'/home/mknepper/medispan/datafiles/mmwadecom.txt' WITH DELIMITER '|';
ERROR:  COPY command, running in backend with effective uid 500, could
not open file '/home/mknepper/medispan/datafiles/mmwadecom.txt' for
reading.  Errno = Permission denied (13).
medispan=#



Table schema:

CREATE TABLE mmw_ade_com
(
    gpi                         VARCHAR(14) NOT NULL,
    mcid                        INTEGER NOT NULL,
    restrictionid               INTEGER NOT NULL,
    sequencenumber              SMALLINT NOT NULL,
    textid                      INTEGER,
    PRIMARY KEY (gpi, mcid, restrictionid, sequencenumber)
);



example of data, from the TXT flat file, called mmwadecom.txt:

01100040100310|5|0|10|14608
01100040100310|8|0|10|17377
01100040100310|8|0|20|18061
01100040100310|8|0|30|14608
01100040100310|22|0|10|18025
01100040100310|30|0|10|14608
01100040100310|36|0|10|14608
01100040100310|115|0|10|13937
01100040100310|115|0|20|18041

Knepper, Michelle wrote:
> Hi out there,
>
> I'm a first-time user of the "Copy ... From..." command, and I'm trying
> to load a table from a text flat file.
>
> http://www.postgresql.org/docs/7.4/static/sql-copy.html
>
> I don't know if I'm using the command correctly.
> Question:  I run this command as a superuser, and the "copy from"
> command is run like a SQL command on the command line, correct?
> I get the following error.  Any suggestions would be greatly
> appreciated.

The user you're logged in as doesn't matter to "copy from".  It's
the user that the Postgres server is running as that is important.

> [mknepper@barney datafiles]$ psql -U postgres medispan
> Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
>
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
>
> medispan=# COPY mmw_ade_com FROM
> '/home/mknepper/medispan/datafiles/mmwadecom.txt' WITH DELIMITER '|';
> ERROR:  COPY command, running in backend with effective uid 500, could
> not open file '/home/mknepper/medispan/datafiles/mmwadecom.txt' for
> reading.  Errno = Permission denied (13).

This error is pretty explicit.  The server is running as UID 500, and
UID 500 doesn't have read access to the file in question.  An easy
solution could be to get everyone read access to the file.  If your
security concerns are high, you'll have to work out something better,
but your definately getting hung up on file permissions.

For future reference:
copy runs with the permissions of the server process.
\copy runs with the permissions of the client process.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com