Обсуждение: Getting list of supported types in Postgres
Hi guys,
I am looking for a way to get list of supported types in Postgres
together with information whether type can have optional size (like
varchar for example), whether it can have precision (like decimal for
example), and whether it can come as value of sequence (like all integer
types for example), but I have trouble getting that info from pg_type
table. This is SQL I was using:
select
pg_catalog.format_type(oid, null),
*
from
pg_type
where
typnamespace = (select oid from pg_namespace where
nspname='pg_catalog') and
typisdefined and
typname not like '$_%' escape '$' and
typname not like 'pg%' and
typtype = 'b'
order by
typname
apparently pg_catalog.format_type for some types return quoted name
("char" for example), also I can't find decimal in results (there is
numeric, but I would like to have complete list of supported types, so
decimal should be included too). In documentation it is said that typlen
of -1 or -2 means that type is variable length, but I don't know how to
find out if type can have additional precision?
Regards,
Ivan
On Wed, Aug 14, 2013 at 02:17:26PM +0200, Ivan Radovanovic wrote:
> Hi guys,
>
> I am looking for a way to get list of supported types in Postgres
> together with information whether type can have optional size (like
> varchar for example), whether it can have precision (like decimal
> for example), and whether it can come as value of sequence (like all
> integer types for example), but I have trouble getting that info
> from pg_type table. This is SQL I was using:
>
> select
> pg_catalog.format_type(oid, null),
> *
> from
> pg_type
> where
> typnamespace = (select oid from pg_namespace where
> nspname='pg_catalog') and
> typisdefined and
> typname not like '$_%' escape '$' and
> typname not like 'pg%' and
> typtype = 'b'
> order by
> typname
>
> apparently pg_catalog.format_type for some types return quoted name
> ("char" for example), also I can't find decimal in results (there is
> numeric, but I would like to have complete list of supported types,
> so decimal should be included too). In documentation it is said that
> typlen of -1 or -2 means that type is variable length, but I don't
> know how to find out if type can have additional precision?
Try psql -E, and run the \dT command to see the query it uses.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian <bruce@momjian.us> wrote:
> Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
--
Michael
On 8/14/2013 5:17 AM, Ivan Radovanovic wrote:
> apparently pg_catalog.format_type for some types return quoted name
> ("char" for example), also I can't find decimal in results (there is
> numeric, but I would like to have complete list of supported types, so
> decimal should be included too). In documentation it is said that
> typlen of -1 or -2 means that type is variable length, but I don't
> know how to find out if type can have additional precision?
DECIMAL is not a type per say, its an alias for NUMERIC.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
On 08/15/13 05:23, Michael Paquier napisa:
> On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian<bruce@momjian.us> wrote:
>> Try psql -E, and run the \dT command to see the query it uses.
> You have also the following commands:
> - ¥dT+, all types with addition information like its size ('var' is
> for example variable length)
> - ¥dTS+, this includes also the catalog types
>
> Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I need,
but it is still missing info if type can have precision, and apparently
aliases are missing (no decimal for numeric, no character for char and
so on). Additionally it would be great if I could somehow also get
information if type can be indexed.
This doesn't have to come through SQL (I simply assumed that info can be
obtained either from information schema or Postgress specific views and
tables) - if you can point me to some source file that would be
acceptable too :-)
Regards,
Ivan
On Thu, Aug 15, 2013 at 11:33:42AM +0200, Ivan Radovanovic wrote:
> On 08/15/13 05:23, Michael Paquier napisa:
> >On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian<bruce@momjian.us> wrote:
> >>Try psql -E, and run the \dT command to see the query it uses.
> >You have also the following commands:
> >- ¥dT+, all types with addition information like its size ('var' is
> >for example variable length)
> >- ¥dTS+, this includes also the catalog types
> >
> >Regards,
>
> Hi Michael and Bruce,
>
> Thank you for taking your time to reply.
>
> If I run \dTS+ that returns something that resembles information I
> need, but it is still missing info if type can have precision, and
> apparently aliases are missing (no decimal for numeric, no character
> for char and so on). Additionally it would be great if I could
> somehow also get information if type can be indexed.
Well, some of that mapping happens in src/backend/parser/gram.y,
particularly the "Numeric" rule. The meaning of the precision/scale is
type-specific, but if you do -E and \d tablename, you can see how the
modifiers are shown for user-defined columns.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
> On 08/15/13 05:23, Michael Paquier napisa:
>> On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian<bruce@momjian.us> wrote:
>>> Try psql -E, and run the \dT command to see the query it uses.
>> You have also the following commands:
>> - ¥dT+, all types with addition information like its size ('var' is
>> for example variable length)
>> - ¥dTS+, this includes also the catalog types
>>
>> Regards,
>
> Hi Michael and Bruce,
>
> Thank you for taking your time to reply.
>
> If I run \dTS+ that returns something that resembles information I need,
> but it is still missing info if type can have precision, and apparently
> aliases are missing (no decimal for numeric, no character for char and
> so on). Additionally it would be great if I could somehow also get
> information if type can be indexed.
>
> This doesn't have to come through SQL (I simply assumed that info can be
> obtained either from information schema or Postgress specific views and
> tables) - if you can point me to some source file that would be
> acceptable too :-)
The docs would seem to be the place to go:
http://www.postgresql.org/docs/9.2/interactive/datatype.html
>
> Regards,
> Ivan
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
On 08/15/13 16:30, Adrian Klaver napisa:
> On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
>> On 08/15/13 05:23, Michael Paquier napisa:
>>> On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian<bruce@momjian.us> wrote:
>>>> Try psql -E, and run the \dT command to see the query it uses.
>>> You have also the following commands:
>>> - ¥dT+, all types with addition information like its size ('var' is
>>> for example variable length)
>>> - ¥dTS+, this includes also the catalog types
>>>
>>> Regards,
>>
>> Hi Michael and Bruce,
>>
>> Thank you for taking your time to reply.
>>
>> If I run \dTS+ that returns something that resembles information I need,
>> but it is still missing info if type can have precision, and apparently
>> aliases are missing (no decimal for numeric, no character for char and
>> so on). Additionally it would be great if I could somehow also get
>> information if type can be indexed.
>>
>> This doesn't have to come through SQL (I simply assumed that info can be
>> obtained either from information schema or Postgress specific views and
>> tables) - if you can point me to some source file that would be
>> acceptable too :-)
>
> The docs would seem to be the place to go:
>
> http://www.postgresql.org/docs/9.2/interactive/datatype.html
>
>
Thanks Adrian, sometimes we overlook most obvious solutions :-)
Now I just need to find out which types can be indexed (and which types
can be part of PK)
Regards,
Ivan
On 08/15/2013 07:37 AM, Ivan Radovanovic wrote:
> On 08/15/13 16:30, Adrian Klaver napisa:
>> On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
>>> On 08/15/13 05:23, Michael Paquier napisa:
>>>> On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian<bruce@momjian.us> wrote:
>>>>> Try psql -E, and run the \dT command to see the query it uses.
>>>> You have also the following commands:
>>>> - ¥dT+, all types with addition information like its size ('var' is
>>>> for example variable length)
>>>> - ¥dTS+, this includes also the catalog types
>>>>
>>>> Regards,
>>>
>>> Hi Michael and Bruce,
>>>
>>> Thank you for taking your time to reply.
>>>
>>> If I run \dTS+ that returns something that resembles information I need,
>>> but it is still missing info if type can have precision, and apparently
>>> aliases are missing (no decimal for numeric, no character for char and
>>> so on). Additionally it would be great if I could somehow also get
>>> information if type can be indexed.
>>>
>>> This doesn't have to come through SQL (I simply assumed that info can be
>>> obtained either from information schema or Postgress specific views and
>>> tables) - if you can point me to some source file that would be
>>> acceptable too :-)
>>
>> The docs would seem to be the place to go:
>>
>> http://www.postgresql.org/docs/9.2/interactive/datatype.html
>>
>>
>
> Thanks Adrian, sometimes we overlook most obvious solutions :-)
>
> Now I just need to find out which types can be indexed (and which types
> can be part of PK)
http://www.postgresql.org/docs/9.2/interactive/indexes.html
>
> Regards,
> Ivan
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
On 08/15/13 16:49, Adrian Klaver napisa:
> On 08/15/2013 07:37 AM, Ivan Radovanovic wrote:
>> On 08/15/13 16:30, Adrian Klaver napisa:
>>> On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
>>>> On 08/15/13 05:23, Michael Paquier napisa:
>>>>> On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjian<bruce@momjian.us>
>>>>> wrote:
>>>>>> Try psql -E, and run the \dT command to see the query it uses.
>>>>> You have also the following commands:
>>>>> - ¥dT+, all types with addition information like its size ('var' is
>>>>> for example variable length)
>>>>> - ¥dTS+, this includes also the catalog types
>>>>>
>>>>> Regards,
>>>>
>>>> Hi Michael and Bruce,
>>>>
>>>> Thank you for taking your time to reply.
>>>>
>>>> If I run \dTS+ that returns something that resembles information I
>>>> need,
>>>> but it is still missing info if type can have precision, and apparently
>>>> aliases are missing (no decimal for numeric, no character for char and
>>>> so on). Additionally it would be great if I could somehow also get
>>>> information if type can be indexed.
>>>>
>>>> This doesn't have to come through SQL (I simply assumed that info
>>>> can be
>>>> obtained either from information schema or Postgress specific views and
>>>> tables) - if you can point me to some source file that would be
>>>> acceptable too :-)
>>>
>>> The docs would seem to be the place to go:
>>>
>>> http://www.postgresql.org/docs/9.2/interactive/datatype.html
>>>
>>>
>>
>> Thanks Adrian, sometimes we overlook most obvious solutions :-)
>>
>> Now I just need to find out which types can be indexed (and which types
>> can be part of PK)
>
> http://www.postgresql.org/docs/9.2/interactive/indexes.html
>
doesn't list which types can be indexed and which can't?
On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: >>> >>> Now I just need to find out which types can be indexed (and which types >>> can be part of PK) >> >> http://www.postgresql.org/docs/9.2/interactive/indexes.html >> > > doesn't list which types can be indexed and which can't? Postgres can handle a variety of indexes including indexing on expressions, which is why I pointed you to that link. To cut to the chase, in the above link at: http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html there is this: SELECT am.amname AS index_method, opf.opfname AS opfamily_name, amop.amopopr::regoperator AS opfamily_operator FROM pg_am am, pg_opfamily opf, pg_amop amop WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid ORDER BY index_method, opfamily_name, opfamily_operator; > > -- Adrian Klaver adrian.klaver@gmail.com
On 08/15/13 16:59, Adrian Klaver napisa: > On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: > >>>> >>>> Now I just need to find out which types can be indexed (and which types >>>> can be part of PK) >>> >>> http://www.postgresql.org/docs/9.2/interactive/indexes.html >>> >> >> doesn't list which types can be indexed and which can't? > > Postgres can handle a variety of indexes including indexing on > expressions, which is why I pointed you to that link. > > To cut to the chase, in the above link at: > > http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html > > there is this: > > SELECT am.amname AS index_method, > opf.opfname AS opfamily_name, > amop.amopopr::regoperator AS opfamily_operator > FROM pg_am am, pg_opfamily opf, pg_amop amop > WHERE opf.opfmethod = am.oid AND > amop.amopfamily = opf.oid > ORDER BY index_method, opfamily_name, opfamily_operator; >> >> Thanks Adrian, but question was how to decide which types are indexable - query which you sent returns list of operators defined for some types - for example it returns operators for bytea too, and you can't index by bytea, so I don't see how you could decide if type can be indexed based on this?
Ivan Radovanovic <radovanovic@gmail.com> writes:
> Thanks Adrian, but question was how to decide which types are indexable
A little bit of research in the system-catalogs documentation will show
you how to find the types that can be accepted by some index opclass
(hint: "pg_opclass.opcintype::regtype").
As far as the other question goes, you could look for types that have a
pg_type.typmodin function -- though I'm not sure whether you want to
consider every possible usage of typmods as being a "precision".
regards, tom lane
On 08/15/13 17:15, Tom Lane napisa: > Ivan Radovanovic<radovanovic@gmail.com> writes: >> Thanks Adrian, but question was how to decide which types are indexable > > A little bit of research in the system-catalogs documentation will show > you how to find the types that can be accepted by some index opclass > (hint: "pg_opclass.opcintype::regtype"). > > As far as the other question goes, you could look for types that have a > pg_type.typmodin function -- though I'm not sure whether you want to > consider every possible usage of typmods as being a "precision". > > regards, tom lane Thanks Tom, I will take type definitions from documentation (as Adrian suggested), and it looks like your pg_opclass suggestion will solve indexability question. Case closed I guess :-) Regards, Ivan
On 08/15/2013 08:07 AM, Ivan Radovanovic wrote: > On 08/15/13 16:59, Adrian Klaver napisa: >> On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: >> >>>>> >>>>> Now I just need to find out which types can be indexed (and which >>>>> types >>>>> can be part of PK) >>>> >>>> http://www.postgresql.org/docs/9.2/interactive/indexes.html >>>> >>> >>> doesn't list which types can be indexed and which can't? >> >> Postgres can handle a variety of indexes including indexing on >> expressions, which is why I pointed you to that link. >> >> To cut to the chase, in the above link at: >> >> http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html >> >> there is this: >> >> SELECT am.amname AS index_method, >> opf.opfname AS opfamily_name, >> amop.amopopr::regoperator AS opfamily_operator >> FROM pg_am am, pg_opfamily opf, pg_amop amop >> WHERE opf.opfmethod = am.oid AND >> amop.amopfamily = opf.oid >> ORDER BY index_method, opfamily_name, opfamily_operator; >>> >>> > > Thanks Adrian, but question was how to decide which types are indexable > - query which you sent returns list of operators defined for some types > - for example it returns operators for bytea too, and you can't index by > bytea, Actually you can: CREATE TABLE bytea_test(id int, fld_1 bytea); test=# \d bytea_test Table "public.bytea_test" Column | Type | Modifiers --------+---------+----------- id | integer | fld_1 | bytea | test=# CREATE INDEX i ON bytea_test (fld_1); test=# \d bytea_test Table "public.bytea_test" Column | Type | Modifiers --------+---------+----------- id | integer | fld_1 | bytea | Indexes: "i" btree (fld_1) -- Adrian Klaver adrian.klaver@gmail.com
On 08/15/13 17:27, Adrian Klaver napisa: > On 08/15/2013 08:07 AM, Ivan Radovanovic wrote: >> On 08/15/13 16:59, Adrian Klaver napisa: >>> On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: >>> >>>>>> >>>>>> Now I just need to find out which types can be indexed (and which >>>>>> types >>>>>> can be part of PK) >>>>> >>>>> http://www.postgresql.org/docs/9.2/interactive/indexes.html >>>>> >>>> >>>> doesn't list which types can be indexed and which can't? >>> >>> Postgres can handle a variety of indexes including indexing on >>> expressions, which is why I pointed you to that link. >>> >>> To cut to the chase, in the above link at: >>> >>> http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html >>> >>> there is this: >>> >>> SELECT am.amname AS index_method, >>> opf.opfname AS opfamily_name, >>> amop.amopopr::regoperator AS opfamily_operator >>> FROM pg_am am, pg_opfamily opf, pg_amop amop >>> WHERE opf.opfmethod = am.oid AND >>> amop.amopfamily = opf.oid >>> ORDER BY index_method, opfamily_name, opfamily_operator; >>>> >>>> >> >> Thanks Adrian, but question was how to decide which types are indexable >> - query which you sent returns list of operators defined for some types >> - for example it returns operators for bytea too, and you can't index by >> bytea, > > Actually you can: > > CREATE TABLE bytea_test(id int, fld_1 bytea); > > test=# \d bytea_test > > Table "public.bytea_test" > > Column | Type | Modifiers > --------+---------+----------- > id | integer | > fld_1 | bytea | > > test=# CREATE INDEX i ON bytea_test (fld_1); > > test=# \d bytea_test > Table "public.bytea_test" > Column | Type | Modifiers > --------+---------+----------- > id | integer | > fld_1 | bytea | > Indexes: > "i" btree (fld_1) > > > Didn't know that - I just tried on one existing table and it failed on account of index row too short ERROR: index row requires 14616 bytes, maximum size is 8191 SQL state: 54000 Although it looked suspicious like it could be solved by defining custom tablespace (never did that on Postgres so I am not sure if it would work), I assumed that it is because bytea can't be indexed. Obviously I learned one more new thing today :-) Thanks, Ivan
On 15 August 2013 17:33, Ivan Radovanovic <radovanovic@gmail.com> wrote:
On 08/15/13 17:27, Adrian Klaver napisa:Didn't know that - I just tried on one existing table and it failed on account of index row too shortActually you can:
CREATE TABLE bytea_test(id int, fld_1 bytea);
test=# \d bytea_test
Table "public.bytea_test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
fld_1 | bytea |
test=# CREATE INDEX i ON bytea_test (fld_1);
test=# \d bytea_test
Table "public.bytea_test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
fld_1 | bytea |
Indexes:
"i" btree (fld_1)
ERROR: index row requires 14616 bytes, maximum size is 8191
SQL state: 54000
Although it looked suspicious like it could be solved by defining custom tablespace (never did that on Postgres so I am not sure if it would work), I assumed that it is because bytea can't be indexed.
Your conclusion is not entirely correct; the problem is that each value in an index is limited to 8191 bytes. Your bytea value is longer than that and therefore the value can't be fit into an index entry. Hence the error.
People usually work around that, for example by defining a functional index on only the first 8191 bytes.
I haven't personally done that with bytea columns, but I think it's safe to assume that is possible.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.