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

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On 11/12/2010 02:03 PM, Tarlika Elisabeth Schmitz wrote:
COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '';
^

--
Adrian Klaver
adrian.klaver (AT) gmail (DOT) com

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Tarlika Elisabeth Schmitz <postgresql2 (AT) numerixtechnology (DOT) de> writes:
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

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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


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: \copyarse 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

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Tarlika Elisabeth Schmitz <postgresql2 (AT) numerixtechnology (DOT) de> writes:
I don't believe you can split backslash commands across lines.

You wanted pstdin, not stdin.

regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Sat, 13 Nov 2010 12:01:35 +0000
Tarlika Elisabeth Schmitz <postgresql2 (AT) numerixtechnology (DOT) de> wrote:


"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

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql