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 majordomo@postgresql.org)

mike.griffin@mygenerationsoftware.com writes:[color=blue]
> 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 :[/color]
[color=blue]
> numeric_precision is returned as 65535
> numeric_scale is returned as 65531[/color]

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 majordomo@postgresql.org

mike.griffin@mygenerationsoftware.com writes:[color=blue]
> 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 :[/color]
[color=blue]
> numeric_precision is returned as 65535
> numeric_scale is returned as 65531[/color]

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 majordomo@postgresql.org

On Fri, Jun 18, 2004 at 11:42:29 -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote:[color=blue]
>
> 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?[/color]

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

On Fri, Jun 18, 2004 at 11:42:29 -0400,
Tom Lane <tgl@sss.pgh.pa.us> wrote:[color=blue]
>
> 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?[/color]

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

Ah yes, is that in pg_types, good idea, I might override that after I make
the query.
[color=blue]
> On Fri, Jun 18, 2004 at 11:42:29 -0400,
> Tom Lane <tgl@sss.pgh.pa.us> wrote:[color=green]
>>
>> 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?[/color]
>
> 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.
>[/color]



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

Ah yes, is that in pg_types, good idea, I might override that after I make
the query.
[color=blue]
> On Fri, Jun 18, 2004 at 11:42:29 -0400,
> Tom Lane <tgl@sss.pgh.pa.us> wrote:[color=green]
>>
>> 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?[/color]
>
> 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.
>[/color]



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

mike.griffin@mygenerationsoftware.com writes:[color=blue]
> I noticed that there is no INFORMATION_SCHEMA.Indexes ? isn't there
> supposed to be one.[/color]

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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

mike.griffin@mygenerationsoftware.com writes:[color=blue]
> I noticed that there is no INFORMATION_SCHEMA.Indexes ? isn't there
> supposed to be one.[/color]

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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

>>> The SQL spec doesn't allow unconstrained lengths for these types[color=blue][color=green][color=darkred]
>>> so it gives no guidance about what to display in the information_schema
>>> views. Any opinions?[/color]
>>
>> If there isn't a set scale for the type, then NULL would probably make the
>> most sense.[/color][/color]

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

>>> The SQL spec doesn't allow unconstrained lengths for these types[color=blue][color=green][color=darkred]
>>> so it gives no guidance about what to display in the information_schema
>>> views. Any opinions?[/color]
>>
>> If there isn't a set scale for the type, then NULL would probably make the
>> most sense.[/color][/color]

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

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.
[color=blue]
> mike.griffin@mygenerationsoftware.com writes:[color=green]
>> 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 :[/color]
>[color=green]
>> numeric_precision is returned as 65535
>> numeric_scale is returned as 65531[/color]
>
> 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
>[/color]



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

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.
[color=blue]
> mike.griffin@mygenerationsoftware.com writes:[color=green]
>> 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 :[/color]
>[color=green]
>> numeric_precision is returned as 65535
>> numeric_scale is returned as 65531[/color]
>
> 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
>[/color]



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

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.
[color=blue]
>
> 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
>[/color]



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

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.
[color=blue]
>
> 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
>[/color]



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