YSQL inherits from Postgres the built-in function pg_typeof function which returns the OID of the data type of the value that is passed to it.
Example:
yugabyte=# CREATE TABLE test (c_int INT, c_numeric NUMERIC, c_timestamp TIMESTAMP, c_date DATE, c_uuid UUID, c_varchar VARCHAR
);
CREATE TABLE
yugabyte=# INSERT INTO test SELECT 1, 1.1, '01/12/2024 12:30:00', '01/12/2024', '40e6215d-b5c6-4896-987c-f30f3678f608', 'A';
INSERT 0 1
yugabyte=# \x
Expanded display is on.
yugabyte=# SELECT pg_typeof(c_int) c_int, pg_typeof(c_numeric) c_numeric, pg_typeof(c_timestamp) c_timestamp, pg_typeof(c_date
) c_date, pg_typeof(c_uuid) c_uuid, pg_typeof(c_varchar) c_varchar FROM test;
-[ RECORD 1 ]----------------------------
c_int | integer
c_numeric | numeric
c_timestamp | timestamp without time zone
c_date | date
c_uuid | uuid
c_varchar | character varying
But what if you want to return the data type of the actual data that is stored in a VARCHAR column? That is, based on the value in the text field, we want to figure out if it is an integer, a numeric, a date, a timestamp, etc.
For that, we can create our own function!
CREATE OR REPLACE FUNCTION data_as_type(VARCHAR)
RETURNS VARCHAR LANGUAGE PLPGSQL AS $$
DECLARE
a VARCHAR;
BEGIN
a = $1::INT;
RETURN 'INT';
EXCEPTION WHEN others THEN
BEGIN
a = $1::NUMERIC;
RETURN 'NUMERIC';
EXCEPTION WHEN others THEN
BEGIN
a = $1::TIME;
RETURN 'TIMESTAMP';
EXCEPTION WHEN others THEN
BEGIN
a = $1::DATE;
RETURN 'DATE';
EXCEPTION WHEN others THEN
BEGIN
a = $1::UUID;
RETURN 'UUID';
EXCEPTION WHEN others THEN
RETURN 'VARCHAR';
END;
END;
END;
END;
END $$;
Example:
yugabyte=# CREATE TABLE test2 (c_varchar VARCHAR);
CREATE TABLE
yugabyte=# INSERT INTO test2 (c_varchar) VALUES ('1'), ('1.1'), ('01/12/2024 12:30:00'), ('01/12/2024'), ('40e6215d-b5c6-4896-987c-f30f3678f608'), ('A');
INSERT 0 6
yugabyte=# SELECT c_varchar, pg_typeof(c_varchar) c_varchar_typeof, data_as_type(c_varchar) c_varchar_real_typeof FROM test2;
c_varchar | c_varchar_typeof | c_varchar_real_typeof
--------------------------------------+-------------------+-----------------------
40e6215d-b5c6-4896-987c-f30f3678f608 | character varying | UUID
01/12/2024 12:30:00 | character varying | TIMESTAMP
A | character varying | VARCHAR
1 | character varying | INT
01/12/2024 | character varying | DATE
1.1 | character varying | NUMERIC
(6 rows)
You can modify the data_as_type function to add additional data types as needed.
Have Fun!