Thread: psql -f COPY from STDIN

The following command works fine when pasing it to psql via the -c
option:

cat event.csv | \
psql -c "COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL
AS ''"


When executed from a file via -f, it does nothing (no error messages
either):

event.sql:
COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''

cat event.csv | psql -f event.sql



What's the problem? Many thanks in advance.

-- 

Best Regards,
Tarlika Elisabeth Schmitz

On 11/12/2010 02:03 PM, Tarlika Elisabeth Schmitz wrote:
> The following command works fine when pasing it to psql via the -c
> option:
>
> cat event.csv | \
> psql -c "COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL
> AS ''"
>
>
> When executed from a file via -f, it does nothing (no error messages
> either):
>
> event.sql:
> COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''

COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '';
            ^
 
>
> cat event.csv | psql -f event.sql
>
>
>
> What's the problem? Many thanks in advance.
>


-- 
Adrian Klaver
adrian.klaver@gmail.com

Tarlika Elisabeth Schmitz <postgresql2@numerixtechnology.de> writes:
> The following command works fine when pasing it to psql via the -c
> option:

> cat event.csv | \
> psql -c "COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL
> AS ''"

> When executed from a file via -f, it does nothing (no error messages
> either):

> event.sql:
> COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''

> cat event.csv | psql -f event.sql

I believe that psql's interpretation of "stdin" when reading from a file
is that the COPY data is to come from that same file (look at the script
produced by pg_dump for an example).  So it reads to the end of the
file, which is right away --- otherwise you'd get some incorrect-data
errors.  The data sourced from the cat command is never noticed at all.

I think you can get the effect you're after using \copy ... from pstdin.
See the psql man page.
        regards, tom lane

On Fri, 12 Nov 2010 22:22:11 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

>Tarlika Elisabeth Schmitz <postgresql2@numerixtechnology.de> writes:
>> The following command works fine when pasing it to psql via the -c
>> option:
>
>> cat event.csv | \
>> psql -c "COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL
>> AS ''"
>
>> When executed from a file via -f, it does nothing 
>
>> event.sql:
>> COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''
>
>> cat event.csv | psql -f event.sql
>
>[...]
>
>I think you can get the effect you're after using \copy ... from
>pstdin. See the psql man page.


Thank you for your reply, Tom. I had not appreciated the difference
between \COPY and COPY. The psql command is executed on the client; the
import data are on the client, so I ought to use \COPY.

Strangely, cat event.csv |psql -c "COPY ...   worked, presumably
because both the COPY command and data came from the same source (client
stdin).

I changed event.sql (3 lines):
\COPY   (event_id, event_name)   FROM STDIN DELIMITER AS ',' NULL AS ''

Now I am getting error messages:
psql:event.sql:1: \copy:parse error at end of line
psql:event.sql:4: ERROR:syntax error at or near "event_id" LINE 1:
(event_id,

When I remove the linefeeds I don't get errors but it does not import
anything.

I'd like the store the COPY command in a separate file without
specifying an input file name. I want to feed it the data from the
shell script that calls psql -f.

-- 

Best Regards,
Tarlika Elisabeth Schmitz

Tarlika Elisabeth Schmitz <postgresql2@numerixtechnology.de> writes:
> I changed event.sql (3 lines):
> \COPY
>     (event_id, event_name)
>     FROM STDIN DELIMITER AS ',' NULL AS ''

> Now I am getting error messages:
> psql:event.sql:1: \copy:parse error at end of line

I don't believe you can split backslash commands across lines.

> When I remove the linefeeds I don't get errors but it does not import
> anything.

You wanted pstdin, not stdin.
        regards, tom lane

On Sat, 13 Nov 2010 12:01:35 +0000
Tarlika Elisabeth Schmitz <postgresql2@numerixtechnology.de> wrote:

>I'd like the store the COPY command in a separate file without
>specifying an input file name. I want to feed it the data from the
>shell script that calls psql


"STDIN: All rows are read from the same source that issued the command"
- As I understand now, this applies to both COPY and \COPY. In other
words the input file must contain command and data.


I have found a few solutions to achieve my objective:

1) using COPY FROM STDIN
cat event.csv | psql -c "$(cat event.sql)"

2) using COPY FROM STDIN
psql -f  <(cat event.sql event.csv)

3) using \COPY FROM PSTDIN
cat event.csv | psql -f event.sql

4) using \COPY FROM STDIN
psql -f  <(cat event.sql event.csv <(echo "\."))


What I don't like about \COPY is that it has to be on one line. Indeed
it can't be split over multiple lines, Tom, not with backslash either.

\COPY FROM STDIN requires the data to be terminated with "\." whereas
COPY is happy with EOF.

I'll go for 2) because it allows me to format the COPY command over
multiple lines.

-- 

Best Regards,
Tarlika Elisabeth Schmitz