ERROR: could not find array type for datatype information_schema.sql_identifier

Question

I am trying to run the below sql command:

SELECT ARRAY(
    SELECT column_name 
    FROM information_schema.columns 
    WHERE table_name ='gis_field_configuration_stage'
);

and I get the below error:

ERROR:  could not find array type for datatype information_schema.sql_identifier

Answer 1

Just cast column_name to text to bypass the error:

SELECT ARRAY(
    SELECT column_name::text
    FROM information_schema.columns 
    WHERE table_name ='gis_field_configuration_stage'
);

Its original type is information_schema.sql_identifier and its happens that an array of that type is not provided in the predefined types.

Answer 2

A table name is not unique

A table name is not a unique identifier. A table of the same name could exist in another schema.

It may work just fine to use table names in your queries without schema-qualifying them. As long as your search_path is set properly, the right tables will be picked.

But this won't help you when querying catalog tables! If other tables of the same name exist, you get back all columns for all of these tables and may not even notice it. Add the schema name to make it unique:

SELECT ARRAY(
   SELECT column_name::text
   FROM   information_schema.columns 
   WHERE  table_name ='gis_field_configuration_stage'
   AND    table_schema ='public'  -- or whatever the schema is
)

information_schema is slow

information_schema is only good for cross-plattform portability (which hardly ever works anyway). The views in information_schema are monstrous and slow. If you don't intend to port this to another RDBMS in the future, and if you don't use exotic features that might change between Postgres releases, use pg_catalog.pg_attribute instead. Postgres is not going to change pg_attribute in a way that would invalidate this equivalent query:

SELECT ARRAY(
   SELECT attname
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = 'public.tbl'::regclass
   AND    NOT attisdropped
   AND    attnum > 0
);

Around 100 times faster in my tests.

Casting to regclass is sure

This has another important advantage: If you mistype a table name or it doesn't exist for some other reason in the first query, it just won't find any columns. The result may be misleading and you'll never know it. If you cast the table like I demonstrate ('public.tbl'::regclass), you get an error message if the table should not exist. More on object identifier types in the manual.

You might even use 'tbl'::regclass, because the search_path is used for the evaluation of this expression. If your base query works without schema-qualification, this will, too. It's still safer to add the schema.

Related:

• How to check if a table exists in a given schema

Invalid query

The approach in your comment is invalid to begin with.
ARRAY[...] NOT IN ARRAY[...] doesn't make sense in Postgres.

If you want to make sure that two tables do not share any column names:

SELECT *
FROM   tbl
WHERE  NOT EXISTS (
   SELECT 1
   FROM   pg_catalog.pg_attribute a1
   JOIN   pg_catalog.pg_attribute a2 USING (attname)
   WHERE  a1.attrelid = 'public.tbl'::regclass
   AND    NOT a1.attisdropped
   AND    a1.attnum > 0
   AND    a2.attrelid = 'public.tbl2'::regclass
   AND    NOT a2.attisdropped
   AND    a2.attnum > 0
   );

If you want to make sure that two tables do not share all their column names:

SELECT *
FROM   tbl
WHERE  EXISTS (
   SELECT 1
   FROM   (
      SELECT attname
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = 'public.tbl'::regclass
      AND    NOT attisdropped
      AND    attnum > 0
      ) a1
   FULL   OUTER JOIN (
      SELECT attname
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = 'public.tbl2'::regclass
      AND    NOT attisdropped
      AND    attnum > 0
      ) a2 USING (attname)
   WHERE  a1.attname IS NULL OR
          a2.attname IS NULL
   );