On Monday 08 June 2009 07:12:33 Tom Lane wrote: > Peter Eisentraut <pete...@gmx.net> writes: > > On Sunday 31 May 2009 18:41:55 Tom Lane wrote: > >> AFAICS, the SQL standard demands that precision and scale fields be > >> non-null all the time for those data types where they make sense > >> (this is encoded in the CHECK CONSTRAINTs that are declared for the > >> various information-schema tables, see particularly 21.15 > >> DATA_TYPE_DESCRIPTOR base table in SQL99). DATE is clearly wrong > >> per spec, but it's not the only problem. > > > > The DATE change is the only thing I'd be prepared to make right now. > > At this point I think the clear decision is "we're not changing anything > for 8.4". I've put the issue on the TODO list for future development > cycles.
After gathering that there will probably be some other changes before release that will require an initdb (even without catversion bump), and after reexamining the issue, I think it's trivial and uncontroversial to fix the datetime issues: diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 9c5672f..cb0296a 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -160,12 +160,12 @@ CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer RETURNS NULL ON NULL INPUT AS $$SELECT - CASE WHEN $2 = -1 /* default typmod */ - THEN null + CASE WHEN $1 IN (1082) /* date */ + THEN 0 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */ - THEN $2 + THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 END WHEN $1 IN (1186) /* interval */ - THEN $2 & 65535 + THEN CASE WHEN $2 = -1 THEN 6 ELSE $2 & 65535 END ELSE null END$$; I have also prepared a patch that creates more realistic values for character_octet_length based on encoding information, which I will propose for 8.5. The issue of whether to report null or some large value for "unlimited" length data types needs some more thought. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers