Reid Thompson wrote:
Allan Kamau wrote:
Sam, I have been unable to understand your shell script well enough
to use it. Seems am slow this afternoon :-)
On this list I saw a message detailing using copy as illustrated
below (see <code/>)when I run this command I get the following output
(see <output/>)
<output>
COPY abc FROM STDIN WITH CSV HEADER;
\.
1 qrst a
2 zvy b
</output>
As you can see the ./ is placed a the top instead of the bottom of
the output. The does create some error when I run this output via psql.
I then get a datatype error when I pass to psql the following
(edited) sql from a text editor (see <sql/>)
<sql>
COPY abc FROM STDIN WITH CSV HEADER;
1 qrst a
2 zvy b
\.
</sql>
The error reads as follows
<output2>
psql:sql/some2.sql:7: ERROR: invalid input syntax for integer:
"1 qrst a"
CONTEXT: COPY item_major, line 1, column id: "1 qrst a"
</output2>
<code>
\echo 'COPY abc FROM STDIN WITH CSV HEADER;'
COPY
(
SELECT * FROM abc
)
to STDOUT
WITH delimiter E'\t'
\echo '\\.'
</code>
Sam Mason wrote:
On Tue, Oct 28, 2008 at 01:25:00PM +0200, Allan Kamau wrote:
The alternative I am attempting is to use "COPY abc FROM STDIN WITH
HEADER". I pipe the contents of the CSV file on my PC to the psql
command (that connects to the remote PC) while issuing this copy
command.
This does seems not to work.
It does whenever I try it and if you've ever restored from a pg_dump
then you've used it as well!
Is there a way around it.
When I've had a CSV file and needed to bung it into a database, I've
tended to end up with shell scripts like this before:
( echo 'COPY abc FROM STDIN WITH CSV HEADER;'
cat "$1"
echo '\.'
) | psql
an alternative is to use the "\copy" feature inside psql that does this
sort of thing internally. One thing to be aware of is that it doesn't
expect a semicolon at the end of the line, but is otherwise the same as
the SQL COPY command.
Sam
are these space delimited values, or tab delimited values?
1 qrst a
2 zvy b
They are (tab delimited values), I've rechecked them, did a find and
replace for any space between the columns with a tab, the error persists.
Allan.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
