Column information

According to the documentation 'numeric_precision_radix' field should
indicate what radix the value of 'numeric_precision' is stored.

However, even though the radix is 2, the actual value is 32, which is
not a radix 2.

https://en.wikipedia.org/wiki/Radix

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi, Alan,

On Thu, May 4, 2017 at 3:18 AM, Alban Hertroys <haramrae@gmail.com> wrote:

According to the documentation 'numeric_precision_radix' field should
indicate what radix the value of 'numeric_precision' is stored.

However, even though the radix is 2, the actual value is 32, which is
not a radix 2.

https://en.wikipedia.org/wiki/Radix

Yes, so for the Radix 2 only 0 and 1 should be used, right?
And so the value should be 100000 and not 32.

Thank you.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Igor Korot <ikorot01@gmail.com> writes:

Yes, so for the Radix 2 only 0 and 1 should be used, right?
And so the value should be 100000 and not 32.

No, certainly not. The radix column says what the units of measurement
are, not that the values in the precision column aren't decimal. So radix
2 indicates that precision 32 means "32 bits", not "32 decimal digits".

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 05/03/2017 08:18 PM, Igor Korot wrote:

Hi, ALL,
One more question if I may.

[code]
draft=# SELECT * FROM information_schema.columns WHERE table_name =
'leagues' AND ordinal_position = 8;
table_catalog | table_schema | table_name | column_name |
ordinal_position | column_default | is_nullable | data_type |
character_maximum_length | character_octet_length | numeric_precision
| numeric_precision_radix | numeric_scale | datetime_precision |
interval_type | interval_precision | character_set_catalog |
character_set_schema | character_set_name | collation_catalog |
collation_schema | collation_name | domain_catalog | domain_schema |
domain_name | udt_catalog | udt_schema | udt_name | scope_catalog |
scope_schema | scope_name | maximum_cardinality | dtd_identifier |
is_self_referencing | is_identity | identity_generation |
identity_start | identity_increment | identity_maximum |
identity_minimum | identity_cycle | is_generated |
generation_expression | is_updatable
---------------+--------------+------------+--------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+-------------------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+----------------+----------------+---------------+-------------+-------------+------------+----------+---------------+--------------+------------+---------------------+----------------+---------------------+-------------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+--------------
draft | public | leagues | benchplayers |
8 | | YES | integer |
| | 32 |
2 | 0 | | |
| | |
| | | |
| | | draft | pg_catalog | int4
| | | |
| 8 | NO | NO |
| | | |
| | NEVER | | YES
(1 row)

[/code]

In this query result field 'numeric_precision' is set to 32 and
'numeric_precision_radix' is set to 2.

According to the documentation 'numeric_precision_radix' field should
indicate what radix the value of 'numeric_precision' is stored.

However, even though the radix is 2, the actual value is 32, which is
not a radix 2.

Could someone please shed some light?

What Postgres version?

Also the data_type is showing as integer not numeric so the numeric_*
values should be NULL.

What is the schema definition for the table?

When I run the query:

production=# select version();
version

-----------------------------------------------------------------------
PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.5, 64-bit
(1 row)

production=# \d projection

...

qty | numeric(7,2) | not null default 0

...

production=# \x
Expanded display is on.

production=# select * from information_schema.columns where table_name
='projection' and column_name='qty';

...

numeric_precision | 7
numeric_precision_radix | 10
numeric_scale | 2

....

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 05/04/2017 07:00 AM, Tom Lane wrote:

Igor Korot <ikorot01@gmail.com> writes:

Yes, so for the Radix 2 only 0 and 1 should be used, right?
And so the value should be 100000 and not 32.

No, certainly not. The radix column says what the units of measurement
are, not that the values in the precision column aren't decimal. So radix
2 indicates that precision 32 means "32 bits", not "32 decimal digits".

Alright now I am confused:

https://www.postgresql.org/docs/9.6/static/infoschema-columns.html

"numeric_precision cardinal_number

If data_type identifies a numeric type, this column contains the
(declared or implicit) precision of the type for this column. The
precision indicates the number of significant digits. It can be
expressed in decimal (base 10) or binary (base 2) terms, as specified in
the column numeric_precision_radix. For all other data types, this
column is null.

"

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 05/04/2017 07:00 AM, Tom Lane wrote:

No, certainly not. The radix column says what the units of measurement
are, not that the values in the precision column aren't decimal. So radix
2 indicates that precision 32 means "32 bits", not "32 decimal digits".

Alright now I am confused:

https://www.postgresql.org/docs/9.6/static/infoschema-columns.html

"numeric_precision cardinal_number

If data_type identifies a numeric type, this column contains the
(declared or implicit) precision of the type for this column. The
precision indicates the number of significant digits. It can be
expressed in decimal (base 10) or binary (base 2) terms, as specified in
the column numeric_precision_radix. For all other data types, this
column is null.
"

I'm not here to defend the wording in our documentation ;-)

Perhaps this would be clearer if it said "measured in ... digits" rather
than "expressed in ... terms"?

It should probably also say "identifies a numeric type of restricted
precision", since for example it'll be null for a column that's
NUMERIC but has no typmod.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 05/04/2017 07:29 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 05/04/2017 07:00 AM, Tom Lane wrote:

No, certainly not. The radix column says what the units of measurement
are, not that the values in the precision column aren't decimal. So radix
2 indicates that precision 32 means "32 bits", not "32 decimal digits".

Alright now I am confused:

https://www.postgresql.org/docs/9.6/static/infoschema-columns.html

"numeric_precision cardinal_number

If data_type identifies a numeric type, this column contains the
(declared or implicit) precision of the type for this column. The
precision indicates the number of significant digits. It can be
expressed in decimal (base 10) or binary (base 2) terms, as specified in
the column numeric_precision_radix. For all other data types, this
column is null.
"

I'm not here to defend the wording in our documentation ;-)

Perhaps this would be clearer if it said "measured in ... digits" rather
than "expressed in ... terms"?

It should probably also say "identifies a numeric type of restricted
precision", since for example it'll be null for a column that's
NUMERIC but has no typmod.

Alright I see that, but why does my example show a
numeric_precision_radix of 10?

Is there some transition point where it goes from base 10 to base 2?

Also why does the OPs query show anything when the data_type is integer?

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver <adrian.klaver@aklaver.com> writes:

Alright I see that, but why does my example show a
numeric_precision_radix of 10?

Is there some transition point where it goes from base 10 to base 2?

In PG, "numeric" always has radix 10, because the underlying
implementation is decimal, and all other numerical types such as int and
float have radix 2, because the underlying implementation is binary.
Other DBMSses could perhaps do it differently.

Hmm ... you could argue that numeric_precision_radix is telling you
something about the type's arithmetic behavior independently of what
the particular column's maximum-precision-if-any is. That's not how
the SQL spec defines it, but that's really what it's doing.

Also why does the OPs query show anything when the data_type is integer?

The point is that our integers are 32-bit integers, not some other size.
If you try it on bigint or smallint columns, you'll get other answers.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 05/04/2017 07:29 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 05/04/2017 07:00 AM, Tom Lane wrote:

No, certainly not. The radix column says what the units of measurement
are, not that the values in the precision column aren't decimal. So radix
2 indicates that precision 32 means "32 bits", not "32 decimal digits".

Alright now I am confused:

https://www.postgresql.org/docs/9.6/static/infoschema-columns.html

"numeric_precision cardinal_number

If data_type identifies a numeric type, this column contains the
(declared or implicit) precision of the type for this column. The
precision indicates the number of significant digits. It can be
expressed in decimal (base 10) or binary (base 2) terms, as specified in
the column numeric_precision_radix. For all other data types, this
column is null.
"

I'm not here to defend the wording in our documentation ;-)

Perhaps this would be clearer if it said "measured in ... digits" rather
than "expressed in ... terms"?

It should probably also say "identifies a numeric type of restricted
precision", since for example it'll be null for a column that's
NUMERIC but has no typmod.

Think I answered my own question. Numeric refers to all the types under:

https://www.postgresql.org/docs/9.6/static/datatype-numeric.html

not just the specific type numeric.

So:

production=# \d projection

....

pot_ct | integer

....

production=# select * from information_schema.columns where table_name
='projection' and column_name='pot_ct';

...

numeric_precision | 32
numeric_precision_radix | 2
numeric_scale | 0

...

Then the output is saying I am looking at a 32 bit integer(int4). If the
table definition had been int8, the output would be:

numeric_precision | 64
numeric_precision_radix | 2
numeric_scale | 0

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 05/04/2017 07:44 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

Alright I see that, but why does my example show a
numeric_precision_radix of 10?

Is there some transition point where it goes from base 10 to base 2?

In PG, "numeric" always has radix 10, because the underlying
implementation is decimal, and all other numerical types such as int and
float have radix 2, because the underlying implementation is binary.
Other DBMSses could perhaps do it differently.

Hmm ... you could argue that numeric_precision_radix is telling you
something about the type's arithmetic behavior independently of what
the particular column's maximum-precision-if-any is. That's not how
the SQL spec defines it, but that's really what it's doing.

Also why does the OPs query show anything when the data_type is integer?

The point is that our integers are 32-bit integers, not some other size.
If you try it on bigint or smallint columns, you'll get other answers.

Got it thanks, I was being too literal in my interpretation of numeric.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi, guys,

On Thu, May 4, 2017 at 10:54 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 05/04/2017 07:44 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

Alright I see that, but why does my example show a
numeric_precision_radix of 10?

Is there some transition point where it goes from base 10 to base 2?

In PG, "numeric" always has radix 10, because the underlying
implementation is decimal, and all other numerical types such as int and
float have radix 2, because the underlying implementation is binary.
Other DBMSses could perhaps do it differently.

Hmm ... you could argue that numeric_precision_radix is telling you
something about the type's arithmetic behavior independently of what
the particular column's maximum-precision-if-any is. That's not how
the SQL spec defines it, but that's really what it's doing.

Also why does the OPs query show anything when the data_type is integer?

The point is that our integers are 32-bit integers, not some other size.
If you try it on bigint or smallint columns, you'll get other answers.

Got it thanks, I was being too literal in my interpretation of numeric.

So basically what you are all saying is that since the value "32"
contains 2 digits:
"3" and "2" the column radix will contain "2". And it is not the
actual representation
of the number 32 the radix applies to.

Am I right?

But then what purpose does this column solves?

Just curious...

Thank you.

regards, tom lane

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, May 4, 2017 at 8:08 AM, Igor Korot <ikorot01@gmail.com> wrote:

Hi, guys,

On Thu, May 4, 2017 at 10:54 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 05/04/2017 07:44 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

Alright I see that, but why does my example show a
numeric_precision_radix of 10?

Is there some transition point where it goes from base 10 to base 2?

In PG, "numeric" always has radix 10, because the underlying
implementation is decimal, and all other numerical types such as int and
float have radix 2, because the underlying implementation is binary.
Other DBMSses could perhaps do it differently.

Hmm ... you could argue that numeric_precision_radix is telling you
something about the type's arithmetic behavior independently of what
the particular column's maximum-precision-if-any is. That's not how
the SQL spec defines it, but that's really what it's doing.

Also why does the OPs query show anything when the data_type is

integer?

The point is that our integers are 32-bit integers, not some other size.
If you try it on bigint or smallint columns, you'll get other answers.

Got it thanks, I was being too literal in my interpretation of numeric.

So basically what you are all saying is that since the value "32"
contains 2 digits:
"3" and "2" the column radix will contain "2". And it is not the
actual representation
of the number 32 the radix applies to.

Am I right?

​No.

32 is the number of digits/positions available to represent a number.

2 is the base of the number being represented.

Therefore there are 2 ^ 32 possible numbers that can be represented by this
column.

If the radix was instead 10, and the ​precision was still 32 - which is
acceptable - there would instead be 10 ^ 32 possible numbers that could be
represented.

And yes, we are talking about parts of numbers here but the concept holds.
Its too early for me to get my head around precision/scale...

So one column is the base and the other is the exponent.

David J.

Adrian Klaver <adrian.klaver@aklaver.com> writes:

Think I answered my own question. Numeric refers to all the types under:
https://www.postgresql.org/docs/9.6/static/datatype-numeric.html
not just the specific type numeric.

Right. This overloading of the term "numeric" is a bit unfortunate,
but it's not all our fault, because the SQL standard does the same
thing --- for instance they write

— The data types NUMERIC, DECIMAL, SMALLINT, INTEGER, and BIGINT are
collectively referred to as exact numeric types.
— The data types FLOAT, REAL, and DOUBLE PRECISION are collectively
referred to as approximate numeric types.
— Exact numeric types and approximate numeric types are collectively
referred to as numeric types. Values of numeric types are referred
to as numbers.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David et al,

On Thu, May 4, 2017 at 11:27 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Thu, May 4, 2017 at 8:08 AM, Igor Korot <ikorot01@gmail.com> wrote:

Hi, guys,

On Thu, May 4, 2017 at 10:54 AM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 05/04/2017 07:44 AM, Tom Lane wrote:

Adrian Klaver <adrian.klaver@aklaver.com> writes:

Alright I see that, but why does my example show a
numeric_precision_radix of 10?

Is there some transition point where it goes from base 10 to base 2?

In PG, "numeric" always has radix 10, because the underlying
implementation is decimal, and all other numerical types such as int
and
float have radix 2, because the underlying implementation is binary.
Other DBMSses could perhaps do it differently.

Hmm ... you could argue that numeric_precision_radix is telling you
something about the type's arithmetic behavior independently of what
the particular column's maximum-precision-if-any is. That's not how
the SQL spec defines it, but that's really what it's doing.

Also why does the OPs query show anything when the data_type is
integer?

The point is that our integers are 32-bit integers, not some other
size.
If you try it on bigint or smallint columns, you'll get other answers.

Got it thanks, I was being too literal in my interpretation of numeric.

So basically what you are all saying is that since the value "32"
contains 2 digits:
"3" and "2" the column radix will contain "2". And it is not the
actual representation
of the number 32 the radix applies to.

Am I right?

No.

32 is the number of digits/positions available to represent a number.

2 is the base of the number being represented.

Therefore there are 2 ^ 32 possible numbers that can be represented by this
column.

If the radix was instead 10, and the precision was still 32 - which is
acceptable - there would instead be 10 ^ 32 possible numbers that could be
represented.

And yes, we are talking about parts of numbers here but the concept holds.
Its too early for me to get my head around precision/scale...

So one column is the base and the other is the exponent.

So if I write for example:

CREATE TABLE foo(id INTEGER PRIMARY KEY, label VARCHAR(50), price
DOUBLE(10, 2));

how can I get 50, 10 and 2 from those 5 fields?

I thought that this is what will be stored in the columns table...

Thank you.

David J.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Igor Korot <ikorot01@gmail.com> writes:

So if I write for example:

CREATE TABLE foo(id INTEGER PRIMARY KEY, label VARCHAR(50), price
DOUBLE(10, 2));

how can I get 50, 10 and 2 from those 5 fields?

(I assume you meant NUMERIC where you wrote DOUBLE, because that's
not valid syntax as given.)

Try something like this:

# select column_name,data_type,numeric_precision,numeric_precision_radix,numeric_scale,character_maximum_length from information_schema.columns where table_name = 'foo';
-[ RECORD 1 ]------------+------------------
column_name | id
data_type | integer
numeric_precision | 32
numeric_precision_radix | 2
numeric_scale | 0
character_maximum_length |
-[ RECORD 2 ]------------+------------------
column_name | label
data_type | character varying
numeric_precision |
numeric_precision_radix |
numeric_scale |
character_maximum_length | 50
-[ RECORD 3 ]------------+------------------
column_name | price
data_type | numeric
numeric_precision | 10
numeric_precision_radix | 10
numeric_scale | 2
character_maximum_length |

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general