Thread

1. Odd behavior with views and numeric columns

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

   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
   
   

2. Re: Odd behavior with views and numeric columns

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-02-27T22:17:02Z

   David Lynn <davidl@ayamba.com> writes:
   > When creating a view with a case statement, the resulting numeric column
   > is listed as numeric(65535,65531).
   
   This appears to be fixed in current sources.
   
   			regards, tom lane