7

I'm using a postgres database created by a script (Osm2pgsql). It has created some fields with the type _text for two dimensional arrays of text. I'm trying to understand why it didn't just create a text[][] field, and why this type exists in postgres.

I can find this link in the docs which says:

When you define a new base type, PostgreSQL automatically provides support for arrays of that type. The array type typically has the same name as the base type with the underscore character (_) prepended.

But I don't understand why it is using this "underscored" type name when text is a built in type of postgres?

1 Answer 1

10

_text and text[] are interchangeable in PostgreSQL. Also text[][] is the same data type.

To make things clearer: type[] means "the array type whose elements are of type type", and the actual name of that type is _type. The reason for that choice of name is that when a type is created, an associated array type is created as well. The name of that internal array type is determined by calling the C function makeArrayTypeName with the type name as argument. This in turn calls makeUniqueTypeName, whose code comment describes what it does:

/*
 * makeUniqueTypeName
 *      Generate a unique name for a prospective new type
 *
 * Given a typeName, return a new palloc'ed name by prepending underscores
 * until a non-conflicting name results.
 *
 * If tryOriginal, first try with zero underscores.
 */

In principle, there would be no problem in renaming _type to something else, but PostgreSQL forbids that in order to avoid confusion.

2
  • Thanks for your answer! I find it confusing that they have two notations for arrays. I'm guessing this is for historical reasons?
    – Eoin
    Feb 12, 2022 at 7:52
  • 2
    No, I'd say for technical reasons. Each type must have a name, and choosing type[] as the name would require double quotes around it and may lead to interesting confusion between type[] and "type[]". Feb 12, 2022 at 10:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.