From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>, 'pinker *EXTERN*' <pinker(at)onet(dot)eu>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type? |
Date: | 2015-09-17 13:39:21 |
Message-ID: | 55FAC289.205@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 09/17/2015 06:32 AM, Albe Laurenz wrote:
> pinker wrote:
>> I've tried to write audit trigger which fires only when data changed, so I used "WHEN (OLD.* IS
>> DISTINCT FROM NEW.*)" clause as described in documentation
>> <http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html> . Should this clause be independent
>> from data type? because an error occurs when I'm trying to modify row with point data type: ERROR:
>> could not identify an equality operator for type point
>
> I guess it is dependent on data type as it requires an equality operator,
> and type "point" doesn't have one.
To echo the OP, why is that?
http://www.postgresql.org/docs/9.4/interactive/functions-comparison.html
For non-null inputs, IS DISTINCT FROM is the same as the <> operator.
So:
test=> select '(1,2)'::point <> '(1,2)'::point;
?column?
----------
f
(1 row)
test=> select '(1,2)'::point <> '(1,3)'::point;
?column?
----------
t
test=> select '(1,2)'::point <> null;
?column?
----------
(1 row)
>
> You'd have to hand-roll a comparison in this case, probably using the
> "same as" operator "~=".
I could force it to work by casting:
test=> select '(1,2)'::point::text is distinct from '(1,2)'::point::text;
?column?
----------
f
(1 row)
test=> select '(1,2)'::point::text is distinct from '(1,3)'::point::text;
?column?
----------
t
(1 row)
test=> select '(1,2)'::point::text is distinct from null;
?column?
----------
t
>
> Yours,
> Laurenz Albe
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2015-09-17 13:39:54 | Re: Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type? |
Previous Message | Albe Laurenz | 2015-09-17 13:32:25 | Re: Shouldn't "WHEN (OLD.* IS DISTINCT FROM NEW.*)" clause be independent from data type? |