Postgres Get Column Type

A column type refers to the data format or type which is assigned to a specific column in a database table. Column types determine the data type that we can store in a particular column such as integers, text, dates, or Boolean values.

Understanding the column types is crucial for database administrators as it helps to ensure the query efficiency and proper data handling.

This tutorial explores the various methods to retrieve the column types in PostgreSQL including the tools such as PSQL.

Sample Table

The following example queries demonstrate how to create a simple table with three columns of various data types:

CREATE TABLE sample_table (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
age INT
);

Once we define the table that we use for demonstration purposes, we can explore the various methods to fetch the column types in PostgreSQL.

Method 1: Using the INFORMATION_SCHEMA

The most common method of fetching the metadata information about various database objects in PostgreSQL is using the INFORMATION_SCHEMA catalog.

To fetch the column type using the information_schema table, we can run the following query:

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

The previous query retrieves the table name, column name, and data type for all columns in the public schema. Adjust the table_schema condition to retrieve the columns from a specific schema.

Assuming that we only have the sample_table in the public schema, we should see an ouput as shown in the following:

As we can see, we get the table name, column name, and its corresponding data type.

Method 2: Using the PSQL Commands

We can also use the “\d” command from the PSQL utility to fetch the information about a given table column.

Once connected to the target database, use the “\d” followed by the table name as shown in the following:

\d table_name

Example:

\d sample_table;

The given command should return the output as follows:

The output includes the column names, data types, and other table structure information.

Method 3: Using the Pg_attribute Catalog Table

We can also query the pg_attribute catalog table to fetch the data type of a table column. The query syntax is as follows:

SELECT attname AS column_name, format_type(atttypid, atttypmod) AS data_type
FROM pg_attribute
WHERE attrelid ='target_table'::regclass
AND attnum > 0
AND NOT attisdropped;

Replace the target_table parameter with the table name on which your target column resides.

An example is as follows:

SELECT attname AS column_name, format_type(atttypid, atttypmod) AS data_type
FROM pg_attribute
WHERE attrelid = 'sample_table'::regclass
AND attnum > 0
AND NOT attisdropped;

This should return the column names and the corresponding data type as follows:

Conclusion

We explored the three main methods to view a table column’s data type using the PostgreSQL tools. Fetching the column data type is essential in building efficient and compatible application queries.