Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
1  
And your question? What is your plan with those column names? –  dezso Jan 15 '13 at 20:56
    
I wanted to limit the confusion by not listing my entire sql command but the below sql is what I ultimately want to run. I want to compare all the fields for each record from 1 table to another to look for data difference between the tables so I am combining all the fields within each record: select * from gis_field_configuration_stage where ARRAY(SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage') not in (select ARRAY(SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration') from gis_field_configuration) –  John Mitchell Jan 15 '13 at 21:04
    
You don't need to make arrays for this. Just do ... WHERE column_name NOT IN (SELECT column_name ... ) –  dezso Jan 15 '13 at 21:34
    
I am aware of that but I don't want to hardcode the field names since I will be looping through a lot of different table with different structures –  John Mitchell Jan 15 '13 at 23:16

2 Answers 2

up vote 0 down vote accepted

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.

share|improve this answer

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.

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
   )
share|improve this answer
    
Actually I don't want to know whether column names between two table share the same name. In fact know that they both contain the same names, type and are in the idential order. I want to be able to list all the fields within each table without having to hard code them in. Example: select * from gis_field_configuration_stage where ARRAY(SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage') not in (select ARRAY(SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration') from gis_field_configuration) –  John Mitchell Jan 16 '13 at 17:10
    
@JohnMitchell: You posted the same code in your comment above, but it doesn't make sense - as I explained in my answer. If you want to list all the fields within each table without having to hard code them, then SELECT * FROM ... is for you. Else, please clarify your question (by editing it!). –  Erwin Brandstetter Jan 16 '13 at 22:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.