Hi. As background/context, I'm working on a script to take a series of databases and make them timezone-aware. This basically involves saving all the view definitions, dropping all the views, changing all the timestamp columns without time zones to TS with TZ, and then recreating all the views. As a sanity check on all of this, I compared the resulting view definitions to what existed before starting. Most of them were equivalent, with a few exceptions.
--
![]()
These exceptions all seem to be where slightly different casting is used when the view definition gets created. For example, starting with this view (payment_form_code is a varchar(20):
ag_reach=> CREATE VIEW test_tmp AS (SELECT 1 FROM l_payment_form WHERE payment_form_code IN ('CREDIT_CARD','OTHER'));
CREATE VIEW
ag_reach=> \d+ test_tmp
View "public.test_tmp"
Column | Type | Modifiers | Storage | Description
----------+---------+-----------+---------+-------------
?column? | integer | | plain |
View definition:
SELECT 1
FROM l_payment_form
WHERE l_payment_form.payment_form_code::text = ANY (ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character varying]::text[]);
I wasn't very surprised by that. But when I take the view definition as stored in PG above, and create a new view:ag_reach=> CREATE VIEW test_tmp2 AS SELECT 1 FROM l_payment_form WHERE l_payment_form.payment_form_code::text = ANY
(ARRAY['CREDIT_CARD'::character varying, 'OTHER'::character varying]::text[]);
CREATE VIEW
ag_reach=> \d+ test_tmp2
View "public.test_tmp2"
Column | Type | Modifiers | Storage | Description
----------+---------+-----------+---------+-------------
?column? | integer | | plain |
View definition:
SELECT 1
FROM l_payment_form
WHERE l_payment_form.payment_form_code::text = ANY (ARRAY['CREDIT_CARD'::character varying::text, 'OTHER'::character varying::text]); you'll see it now casts the individual array elements to text, rather than the whole array as in test_tmp.This doesn't impair the view's functionality, so I can't necessarily complain. But it does make it harder for me to know if the views were recreated correctly. I'd be curious to know what is going on here, and if there's any way to avoid this behavior.
Thanks!
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
