Thread: UPDATE an updatable view
Good morning all,
I am creating an updatable view on a set of tables, and just ran into
unexpected (or more likely misunderstood) behavior with the UPDATE
statement. If an attribute is not explicitly listed in the UPDATE statement,
the NEW value appears to be populated with the OLD value. Unless I'm missing
something, this means there is no way to check to see if the UPDATE statement
includes references to attributes unless it changes them.
I doubt that statement is is very clear, so let me clarify using Example
39-3 from the 9.1 manual:
\pset expanded on
Expanded display is on.
SELECT VERSION();
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
DROP TRIGGER emp_stamp ON emp;
ERROR: relation "emp" does not exist
DROP FUNCTION emp_stamp();
ERROR: function emp_stamp() does not exist
DROP TABLE emp;
ERROR: table "emp" does not exist
CREATE TABLE emp
(
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE TABLE
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
CREATE TRIGGER
INSERT INTO emp
(
empname,
salary,
last_date,
last_user
)
VALUES
(
'John Doe',
45000,
'2015-08-27 09:50:21-05',
'no_such_person'
);
INSERT 0 1
SELECT *
FROM emp;
-[ RECORD 1 ]------------------------
empname | John Doe
salary | 45000
last_date | 2015-08-27 10:15:57.64472
last_user | postgres
UPDATE emp
SET last_user = 'someone_else';
UPDATE 1
SELECT *
FROM emp;
-[ RECORD 1 ]-------------------------
empname | John Doe
salary | 45000
last_date | 2015-08-27 10:16:40.101402
last_user | postgres
So in the UPDATE statement, I only provided a value for last_user. But the
first test of the trigger function tests for a NULL value of NEW.empname. Since
I did not provide one, I was expecting it to be NULL and an exception to
be thrown. Am I just misunderstanding how things work? Is there any way to
test to see if the UPDATE statement contained a reference to empname? If the
answer is no, I can certainly work with that, but before I go on I wanted
to make sure I wasn't missing anything.
Thanks!
I am creating an updatable view on a set of tables, and just ran into
unexpected (or more likely misunderstood) behavior with the UPDATE
statement. If an attribute is not explicitly listed in the UPDATE statement,
the NEW value appears to be populated with the OLD value. Unless I'm missing
something, this means there is no way to check to see if the UPDATE statement
includes references to attributes unless it changes them.
I doubt that statement is is very clear, so let me clarify using Example
39-3 from the 9.1 manual:
\pset expanded on
Expanded display is on.
SELECT VERSION();
-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
DROP TRIGGER emp_stamp ON emp;
ERROR: relation "emp" does not exist
DROP FUNCTION emp_stamp();
ERROR: function emp_stamp() does not exist
DROP TABLE emp;
ERROR: table "emp" does not exist
CREATE TABLE emp
(
empname text,
salary integer,
last_date timestamp,
last_user text
);
CREATE TABLE
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';
END IF;
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% cannot have null salary', NEW.empname;
END IF;
-- Who works for us when she must pay for it?
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
END IF;
-- Remember who changed the payroll when
NEW.last_date := current_timestamp;
NEW.last_user := current_user;
RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;
CREATE FUNCTION
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
CREATE TRIGGER
INSERT INTO emp
(
empname,
salary,
last_date,
last_user
)
VALUES
(
'John Doe',
45000,
'2015-08-27 09:50:21-05',
'no_such_person'
);
INSERT 0 1
SELECT *
FROM emp;
-[ RECORD 1 ]------------------------
empname | John Doe
salary | 45000
last_date | 2015-08-27 10:15:57.64472
last_user | postgres
UPDATE emp
SET last_user = 'someone_else';
UPDATE 1
SELECT *
FROM emp;
-[ RECORD 1 ]-------------------------
empname | John Doe
salary | 45000
last_date | 2015-08-27 10:16:40.101402
last_user | postgres
So in the UPDATE statement, I only provided a value for last_user. But the
first test of the trigger function tests for a NULL value of NEW.empname. Since
I did not provide one, I was expecting it to be NULL and an exception to
be thrown. Am I just misunderstanding how things work? Is there any way to
test to see if the UPDATE statement contained a reference to empname? If the
answer is no, I can certainly work with that, but before I go on I wanted
to make sure I wasn't missing anything.
Thanks!
On 08/27/2015 09:07 AM, David Nelson wrote: > Good morning all, > > I am creating an updatable view on a set of tables, and just ran into > unexpected (or more likely misunderstood) behavior with the UPDATE > statement. If an attribute is not explicitly listed in the UPDATE statement, > the NEW value appears to be populated with the OLD value. Unless I'm missing > something, this means there is no way to check to see if the UPDATE > statement > includes references to attributes unless it changes them. > > I doubt that statement is is very clear, so let me clarify using Example > 39-3 from the 9.1 manual: > > \pset expanded on > Expanded display is on. > > SELECT VERSION(); > -[ RECORD 1 > ]----------------------------------------------------------------------------------------------------------- > version | PostgreSQL 9.1.18 on x86_64-unknown-linux-gnu, compiled by gcc > (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit > > DROP TRIGGER emp_stamp ON emp; > ERROR: relation "emp" does not exist > DROP FUNCTION emp_stamp(); > ERROR: function emp_stamp() does not exist > DROP TABLE emp; > ERROR: table "emp" does not exist > > CREATE TABLE emp > ( > empname text, > salary integer, > last_date timestamp, > last_user text > ); > CREATE TABLE > > CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$ > BEGIN > -- Check that empname and salary are given > IF NEW.empname IS NULL THEN > RAISE EXCEPTION 'empname cannot be null'; > END IF; > IF NEW.salary IS NULL THEN > RAISE EXCEPTION '% cannot have null salary', NEW.empname; > END IF; > -- Who works for us when she must pay for it? > IF NEW.salary < 0 THEN > RAISE EXCEPTION '% cannot have a negative salary', NEW.empname; > END IF; > -- Remember who changed the payroll when > NEW.last_date := current_timestamp; > NEW.last_user := current_user; > RETURN NEW; > END; > $emp_stamp$ LANGUAGE plpgsql; > CREATE FUNCTION > > CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp > FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); > CREATE TRIGGER > > INSERT INTO emp > ( > empname, > salary, > last_date, > last_user > ) > VALUES > ( > 'John Doe', > 45000, > '2015-08-27 09:50:21-05', > 'no_such_person' > ); > INSERT 0 1 > > SELECT * > FROM emp; > -[ RECORD 1 ]------------------------ > empname | John Doe > salary | 45000 > last_date | 2015-08-27 10:15:57.64472 > last_user | postgres > > UPDATE emp > SET last_user = 'someone_else'; > UPDATE 1 > > SELECT * > FROM emp; > -[ RECORD 1 ]------------------------- > empname | John Doe > salary | 45000 > last_date | 2015-08-27 10:16:40.101402 > last_user | postgres > > So in the UPDATE statement, I only provided a value for last_user. But the > first test of the trigger function tests for a NULL value of > NEW.empname. Since > I did not provide one, I was expecting it to be NULL and an exception to > be thrown. Am I just misunderstanding how things work? Is there any way to > test to see if the UPDATE statement contained a reference to empname? If the > answer is no, I can certainly work with that, but before I go on I wanted > to make sure I wasn't missing anything. An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted and the NEW one inserted with the OLD values unless they where explicitly changed. So in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That test would only work if someone explicitly set empname = NULL in the update. If you want to check whether the value has not been changed then: IF NEW.empname = OLD.empname THEN > > Thanks! -- Adrian Klaver adrian.klaver@aklaver.com
>> So in the UPDATE statement, I only provided a value for last_user. But the
>> first test of the trigger function tests for a NULL value of
>> NEW.empname. Since
>> I did not provide one, I was expecting it to be NULL and an exception to
>> be thrown. Am I just misunderstanding how things work? Is there any way to
>> test to see if the UPDATE statement contained a reference to empname? If the
>> answer is no, I can certainly work with that, but before I go on I wanted
>> to make sure I wasn't missing anything.
>
>
> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted and the NEW one inserted with the OLD values unless they where explicitly changed. So
Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing
a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
called MVCC. Thanks for setting me straight!
> in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That test would only work if someone explicitly set empname = NULL in the update. If you want to check whether the value has not been changed then:
>
> IF NEW.empname = OLD.empname THEN
That's exactly the solution I hit on. Back to work, and thanks again.>> first test of the trigger function tests for a NULL value of
>> NEW.empname. Since
>> I did not provide one, I was expecting it to be NULL and an exception to
>> be thrown. Am I just misunderstanding how things work? Is there any way to
>> test to see if the UPDATE statement contained a reference to empname? If the
>> answer is no, I can certainly work with that, but before I go on I wanted
>> to make sure I wasn't missing anything.
>
>
> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted and the NEW one inserted with the OLD values unless they where explicitly changed. So
Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing
a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
called MVCC. Thanks for setting me straight!
> in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That test would only work if someone explicitly set empname = NULL in the update. If you want to check whether the value has not been changed then:
>
> IF NEW.empname = OLD.empname THEN
David Nelson <dnelson77808@gmail.com> writes: >>> So in the UPDATE statement, I only provided a value for last_user. But the >>> first test of the trigger function tests for a NULL value of >>> NEW.empname. Since >>> I did not provide one, I was expecting it to be NULL and an exception to >>> be thrown. Am I just misunderstanding how things work? Is there any way to >>> test to see if the UPDATE statement contained a reference to empname? If the >>> answer is no, I can certainly work with that, but before I go on I wanted >>> to make sure I wasn't missing anything. >> >> >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted and the NEW one inserted with the OLD valuesunless they where explicitly changed. So > > Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing > a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's > called MVCC. Thanks for setting me straight! Huh? I think any DB platform regardless of how it does MVCC is going to leave existing fields as-is in an update if same fields aren't specified. This has nothing specifically to do with Postgres, MVCC, updatable views etc. IMO. >> in your test  NEW.empname is still 'John Doe' and therefore NOT NULL. That test would only work if someone explicitlyset empname = NULL in the update. If you want to > check whether the value has not been changed then: >> >> IF NEW.empname = OLD.empname THEN > > That's exactly the solution I hit on. Back to work, and thanks again. > >> >>> >>> Thanks! >> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
Hello. On 27.8.2015 18:35, David Nelson wrote: >>> So in the UPDATE statement, I only provided a value for last_user. But the >>> first test of the trigger function tests for a NULL value of >>> NEW.empname. Since >>> I did not provide one, I was expecting it to be NULL and an exception to >>> be thrown. Am I just misunderstanding how things work? Is there any way to >>> test to see if the UPDATE statement contained a reference to empname? If the >>> answer is no, I can certainly work with that, but before I go on I wanted >>> to make sure I wasn't missing anything. >> >> >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted > and the NEW one inserted with the OLD values unless they where explicitly > changed. So > > Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing > a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's > called MVCC. Thanks for setting me straight! > >> in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That > test would only work if someone explicitly set empname = NULL in the update. If > you want to check whether the value has not been changed then: >> >> IF NEW.empname = OLD.empname THEN > > That's exactly the solution I hit on. Back to work, and thanks again. Just for the sake of completeness... If the value (empname in the above example) can be NULL, the compare does not work, because SELECT NULL = NULL returns NULL which is treated as FALSE. But I am sure you know this :-) HTH, Ladislav Lenart
On Thu, Aug 27, 2015 at 1:21 PM, Jerry Sievers <gsievers19@comcast.net> wrote:
>
> David Nelson <dnelson77808@gmail.com> writes:
>
> >>> So in the UPDATE statement, I only provided a value for last_user. But the
> >>> first test of the trigger function tests for a NULL value of
> >>> NEW.empname. Since
> >>> I did not provide one, I was expecting it to be NULL and an exception to
> >>> be thrown. Am I just misunderstanding how things work? Is there any way to
> >>> test to see if the UPDATE statement contained a reference to empname? If the
> >>> answer is no, I can certainly work with that, but before I go on I wanted
> >>> to make sure I wasn't missing anything.
> >>
> >>
> >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted and the NEW one inserted with the OLD values unless they where explicitly changed. So
> >
> > Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing
> > a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
> > called MVCC. Thanks for setting me straight!
>
> Huh?
>
> I think any DB platform regardless of how it does MVCC is going to leave
> existing fields as-is in an update if same fields aren't specified.
>
> This has nothing specifically to do with Postgres, MVCC, updatable views
> etc. IMO.
>
You are of course correct, but I could swear I've done an updatable view oin
another system in the past and could count on having the values for column
names that were not called in the UPDATE statement on the view as being either
NULL or maybe undefined values. But I haven't done an updable view in anything
other than PostgreSQL in so long that I most likely dreamed that up (because,
as you seem to be saying, that wouldn't make much sense). So I humbly retract
that part of my reply. Regardless, the DELETE/INSERT explanation makes it
all clear, and I've gotten the trigger function seemingly working the way
I need for it to. At least it's passed all 30 something tests I've thought
of to throw at it. Still testing though. And I'm sure the one test I fail to
think of will be the first thing the front-end developers throw at it. It
always works that way...
Regards
> >> in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That test would only work if someone explicitly set empname = NULL in the update. If you want to
> > check whether the value has not been changed then:
> >>
> >> IF NEW.empname = OLD.empname THEN
> >
> > That's exactly the solution I hit on. Back to work, and thanks again.
> >
> >>
> >>>
> >>> Thanks!
> >>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.klaver@aklaver.com
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consulting@comcast.net
> p: 312.241.7800
>
> David Nelson <dnelson77808@gmail.com> writes:
>
> >>> So in the UPDATE statement, I only provided a value for last_user. But the
> >>> first test of the trigger function tests for a NULL value of
> >>> NEW.empname. Since
> >>> I did not provide one, I was expecting it to be NULL and an exception to
> >>> be thrown. Am I just misunderstanding how things work? Is there any way to
> >>> test to see if the UPDATE statement contained a reference to empname? If the
> >>> answer is no, I can certainly work with that, but before I go on I wanted
> >>> to make sure I wasn't missing anything.
> >>
> >>
> >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted and the NEW one inserted with the OLD values unless they where explicitly changed. So
> >
> > Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing
> > a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
> > called MVCC. Thanks for setting me straight!
>
> Huh?
>
> I think any DB platform regardless of how it does MVCC is going to leave
> existing fields as-is in an update if same fields aren't specified.
>
> This has nothing specifically to do with Postgres, MVCC, updatable views
> etc. IMO.
>
You are of course correct, but I could swear I've done an updatable view oin
another system in the past and could count on having the values for column
names that were not called in the UPDATE statement on the view as being either
NULL or maybe undefined values. But I haven't done an updable view in anything
other than PostgreSQL in so long that I most likely dreamed that up (because,
as you seem to be saying, that wouldn't make much sense). So I humbly retract
that part of my reply. Regardless, the DELETE/INSERT explanation makes it
all clear, and I've gotten the trigger function seemingly working the way
I need for it to. At least it's passed all 30 something tests I've thought
of to throw at it. Still testing though. And I'm sure the one test I fail to
think of will be the first thing the front-end developers throw at it. It
always works that way...
Regards
> >> in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That test would only work if someone explicitly set empname = NULL in the update. If you want to
> > check whether the value has not been changed then:
> >>
> >> IF NEW.empname = OLD.empname THEN
> >
> > That's exactly the solution I hit on. Back to work, and thanks again.
> >
> >>
> >>>
> >>> Thanks!
> >>
> >>
> >>
> >> --
> >> Adrian Klaver
> >> adrian.klaver@aklaver.com
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consulting@comcast.net
> p: 312.241.7800
On Fri, Aug 28, 2015 at 6:39 AM, Ladislav Lenart <lenartlad@volny.cz> wrote:
>
> Hello.
>
>
> On 27.8.2015 18:35, David Nelson wrote:
> >>> So in the UPDATE statement, I only provided a value for last_user. But the
> >>> first test of the trigger function tests for a NULL value of
> >>> NEW.empname. Since
> >>> I did not provide one, I was expecting it to be NULL and an exception to
> >>> be thrown. Am I just misunderstanding how things work? Is there any way to
> >>> test to see if the UPDATE statement contained a reference to empname? If the
> >>> answer is no, I can certainly work with that, but before I go on I wanted
> >>> to make sure I wasn't missing anything.
> >>
> >>
> >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted
> > and the NEW one inserted with the OLD values unless they where explicitly
> > changed. So
> >
> > Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing
> > a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
> > called MVCC. Thanks for setting me straight!
> >
> >> in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That
> > test would only work if someone explicitly set empname = NULL in the update. If
> > you want to check whether the value has not been changed then:
> >>
> >> IF NEW.empname = OLD.empname THEN
> >
> > That's exactly the solution I hit on. Back to work, and thanks again.
>
> Just for the sake of completeness...
>
> If the value (empname in the above example) can be NULL, the compare does not
> work, because
>
> SELECT NULL = NULL
>
> returns NULL which is treated as FALSE.
>
> But I am sure you know this :-)
>
>
> HTH,
>
> Ladislav Lenart
Thanks Ladislav. To be able to capture updates to NULL, I ended up going with
IF NEW.empname IS DISTINCT FROM OLD.empname THEN ...
>
> Hello.
>
>
> On 27.8.2015 18:35, David Nelson wrote:
> >>> So in the UPDATE statement, I only provided a value for last_user. But the
> >>> first test of the trigger function tests for a NULL value of
> >>> NEW.empname. Since
> >>> I did not provide one, I was expecting it to be NULL and an exception to
> >>> be thrown. Am I just misunderstanding how things work? Is there any way to
> >>> test to see if the UPDATE statement contained a reference to empname? If the
> >>> answer is no, I can certainly work with that, but before I go on I wanted
> >>> to make sure I wasn't missing anything.
> >>
> >>
> >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is deleted
> > and the NEW one inserted with the OLD values unless they where explicitly
> > changed. So
> >
> > Shoot, I went totally brain-dead on that one. I forgot that I'm actually doing
> > a DELETE/INSERT, and the behaviour makes perfect sense in that light. It's
> > called MVCC. Thanks for setting me straight!
> >
> >> in your test NEW.empname is still 'John Doe' and therefore NOT NULL. That
> > test would only work if someone explicitly set empname = NULL in the update. If
> > you want to check whether the value has not been changed then:
> >>
> >> IF NEW.empname = OLD.empname THEN
> >
> > That's exactly the solution I hit on. Back to work, and thanks again.
>
> Just for the sake of completeness...
>
> If the value (empname in the above example) can be NULL, the compare does not
> work, because
>
> SELECT NULL = NULL
>
> returns NULL which is treated as FALSE.
>
> But I am sure you know this :-)
>
>
> HTH,
>
> Ladislav Lenart
Thanks Ladislav. To be able to capture updates to NULL, I ended up going with
IF NEW.empname IS DISTINCT FROM OLD.empname THEN ...
On 27.8.2015 18:35, David Nelson wrote: >>> So in the UPDATE statement, I only provided a value for last_user. >>> But the first test of the trigger function tests for a NULL value of >>> NEW.empname. Since I did not provide one, I was expecting it to be >>> NULL and an exception to be thrown. Am I just misunderstanding how >>> things work? Is there any way to test to see if the UPDATE statement >>> contained a reference to empname? If the answer is no, I can >>> certainly work with that, but before I go on I wanted to make sure I >>> wasn't missing anything. >> >> >> An UPDATE in Postgres is really a DELETE/INSERT where the OLD row is >> deleted > and the NEW one inserted with the OLD values unless they where > explicitly changed. So > > Shoot, I went totally brain-dead on that one. I forgot that I'm > actually doing a DELETE/INSERT, and the behaviour makes perfect sense > in that light. It's called MVCC. Thanks for setting me straight! > >> in your test NEW.empname is still 'John Doe' and therefore NOT NULL. >> That > test would only work if someone explicitly set empname = NULL in the > update. If you want to check whether the value has not been changed then: >> >> IF NEW.empname = OLD.empname THEN > > That's exactly the solution I hit on. Back to work, and thanks again. Just for the sake of completeness... If the value (empname in the above example) can be NULL, the compare does not work, because SELECT NULL = NULL returns NULL which is treated as FALSE. But I am sure you know this :-) HTH, Ladislav Lenart ___________________________ Right. And that's why you use very nice option provided by PG: IF NEW.empname IS DISTINCT FROM OLD.empname THEN which again you probably know :) Regards, Igor Neyman
> Just for the sake of completeness...
>
> If the value (empname in the above example) can be NULL, the compare does not work, because
>
> SELECT NULL = NULL
>
> returns NULL which is treated as FALSE.
>
> But I am sure you know this :-)
>
>
> HTH,
>
> Ladislav Lenart
>
> ___________________________
>
> Right. And that's why you use very nice option provided by PG:
>
> IF NEW.empname IS DISTINCT FROM OLD.empname THEN
>
> which again you probably know :)
>
> Regards,
> Igor Neyman
Yep, I got there pretty quickiy. IS [NOT] DISTINCT FROM is pretty cool stuff!>
> If the value (empname in the above example) can be NULL, the compare does not work, because
>
> SELECT NULL = NULL
>
> returns NULL which is treated as FALSE.
>
> But I am sure you know this :-)
>
>
> HTH,
>
> Ladislav Lenart
>
> ___________________________
>
> Right. And that's why you use very nice option provided by PG:
>
> IF NEW.empname IS DISTINCT FROM OLD.empname THEN
>
> which again you probably know :)
>
> Regards,
> Igor Neyman