COPY seems to work, but no data in the table

On Mon, Jul 15, 2002 at 07:36:58PM -0700, Brian Dougherty wrote:

I executed a successful COPY, or at least psql responded "COPY", but
when I looked at the table with SELECT *, no rows were found.

What's going on there?

Can you give us a full example? For instance, the definition of the
table, the command you're using to COPY in data, and a sample of
the source data.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

In article <20020716162506.GB27614@klamath.dyndns.org>, Neil Conway wrote:

On Mon, Jul 15, 2002 at 07:36:58PM -0700, Brian Dougherty wrote:

I executed a successful COPY, or at least psql responded "COPY", but
when I looked at the table with SELECT *, no rows were found.

What's going on there?

Can you give us a full example? For instance, the definition of the
table, the command you're using to COPY in data, and a sample of
the source data.

I have run in to the exact same problem (check in the archives...
"missing foreign key fails silently using COPY")

oh. Here is is:

http://archives.postgresql.org/pgsql-bugs/2002-02/msg00173.php

(you can view source if the page appears blank...)

What I found was that if a foreign key constraint was not satisfied
while COPYing data in to a table, the whole COPY would fail, but
there was never any error message.

As I recall it, Tom Lane looked at this problem at the time and said
that it would be less than simple to fix.

Lee Harr <missive@frontiernet.net> writes:

oh. Here is is:
http://archives.postgresql.org/pgsql-bugs/2002-02/msg00173.php

What I found was that if a foreign key constraint was not satisfied
while COPYing data in to a table, the whole COPY would fail, but
there was never any error message.

As I recall it, Tom Lane looked at this problem at the time and said
that it would be less than simple to fix.

The example I cited in my followup works in CVS tip ... or at least
it reports a failure message:

regression=# create table foo(f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE TABLE
regression=# create table bar(f2 int references foo);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
regression=# copy bar from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

1
\.

ERROR: $1 referential integrity violation - key referenced from bar not found in foo
lost synchronization with server, resetting connection
regression=#

I believe it works the same way in 7.2.1, but don't have an
exactly-7.2.1-release code tree handy to check. (This is a post-7.2
bug fix though, 7.2 will fail :-()

regards, tom lane

Tom Lane wrote:

ERROR: $1 referential integrity violation - key referenced from bar
not found in foo
lost synchronization with server, resetting connection
regression=#

I believe it works the same way in 7.2.1, but don't have an
exactly-7.2.1-release code tree handy to check. (This is a post-7.2
bug fix though, 7.2 will fail :-()

I do:

test=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

test=# create table foo(f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'foo_pkey' for table 'foo'
CREATE
test=# create table bar(f2 int references foo);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
test=# copy bar from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

1
\.

ERROR: <unnamed> referential integrity violation - key referenced from
bar not found in foo
lost synchronization with server, resetting connection

Same result.

Joe