postgresql 语法——迹忆客-ag捕鱼王app官网

默认情况下 postgresql 安装完成后，自带了一个命令行工具 sql shell(psql)。

linux 系统可以直接切换到 postgres 用户来开启命令行工具：

[root@localhost ~]$  sudo -i -u postgres

windows 系统一般在它的安装目录下：

program files → postgresql 11.3 → sql shell(psql)

mac os 我们直接搜索就可以找到：

进入命令行工具，我们可以使用 \help 来查看各个命令的语法 ：

postgres-# \help 

---

sql 语句

一个 sql 语句通常包含了关键字、标识符（字段）、常量、特殊符号等，下面是一个简单的 sql 语句：

select id, name from jiyik

	select	id, name	from	jiyik
符号类型	关键字	标识符（字段）	关键字	标识符
描述	命令	id 和 name 字段	语句，用于设置条件规则等	表名

---

postgresql 命令

abort

abort 用于退出当前事务。

abort [ work | transaction ]

alter aggregate

修改一个聚集函数的定义 。

alter aggregate _name_ ( _argtype_ [ , ... ] ) rename to _new_name_
alter aggregate _name_ ( _argtype_ [ , ... ] ) owner to _new_owner_
alter aggregate _name_ ( _argtype_ [ , ... ] ) set schema _new_schema_

alter collation

修改一个排序规则定义 。

alter collation _name_ rename to _new_name_
alter collation _name_ owner to _new_owner_
alter collation _name_ set schema _new_schema_

alter conversion

修改一个编码转换的定义。

alter conversion name rename to new_name
alter conversion name owner to new_owner

alter database

修改一个数据库。

alter database name set parameter { to | = } { value | default }
alter database name reset parameter
alter database name rename to new_name
alter database name owner to new_owner

alter default privileges

定义默认的访问权限。

alter default privileges
    [ for { role | user } target_role [, ...] ]
    [ in schema schema_name [, ...] ]
    abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:
grant { { select | insert | update | delete | truncate | references | trigger }
    [, ...] | all [ privileges ] }
    on tables
    to { [ group ] role_name | public } [, ...] [ with grant option ]
...

alter domain

修改一个域的定义。

alter domain name { set default expression | drop default }
alter domain name { set | drop } not null
alter domain name add domain_constraint
alter domain name drop constraint constraint_name [ restrict | cascade ]
alter domain name owner to new_owner

alter function

修改一个函数的定义。

alter function name ( [ type [, ...] ] ) rename to new_name
alter function name ( [ type [, ...] ] ) owner to new_owner

alter group

修改一个用户组。

alter group groupname add user username [, ... ]
alter group groupname drop user username [, ... ]
alter group groupname rename to new_name

alter index

修改一个索引的定义。

alter index name owner to new_owner
alter index name set tablespace indexspace_name
alter index name rename to new_name

alter language

修改一个过程语言的定义。

alter language name rename to new_name

alter operator

改变一个操作符的定义。

alter operator name ( { lefttype | none }, { righttype | none } )
owner to new_owner

alter operator class

修改一个操作符表的定义。

alter operator class name using index_method rename to new_name
alter operator class name using index_method owner to new_owner

alter schema

修改一个模式的定义。

alter schema name rename to new_name
alter schema name owner to new_owner

alter sequence

修改一个序列生成器的定义。

alter sequence name [ increment [ by ] increment ]
[ minvalue minvalue | no minvalue ]
[ maxvalue maxvalue | no maxvalue ]
[ restart [ with ] start ] [ cache cache ] [ [ no ] cycle ]

alter table

修改表的定义。

alter table [ only ] name [ * ]
action [, ... ]
alter table [ only ] name [ * ]
rename [ column ] column to new_column
alter table name
rename to new_name

其中 action 可以是以选项之一：

add [ column ] column_type [ column_constraint [ ... ] ]
drop [ column ] column [ restrict | cascade ]
alter [ column ] column type type [ using expression ]
alter [ column ] column set default expression
alter [ column ] column drop default
alter [ column ] column { set | drop } not null
alter [ column ] column set statistics integer
alter [ column ] column set storage { plain | external | extended | main }
add table_constraint
drop constraint constraint_name [ restrict | cascade ]
cluster on index_name
set without cluster
set without oids
owner to new_owner
set tablespace tablespace_name

alter tablespace

修改一个表空间的定义。

alter tablespace name rename to new_name
alter tablespace name owner to new_owner

alter trigger

修改改变一个触发器的定义 。

alter trigger name on table rename to new_name

alter type

修改一个类型的定义 。

alter type name owner to new_owner

alter user

修改数据库用户帐号 。

alter user name [ [ with ] option [ ... ] ]
alter user name rename to new_name
alter user name set parameter { to | = } { value | default }
alter user name reset parameter

选项可以放置的位置

[ encrypted | unencrypted ] password 'password'
| createdb | nocreatedb
| createuser | nocreateuser
| valid until 'abstime'
analyze

收集与数据库有关的统计。

analyze [ verbose ] [ table [ (column [, ...] ) ] ]

begin

开始一个事务块。

begin [ work | transaction ] [ transaction_mode [, ...] ]

transaction_mode 可以是以下选项之一：

isolation level { 
   serializable | repeatable read | read committed
   | read uncommitted
}
read write | read only

checkpoint

强制一个事务日志检查点 。

checkpoint

close

关闭游标。

close name

cluster

根据一个索引对某个表盘簇化排序。

cluster index_name on table_name
cluster table_name
cluster

comment

定义或者改变一个对象的注释。

comment on {
   table object_name |
   column table_name.column_name |
   aggregate agg_name (agg_type) |
   cast (source_type as target_type) |
   constraint constraint_name on table_name |
   conversion object_name |
   database object_name |
   domain object_name |
   function func_name (arg1_type, arg2_type, ...) |
   index object_name |
   large object large_object_oid |
   operator op (left_operand_type, right_operand_type) |
   operator class object_name using index_method |
   [ procedural ] language object_name |
   rule rule_name on table_name |
   schema object_name |
   sequence object_name |
   trigger trigger_name on table_name |
   type object_name |
   view object_name
} 
is 'text'

commit

提交当前事务。

commit [ work | transaction ]

copy

在表和文件之间拷贝数据。

copy table_name [ ( column [, ...] ) ]
from { 'filename' | stdin }
[ with ]
[ binary ]
[ oids ]
[ delimiter [ as ] 'delimiter' ]
[ null [ as ] 'null string' ]
[ csv [ quote [ as ] 'quote' ]
[ escape [ as ] 'escape' ]
[ force not null column [, ...] ]
copy table_name [ ( column [, ...] ) ]
to { 'filename' | stdout }
[ [ with ]
[ binary ]
[ oids ]
[ delimiter [ as ] 'delimiter' ]
[ null [ as ] 'null string' ]
[ csv [ quote [ as ] 'quote' ]
[ escape [ as ] 'escape' ]
[ force quote column [, ...] ]

create aggregate

定义一个新的聚集函数。

create aggregate name (
   basetype = input_data_type,
   sfunc = sfunc,
   stype = state_data_type
   [, finalfunc = ffunc ]
   [, initcond = initial_condition ]
)

create cast

定义一个用户定义的转换。

create cast (source_type as target_type)
with function func_name (arg_types)
[ as assignment | as implicit ]
create cast (source_type as target_type)
without function
[ as assignment | as implicit ]

create constraint trigger

定义一个新的约束触发器 。

create constraint trigger name
after events on
table_name constraint attributes
for each row execute procedure func_name ( args )

create conversion

定义一个新的的编码转换。

create [default] conversion name
for source_encoding to dest_encoding from func_name

create database

创建新数据库。

create database name
[ [ with ] [ owner [=] db_owner ]
   [ template [=] template ]
   [ encoding [=] encoding ]
   [ tablespace [=] tablespace ] 
]

create domain

定义一个新域。

create domain name [as] data_type
[ default expression ]
[ constraint [ ... ] ]

constraint 可以是以下选项之一：

[ constraint constraint_name ]
{ not null | null | check (expression) }

create function

定义一个新函数。

create [ or replace ] function name ( [ [ arg_name ] arg_type [, ...] ] )
returns ret_type
{ language lang_name
   | immutable | stable | volatile
   | called on null input | returns null on null input | strict
   | [ external ] security invoker | [ external ] security definer
   | as 'definition'
   | as 'obj_file', 'link_symbol'
} ...
[ with ( attribute [, ...] ) ]

create group

定义一个新的用户组。

create group name [ [ with ] option [ ... ] ]
where option can be:
sysid gid
| user username [, ...]

create index

定义一个新索引。

create [ unique ] index name on table [ using method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ tablespace tablespace ]
[ where predicate ]

create language

定义一种新的过程语言。

create [ trusted ] [ procedural ] language name
handler call_handler [ validator val_function ]

create operator

定义一个新的操作符。

create operator name (
   procedure = func_name
   [, leftarg = left_type ] [, rightarg = right_type ]
   [, commutator = com_op ] [, negator = neg_op ]
   [, restrict = res_proc ] [, join = join_proc ]
   [, hashes ] [, merges ]
   [, sort1 = left_sort_op ] [, sort2 = right_sort_op ]
   [, ltcmp = less_than_op ] [, gtcmp = greater_than_op ]
)

create operator class

定义一个新的操作符表。

create operator class name [ default ] for type data_type
using index_method as
{ operator strategy_number operator_name [ ( op_type, op_type ) ] [ recheck ]
   | function support_number func_name ( argument_type [, ...] )
   | storage storage_type
} [, ... ]

create role

定义一个新的数据库角色。

create role _name_ [ [ with ] _option_ [ ... ] ]

选项可以是以下值：

      superuser | nosuperuser
    | createdb | nocreatedb
    | createrole | nocreaterole
...

create rule

定义一个新重写规则。

create [ or replace ] rule name as on event
to table [ where condition ]
do [ also | instead ] { nothing | command | ( command ; command ... ) }

create schema

定义一个新模式。

create schema schema_name
[ authorization username ] [ schema_element [ ... ] ]
create schema authorization username
[ schema_element [ ... ] ]

create server

定义一个新的外部服务器。。

create server _server_name_ [ type '_server_type_' ] [ version '_server_version_' ]
    foreign data wrapper _fdw_name_
    [ options ( _option_ '_value_' [, ... ] ) ]

create sequence

定义一个新序列发生器。

create [ temporary | temp ] sequence name
[ increment [ by ] increment ]
[ minvalue minvalue | no minvalue ]
[ maxvalue maxvalue | no maxvalue ]
[ start [ with ] start ] [ cache cache ] [ [ no ] cycle ]

create table

定义一个新表。

create [ [ global | local ] { 
   temporary | temp } ] table table_name ( { 
      column_name data_type [ default default_expr ] [ column_constraint [ ... ] ]
      | table_constraint
      | like parent_table [ { including | excluding } defaults ] 
   } [, ... ]
)
[ inherits ( parent_table [, ... ] ) ]
[ with oids | without oids ]
[ on commit { preserve rows | delete rows | drop } ]
[ tablespace tablespace ]

column_constraint 可以是以下选项之一：

[ constraint constraint_name ] { 
   not null |
   null |
   unique [ using index tablespace tablespace ] |
   primary key [ using index tablespace tablespace ] |
   check (expression) |
   references ref_table [ ( ref_column ) ]
   [ match full | match partial | match simple ]
   [ on delete action ] [ on update action ] 
}
[ deferrable | not deferrable ] [ initially deferred | initially immediate ]

table_constraint 可以是以下选项之一：

[ constraint constraint_name ]
{ unique ( column_name [, ... ] ) [ using index tablespace tablespace ] |
primary key ( column_name [, ... ] ) [ using index tablespace tablespace ] |
check ( expression ) |
foreign key ( column_name [, ... ] )
references ref_table [ ( ref_column [, ... ] ) ]
[ match full | match partial | match simple ]
[ on delete action ] [ on update action ] }
[ deferrable | not deferrable ] [ initially deferred | initially immediate ]

create table as

从一条查询的结果中定义一个新表。

create [ [ global | local ] { temporary | temp } ] table table_name
[ (column_name [, ...] ) ] [ [ with | without ] oids ]
as query

create tablespace

定义一个新的表空间。

create tablespace tablespace_name [ owner username ] location 'directory'

create trigger

定义一个新的触发器。

create trigger name { before | after } { event [ or ... ] }
on table [ for [ each ] { row | statement } ]
execute procedure func_name ( arguments )

create type

定义一个新的数据类型。

create type name as
( attribute_name data_type [, ... ] )
create type name (
input = input_function,
output = output_function
[, receive = receive_function ]
[, send = send_function ]
[, analyze = analyze_function ]
[, internallength = { internal_length | variable } ]
[, passedbyvalue ]
[, alignment = alignment ]
[, storage = storage ]
[, default = default ]
[, element = element ]
[, delimiter = delimiter ]
)

create user

创建一个新的数据库用户帐户。

create user name [ [ with ] option [ ... ] ]

option 可以是以下选项之一：

sysid uid
| [ encrypted | unencrypted ] password 'password'
| createdb | nocreatedb
| createuser | nocreateuser
| in group group_name [, ...]
| valid until 'abs_time'

create view

定义一个视图。

create [ or replace ] view name [ ( column_name [, ...] ) ] as query

deallocate

删除一个准备好的查询。

deallocate [ prepare ] plan_name

declare

定义一个游标。

declare name [ binary ] [ insensitive ] [ [ no ] scroll ]
cursor [ { with | without } hold ] for query
[ for { read only | update [ of column [, ...] ] } ]

delete

删除一个表中的行。

delete from [ only ] table [ where condition ]

drop aggregate

删除一个用户定义的聚集函数。

drop aggregate name ( type ) [ cascade | restrict ]

drop cast

删除一个用户定义的类型转换。

drop cast (source_type as target_type) [ cascade | restrict ]

drop conversion

删除一个用户定义的编码转换。

drop conversion name [ cascade | restrict ]

drop database

删除一个数据库。

drop database name

drop domain

删除一个用户定义的域。

drop domain name [, ...] [ cascade | restrict ]

drop function

删除一个函数。

drop function name ( [ type [, ...] ] ) [ cascade | restrict ]

drop group

删除一个用户组。

drop group name

drop index

删除一个索引。

drop index name [, ...] [ cascade | restrict ]

drop language

删除一个过程语言。

drop [ procedural ] language name [ cascade | restrict ]

drop operator

删除一个操作符。

drop operator name ( { left_type | none }, { right_type | none } )
[ cascade | restrict ]

drop operator class

删除一个操作符表。

drop operator class name using index_method [ cascade | restrict ]

drop role

删除一个数据库角色。

drop role [ if exists ] _name_ [, ...]

drop rule

删除一个重写规则。

drop rule name on relation [ cascade | restrict ]

drop schema

删除一个模式。

drop schema name [, ...] [ cascade | restrict ]

drop sequence

删除一个序列。

drop sequence name [, ...] [ cascade | restrict ]

drop table

删除一个表。

drop table name [, ...] [ cascade | restrict ]

drop tablespace

删除一个表空间。

drop tablespace tablespace_name

drop trigger

删除一个触发器定义。

drop trigger name on table [ cascade | restrict ]

drop type

删除一个用户定义数据类型。

drop type name [, ...] [ cascade | restrict ]

drop user

删除一个数据库用户帐号。

drop user name

drop view

删除一个视图。

drop view name [, ...] [ cascade | restrict ]

end

提交当前的事务。

end [ work | transaction ]

execute

执行一个准备好的查询。

execute plan_name [ (parameter [, ...] ) ]

explain

显示一个语句的执行规划。

explain [ analyze ] [ verbose ] statement

fetch

用游标从查询中抓取行。

fetch [ direction { from | in } ] cursor_name

direction 可以是以下选项之一：

next
prior
first
last
absolute count
relative count
count
all
forward
forward count
forward all
backward
backward count
backward all

grant

定义访问权限。

grant { { select | insert | update | delete | rule | references | trigger }
[,...] | all [ privileges ] }
on [ table ] table_name [, ...]
to { username | group group_name | public } [, ...] [ with grant option ]
grant { { create | temporary | temp } [,...] | all [ privileges ] }
on database db_name [, ...]
to { username | group group_name | public } [, ...] [ with grant option ]
grant { create | all [ privileges ] }
on tablespace tablespace_name [, ...]
to { username | group group_name | public } [, ...] [ with grant option ]
grant { execute | all [ privileges ] }
on function func_name ([type, ...]) [, ...]
to { username | group group_name | public } [, ...] [ with grant option ]
grant { usage | all [ privileges ] }
on language lang_name [, ...]
to { username | group group_name | public } [, ...] [ with grant option ]
grant { { create | usage } [,...] | all [ privileges ] }
on schema schema_name [, ...]
to { username | group group_name | public } [, ...] [ with grant option ]

insert

在表中创建新行，即插入数据。

insert into table [ ( column [, ...] ) ]
{ default values | values ( { expression | default } [, ...] ) | query }

listen

监听一个通知。

listen name

load

加载或重载一个共享库文件。

load 'filename'

lock

锁定一个表。

lock [ table ] name [, ...] [ in lock_mode mode ] [ nowait ]

lock_mode 可以是以下选项之一：

access share | row share | row exclusive | share update exclusive
| share | share row exclusive | exclusive | access exclusive

move

定位一个游标。

move [ direction { from | in } ] cursor_name

notify

生成一个通知。

notify name

prepare

创建一个准备好的查询。

prepare plan_name [ (data_type [, ...] ) ] as statement

reindex

重建索引。

reindex { database | table | index } name [ force ]

release savepoint

删除一个前面定义的保存点。

release [ savepoint ] savepoint_name

reset

把一个运行时参数值恢复为默认值。

reset name
reset all

revoke

删除访问权限。

revoke [ grant option for ]
{ { select | insert | update | delete | rule | references | trigger }
[,...] | all [ privileges ] }
on [ table ] table_name [, ...]
from { username | group group_name | public } [, ...]
[ cascade | restrict ]
revoke [ grant option for ]
{ { create | temporary | temp } [,...] | all [ privileges ] }
on database db_name [, ...]
from { username | group group_name | public } [, ...]
[ cascade | restrict ]
revoke [ grant option for ]
{ create | all [ privileges ] }
on tablespace tablespace_name [, ...]
from { username | group group_name | public } [, ...]
[ cascade | restrict ]
revoke [ grant option for ]
{ execute | all [ privileges ] }
on function func_name ([type, ...]) [, ...]
from { username | group group_name | public } [, ...]
[ cascade | restrict ]
revoke [ grant option for ]
{ usage | all [ privileges ] }
on language lang_name [, ...]
from { username | group group_name | public } [, ...]
[ cascade | restrict ]
revoke [ grant option for ]
{ { create | usage } [,...] | all [ privileges ] }
on schema schema_name [, ...]
from { username | group group_name | public } [, ...]
[ cascade | restrict ]

rollback

退出当前事务。

rollback [ work | transaction ]

rollback to savepoint

回滚到一个保存点。

rollback [ work | transaction ] to [ savepoint ] savepoint_name

savepoint

在当前事务里定义一个新的保存点。

savepoint savepoint_name

select

从表或视图中取出若干行。

select [ all | distinct [ on ( expression [, ...] ) ] ]
* | expression [ as output_name ] [, ...]
[ from from_item [, ...] ]
[ where condition ]
[ group by expression [, ...] ]
[ having condition [, ...] ]
[ { union | intersect | except } [ all ] select ]
[ order by expression [ asc | desc | using operator ] [, ...] ]
[ limit { count | all } ]
[ offset start ]
[ for update [ of table_name [, ...] ] ]from_item 可以是以下选项：
[ only ] table_name [ * ] [ [ as ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ as ] alias [ ( column_alias [, ...] ) ]
function_name ( [ argument [, ...] ] )
[ as ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) as ( column_definition [, ...] )
from_item [ natural ] join_type from_item
[ on join_condition | using ( join_column [, ...] ) ]

select into

从一个查询的结果中定义一个新表。

select [ all | distinct [ on ( expression [, ...] ) ] ]
* | expression [ as output_name ] [, ...]
into [ temporary | temp ] [ table ] new_table
[ from from_item [, ...] ]
[ where condition ]
[ group by expression [, ...] ]
[ having condition [, ...] ]
[ { union | intersect | except } [ all ] select ]
[ order by expression [ asc | desc | using operator ] [, ...] ]
[ limit { count | all } ]
[ offset start ]
[ for update [ of table_name [, ...] ] ]

set

修改运行时参数。

set [ session | local ] name { to | = } { value | 'value' | default }
set [ session | local ] time zone { time_zone | local | default }

set constraints

设置当前事务的约束检查模式。

set constraints { all | name [, ...] } { deferred | immediate }

set session authorization

为当前会话设置会话用户标识符和当前用户标识符。

set [ session | local ] session authorization username
set [ session | local ] session authorization default
reset session authorization

set transaction

开始一个事务块。

set transaction transaction_mode [, ...]
set session characteristics as transaction transaction_mode [, ...]

transaction_mode 是以下值之一

isolation level { serializable | repeatable read | read committed
| read uncommitted }
read write | read only

show

显示运行时参数的值。

show name
show all

start transaction

开始一个事务块。

start transaction [ transaction_mode [, ...] ]

transaction_mode 可以是下面的选项之一：

isolation level { serializable | repeatable read | read committed
| read uncommitted }
read write | read only

truncate

清空一个或一组表。

truncate [ table ] name

unlisten

停止监听通知信息。

unlisten { name | * }

update

更新一个表中的行。

update [ only ] table set column = { expression | default } [, ...]
[ from from_list ]
[ where condition ]

vacuum

垃圾收集以及可选地分析一个数据库。

vacuum [ full ] [ freeze ] [ verbose ] [ table ]
vacuum [ full ] [ freeze ] [ verbose ] analyze [ table [ (column [, ...] ) ] ]

values

计算一个或一组行。

values ( _expression_ [, ...] ) [, ...]
    [ order by _sort_expression_ [ asc | desc | using _operator_ ] [, ...] ]
    [ limit { _count_ | all } ]
    [ offset _start_ [ row | rows ] ]
    [ fetch { first | next } [ _count_ ] { row | rows } only ]