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.
Show quoted text
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 65531Yeah, 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.
Show quoted text
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.
Show quoted text
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?