How to Check Column Types in PostgreSQL?

In Procedural Language/Structured Query Language is an extension of SQL Language checking column types is an important aspect of understanding the structure and data stored in a database table. In this article, We will understand various method that helps in checking the column types in PostgreSQL with the help of examples and so on.

Ways to Check Column Types in PostgreSQL

To understand How to Check Column Types in PostgreSQL we need a table on which we will perform various operations. So we create a table example_table.

Creating the table example_table

CREATE TABLE example_table 
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    is_student BOOLEAN
);

How to Check Column Types using the \d Command?

The “\d” command in PostgreSQL can be used with the table name to provide details about the table, such as the column’s data types.

Syntax :

\d table_name

Replace table_name with the name of the table. The result will include the data type of every column.

Example:

\d example_table;

Output:

Output of \d command

Explanation: The above query will retrieve the data types of all columns in the table example_table.

How to Check Column Types using pg_typeof() Function?

PostgreSQL comes with a built-in function called pg_typeof() that may take a column as an input and return the data type of the given column.

Syntax:

SELECT pg_typeof(expression) FROM table_name;

To find the data type for a given value or column, replace expression with that value or column.

Example:

SELECT pg_typeof(name),pg_typeof(age) 
FROM example_table
LIMIT 1;

In the above query, “LIMIT 1” is used to retrieve the data type only once . The data type will be retrieved as many times as the column values if the LIMIT clause is omitted.

Output:

Output of pg_typeof() function

Explanation: The above query will retrieve the data type of the columns “name” and “age” of the table example_table.

How to Check Column Types using information_schema.columns View?

PostgreSQL’s information_schema.columns view is used to obtain details about the columns in database tables. we can verify the data type of any or all of the columns using the information_schema.columns view.

Syntax:

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'your_schema_name'
  AND table_name = 'your_table_name';

Replace ‘your_schema_name’ and ‘your_table_name’ with the actual schema and table names we want to query.

Example:

Query:

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

Output:

Output for information_schema.columns view

Explanation: The above query will retrieve the data types of all columns in the table example_table.

Conclusion

Overaall, After reading whole article you have good understanding of How to Check Column Types in PostgreSQL using various method which are Using the \d Command, Using the information_schema.columns View and Using the pg_typeof() Function. We also saw the examples of these method to get better understanding. With the help of these method you cane easily perform query and get the desired output.