> On 4 Jan 2021, at 20:02, Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
>
>>> On 1 Jan 2021, at 16:56, Dirk Mika <Dirk.Mika@mikatiming.de> wrote:
>>>
>>> Hi all and a happy new Year!
>>>
>>> We have an Oracle schema that is to be converted to PostgreSQL, where conditional predicates are used in some
triggers.
>>>
>>> In particular, columns are populated with values if they are not specified in the update statement which is used.
>>> Usually with an expression like this:
>>>
>>> IF NOT UPDATING('IS_CANCELED')
>>> THEN
>>> :new.is_canceled := ...;
>>> END IF;
>>>
>>> I have not found anything similar in PostgreSQL. What is the common approach to this problem?
>
>> Can't you use column defaults to handle these cases?
>
> That would work for inserts, but not for updates.
Usually, if you don’t mention a column in an UPDATE, you want the value to remain as it was, which is precisely what
happensby default. That certainly makes sense to me when you’re dealing with an application that doesn’t know about the
existenceof said column; overwriting an existing value that some other application put there looks like a problem to
me.But of course, that depends a lot on what you’re trying to achieve.
What is your use-case that that is not the desired behaviour? Or are we talking about a mixed problem here, where this
approachworks for some fields, but other fields (such as a status change date) always need to be updated (regardless of
whethera value was specified)?
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.