PostgreSQL provides a built-in function named “pg_typeof()” that helps us in getting the data type of a value or table’s column. It accepts a value or a column as an argument and retrieves the OID of the respective value or column. More specifically, it retrieves a “regtype” which is an OID alias type(doesn’t have operations of its own).

This article will guide you on how to get a column’s type using the pg_typeof() function.

How to Get the Column’s Type Using pg_typeof() Function in Postgres?

To get the column’s type in Postgres all you need to do is simply pass the column name as an argument to the pg_typeof() function. The pg_typeof() function will handle the rest. Here is a basic syntax for using the pg_typeof() function in Postgres:

SELECT pg_typeof(col_name)
FROM tab_name
LIMIT 1;

The “LIMIT 1” clause is used to limit/restrict the result set to 1 record only.

Example 1: How to Get the Type of a Specific Column?

In this example, we will utilize an already created sample table named “author_info” that has the following records:

SELECT * FROM author_info
ORDER BY author_id;

Let’s apply the pg_typeof() function to the author_name column to get the data type of the specified column:

SELECT pg_typeof(author_name) AS auth_name
FROM author_info
LIMIT 1;

The output reveals that the data type of the specified column is TEXT.

Example 2: How to Get the Type of a Specific Column?

To get the data type of multiple columns, you must use multiple pg_typeof() functions with comma-separated syntax. An example of getting data type of various columns is illustrated in the following snippet:

SELECT pg_typeof(author_name) AS auth_name,
pg_typeof(author_exp) AS auth_name
FROM author_info
LIMIT 1;

The pg_typeof() function retrieves the data type of the given columns.

Conclusion

To get the column’s type in Postgres all you need to do is simply pass the column name as an argument to the pg_typeof() function. The stated function will take care of the rest. It accepts a value or a column as an argument and retrieves the OID of the respective value or column. This post has demonstrated the use of the pg_typeof() function in PostgreSQL using appropriate examples.