Cloud

pg_typeof

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

SELECT pg_typeof(`any`);

Parameters

  • any: Represents any value used to determine the data type.

Examples

Numeric

This example shows the function usage with a numeric value:

SELECT pg_typeof(100) as "data type";
 data type
-----------
 integer

String

This example uses a string value as an input:

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

Interval

This example uses an interval input:

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

Table

This section shows how to create a sample table and then uses 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');

Use the 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;

The query returns:

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