How to check the data type of a table's columns [Examples: MYSQL, POSTGRESQL, REDSHIFT, BIGQUERY]

Knowledge Drop

Last tested: Jan 21, 2019
 

In SQL Runner, you can use these queries:


MySQL:

SELECT

COLUMN_NAME, DATA_TYPE

FROM

INFORMATION_SCHEMA.COLUMNS

WHERE

TABLE_SCHEMA = 'db_name'

AND

TABLE_NAME = 'table_name'

AND

COLUMN_NAME = 'column_name

 

PostgreSQL:

pg_typeof(col_name)
 

Redshift:

SELECT "column", type

FROM PG_TABLE_DEF

WHERE tablename = 'table_name'

AND "column" = 'column_name'


BQ Legacy and Standard don't support these types of queries. But if we try and do an incorrect cast of a column in BQ, the error message will tell us the data type.

Version history
Last update:
‎05-07-2021 09:11 AM
Updated by: