In PostgreSQL, the pg_typeof() function allows you to get the data type of any value.

More specifically, it returns the OID of the data type of the value that is passed to it. It returns a regtype, which is an OID alias type. Therefore it’s the same as an OID for comparison purposes but displays as a type name.

Syntax

The function has the following syntax:

pg_typeof(any)

Where any is any value.

Example

Here’s a basic example to demonstrate.

SELECT pg_typeof(100);

Result:

integer

Arbitrary Strings

In Postgres, there’s more than one type of string (e.g. char, varchar, text). Therefore, you can’t just surround an argument in single quotes and expect it to know what its data type is.

SELECT pg_typeof('Elephant'); 

Result:

unknown

Therefore you’ll need to specify its actual data type.

SELECT pg_typeof('Elephant'::varchar); 

Result:

character varying

In Postgres, character varying is the name for varchar (actually, varchar is the alias for character varying).

This last example was a bit superfluous, as I explicitly stated the variable type, which meant that I already knew what the result was going to be.

In the real world, it’s more likely that you’ll be trying to get the data type of a variable.

Return Type of a Variable

In this example, I put the previous string into a variable, then get its data type.

DO $$
DECLARE myString varchar(10) := 'Elephant';
BEGIN
 raise notice 'Value: % % Type: %', myString, E'\n', pg_typeof(myString);
END $$

Result:

NOTICE: Value: Elephant
Type: character varying

Here it is again, except that I change the data type to a different string type (char(8)).

DO $$
DECLARE myString char(8) := 'Elephant';
BEGIN
 raise notice 'Value: % % Type: %', myString, E'\n', pg_typeof(myString);
END $$

Result:

NOTICE: Value: Elephant
Type: character

Boolean Values

Here’s an example of expilictly providing a boolean value.

SELECT 
  pg_typeof(true),
  pg_typeof(false); 

Result:

 pg_typeof | pg_typeof
-----------+-----------
   boolean | boolean

Return Type of a Function

Again, it’s unlikely you’ll be explicitly providing true or false to this function. It’s more likely that it will come from a variable.

But you could also use pg_typeof() to find out the return type of a function.

Here’s an example.

SELECT pg_typeof(isfinite(date '2020-07-18'));

Result:

boolean

In this case I passed the isfinite() function to the pg_typeof() function as its argument.

And here’s another example. In this one I get the data type of the return value of the make_date() function.

SELECT pg_typeof(make_date('1999', '09', '19')); 

Result:

date

Data Type of a Column

Here’s an example of returning the data type of a database column.

SELECT pg_typeof(last_update)
FROM actor
LIMIT 1;

Result:

timestamp without time zone

In this case I checked the data type of the actor.last_update column in the pagila sample database.

Let’s check another column:

SELECT pg_typeof(name)
FROM language
LIMIT 1;

Result:

character

This column displays as bpchar in my interface, which is apparently the internal type name for CHAR(n).

By the way, you can query information_schema.columns for the data type of a column.

Therefore, we could use the following query instead:

SELECT 
  column_name, 
  data_type 
FROM information_schema.columns
WHERE table_name = 'language';

Result:

 column_name |          data_type          
-------------+-----------------------------
 language_id | integer
 name        | character
 last_update | timestamp without time zone

In this case I returned the data type for all columns in the table.