pg_typeof()

Overview

The pg_typeof() is a system catalog information function that retrieves the data type of any given value. It returns a string literal corresponding to the expression type.

Syntax

The syntax of the pg_typeof() function is as follows:

SELECT pg_typeof(`any`);

Parameters

The following parameters are required to execute this function:

• any: represents any value you want to determine the data type of

Examples

Numeric

This example shows the function usage with a numeric value:

SELECT pg_typeof(100) as "data type";

 data type 
-----------
 integer

String

In this example, we will use a string value as an input:

SELECT pg_typeof('event'::TEXT) as "data type";

 data type 
-----------
 text

Interval

Here we will focus on using an interval input:

SELECT pg_typeof(INTERVAL '1 day') as "data type";

 data type 
-----------
 interval

Table

For the needs of this section we will create a sample table and then use pg_typeof() to retrieve the data types of information stored in the table

CREATE TABLE timestamp_example (
    id int,
    event_time timestamp,
    description text
);

INSERT INTO timestamp_example (event_time, description)
VALUES 
  ('2023-10-20 12:30:00', 'Event 1'),
  (NULL, 'Event 2');

Now that we created the table, let’s use pg_typeof() function to determine the data types of the event_time and description columns for each row

SELECT 
    pg_typeof(event_time) AS event_time_type,
    pg_typeof(description) AS description_type
FROM timestamp_example;

By executing the query above we will get the following output

       event_time_type       | description_type 
-----------------------------+------------------
 timestamp without time zone | text
 timestamp without time zone | text