Postgres: Check Data Type

(Last Updated On: )

When you deal with json data type there is the option of using json_typeof(value). However this doesn’t always give you what you expect. For example a date field renders as text. To get around this I have the following two functions. Feel free to use them or suggest alternatives.

Test Value is Date:

 create or replace function is_date(s varchar) returns boolean as $$
begin
  perform s::date;
  return true;
exception when others then
  return false;
end;
$$ language plpgsql;

Test Json Value for Type:

 CREATE OR REPLACE FUNCTION get_json_type(s json) returns text as $$
DECLARE val_type text;
BEGIN
     IF (SELECT public.is_date(s::text)) THEN
        val_type := 'date';
     ELSEIF (json_typeof(s) = 'number') THEN
        val_type := 'double precision';
     ELSEIF (json_typeof(s) = 'string') THEN
        val_type := 'text';
     ELSEIF (json_typeof(s) = 'boolean') THEN
        val_type := 'boolean::integer';
     ELSE
        val_type := json_typeof(s);
     END IF;

     return val_type;
END;
$$ language plpgsql;

Related