Odd behavior with views and numeric columns

From: David Lynn <davidl(at)ayamba(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Odd behavior with views and numeric columns
Date: 2001-02-27 19:26:05
Message-ID: 3A9BFF4D.C4CE065C@ayamba.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2001-02-27 19:34:50 Re: Re: Slowdown problem when writing 1.7million records
Previous Message Tom Lane 2001-02-27 19:25:41 Re: Slowdown problem when writing 1.7million records