From 208d3a7555541383aadf682e47c14fed21468374 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
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       | 33 ++++++++++++++--------
 src/backend/catalog/information_schema.sql | 10 +++----
 2 files changed, 26 insertions(+), 17 deletions(-)

diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 06ccb6d2de..b6e143c5da 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.38 2009/02/06 21:15:11 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.39 2009/06/10 07:03:34 petere Exp $ -->
 
 <chapter id="information-schema">
  <title>The Information Schema</title>
@@ -395,9 +395,12 @@
       <entry><literal>datetime_precision</literal></entry>
       <entry><type>cardinal_number</type></entry>
       <entry>
-       If <literal>data_type</literal> identifies a date, time, or
-       interval type, the declared precision; null for all other data
-       types or if no precision was declared.
+       If <literal>data_type</literal> 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.
       </entry>
      </row>
 
@@ -995,9 +998,12 @@
       <entry><literal>datetime_precision</literal></entry>
       <entry><type>cardinal_number</type></entry>
       <entry>
-       If <literal>data_type</literal> identifies a date, time, or
-       interval type, the declared precision; null for all other data
-       types or if no precision was declared.
+       If <literal>data_type</literal> 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.
       </entry>
      </row>
 
@@ -1729,7 +1735,7 @@
       <entry><type>cardinal_number</type></entry>
       <entry>
        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 @@
       <entry><type>cardinal_number</type></entry>
       <entry>
        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 @@
       <entry><literal>datetime_precision</literal></entry>
       <entry><type>cardinal_number</type></entry>
       <entry>
-       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 <literal>data_type</literal> 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.
       </entry>
      </row>
 
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 80132e27ea..b295abbeab 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -4,7 +4,7 @@
  *
  * Copyright (c) 2003-2009, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.53 2009/02/24 10:06:32 petere Exp $
+ * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.54 2009/06/10 07:03:34 petere Exp $
  */
 
 /*
@@ -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.40.0