Search code examples
pythonpostgresqlpsycopg2

What is the Postgres _text type?


I have a Postgres table with a _text type (note the underscore) and am unable to determine how to insert the string [] into that table.

Here is my table definition:

CREATE TABLE public.newtable (
    column1 _text NULL
);

I have the postgis extension enabled:

CREATE EXTENSION IF NOT EXISTS postgis;

And my python code:

conn = psycopg2.connect()
conn.autocommit = True
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

rows = [("[]",)]
insert_query = f"INSERT INTO newtable (column1) values %s"
psycopg2.extras.execute_values(cur, insert_query, rows, template=None, page_size=100)

This returns the following error:

psycopg2.errors.InvalidTextRepresentation: malformed array literal: "[]"
LINE 1: INSERT INTO newtable (column1) values ('[]')
                                               ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.

How can I insert this data? What does this error mean? And what is a _text type in Postgres?


Solution

  • Pulling my comments together:

    CREATE TABLE public.newtable (
        column1 _text NULL
    );
    
    --_text gets transformed into text[]
    
    \d newtable 
                  Table "public.newtable"
     Column  |  Type  | Collation | Nullable | Default 
    ---------+--------+-----------+----------+---------
     column1 | text[] |           |          | 
    
    insert into newtable values ('{}');
    
    select * from newtable ;
     column1 
    ---------
     {}
    

    In Python:

    import psycopg2
    con = psycopg2.connect(dbname="test", host='localhost', user='postgres')
    cur = con.cursor()
    cur.execute("insert into newtable values ('{}')")
    con.commit()
    cur.execute("select * from newtable")
    cur.fetchone()
    ([],)
    cur.execute("truncate newtable")
    con.commit()
    cur.execute("insert into newtable values (%s)", [[]])
    con.commit()
    cur.execute("select * from newtable")
    cur.fetchone()                                                                                                                                                            
    ([],)
    
    

    From the psycopg2 docs Type adaption Postgres arrays are adapted to Python lists and vice versa.

    UPDATE

    Finding _text type in Postgres system catalog pg_type. In psql:

    \x
    Expanded display is on.
    
    select * from pg_type where typname = '_text';
    -[ RECORD 1 ]--+-----------------
    oid            | 1009
    typname        | _text
    typnamespace   | 11
    typowner       | 10
    typlen         | -1
    typbyval       | f
    typtype        | b
    typcategory    | A
    typispreferred | f
    typisdefined   | t
    typdelim       | ,
    typrelid       | 0
    typelem        | 25
    typarray       | 0
    typinput       | array_in
    typoutput      | array_out
    typreceive     | array_recv
    typsend        | array_send
    typmodin       | -
    typmodout      | -
    typanalyze     | array_typanalyze
    typalign       | i
    typstorage     | x
    typnotnull     | f
    typbasetype    | 0
    typtypmod      | -1
    typndims       | 0
    typcollation   | 100
    typdefaultbin  | NULL
    typdefault     | NULL
    typacl         | NULL
    
    

    Refer to the pg_type link above to get information on what the columns refer to. The typcategory of A as mapped in "Table 52.63. typcategory Codes Code Category A Array types" at the link is one clue. As well as typinput, typoutput, etc values.