Re: Reserved word is a column name - how to reference it in a trigger

Am 19.09.2013 15:58, schrieb Walt:
> On 9/18/2013 6:15 PM, Peter Schneider wrote:
>> Am 18.09.2013 21:47, schrieb Walt:
>>>
>>
>> That column isn't there any more, as you renamed it.
>
> That was it, although a bit more complicated. The old column is still
> there on our production database, but had been renamed to TYPE_DEL on
> development database. I was looking at production for the table
> definition, but creating the trigger on development.
>
>
> Thanks.
>
>> Perhaps, if you could originally create it at all, try to recreate it as
>> virtual column based on your new one. I didn't try this though...
>
> This is a good idea. I'll give it a try.

I just tried this for myself, and it works fine. E.g.:

ALTER TABLE mytable ADD (type AS (interview_type || ''));

The concatenation of the empty string is just because for a virtual column, you need a colum expression, not just a colum reference.

But, and this is a big one: your old application will of course not be able to insert into this virtual column, or update it. This is simply not supported for virtual columns. Just querying will be fine.

So this might or might not work for you, but anyway you'll be better off if you just clean up this mess properly and get a grip on the old application to change it.

Regards
Peter

-- 
Climb the mountain not to plant your flag, but to embrace the challenge,
enjoy the air and behold the view. Climb it so you can see the world,
not so the world can see you.                    -- David McCullough Jr.