how to find data type of each record in table

verticaUserATTverticaUserATT Registered User

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.

Answers

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    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)
    
  • verticaUserATTverticaUserATT Registered User

    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_KnicelyJim_Knicely Employee, Registered User, VerticaExpert

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file