From 4c62779131cb73620dd391ad6dd767ab37fd0d5f Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 10 Jun 2009 07:03:34 +0000 Subject: [PATCH] Correct/improve the datetime_precision field in the information schema. In particular, always show 0 for the date type instead of null, and show 6 (the default) for time, timestamp, and interval without a declared precision. This is now in fuller conformance with the SQL standard. Also clarify the documentation about this. discovered and analyzed by Konstantin Izmailov and Tom Lane --- doc/src/sgml/information_schema.sgml | 31 ++++++++++++++-------- src/backend/catalog/information_schema.sql | 8 +++--- 2 files changed, 24 insertions(+), 15 deletions(-) diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 19cbb5d65f..8e145d7ef1 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -395,9 +395,12 @@ datetime_precision cardinal_number - If data_type identifies a date, time, or - interval type, the declared precision; null for all other data - types or if no precision was declared. + If data_type identifies a date, time, + timestamp, or interval type, this column contains the (declared + or implicit) fractional seconds precision of the type for this + attribute, that is, the number of decimal digits maintained + following the decimal point in the seconds value. For all + other data types, this column is null. @@ -995,9 +998,12 @@ datetime_precision cardinal_number - If data_type identifies a date, time, or - interval type, the declared precision; null for all other data - types or if no precision was declared. + If data_type identifies a date, time, + timestamp, or interval type, this column contains the (declared + or implicit) fractional seconds precision of the type for this + column, that is, the number of decimal digits maintained + following the decimal point in the seconds value. For all + other data types, this column is null. @@ -1729,7 +1735,7 @@ cardinal_number If the domain has a numeric type, this column contains the - (declared or implicit) precision of the type for this column. + (declared or implicit) precision of the type for this domain. The precision indicates the number of significant digits. It can be expressed in decimal (base 10) or binary (base 2) terms, as specified in the column @@ -1755,7 +1761,7 @@ cardinal_number If the domain has an exact numeric type, this column contains - the (declared or implicit) scale of the type for this column. + the (declared or implicit) scale of the type for this domain. The scale indicates the number of significant digits to the right of the decimal point. It can be expressed in decimal (base 10) or binary (base 2) terms, as specified in the column @@ -1768,9 +1774,12 @@ datetime_precision cardinal_number - If the domain has a date, time, or interval type, the declared - precision; null for all other data types or if no precision was - declared. + If data_type identifies a date, time, + timestamp, or interval type, this column contains the (declared + or implicit) fractional seconds precision of the type for this + domain, that is, the number of decimal digits maintained + following the decimal point in the seconds value. For all + other data types, this column is null. diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 9c5672f3e1..fe753221c6 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 < 0 THEN 6 ELSE $2 END WHEN $1 IN (1186) /* interval */ - THEN $2 & 65535 + THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 & 65535 END ELSE null END$$; -- 2.39.5