RESTRICT and NO ACTION where to see the difference?

Question

PostgreSQL

Reading the documentation:
RESTRICT prevents the associated row from being deleted. NO ACTION means that if dependent rows continue to exist when the constraint is checked, an error occurs (this is the default behavior). (The main difference between these two options is that NO ACTION allows you to defer validation during a transaction, while RESTRICT does not.)

And not a single example to see this difference.

Answer 1

Well, here's an example for you.

create table p (id serial primary key, val text);

create table c (id serial primary key, p_id int not null references p(id) on delete no action deferrable, val text);

insert into p (val) values ('a'), ('b');

insert into c (p_id, val) values (1, 'a1'), (1, 'a2'), (1, 'a3'), (2, 'b1');

begin;
set constraints all deferred;
delete from p where id = 2;
delete from c where p_id = 2;
commit;

Read also about deferred restrictions