vertica 获取字段备注、名称、类型的sql

-- 增加字段备注

comment on column public.user_1_super.name is '用户名称'; comment on column public.test1_super.col1 is 'day';

 

-- 获取每个字段的名称类型 注释 CONCAT这个函数如果常量字符串则后面不能跟列否则失效

SELECT col.column_name,col.data_type,com.comment FROM COLUMNS as col left join comments com on com.object_schema = col.table_schema and com.object_type = 'COLUMN' and com.object_name = CONCAT(CONCAT(col.TABLE_NAME,'_super.'),col.column_name) WHERE col.TABLE_NAME = 'user_1' AND table_schema = 'public'

-- 创建一张表并添加备注字段

CREATE TABLE public.base_role
(
    role_id int NOT NULL,
    role_name varchar(100),
    role_code varchar(1024)
);


CREATE PROJECTION public.base_role_super /*+baserole_name(user_1),createtype(L)*/ 
(
 role_id,
 role_name,
 role_code
)
AS
 SELECT base_role.role_id,
        base_role.role_name,
        base_role.role_code
			
 FROM public.base_role
 ORDER BY base_role.role_id,
          base_role.role_name,
          base_role.role_code
SEGMENTED BY hash(base_role.role_id, base_role.role_name, base_role.role_code) ALL NODES OFFSET 0;


COMMENT ON COLUMN public.base_role_super."role_id" IS '角色id';
COMMENT ON COLUMN public.base_role_super."role_code" IS '角色编码';
COMMENT ON COLUMN public.base_role_super."role_name" IS '角色名称';