Skip to main content

​
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
⌘I