CODEWITHSUNDEEP
sql
Ritesh Bhakre
Ritesh Bhakre

Reputation: 203

How to get data types of multiple columns?

I have a column in a table that contains names of columns of another table. I wish to automate the process of finding the data types corresponding to all the column names.

If it were 1 column, I would've used:

SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
  WHERE     
     TABLE_NAME = 'table' AND 
     COLUMN_NAME = 'column'

However there are muliple columns. For instance, 

Columns
---------
A
B
C    
D

Now how to automate this process of finding the data type corresponding to each column name?

Ps. The name of the table that possesses these columns is known.

Upvotes: 1

Views: 457

Answers (1)

musically_ut
musically_ut

Reputation: 34288

Caveat: Case of the strings in the WHERE clause is important

SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
  WHERE     
     TABLE_NAME = 'table' AND 
     COLUMN_NAME IN ('a', 'b', 'c' ,'d');

example:

> select column_name, data_type from information_schema.columns where
         table_name='posts' and column_name in ('id', 'creationdate');
 column_name  |          data_type          
--------------+-----------------------------
 id           | integer
 creationdate | timestamp without time zone
(2 rows)

Upvotes: 1

Related Questions