Is _<typename> a supported way to create a column of array type?

From: Piotr Findeisen <piotr(dot)findeisen(at)starburstdata(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Is _<typename> a supported way to create a column of array type?
Date: 2019-04-25 19:52:51
Message-ID: CAMrFzbt_DjD1BSLOhAUgNApu8MV3CCPmbEac9FfMxOOq6GAD0g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

As documented to
https://www.postgresql.org/docs/11/arrays.html#ARRAYS-DECLARATION one can
create column of an array type using `<typename>[]` form.

Internally, array types get a name in the form of `_<typename>`.
This is documented
https://www.postgresql.org/docs/11/sql-createtype.html#id-1.9.3.94.5.9

*So -- the question: *
*Can a user use `_<typename>` to define a column of array type?*
*Is it supported?*

The reason I am asking is that e.g. int4[] and _int4 behave differently.
Although they look the same, the have different pg_attribute.attndims.

I am testing on Postgres 11.2.

================================================
create table t(a int4[], b _int4);

\d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+-----------+-----------+----------+---------
a | integer[] | | |
b | integer[] | | |

SELECT attname, attndims FROM pg_attribute att JOIN pg_class tbl ON
tbl.oid = att.attrelid WHERE tbl.relname = 't';
attname | attndims
----------+----------
...
* a | 1*
* b | 0*
================================================

This has also been discussed previously in 2006 in the
https://www.postgresql.org/message-id/8C5B026B51B6854CBE88121DBF097A8651DB95%40ehost010-33.exch010.intermedia.net
user
group thread. However, it was a while ago (so something might have changed
since then) and the conclusion from that discussion wasn't fully clear to
me.

Best regards,
Piotr

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2019-04-25 19:58:16 Re: analyze causes query planner to choose suboptimal plan for a select query in separate transaction
Previous Message Martin Kováčik 2019-04-25 19:47:39 Re: analyze causes query planner to choose suboptimal plan for a select query in separate transaction