Re: Weird issue with truncation of values in array with some tables

Thanks!
Possibly the use of name type in pg_tables could be emphasized and maybe a
note about name type in docs somewhere

On Sun, 16 Aug 2020, 18:12 Tom Lane, <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mike Martin <redtux1(at)gmail(dot)com> writes:
> > I have come across a weird issue with truncation of text in an array (in
> > this case using pg_indexes view)
> > This query truncates the second array element at 63 characters
> > SELECT ARRAY[indexname,indexdef] FROM pg_indexes
> > However reversing the order doesn't truncate
> > SELECT ARRAY[indexdef,indexname] FROM pg_indexes
> > Anyone know why this behaviour occurs?
>
> indexname is of type name, indexdef is of type text, and the rules
> for inferring the type of an array[] construct are such that the
> first element's type wins in these cases.
>
> regression=# SELECT pg_typeof(ARRAY[indexname,indexdef]) FROM pg_indexes
> limit 1;
> pg_typeof
> -----------
> name[]
> (1 row)
>
> regression=# SELECT pg_typeof(ARRAY[indexdef,indexname]) FROM pg_indexes
> limit 1;
> pg_typeof
> -----------
> text[]
> (1 row)
>
> You could insert an explicit cast to text to avoid the truncation
> of indexdef to name:
>
> regression=# SELECT pg_typeof(ARRAY[indexname::text,indexdef]) FROM
> pg_indexes limit 1;
> pg_typeof
> -----------
> text[]
> (1 row)
>
> The documentation about that is here:
> https://www.postgresql.org/docs/current/typeconv-union-case.html
> although looking at this example it seems like that description isn't
> telling the full truth.
>
> regards, tom lane
>