Column Data Types

It is prudent to inspect column data types at the onset of a data analysis project as well as before and after running data manipulation operations to spot any columns whose data type is not suited for the actions to be carried out. We will cover inspecting column data types here. For detailed coverage of data types in SQL and for how to change a column’s data type, please see Data Types.

All Columns

We wish to obtain the data type of a particular column.

SELECT column_name, data_type
FROM refcon.dataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_1';

Here is how this works:

  • INFORMATION_SCHEMA provides metadata for each table in the dataset.
  • COLUMNS view returns the info for each column in the table.
  • We filter the target table in the WHERE clause.

Particular Column

We wish to obtain the data type of a particular column.

SELECT column_name, data_type
FROM refcon.dataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_1'
  AND column_name = 'col1_1';

Here is how this works:

  • Works get columns metadata from INFORMATION_SCHEMA.COLUMNS as explained above.
  • We filter the target table and column name in the WHERE clause.

Type Distribution

We wish to obtain a distribution of columns over data types i.e. the number of columns in each data type in a table.

SELECT data_type, COUNT(1) AS type_count
FROM refcon.dataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table_1'
GROUP BY data_type;

Here is how this works:

  • Works get columns metadata from INFORMATION_SCHEMA.COLUMNS as explained above.
  • We group by data_type and count the number of columns per type.
SQL
I/O