I used a COPY command to load a bunch of data in the database and then created the primary keys and foreign keys afterwards.
When I created one of the foreign keys, I got the error message: violates foreign key constraint "some_fkey" . Is there a way that I can raise the log level so that I can see on which row this violation happened so that I can go back and debug/check the data?
A workaround:
Suppose your tables look like this:
CREATE TABLE a (a_id INTEGER PRIMARY KEY, x TEXT);
CREATE TABLE b (b_id INTEGER PRIMARY KEY, a_id INTEGER, x TEXT);
And the creation of the following foreign key gives you the error:
ALTER TABLE b ADD CONSTRAINT a_a_id_fk FOREIGN KEY (a_id) REFERENCES a (a_id) ON UPDATE CASCADE ON DELETE RESTRICT;
You could join the two tables to get the problematic rows:
SELECT b_id, a_id FROM b LEFT JOIN a USING (a_id) WHERE a.a_id IS NULL;
Let's see!
Sample data:
INSERT INTO a VALUES (1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO b VALUES (1, 1, 'x'), (2, 3, 'y'), (3, 1000, 'z');
And the result of the query above:
b_id | a_id
------+------
3 | 1000
(1 row)
