What is the difference between POSTGRES _text and text[][] array types?



  • I'm using a postgres database created by a script ( https://wiki.openstreetmap.org/wiki/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 https://www.postgresql.org/docs/9.3/xtypes.html 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?



  • _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.


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2