Getting list of supported types in Postgres

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. +

--
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, 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

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

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

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

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

--
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, 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. +

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

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

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

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

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

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

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

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?

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

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

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

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?

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

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

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

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

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

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

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

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

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

On 15 August 2013 17:33, Ivan Radovanovic <radovanovic@gmail.com> wrote:

On 08/15/13 17:27, Adrian Klaver napisa:

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.

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.