Обсуждение: datatype preceded by underscore creates array

Is there any special meaning to preceding a datatype (or at least some
datatypes) in a table or function definition by underscore that is a
synonym for an array? I can't see it documented anywhere. Below are some
examples. The other question is why "_int4" parses to int[], but "_int"
does not, etc. This is on PostgreSQL 8.1.3 Linux.

gp=> create table a1 (b _int4);
CREATE TABLE
gp=> \d a1
         Table "mnp.a1"
 Column |   Type    | Modifiers
--------+-----------+-----------
 b      | integer[] |

gp=> create table a2 (b _char);
CREATE TABLE
gp=> \d a2
        Table "mnp.a2"
 Column |   Type   | Modifiers
--------+----------+-----------
 b      | "char"[] |

gp=> create table a3 (b _int);
ERROR:  type "_int" does not exist

gp=> create table a4 (b _char(1));
ERROR:  syntax error at or near "(" at character 25
LINE 1: create table a4 (b _char(1));
                                ^

On Mon, Oct 16, 2006 at 12:33:35PM -0700, George Pavlov wrote:
> Is there any special meaning to preceding a datatype (or at least some
> datatypes) in a table or function definition by underscore that is a
> synonym for an array? I can't see it documented anywhere. Below are some
> examples. The other question is why "_int4" parses to int[], but "_int"
> does not, etc. This is on PostgreSQL 8.1.3 Linux.

Yep, the array type is represented internally by prefixings an
underscore. It's mentioned somewhere in the docs, but you may as well
ignore it.

"int4" is the actual type name, "integer" is the sql standard name.
PostgreSQL displays SQL compliant output where possible. _int simply
doesn't exist, and oddities like (3) after the char does have array
support at all...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

> Yep, the array type is represented internally by prefixings an
> underscore. It's mentioned somewhere in the docs, but you may as well
> ignore it.

Hmm, I am not sure I particularly like this behavior or the "ignore it"
advice. Suppose someone makes a typo in his/her table definition: meant
to create an int4 column but accidentally typed an underscore. You'd
expect the statement to fail. Instead it doesn't fail but creates an
unexpected datatype for the column. If undescore is a purposeful (rather
than an accidental) SQL standard extension one would expect it to be (a)
documented in some place like
http://www.postgresql.org/docs/8.1/static/arrays.html#AEN5584, and (b)
behaving more consistently (if _foo is a synonym for foo[] then all
variations of "foo" should support it).

> "int4" is the actual type name, "integer" is the sql standard name.
> PostgreSQL displays SQL compliant output where possible. _int simply
> doesn't exist, and oddities like (3) after the char does have array
> support at all...

Not sure what you mean--char(x) is not an oddity and it does have array
support:

create table a7 (b char(1)[]);
CREATE TABLE

George

On Mon, Oct 16, 2006 at 01:16:34PM -0700, George Pavlov wrote:
> Hmm, I am not sure I particularly like this behavior or the "ignore it"
> advice. Suppose someone makes a typo in his/her table definition: meant
> to create an int4 column but accidentally typed an underscore. You'd
> expect the statement to fail. Instead it doesn't fail but creates an
> unexpected datatype for the column. If undescore is a purposeful (rather
> than an accidental) SQL standard extension one would expect it to be (a)
> documented in some place like
> http://www.postgresql.org/docs/8.1/static/arrays.html#AEN5584, and (b)
> behaving more consistently (if _foo is a synonym for foo[] then all
> variations of "foo" should support it).

Hmm, if someone typos to get "int8" instead of "int4" they get the
wrong datatype too, I don't know if that's an argument. The reason is
that all types need to have an identifier. I suppose they could be
called "pg_internal_array_type_for_int4", but for historical reasons
it's just _int4.

It's deprecated, you're not encouraged to use it and if it were easy to
get rid of it would have been done a long time ago.

> Not sure what you mean--char(x) is not an oddity and it does have array
> support:

The types char, integer, real, etc are the SQL names for the types and
they have special SQL incantations for them. The underlying types are
actually called bpchar, int4 and float8. The latter have magic array
types, the former don't.

> create table a7 (b char(1)[]);
> CREATE TABLE

This is just another way of saying:

create table a7 (b _bpchar)

except that doesn't allow you to specify a length...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

> On Mon, Oct 16, 2006 at 01:16:34PM -0700, George Pavlov wrote:
> > Hmm, I am not sure I particularly like this behavior or the
> > "ignore it"
> > advice. Suppose someone makes a typo in his/her table
> > definition: meant
> > to create an int4 column but accidentally typed an underscore. You'd
> > expect the statement to fail. Instead it doesn't fail but creates an
> > unexpected datatype for the column. If undescore is a
> > purposeful (rather
> > than an accidental) SQL standard extension one would expect
> > it to be (a) documented in some place like
> >
> > http://www.postgresql.org/docs/8.1/static/arrays.html#AEN5584, and
(b)
> > behaving more consistently (if _foo is a synonym for foo[] then all
> > variations of "foo" should support it).
>
> Hmm, if someone typos to get "int8" instead of "int4" they get the
> wrong datatype too, I don't know if that's an argument. The reason is
> that all types need to have an identifier. I suppose they could be
> called "pg_internal_array_type_for_int4", but for historical reasons
> it's just _int4.

yes, but int8 is a clearly documented while preceding certain "magic"
datatype names with underscores is not. i really don't have much of a
problem with this, but little things like this contribute to people
coming from other DBMSs developing opinions that "this open-source stuff
does weird, undocumented things" (not that commercial DBMSs don't do
weird stuff :). sorry to belabor a minor point.

george

"George Pavlov" <gpavlov@mynewplace.com> writes:
> yes, but int8 is a clearly documented while preceding certain "magic"
> datatype names with underscores is not. i really don't have much of a
> problem with this, but little things like this contribute to people
> coming from other DBMSs developing opinions that "this open-source stuff
> does weird, undocumented things" (not that commercial DBMSs don't do
> weird stuff :).

Undocumented?  Read the CREATE TYPE manual page.

I'll agree it's pretty ugly, but it's not worth changing ... especially
since a change would break some legacy application code.

            regards, tom lane