数据库——表结构相关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 '版本号';

二、待补充