Display View Column Data Types

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

Like table columns, view columns also have a data type. You can display the data types of view columns by querying the VIEW_COLUMNS system table.

Example:

dbadmin=> CREATE VIEW MyAccount_View AS SELECT * FROM public.MyAccount;
CREATE VIEW

dbadmin=> SELECT column_name, data_type FROM view_columns 
WHERE table_schema = 'public' AND table_name = 'MyAccount_View'
AND column_name IN ('LateFeesDue', 'DaysInYear');

column_name |   data_type
-------------+---------------
LateFeesDue | numeric(18,4)
DaysInYear  | int
(2 rows)

Helpful Links:

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/SystemTables/CATALOG/VIEW_COLUMNS.htm

https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/SQLReferenceManual/Statements/CREATEVIEW.htm

Have fun!

Sign In or Register to comment.