How to Check Column Types in PostgreSQL?

In PostgreSQL, checking column types is an important aspect of understanding the structure and data stored in a database table. It helps database developers and administrators work effectively by providing a clear picture of the database schema.

In this article, we will explore various methods to check column types in PostgreSQL, including using the \d command, pg_typeof() function, and information_schema.columns view. Each method will be explained with examples to give us a practical understanding.

How 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. This table contains four columns with different data types, which we will use to check their types through multiple methods.

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?

One of the simplest ways to check the column types in PostgreSQL is by using the \d command. This command provides a detailed overview of the table structure, including the data types of all columns.

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:

Explanation:

The \d command gives us a detailed overview of the table structure, including column names, data types, and other attributes like nullability and default values.

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. To find the data type for a given value or column, replace expression with that value or column.

Syntax:

SELECT pg_typeof(expression) FROM table_name;

Example

In the below 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.

Query:

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

Output:

Explanation:

In this query, we used the pg_typeof() function to retrieve the data types of the columns "name" and "age". The LIMIT 1 clause ensures that only one row is retrieved.

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

Retrieve the column names and their corresponding data types from the example_table in the public schema to understand the table's structure in PostgreSQL. Write a query to achieve this.

Query:

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

Output

Explanation:

This query returns the column names and their corresponding data types from the example_table. It queries the information_schema.columns view, providing detailed information about each column's type.

Conclusion

To retrieve the column types in PostgreSQL, we have several options at our disposal, including the \d command, the pg_typeof() function, and the information_schema.columns view. Each method offers different levels of detail and flexibility depending on our needs.

By following the examples and using the appropriate method, we can quickly and accurately check column types in PostgreSQL. Understanding these methods is essential for efficient database management and development.