how to find data type of each record in table

Answers

• Jim_Knicely - Select Field - Administrator

  July 2018

  Maybe something like this?

  dbadmin=> CREATE OR REPLACE FUNCTION pg_typeof (x VARCHAR) RETURN VARCHAR
  dbadmin-> AS
  dbadmin-> BEGIN
  dbadmin->   RETURN CASE
  dbadmin->            WHEN x::!INT IS NOT NULL THEN 'INTEGER'
  dbadmin->            WHEN x::!VARCHAR IS NOT NULL THEN 'VARCHAR'
  dbadmin->          END;
  dbadmin-> END;
  
  CREATE FUNCTION
  dbadmin=> CREATE OR REPLACE FUNCTION pg_typeof (x BOOLEAN) RETURN VARCHAR
  dbadmin-> AS
  dbadmin-> BEGIN
  dbadmin->   RETURN 'BOOLEAN';
  dbadmin->
  dbadmin-> END;
  CREATE FUNCTION
  
  dbadmin=> CREATE OR REPLACE FUNCTION pg_typeof (x NUMERIC) RETURN VARCHAR
  dbadmin-> AS
  dbadmin-> BEGIN
  dbadmin->   RETURN 'NUMERIC';
  dbadmin->
  dbadmin-> END;
  CREATE FUNCTION
  
  dbadmin=> CREATE OR REPLACE FUNCTION pg_typeof (x FLOAT) RETURN VARCHAR
  dbadmin-> AS
  dbadmin-> BEGIN
  dbadmin->   RETURN 'FLOAT';
  dbadmin->
  dbadmin-> END;
  CREATE FUNCTION
  
  dbadmin=> \d data_types;
                                          List of Fields by Tables
   Schema |   Table    |  Column   |     Type      | Size | Default | Not Null | Primary Key | Foreign Key
  --------+------------+-----------+---------------+------+---------+----------+-------------+-------------
   public | data_types | c_int     | int           |    8 |         | f        | f           |
   public | data_types | c_varchar | varchar(100)  |  100 |         | f        | f           |
   public | data_types | c_boolean | boolean       |    1 |         | f        | f           |
   public | data_types | c_numeric | numeric(10,2) |    8 |         | f        | f           |
   public | data_types | c_float   | float         |    8 |         | f        | f           |
  (5 rows)
  
  dbadmin=> SELECT * FROM data_types;
   c_int | c_varchar | c_boolean | c_numeric | c_float
  -------+-----------+-----------+-----------+---------
       1 | TEST      | t         |      1.10 |    1.55
  (1 row)
  
  dbadmin=> SELECT pg_typeof(c_int), pg_typeof(c_varchar), pg_typeof(c_boolean), pg_typeof(c_numeric), pg_typeof(c_float) FROM data_types;
   pg_typeof | pg_typeof | pg_typeof | pg_typeof | pg_typeof
  -----------+-----------+-----------+-----------+-----------
   INTEGER   | VARCHAR   | BOOLEAN   | NUMERIC   | FLOAT
  (1 row)
  

  1
• verticaUserATT

  July 2018

  Thank you Jim very much. Unfortunately, it is not what I asked. In vertica, columns table has definition of table & columns. "select data_type, column_name from columns where table_name = 'xyz'" returns column name and its defined data type of 'xyz' table. What I need is, to know the data type of each record in 'xyz' table. something like, select columnA, pg_typeof(columnA) from xyz. In this query, pg_typeof() is predefined function of postgres. Is there similar / same function (as pg_typeof()) in vertica?

  0
• Jim_Knicely - Select Field - Administrator

  July 2018

  Hi,

  So the table's column has to be a VARCHAR if it's going to contain data that can be coerced to different data types.

  Maybe creating a function like this is what you need?

  dbadmin=> CREATE OR REPLACE FUNCTION pg_typeof (x VARCHAR) RETURN VARCHAR
  dbadmin-> AS
  dbadmin-> BEGIN
  dbadmin-> RETURN CASE
  dbadmin->          WHEN x::!NUMERIC IS NOT NULL THEN
  dbadmin->          (CASE
  dbadmin(>             WHEN MOD(x, 1.0) = 0 THEN 'INTEGER'
  dbadmin(>             WHEN MOD(x, 1.0) <> 0 THEN 'NUMERIC'
  dbadmin(>           END)
  dbadmin->          WHEN x::!DATE IS NOT NULL THEN 'DATE'
  dbadmin->          WHEN x::!VARCHAR IS NOT NULL THEN 'VARCHAR'
  dbadmin->        END;
  dbadmin-> END;
  CREATE FUNCTION
  
  dbadmin=> SELECT c_varchar, pg_typeof(c_varchar) FROM data_types;
   c_varchar  | pg_typeof
  ------------+-----------
   TEST       | VARCHAR
   01/01/2018 | DATE
   1          | INTEGER
   1.245      | NUMERIC
  (4 rows)
  

  0