CODEWITHSUNDEEP
phppostgresqlcodeigniter
DS9
DS9

Reputation: 3033

affected rows in postgres with CodeIgniter

I have a following query:

UPDATE "user" SET "first_name" = 'abc', "last_name" = '', "mobile" = '988988888', "comment" = 'Hello' WHERE "id" = '15'

and above data is already in db. means i submit form without changing any data.

I hit above query in terminal and it says : UPDATE 1

CI CODE:

$query = $this->db->get_where('user',array('id'=>$id),1);
if ($query->num_rows() > 0)
{
    $this->db->update('user', $data, array('id'=>$id));         
    echo $afftected_rows = $this->db->affected_rows();exit;
}

DB SCHEMA(export using psql cmmand)

CREATE TABLE "user" (
  id integer NOT NULL,
  first_name character varying(50),
  last_name character varying(50),
  mobile character varying(50),  
  comment character varying(500)
);

so what is the problem? why it is return 1, even if i don't change any data. is this the normal behaviour for postgres?

Upvotes: 0

Views: 1163

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61516

Yes, it's the normal behavior.

MySQL has the notion of "matched rows" and "affected rows" which might differ: when a "matched row" would be updated with the values that it already holds, it's not affected.

In PostgreSQL, only the count of "affected rows" comes back to the user, and all "matched rows" are affected.

To be complete, strictly speaking, this behavior is amendable in PostgreSQL. Skipping these updates is always possible with a fairly generic trigger.

Example:

First the baseline,default behavior, to compare against:

test=> create table update_test(val text);
CREATE TABLE
test=> insert into update_test values('abc');
INSERT 0 1
test=> update update_test set val='abc';
UPDATE 1

This UPDATE 1 indicates that 1 row was affected, even though the value is the same.

Now let's tell it to skip the rows for which values don't change.

-- generic trigger to void the update of one row
create function void_update() returns trigger language plpgsql as 
 'begin return null; end';

-- trigger affecting unchanged rows in a BEFORE UDPATE event
create trigger update_trigger 
  before update on update_test for each row
  when (old is not distinct from new)
  execute procedure void_update();

Now we get the MySQL-like behavior:

test=> update update_test set val='abc';
UPDATE 0

0 row is affected, because we're updating the only row of the table with a value it already has.

Test again with more data, some rows to skip, other rows to change:

test=> insert into update_test values('def'),('ghi');
INSERT 0 2
test=> select * from update_test ;
 val 
-----
 abc
 def
 ghi
(3 rows)

test=> update update_test set val='ghi';
UPDATE 2

Only 2 rows were affected since the last row already contained 'ghi'

Check that the update actually works:

test=> select * from update_test ;
 val 
-----
 ghi
 ghi
 ghi
(3 rows)

Upvotes: 2

Related Questions