Thread: 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.
Attachment
> 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.
Attachment
> 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