Re: Getting list of supported types in Postgres

On 15 August 2013 17:33, Ivan Radovanovic <radovanovic(at)gmail(dot)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.