Why the data type of :'variable' in psql is unknown?

January 30, 2024

Store 2024-01-01 in variable mydate:

\set mydate '2024-01-01'

Call it:

\echo  :mydate
2024-01-01
\echo  :'mydate'
'2024-01-01'

Check data type:

select pg_typeof(:mydate);
 pg_typeof 
-----------
 integer
(1 row)

Time: 0.379 ms
select pg_typeof(:'mydate');
 pg_typeof 
-----------
 unknown

Why pg_typeof(:'mydate') is unknown type?

>Solution :

“Data type” is a matter of SQL, but your variable is a psql variable. psql variables have no data type, they are all strings. When you use the variable in an SQL statement, psql replaces the variable with its value before sending the statement to the server.

All that the server sees is a plain string literal, and the internal type of that is unknown:

SELECT pg_typeof('string');
 pg_typeof 
═══════════
 unknown
(1 row)

Data of type unknown get converted to the appropriate type based on context:

-- here, PostgreSQL deduces the type "text"
SELECT 'some' || 'string';

  ?column?  
════════════
 somestring
(1 row)

-- here, PostgreSQL deduces type "integer" and throws an error
SELECT 'some' + 1;
ERROR:  invalid input syntax for type integer: "some"
LINE 1: SELECT 'some' + 1;
               ^