A team mate left the job and you became the responsible for their systems, or you changed your job and trying to learn more about the systems. We all would need to understand column types. In this article i will show you postgresql how to check column type.

Why Do We Need?

If we takeover the ownership of a PostgreSQL database, we would need to understand the technical details of the database. Technical details include configuration parameters, replication, partitioning, tuning, as well as data design. Without understanding table design it is not easy to discover performance pits.

Does It Makes Difference?

Of course! It really does make the difference.

Each data type in PostgreSQL has different performance, storage and tuning options. Even, think simpler, you are writing a simple script to collect statistical data and you need the data type of the column.

How to Check Column Type – Option 1

Purpose of this article is introduce you options postgresql how to check column type. This is the first option to check column type in PostgreSQL.

pg_typeof() is a function that returns data type of the cell. That does not return data type of the column, again, that return the data type of column, lets see it step by step. I am using the table i created for unlogged table performance measurement.

As you see it returns data type of each row, it does not return the column type, we need to add one more thing to make this query clever.

select pg_typeof(mycolumn) from logged_table limit 1;

As you can see, now we got the column type of PostgreSQL.

Checking Column Type – Option 2

If you have access to psql command line client you can use this option. This options is my favorite because it is quick and allows me to interact with using bash.

For each table you can use command \d logged_table for all tables you have. That shows the basic information about columns of the table.

you can use psql -c “\d logged_table” from command line to find data type of a column in Postgreql.

PostgreSQL How to Check Column Ttype – Option 3

This option is relevant with option #2. Let me show you:

Same as the previous one, second column of the output is the data type of the column. It is also possbile for this option to check data type from a bash script as well:

Difference Between \d and \d+

d stands for describe, our command is describe and decribe plus. Let’s call them basic describe and describe plus : ). Basic describe operator returns name of the column, type of the column, collation, nullable or not and default value.

Description plus operator return storage type, compression, statistics target and description as well.

psql’s -c parameter runs following statement and prints the result to the standard output of the bash.

Hi! I’m an IT Specialist

I want to hear from you! I am Working with enterprises for 10+ years to improve their infrastructure and efficiency.

Get in touch with me.

Get in touch