PostgreSQL provides several ways to check the data type of the table’s columns. For example, the \d command, information_schema, pg_typeof() function, and SELECT query. To check/find the data type of a particular column, use the information_schema or pg_typeof() function. The “\d” command and SELECT statement retrieve the data types of all columns.

This blog post will teach you how to check the column type of a table in PostgreSQL. In this regard, the below-listed concepts will be explained with practical examples:

• How to Check Column Type Using pg_typeof() Function?
• How to Check Column Type Using \d Command?
• How to Check Column Type Using SELECT Statement?
• How to Check Column Type Using information_schema?

So, let’s begin!

How to Check Column Type Using pg_typeof() Function?

pg_typeof() is a built-in function in Postgres that can accept a column as an argument and retrieves the data type of the specified column:

SELECT pg_typeof(bike_launch_date)
FROM public.bike_details
LIMIT 1;

In the above snippet, “LIMIT 1” is used to avoid fluff/repetition. Omitting the LIMIT clause will retrieve the data type as many times as the column values.

The output shows that the “bike_launch_date” column has a “TEXT” data type.

How to Check Column Type Using \d Command?

From SQL Shell, run the “\d” command followed by the table name to check the column types of a specific table:

\d bike_details;

The “Type” column shows the data type of each column of the targeted table.

How to Check Column Type Using SELECT Statement?

Another very convenient way to retrieve column types is to execute the SELECT query from the pgAdmin:

SELECT * FROM bike_details;

The above query will fetch all the data of the bike_details table along with column types:

The above snippet shows the data type of each column.

How to Check Column Type Using information_schema?

The information_schema can be used to check the data type of a single or all columns.

How to Check/Find Column Types of a Table?

The output verifhjjies that the information schema retrieves the column names and trespective data types of the bike_details table.

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND 
table_name = 'bike_details';

The output verifies that the information schema retrieves the column names and their respective data types for the bike_details table.

How to Check/Find the Type of a Particular Column?

To check the data type of only a specific column, you must specify the name of that particular column in the WHERE clause:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND 
table_name = 'bike_details' AND
column_name = 'bike_color';

The above query will retrieve the data type of the column named “bike_color”:

The output proves that this time, the information schema retrieves the data type of only a specific column.

Conclusion

In PostgreSQL, the SELECT statement, information_schema, \d command, and pg_typeof() function are used to check the data type of a column. To check/find the data type of a particular column, use the information_schema or pg_typeof() function. The “\d” command and SELECT statement retrieve the data types of all columns. This write-up has demonstrated four different ways to check the data type of the table’s columns.