how to find data type of each record in table

verticaUserATT

in postgres, "select pg_typeof(columnA for example) from tableName" returns each record's "data type" of column "columnA" in table "tableName". how to do this in vertica database?

"select data_type from columns where table_name = " is not the one I am looking.

Jim_Knicely

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)

verticaUserATT

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?

Jim_Knicely

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)