PG_TYPEOF

​

Overview

The pg_typeof() function allows you to retrieve 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:

SELECT pg_typeof("any");

Here, any represents any value you want to determine the data type.

​

Example

​

Case #1: Type of Numeric

This case shows a basic example with a numeric value:

SELECT pg_typeof(100) as "data type";

It will return its data type, which is “integer”.

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

​

Case #2: Type of String

Here, we will use a string value as an input:

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

It will return its data type, which is “text”.

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

​

Case #3: Type of Interval

Another example is using an interval input:

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

It will return its data type in string literal.

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

​

Case #4: Work with Table

Suppose you have a sample table and want to use pg_typeof() to retrieve their data types.

1. Create the table and insert some data into 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');

2. Now, use pg_typeof() 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;

3. The result will show that the data type of the event_time column is a timestamp, and the data type of the description column is text:

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