Thread

Odd behavior with views and numeric columns

David Lynn <davidl@ayamba.com> — 2001-02-27T19:26:05Z

Hello -

When creating a view with a case statement, the resulting numeric column
is listed as numeric(65535,65531).  Can somebody please explain this
oddity to me, and whether or not it is an issue worth fixing from a
performance standpoint?

In order to make the view list the column as numeric(5,2), I can put a
CAST around the case expression - but presumably that has a slight
performance impact.  Additionally, when creating the view, no matter how
I cast the 0 (numerically), it still says '0'::"numeric" in the view
definition.

DEV# \d tab1
            Table "tab1"
 Attribute |     Type     | Modifier
-----------+--------------+----------
 col1      | integer      |
 col2      | numeric(5,2) |

DEV# \d v1
                  View "v1"
 Attribute |         Type         | Modifier
-----------+----------------------+----------
 col1      | integer              |
 col2      | numeric(65535,65531) |
View definition: SELECT tab1.col1, CASE WHEN (tab1.col2 ISNULL) THEN
'0'::"numer
ic" ELSE tab1.col2 END AS col2 FROM tab1;


                            version
----------------------------------------------------------------
 PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3

Thanks.

--d