数据库——表结构相关SQL
一、GP或PostgreSQL
1.获取表结构
SELECT a.schemaname schema_name, a.tablename table_name, string_agg(b.column_name, ',') AS columns FROM (SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'public' and tablename like 'test%') a
LEFT JOIN (SELECT table_name, column_name FROM information_schema.columns where table_schema = 'public') b
ON a.tablename = b.table_name
GROUP BY a.schemaname, a.tablename;
2.新增索引
CREATE INDEX ind_test_idempotent_version ON test (idempotent, version);
3.创建主键索引
ALTER TABLE test ADD PRIMARY KEY (xh);
4.添加字段
ALTER TABLE test ADD COLUMN version VARCHAR(32);
5.添加字段注释
comment on column test.version is '版本号';