Is there any way I can override system-defined casts?
Tried
create cast (varchar as timestamptz) with function
user_timestamptz(varchar) ;
and got
ERROR: cast from type pg_catalog.varchar to type timestamptz already exists
DROP CAST does not work:
ERROR: cannot drop cast from character varying to timestamp with time
zone because it is required by the database system
(or are my permissions insufficient?)
Basically my problem is converting '' (empty string) to
NULL::timestampz, and built-in cast cannot convert blank string to
timestamptz. Maybe I'm wondering up the wrong alley with casts?
One solution I can see is create user-defined type (which would be the
same timestamptz) and define varchar->mytype cast, but that will require
rather extensive changes to database schema. Plus, our user-interface
currently relies on PG datatypes to format input/output data.
Any suggestions?
How about a function with a CASE statement in it?
That wouldn't be The Way of The Dragon ;)
Most of my SQL statements are dynamically generated. Using CASE means I will have to check target field datatype, and apply CASE whenever it's timestamptz. Rather messy.
I tried defining my own base type using timestamptz _in and _out functions, and it seems to work. Had to re-create half of my database objects due to dependencies, but now that it's done it seems to work quite well.
Peter