On Nov 28, 2009, at 5:40 PM, Bruce Momjian wrote:
> I think there is general agreement that we should have a timezone data
> type which validates against pg_timezone_names().name. It might be
> enough to just document how users can create such a domain data type,
> but I don't know of a way to do that. Is this a TODO?
From http://justatheory.com/computers/databases/postgresql/citext-patch-submitted.html
CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$
BEGIN PERFORM now() AT TIME ZONE tz; RETURN TRUE;
EXCEPTION WHEN invalid_parameter_value THEN RETURN FALSE;
END;
$$ language plpgsql STABLE;
CREATE DOMAIN timezone AS CITEXT
CHECK ( is_timezone( value ) );
It could also be TEXT I suppose, but "America/Los_Angeles" and "america/los_angeles" should be considered the same.
Best,
David