PostgreSQL常用命令

1.DDL 命令

--创建数据库
CREATE DATABASE [IF NOT EXISTS] db_name;

--删除数据库
DROP DATABASE [IF EXISTS] db_name;

--创建表
CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
  | table_constraints}
   [, ... ]  )
   table_attribute
   [partition_options]
   [storage_policy]
   [block_size]
[AS] query_expression
   COMMENT 'string'

table_attribute:
   DISTRIBUTED BY HASH(column_name,...) | DISTRIBUTED BY BROADCAST

partition_options:
  PARTITION BY 
        {VALUE(column_name) | VALUE(date_format(column_name, ?))}
  LIFECYCLE N

2.ALTER TABLE 命令

ALTER TABLE table_name
  {
    ADD [COLUMN] (column_name column_definition,...)
  | ADD {INDEX|KEY} [index_name] (column_name,...) 
  | ADD CLUSTERED [INDEX|KEY] [index_name] (column_name,...)
  | DROP [COLUMN] column_name
  | DROP {INDEX|KEY} index_name
  | DROP CLUSTERED [INDEX|KEY] index_name
  | MODIFY [COLUMN] column_name column_definition
  | RENAME COLUMN column_name to new_column_name
  | RENAME new_table_name
  | TRUNCATE PARTITION {partition_names | ALL}
  | STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' [hot_partition_count=N]}}

案例

-- 1.添加字段
ALTER TABLE table_name ADD column_name datatype;
-- 2.删除字段
ALTER TABLE table_name DROP COLUMN column_name;
-- 3.修改字段数据类型
ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;
-- 4.给字段添加 NOT NULL 约束
ALTER TABLE table_name ALTER column_name datatype NOT NULL;
-- 5.修改字段英文名
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
-- 6.修改字段中文注释
COMMENT ON COLUMN table_name.column_name IS 'column_comment';
-- 7.修改分布键
ALTER TABLE table_name SET DISTRIBUTED BY (column_name);

3.清空表数据或者表分区数据

--清空表
TRUNCATE TABLE db_name.table_name;  
--清空表中指定分区
TRUNCATE TABLE db_name.table_name PARTITION partition_name[,...];