7.4's INFORMATION_SCHEMA.Columns View

[mike.griffin]

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,


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

mi**********@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

mi**********@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Bruno Wolff III]

On Fri, Jun 18, 2004 at 11:42:29 -0400,
Tom Lane <tg*@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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruno Wolff III]

On Fri, Jun 18, 2004 at 11:42:29 -0400,
Tom Lane <tg*@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.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[mike.griffin]

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 <tg*@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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[mike.griffin]

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 <tg*@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.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

mi**********@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

mi**********@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[mike.griffin]

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.

mi**********@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[mike.griffin]

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.

mi**********@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[mike.griffin]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[mike.griffin]

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)