What is the Postgres _text type?-CodePudding

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?

CodePudding user response：

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.