CODEWITHSUNDEEP
sqlpostgresql
merlin2011
merlin2011

Reputation: 75545

How can I list the columns in INFORMATION_SCHEMA.COLUMNS?

Normally, if I want to list columns and types in a table called foo in the current database, I would use the following SQL statement.

SELECT column_name, data_type 
FROM INFORMATION_SCHEMA.COLUMNS 
where table_name = 'foo';

However, I want to list the columns in the table INFORMATION_SCHEMA.COLUMNS, and the following statement returns zero results.

SELECT column_name, data_type 
FROM INFORMATION_SCHEMA.COLUMNS 
where table_name = 'INFORMATION_SCHEMA.COLUMNS';

What is the correct way to list the columns in the table above, in psql?

Upvotes: 5

Views: 22098

Answers (1)

user330315
user330315

Reputation:

Tablename and schema name are stored in two columns:

SELECT column_name, data_type 
FROM INFORMATION_SCHEMA.COLUMNS 
where table_name = 'columns'
  and table_schema = 'information_schema';

More details in the manual: http://www.postgresql.org/docs/current/static/infoschema-columns.html

Upvotes: 10

Related Questions