Обсуждение: [GENERAL] Column information
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?
Thank you.
> 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.
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. >
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
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
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
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
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
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
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
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
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
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
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.
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.columnswhere 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