For a statment show all rows that violate constraints

Question

In PostgreSQL I am inserting several rows to a table from select. If there is some constraint violation I can see an error that shows data that violates constraint. But only one row.

Is there a way to see all rows that violates constraints? I know one solution use PLSQL and cursor, but this is toooo slow for my case. Any other solution? Sample code that illustrates the issue.

create table t_source (id int, ref int);
insert into t_source (id,ref) values (1,1),(1,2),(1,3),(1,4),(1,5);

create table t_dict(id int primary KEY, name varchar);
insert into t_dict (id,name) values (2,'two'),(3,'three');

create table t_target (
 id int, ref int,
 FOREIGN KEY (ref) REFERENCES t_dict(id)
);

insert into t_target (id,ref) select id,ref from t_source;

shows one error:

SQL Error [23503]: ERROR: insert or update on table "t_target" violates foreign key constraint "t_target_ref_fkey"
  Detail: Key (ref)=(1) is not present in table "t_dict".

In this specific case I think you can just check if the data exist in both table. and simply keep the rows exist in both table. — T. Peter, Feb 18 '21 at 09:42
This is just an example. Real case has 150 lines of SQL and client specific stuff. — Denis, Feb 18 '21 at 10:07

T. Peter · Answer 1 · 2021-02-18T09:57:25.700

0

I think OP can check if same data exist in both table. slightly alter the insert statement that exclude all data fail to exist in both t_source and t_dict.

insert into t_target (id,ref) select id,ref 
from (select id,ref from t_source where ref in (select id from t_dict)) a;

here is db<>fiddle

if you just want to know which row violate the FK then you can use :

select id,ref from t_source where ref not in (select id from t_dict)

edited Feb 18 '21 at 09:57

answered Feb 18 '21 at 09:48

T. Peter

887
4
13

Well, yes, this is what we are doing currently. But because this is ~150 lines of code (worst case, actually we have ~100 queries like this). We are moving data between two databases and we need to know all rows that causes issues because in some cases client support team resolves issues by fixing source DB – Denis Feb 18 '21 at 10:11
well then please include all the information in your question as those are needed to actually solve your case. – T. Peter Feb 18 '21 at 10:13
all info IS there, just read the question one more time – Denis Feb 18 '21 at 10:13
@Denis your comment IS the info we need, you never describe your actual case and with sample data those ARE valid answer. if those thing is not working and is what you have done at least describe those situation in question which is clearly NOT in your question. – T. Peter Feb 19 '21 at 00:29

score -1 · Answer 2 · answered Feb 18 '21 at 10:42

To see all rows violating this particular FK constraint:

SELECT *
FROM   t_source s
WHERE  NOT EXISTS (
   SELECT FROM t_dict
   WHERE id = c.ref
   );

See:

Select rows which are not present in other table

But there is no way to show all rows violating any (unknown) constraint in the target table. Once we know the constraints, we can formulate a query to test for them ...

For a statment show all rows that violate constraints

2 Answers2