Re: Getting list of supported types in Postgres

[Alban Hertroys <haramrae@xxxxxxxxx>]

On 15 August 2013 17:33, Ivan Radovanovic <radovanovic@xxxxxxxxx> 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.