Search code examples
postgresqlpostgresql-11

_text postgres data type

I get a table with type _text

create table mt ( id int8,
directions _text null)

Its not my code so I don't know what is type _text and how to work with it. I look for it in doc https://www.postgresql.org/docs/11/datatype.html but I didn't find.

My question is does anybody works with _text in postgresql? I need an examples.

Solution

  • It's the internal type name for an array. Similar to int8 being the internal name for bigint.

    So your statement is the same as:

    create table mt 
(
  id bigint,
  directions text[]
)

    I can't find the reference any more, but the reason was that [] is invalid in an identifier (and a type name is an identifier) and thus array types are identified with the _ prefix in pg_type

    This is detectable through the pg_type system catalog:

    select bt.typname as base_type, 
       at.typname as array_type
from pg_type bt
  join pg_type at on bt.typarray = at.oid
where bt.typnamespace = 'pg_catalog'::regnamespace  
order by bt.typname

    pg_type.typarray links to the pg_type row that contains the array type for the base type.

    Laurenz answered a similar question on Database Administrators with some more technical insight.