Unexpected update count received (Actual: 0, Expected: 1). - Unable to update view by using sql rules



I am a happy user of your Intellij IDE. I am working on PostgreSQL database right now and I like the idea to be able to update views. Since I am using a View of multiple tables, I use rules to do a proper update.

However, underlying jdbc on in the Intellij seems to be a bit strick regarding returned rows.

E.g. if I wanted to create the following rule:

create or replace rule my_rule
AS ON UPDATE to public.my_view

I would get an error:

Unexpected update count received (Actual: 0, Expected: 1). All changes will be rolled back.

psql runs completely fine on it. Even if I run a query from the query console, it all works fine. E.g.

update public.my_view set category_name = 'new_name' where category_pk = 1;

It seems that the view graphical interface has a problem with it.

Thank you for help, 


Comment actions Permalink

Thanks for reporting this issue.
I am trying to reproduce this issue with a SQL like your provided one but failed to reproduce it.
Would it be possible to share a sample DDL of your `public.my_view` for checking?

Also, what's your current IDE version and the PostgreSQL driver version: https://www.jetbrains.com/help/datagrip/configuring-database-connections.html#using-jdbc-drivers
Comment actions Permalink

Hi Lejia,

here is the code that does not work. 

First, create two simple tables and connect them together through a separate table:

create table _testing.first_name (
id bigserial primary key,
first_name varchar(255)

create table _testing.last_name (
id bigserial primary key,
second_name varchar(255)

create table _testing.first_to_last_name(
first_id bigint,
last_id bigint,
constraint fk_first_id foreign key (first_id) references _testing.first_name(id),
constraint fk_last_id foreign key (last_id) references _testing.last_name(id),
primary key (first_id, last_id)

Then you create a view for those two tables:

create view _testing.full_name as
first.id as first_id,
last.id as last_id,
from _testing.first_name first
left join _testing.first_to_last_name ftln on first.id = ftln.first_id
left join _testing.last_name last on ftln.last_id = last.id;

You also insert some values:

insert into _testing.first_name(first_name) VALUES ('Thomas');
insert into _testing.first_name(first_name) VALUES ('Peter');
insert into _testing.first_name(first_name) VALUES ('George');
insert into _testing.first_name(first_name) VALUES ('John');

insert into _testing.last_name(second_name) VALUES ('Jefferson');
insert into _testing.last_name(second_name) VALUES ('Pan');
insert into _testing.last_name(second_name) VALUES ('Lukas');
insert into _testing.last_name(second_name) VALUES ('Doe');

Connection table looks like this:

first_id | last_id
   1     |    1
   2     |    2
3     | 3
4     | 4

Finally, you create one of these rules, I just want to show you that it does not work on various scenarios.)

create or replace rule rule1
AS ON UPDATE to _testing.full_name
insert into _testing.last_name(second_name) VALUES ('BBB');

create or replace rule rule2
AS ON UPDATE to _testing.full_name

Now, if you open full_name view in the IDE, it will look like this.

If you try to update any row, you will get an error:

But if you run an update from the console, it just works fine:

update _testing.full_name set first_name = 'a' where first_id = 1;

Thank you and best regards, 


Comment actions Permalink

Hey Lejia,

One more thing, please. If you fail to reproduce it, please send me your version of the tables and views and I will try your code.

My PostgreSQL driver is 42.5.0 and IntelliJ IDEA 2022.2.4 (Ultimate Edition)

Best regards, 



Comment actions Permalink

@Vlcek Vv Really thanks for the detailed steps and that's really helpful.
I could reproduce this.

I have created a bug report here: https://youtrack.jetbrains.com/issue/DBE-17423/Unable-to-update-view-by-using-sql-rules-when-using-PostgreSQL-due-to-Unexpected-update-count-received-Actual-0-Expected-1-error

Please watch/vote for it so you can receive notifications about this issue when there are any updates.

Please sign in to leave a comment.