PostgreSQL

命令行工具

用户管理

命令行模式：

createuser -h localhost USERNAME -P  # 最后需要输入postgres的密码*

*：如果使用密码登录，则需在数据库访问规则中添加密码访问规则（md5）。

显示用户信息：

\du -- lists all user

授权

使用管理员账户登录为用户授权。

授权数据库访问权限：

GRANT CREATE ON DATABASE database_name TO [GROUP] role_name;

权限声明包括：CREATE,CONNECT,TEMPORARY,TEMP或全部权限ALL [PRIVILEGES]。

授权表的访问权限：

\c DB_NAME  # use ngsoc
GRANT SELECT ON TABLE table_name TO [GROUP] role_name;
GRANT ... ON ALL TABLES IN SCHEMA schema_name TO [GROUP] role_name;

权限声明包括一个或多个：SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER或使用ALL表示所有权限。

PostgreSQL: Documentation: 9.0: GRANT

执行SQL脚本

psql obejctrocket [options] -d db_name -f test.sql

在交互式环境下执行SQL脚本：

\c db_name
\i /path/TO/file_name.sql

数据库管理命令

Database管理

\l               -- list databases
\c db_name       -- use database
select * from pg_catalog.pg_collation;               -- show collation

Basically, information schema is a set of views of pg_catalog.

PostgreSQL supports the standard SQL types int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, and interval, as well as other types of general utility and a rich set of geometric types. PostgreSQL can be customized with an arbitrary number of user-defined data types.

Constants that are not simple numeric values usually must be surrounded by single quotes (').

创建database

使用SQL语句创建数据库。或使用数据库命令行工具，创建：

createdb mydb  # <-> CREATE DATABASE mydb

删除database

dropdb mydb    # DROP DATABASE mydb

Schema管理

获取命名空间信息

命名空间（schema）是一个数据库所包含数据表的逻辑分组。

SELECT catalog_name,schema_name,schema_owner
  FROM information_schema.schemata;
SELECT nspname,nsponwer,nspacl FROM pg_catalog.pg_namespace;

schema_name和nspname是一一对应的。

创建Schema

CREATE SCHEMA [IF NOT EXISTS] schema_name [AUTHORIZATION user_name] 
    [schema_element [...]];
DROP SCHEMA schema_name;

schema_name：defaults to user_name.

user_name：the new schema's owner, defaults to the current user.

schema_element：Schema中需要创建的内容（包括TABLE, VIEW, INDEX, GRANT等），也可在创建Schema之后创建内容。

切换Schema

set search_path to schema_name;

Table管理

获取数据表

pgsql命令：

\d[+]            -- show tables [and schema]
-- Schema |      Name      |       Type        |  Owner
-- -------+----------------+-------------------+----------
-- public | pokemon        | partitioned table | postgres
-- public | pokemon_gen_01 | table             | postgres
\dn -- show user created schema

SQL语句：

SELECT schemaname,tablename,tableowner,* 
  FROM pg_catalog.pg_tables
  WHERE schemaname != 'pg_catalog' 
    AND schemaname != 'information_schema';
SELECT table_schema,table_name
  FROM information_schema.tables
  WHERE table_schema = 'public';

获取数据表详细信息

pgsql命令：

\d[+] table_name -- -> DESC table: '+'显示更多列和分区定义

SQL语句：

SELECT column_name,data_type 
  FROM information_schema.columns
  WHERE table_schema = 'table_name';

创建数据表

If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is created in the specified schema. Otherwise it is created in the current schema. Temporary tables exist in a special schema, so a schema name cannot be given when creating a temporary table. The name of the table must be distinct from the name of any other table, sequence, index, view, or foreign table in the same schema.

ALTER TABLE [IF EXISTS] name RENAME TO new_name
ALTER TABLE [IF EXISTS] name SET SCHEMA new_schema
ALTER TABLE [IF EXISTS] name RENAME [COLUMN] column_name TO new_column_name

数据表分区

The partitioned table itself is a “virtual” table having no storage of its own. Instead, the storage belongs to partitions, which are otherwise-ordinary tables associated with the partitioned table. Each partition stores a subset of the data as defined by its partition bounds. All rows inserted into a partitioned table will be routed to the appropriate one of the partitions based on the values of the partition key column(s). Updating the partition key of a row will cause it to be moved into a different partition if it no longer satisfies the partition bounds of its original partition.

分区引用的列使用PARTITION BY声明。

CREATE TABLE table_name (column1 data_type, column2 data_type, …)     
  PARTITION BY RANGE(col_name1,...);
CREATE TABLE table_part_xx PARTITION OF table_name
  FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

每个分区被存储为一个目录。

INSERT INTO TABLE table_name 
  PARTITION (partition1='partition1_val', partition2='partition2_val', ...)
  VALUES (col1_val, col2_val, …, partition1_val, partition2_val, …);

必须在创建时定义分区，创建分区表后，仅能添加或移除分区。

ALTER TABLE [ IF EXISTS ] name
    ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
ALTER TABLE [IF EXISTS] name DETACH PARTITION partition_name

不支持分区和分解和合并，可以将相关分区先移出父表，再修改分区定义，并将移出的子表格数据写回父表。

数据类型

PostgreSQL 数据类型 | 菜鸟教程 (runoob.com)

继承

CREATE TABLE cities (
 name text,
 population real,
 elevation int -- (in ft)
);
CREATE TABLE capitals (
 state char(2) UNIQUE NOT NULL
) INHERITS (cities);   -- 继承表的定义并进行扩展

ONLY

Learning Programming Book

PostgreSQL

安装

Ubuntu

CentOS

docker

配置

远程访问

网络访问规则

数据存储位置

启动服务