Thread: 7.4's INFORMATION_SCHEMA.Columns View
This is part of the Columns View, if you add a numeric field to your table
and don't provide any Length or Precision then :
numeric_precision is returned as 65535
numeric_scale is returned as 65531
Is this what you'd expect, and what does it mean to create a column with
no Length or Precision, I'm using pgAdmin to create the tables and
columns, but the tables are created and seem to work.
====================================
CAST(
CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE
a.atttypid END)
WHEN 21 /*int2*/ THEN 16
WHEN 23 /*int4*/ THEN 32
WHEN 20 /*int8*/ THEN 64
WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN
t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535
WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
ELSE null END
AS cardinal_number)
AS numeric_precision,
====================================
CAST(
CASE WHEN t.typtype = 'd' THEN
CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) &
65535
ELSE null END
ELSE
CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
ELSE null END
END
AS cardinal_number)
AS numeric_scale,
mike.griffin@mygenerationsoftware.com writes:
> This is part of the Columns View, if you add a numeric field to your table
> and don't provide any Length or Precision then :
> numeric_precision is returned as 65535
> numeric_scale is returned as 65531
Yeah, that's what you'd get for a numeric field with no length
constraint. (I suspect varchar with no length constraint will
display funny as well.)
The SQL spec doesn't allow unconstrained lengths for these types
so it gives no guidance about what to display in the information_schema
views. Any opinions?
regards, tom lane
On Fri, Jun 18, 2004 at 11:42:29 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > The SQL spec doesn't allow unconstrained lengths for these types > so it gives no guidance about what to display in the information_schema > views. Any opinions? It might make some sense to use the maximum length supported by the type for the precision. The documentations says that numeric is limited to 1000 digits. If there isn't a set scale for the type, then NULL would probably make the most sense.
Well, does it have an actual length, that seems strange to me, is in infinitely large? I guess I'm just not used to being allowed not to define something, I'm a Microsoft type (he he) don't get me wrong, we've taken our product into all kinds of open source areas, what an eye opener it's been. I really like PostgreSQL, it's so much more powerful than MySQL yet you don't hear much about it? Anyway, I'm working on our foreign key queries tonight, we're pulling back lots of good meta data. I noticed that there is no INFORMATION_SCHEMA.Indexes ? isn't there supposed to be one. Thank goodness PostgreSQL has good documentation for the system catalogs. > mike.griffin@mygenerationsoftware.com writes: >> This is part of the Columns View, if you add a numeric field to your >> table >> and don't provide any Length or Precision then : > >> numeric_precision is returned as 65535 >> numeric_scale is returned as 65531 > > Yeah, that's what you'd get for a numeric field with no length > constraint. (I suspect varchar with no length constraint will > display funny as well.) > > The SQL spec doesn't allow unconstrained lengths for these types > so it gives no guidance about what to display in the information_schema > views. Any opinions? > > regards, tom lane >
Ah yes, is that in pg_types, good idea, I might override that after I make the query. > On Fri, Jun 18, 2004 at 11:42:29 -0400, > Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> The SQL spec doesn't allow unconstrained lengths for these types >> so it gives no guidance about what to display in the information_schema >> views. Any opinions? > > It might make some sense to use the maximum length supported by the type > for the precision. The documentations says that numeric is limited to > 1000 digits. > > If there isn't a set scale for the type, then NULL would probably make the > most sense. >
mike.griffin@mygenerationsoftware.com writes:
> I noticed that there is no INFORMATION_SCHEMA.Indexes ? isn't there
> supposed to be one.
Nope. Indexes are not a concept used in the SQL spec at all (they
consider 'em an implementation detail), so they'd hardly want to
standardize a view to describe 'em.
You can look at the constraints views to find out about unique and
primary key constraints, which are implemented by indexes in PG.
But that won't tell you about indexes made by CREATE INDEX.
regards, tom lane
>>> The SQL spec doesn't allow unconstrained lengths for these types
>>> so it gives no guidance about what to display in the information_schema
>>> views. Any opinions?
>>
>> If there isn't a set scale for the type, then NULL would probably make the
>> most sense.
After more thought I like returning NULL for both precision and scale in
the case of unconstrained numeric columns. Any other value is
arbitrary. In particular, the 1000 cited in the docs is *very*
arbitrary, and I don't think it actually constrains what you can store,
only what you can declare as a column precision. [tries it...] Yup,
I can store "power(10.0, 10000)" in an unconstrained numeric column.
It seems to fail around 10^140000 but I'm not sure where that limit
is coming from exactly...
regards, tom lane
I agree, the current numbers are misleading, nothing other than null really makes sense, at least the consumers of it can decide what to do if they need to rather than check for some strange number, sounds good to me. > > After more thought I like returning NULL for both precision and scale in > the case of unconstrained numeric columns. Any other value is > arbitrary. In particular, the 1000 cited in the docs is *very* > arbitrary, and I don't think it actually constrains what you can store, > only what you can declare as a column precision. [tries it...] Yup, > I can store "power(10.0, 10000)" in an unconstrained numeric column. > It seems to fail around 10^140000 but I'm not sure where that limit > is coming from exactly... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >