CODEWITHSUNDEEP
sqlpostgresqlpsycopg2
Nathan
Nathan

Reputation: 3648

SQL INFORMATION_SCHEMA.COLUMNS returning incomplete values

I would like to request all the column_names, table_names and corresponding data_types. I'm doing this using:

SELECT column_name, table_name, data_type FROM INFORMATION_SCHEMA.COLUMNS

However, the data_type occasionally returns ARRAY. This is technically speaking correct, as these are, for instance, double_precision[].

Is it possible to return double precision[] instead of ARRAY?

Upvotes: 1

Views: 428

Answers (1)

S-Man
S-Man

Reputation: 23726

The array type can be requested by udt_name:

data_type: character_data Data type of the array elements, if it is a built-in type, else USER-DEFINED (in that case, the type is identified in udt_name and associated columns). (Postgres documentation)

demo: db<>fiddle

CREATE TABLE test_table (
    id int,
    somearrays text[],
    somearrays_i int[]
);

SELECT 
    data_type, 
    udt_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'test_table'

Result:

data_type   udt_name
integer     int4
ARRAY       _text
ARRAY       _int4

Upvotes: 1

Related Questions