Обсуждение: COPY seems to work, but no data in the table
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?
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