Is _ a supported way to create a column of array type?
Hi, As documented to https://www.postgresql.org/docs/11/arrays.html#ARRAYS-DECLARATION one can create column of an array type using `[]` form. Internally, array types get a name in the form of `_`. 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 `_` 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
Re: Is _ a supported way to create a column of array type?
Hi Tom, Thanks for your response. I think I need to provide some context for my question. I am maintaining Presto connector to Postgres ( https://github.com/prestosql/presto/tree/master/presto-postgresql). When accessing a table in Postgres, we need to map columns' types to appropriate types in Presto. For mapping arrays, we need to know number of array dimensions. Currently we read this from pg_attribute.attndims and this does not work for _ columns. 1. is there a better way to get array dimensions for a column of array type? 2. is it possible to make pg_attribute.attndims have correct value when column is defined using _ form? Best, Piotr On Thu, Apr 25, 2019 at 11:03 PM Tom Lane wrote: > Piotr Findeisen writes: > > Internally, array types get a name in the form of `_`. > > Typically, yes. > > > *Can a user use `_` to define a column of array type?* > > Sure ... didn't you try it? > > > *Is it supported?* > > Not really, because it's not guaranteed that the name looks like that. > There are various corner cases where something else would be generated, > either to avoid a collision, or because truncation is needed. > > However, if you've taken the trouble to check what name actually got > assigned to the array type, it's perfectly valid to use that name. > > > 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. > > Yeah. Nothing really cares about attndims though ... it's vestigial. > Perhaps we should remove it someday. > > regards, tom lane >
Re: Is _ a supported way to create a column of array type?
Hi Tom, I think I understand now. Postgres type system does not distinguish between array types with different number of dimensions. int[], int[][] and int[][][][][][] are all equivalent to the type system. Number of dimensions is part of the value though and execution takes care of it. If I subscript an array with wrong "number of brackets" (as in `select (array[1,2,3])[1][1][1]`) I get NULL. Presto type system however distinguishes array(integer), array(array(integer))... (using Presto therms). And execution is (expectedly) not as flexible. We can inspect number of brackets that were written in the table creation command but that's inferring (and enforcing) strong typing for something that is not strongly typed. Thus, we can fail at execution. Do you have any plans to support arrays with different number of dimensions in the type system? Best, Piotr
