Copy/foreign key contraints
Being fairly new to postgres I'm trying to find my way
around this, did some searching and see this has come
up quite a few times, but..........
table 1: customer.custid primary key
table 2: transx.custid foreign key
When using COPY to import data I received;
"<unnamed>referential integerity violation-key referenced from
table transx not found in customer" (fairly vague statement)
I'm sure many know where this is going, but I'll still explain
what I've tried so far.......
Then created a copy/temp table of transx with no constraints
and COPY'ed data into it, ran some "where not exists" statements
against the customer.custid and found no unmatched
primary/foreign keys. Tried to do an INSERT from the temp table
into the transx table and still got the referential integerity violation.
At this point I'm presuming that my data's ok, so now what.....
Drop the foreign key, import data, then add foreign key back?
But what if there is a real "referential integerity violation"?
How are others dealing with this or am I just doing something
wrong?
postgres 7.2.1 / Rh7.3
Also as a side note;
It would be nice while data import if it
would just create an exception file and continue the import
of data with a way to see/count exceptions at the end of import
with a choice to then process the file and save the exceptions.
Sorry to ramble........
Ken
explore options and output of pgdump, it must do it correctly
Ken Godee wrote:
Show quoted text
Being fairly new to postgres I'm trying to find my way
around this, did some searching and see this has come
up quite a few times, but..........table 1: customer.custid primary key
table 2: transx.custid foreign keyWhen using COPY to import data I received;
"<unnamed>referential integerity violation-key referenced from
table transx not found in customer" (fairly vague statement)I'm sure many know where this is going, but I'll still explain
what I've tried so far.......Then created a copy/temp table of transx with no constraints
and COPY'ed data into it, ran some "where not exists" statements
against the customer.custid and found no unmatched
primary/foreign keys. Tried to do an INSERT from the temp table
into the transx table and still got the referential integerity violation.
At this point I'm presuming that my data's ok, so now what.....Drop the foreign key, import data, then add foreign key back?
But what if there is a real "referential integerity violation"?
How are others dealing with this or am I just doing something
wrong?postgres 7.2.1 / Rh7.3
Also as a side note;
It would be nice while data import if it
would just create an exception file and continue the import
of data with a way to see/count exceptions at the end of import
with a choice to then process the file and save the exceptions.Sorry to ramble........
Ken
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
The data's not originally coming from a postgres database,
it's in a ascii delimited format, sorry for not mentioning that.
Show quoted text
explore options and output of pgdump, it must do it correctly
Ken Godee wrote:
Being fairly new to postgres I'm trying to find my way
around this, did some searching and see this has come
up quite a few times, but..........table 1: customer.custid primary key
table 2: transx.custid foreign keyWhen using COPY to import data I received;
"<unnamed>referential integerity violation-key referenced from
table transx not found in customer" (fairly vague statement)
On Fri, 13 Dec 2002, Ken Godee wrote:
Being fairly new to postgres I'm trying to find my way
around this, did some searching and see this has come
up quite a few times, but..........table 1: customer.custid primary key
table 2: transx.custid foreign keyWhen using COPY to import data I received;
"<unnamed>referential integerity violation-key referenced from
table transx not found in customer" (fairly vague statement)I'm sure many know where this is going, but I'll still explain
what I've tried so far.......Then created a copy/temp table of transx with no constraints
and COPY'ed data into it, ran some "where not exists" statements
against the customer.custid and found no unmatched
primary/foreign keys. Tried to do an INSERT from the temp table
into the transx table and still got the referential integerity violation.
At this point I'm presuming that my data's ok, so now what.....
It's hard to say what's causing that without more details. Can you crete a
simplified self contained example of it misbehaving to send to the list
(or failing that if the data/schema isn't secret and not too big, that)
Ron,
The file I'm COPYing is straight ascii data, and just delimited
no sql statements, I'm adding data to an existing table.
ie.
------file-----------
25467^John Doe^480-555-1212^^12/13/2002
---------------------
"COPY transx from '/tmp/transx.dat' delimiters '^' with null as '';
I had thought this was pretty straight forward, I don't
have a very complex set up and thought I was just missing
something (and probally am) or this was a common problem.
Another poster suggested forwarding alittle more info and
schema to list, which I'll post a little later.
Show quoted text
OOPS, correction as:
Also, if names are NOT double quoted "custID" any reference to them get
converted to lower case, so you're references must be case-correct too.
eg CONSTRAINT "mycon" FOREIGN KEY ("custID") REFERENCES .....Ron St.Pierre wrote:
Check your ascii file and make sure that the column or constraint
names are not quoted. If so, check for spaces at the end of names
eg CONSTRAINT 'mycon' FOREIGN KEY ('custid ') REFERENCES ...... <-
note the space inside quotes after custidAlso, if names are double quoted "custID" any reference to them get
converted to lower case, so you're references must be case-correct too.
eg CONSTRAINT "mycon" FOREIGN KEY ("custID") REFERENCES .....Ken Godee wrote:
The data's not originally coming from a postgres database,
it's in a ascii delimited format, sorry for not mentioning that.Ken Godee wrote:
table 1: customer.custid primary key
table 2: transx.custid foreign keyWhen using COPY to import data I received;
"<unnamed>referential integerity violation-key referenced from
table transx not found in customer" (fairly vague statement)---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre@syscor.com
Import Notes
Reply to msg id not found: 3DFA56BD.5050405@syscor.comReference msg id not found: nAsK9.158373$ea.3080117@news2.calgary.shaw.ca | Resolved by subject fallback
Thanks for everyones feedback but.......
Once again I find myself "Eating crow"
Had nothing to do with postgres, my "not exists" statement
was wrong and didn't find the missing data. After correctly
writting the not exsists statement about 30 rows appeared
in the table with the foreign key with no matches from the
table with the primary key.
Show quoted text
Being fairly new to postgres I'm trying to find my way
around this, did some searching and see this has come
up quite a few times, but..........table 1: customer.custid primary key
table 2: transx.custid foreign keyWhen using COPY to import data I received;
"<unnamed>referential integerity violation-key referenced from
table transx not found in customer" (fairly vague statement)I'm sure many know where this is going, but I'll still explain
what I've tried so far.......Then created a copy/temp table of transx with no constraints
and COPY'ed data into it, ran some "where not exists" statements
against the customer.custid and found no unmatched
primary/foreign keys. Tried to do an INSERT from the temp table
into the transx table and still got the referential integerity violation.
At this point I'm presuming that my data's ok, so now what.....Drop the foreign key, import data, then add foreign key back?
But what if there is a real "referential integerity violation"?
How are others dealing with this or am I just doing something
wrong?postgres 7.2.1 / Rh7.3
Also as a side note;
It would be nice while data import if it
would just create an exception file and continue the import
of data with a way to see/count exceptions at the end of import
with a choice to then process the file and save the exceptions.Sorry to ramble........
Ken
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org