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:

  1.  create or replace function is_date(s varchar) returns boolean as $$
  2. begin
  3.   perform s::date;
  4.   return true;
  5. exception when others then
  6.   return false;
  7. end;
  8. $$ language plpgsql;

 

Test Json Value for Type:

  1.  CREATE OR REPLACE FUNCTION get_json_type(s json) returns text as $$
  2. DECLARE val_type text;
  3. BEGIN
  4.      IF (SELECT public.is_date(s::text)) THEN
  5.         val_type := 'date';
  6.      ELSEIF (json_typeof(s) = 'number') THEN
  7.         val_type := 'double precision';
  8.      ELSEIF (json_typeof(s) = 'string') THEN
  9.         val_type := 'text';
  10.      ELSEIF (json_typeof(s) = 'boolean') THEN
  11.         val_type := 'boolean::integer';
  12.      ELSE
  13.         val_type := json_typeof(s);
  14.      END IF;
  15.  
  16.      return val_type;
  17. END;
  18. $$ language plpgsql;