5.5. PostgreSQL syntax

发布时间 ： 2025-10-25 13:32:28 UTC      

Page Views: 9 views

By default, when PostgreSQL is installed, it comes with a command line tool SQL Shell(psql) .

Linux The system can switch directly to the postgres user to open the command line tool:

# sudo -i -u postgres

Windows The system is generally under its installation directory:

Program Files → PostgreSQL 11.3 → SQL Shell (psql)

Mac OS We can just search and find:

Image0

Entering the command line tool, we can use\ help to view the syntax of each command:

postgres-# \help     
 For example, let’s look at the syntax of the select statement: 
 
 
 
postgres=# \help SELECT Command: SELECT Description: retrieve rows from a table or view Syntax: [ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] [ * | expression [ [ AS ] output_name ] [, ...] ] [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ] [ WINDOW window_name AS ( window_definition ) [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] [ LIMIT { count | ALL } ] [ OFFSET start [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ] from_item 可以是以下选项之一： [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] 
 
 
 
 
  5.5.1.  SQL statement  
 
 A SQL statement usually contains keywords, identifiers (fields), constants, special symbols, and so on. Here is a simple SQL statement: 
 
 
 
SELECT id, name FROM runoob 
 
 
 
  
    
 SELECT 
 		  
 Id, name 
 		  
 FROM 
 		  
 Runoob 
 		 
   
  
 Symbol type 
 		  
 Keyword 
 		  
 Identifier (field) 
 		  
 Keyword 
 		  
 Identifier 
 		 
 
  
 Description 
 		  
 Command 
 		  
 Id and name fields 
 		  
 Statement, used to set conditional rules, etc. 
 		  
 Table name 
 		 
  
 
 
 
  5.5.2.  PostgreSQL command  
 
 
 ABORT  
 
 ABORT is used to exit the current transaction. 
 
 
 
ABORT [ WORK | TRANSACTION ] 
 
 
 
 
 
 ALTER AGGREGATE  
 
 Modify the definition of an aggregate function. 
 
 
 
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  
 
 Modify a collation definition. 
 
 
 
ALTER COLLATION _name_ RENAME TO _new_name_ ALTER COLLATION _name_ OWNER TO _new_owner_ ALTER COLLATION _name_ SET SCHEMA _new_schema_ 
 
 
 
 
 
 ALTER CONVERSION  
 
 Modify the definition of a transcoding. 
 
 
 
ALTER CONVERSION name RENAME TO new_name ALTER CONVERSION name OWNER TO new_owner 
 
 
 
 
 
 ALTER DATABASE  
 
 Modify a 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  
 
 Define default access permissions. 
 
 
 
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  
 
 Modify the definition of a 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  
 
 Modify the definition of a function. 
 
 
 
ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner 
 
 
 
 
 
 ALTER GROUP  
 
 Modify a user group. 
 
 
 
ALTER GROUP groupname ADD USER username [, ... ] ALTER GROUP groupname DROP USER username [, ... ] ALTER GROUP groupname RENAME TO new_name 
 
 
 
 
 
 ALTER INDEX  
 
 Modify the definition of an index. 
 
 
 
ALTER INDEX name OWNER TO new_owner ALTER INDEX name SET TABLESPACE indexspace_name ALTER INDEX name RENAME TO new_name 
 
 
 
 
 
 ALTER LANGUAGE  
 
 Modify the definition of a process language. 
 
 
 
ALTER LANGUAGE name RENAME TO new_name 
 
 
 
 
 
 ALTER OPERATOR  
 
 Change the definition of an operator. 
 
 
 
ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } ) OWNER TO new_owner 
 
 
 
 
 
 ALTER OPERATOR CLASS  
 
 Modify the definition of an operator table. 
 
 
 
ALTER OPERATOR CLASS name USING index_method RENAME TO new_name ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner 
 
 
 
 
 
 ALTER SCHEMA  
 
 Modify the definition of a pattern. 
 
 
 
ALTER SCHEMA name RENAME TO new_name ALTER SCHEMA name OWNER TO new_owner 
 
 
 
 
 
 ALTER SEQUENCE  
 
 Modify the definition of a sequence generator. 
 
 
 
ALTER SEQUENCE name [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] 
 
 
 
 
 
 ALTER TABLE  
 
 Modify the definition of the table. 
 
 
 
ALTER TABLE [ ONLY ] name [ * ] action [, ... ] ALTER TABLE [ ONLY ] name [ * ] RENAME [ COLUMN ] column TO new_column ALTER TABLE name RENAME TO new_name 
 
 
 
 Where action can be one of the options: 
 
 
 
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  
 
 Modify the definition of a table space. 
 
 
 
ALTER TABLESPACE name RENAME TO new_name ALTER TABLESPACE name OWNER TO new_owner 
 
 
 
 
 
 ALTER TRIGGER  
 
 Change the definition of a trigger. 
 
 
 
ALTER TRIGGER name ON table RENAME TO new_name 
 
 
 
 
 
 ALTER TYPE  
 
 Modify the definition of a type. 
 
 
 
ALTER TYPE name OWNER TO new_owner 
 
 
 
 
 
 ALTER USER  
 
 Modify the database user account. 
 
 
 
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 
 
 
 
 Where  option  Can be − 
 
 
 
[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | VALID UNTIL 'abstime' 
 
 
 
 
 
 ANALYZE  
 
 Collect statistics related to the database. 
 
 
 
ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ] 
 
 
 
 
 
 BEGIN  
 
 Start a transaction block. 
 
 
 
BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ] 
 
 
 
  transaction_mode  Can be one of the following options: 
 
 
 
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY 
 
 
 
 
 
 CHECKPOINT  
 
 Force a transaction log checkpoint. 
 
 
 
CHECKPOINT 
 
 
 
 
 
 CLOSE  
 
 Close the cursor. 
 
 
 
CLOSE name 
 
 
 
 
 
 CLUSTER  
 
 Clusters and sorts a dial according to an index. 
 
 
 
CLUSTER index_name ON table_name CLUSTER table_name CLUSTER 
 
 
 
 
 
 COMMENT  
 
 Define or change the comments of an object. 
 
 
 
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 the current transaction. 
 
 
 
COMMIT [ WORK | TRANSACTION ] 
 
 
 
 
 
 COPY  
 
 Copy data between tables and files. 
 
 
 
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  
 
 Define a new aggregation function. 
 
 
 
CREATE AGGREGATE name ( BASETYPE = input_data_type, SFUNC = sfunc, STYPE = state_data_type [, FINALFUNC = ffunc ] [, INITCOND = initial_condition ] ) 
 
 
 
 
 
 CREATE CAST  
 
 Define a user-defined transformation. 
 
 
 
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  
 
 Define a new constraint trigger. 
 
 
 
CREATE CONSTRAINT TRIGGER name AFTER events ON table_name constraint attributes FOR EACH ROW EXECUTE PROCEDURE func_name ( args ) 
 
 
 
 
 
 CREATE CONVERSION  
 
 Define a new transcoding. 
 
 
 
CREATE [DEFAULT] CONVERSION name FOR source_encoding TO dest_encoding FROM func_name 
 
 
 
 
 
 CREATE DATABASE  
 
 Create a new database. 
 
 
 
CREATE DATABASE name [ [ WITH ] [ OWNER [=] db_owner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ TABLESPACE [=] tablespace ] ] 
 
 
 
 
 
 CREATE DOMAIN  
 
 Define a new domain. 
 
 
 
CREATE DOMAIN name [AS] data_type [ DEFAULT expression ] [ constraint [ ... ] ] 
 
 
 
  constraint  Can be one of the following options: 
 
 
 
[ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK (expression) } 
 
 
 
 
 
 CREATE FUNCTION  
 
 Define a new 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  
 
 Define a new user group. 
 
 
 
CREATE GROUP name [ [ WITH ] option [ ... ] ] Where option can be: SYSID gid | USER username [, ...] 
 
 
 
 
 
 CREATE INDEX  
 
 Define a new index. 
 
 
 
CREATE [ UNIQUE ] INDEX name ON table [ USING method ] ( { column | ( expression ) } [ opclass ] [, ...] ) [ TABLESPACE tablespace ] [ WHERE predicate ] 
 
 
 
 
 
 CREATE LANGUAGE  
 
 Define a new process language. 
 
 
 
CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name HANDLER call_handler [ VALIDATOR val_function ] 
 
 
 
 
 
 CREATE OPERATOR  
 
 Define a new 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  
 
 Define a new operator table. 
 
 
 
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  
 
 Define a new database role. 
 
 
 
CREATE ROLE _name_ [ [ WITH ] _option_ [ ... ] ] where `_option_` can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE ... 
 
 
 
 
 
 CREATE RULE  
 
 Define a new rewriting rule. 
 
 
 
CREATE [ OR REPLACE ] RULE name AS ON event TO table [ WHERE condition ] DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) } 
 
 
 
 
 
 CREATE SCHEMA  
 
 Define a new pattern. 
 
 
 
CREATE SCHEMA schema_name [ AUTHORIZATION username ] [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ] 
 
 
 
 
 
 CREATE SERVER  
 
 Define a new external server. 
 
 
 
CREATE SERVER _server_name_ [ TYPE '_server_type_' ] [ VERSION '_server_version_' ] FOREIGN DATA WRAPPER _fdw_name_ [ OPTIONS ( _option_ '_value_' [, ... ] ) ] 
 
 
 
 
 
 CREATE SEQUENCE  
 
 Define a new sequence generator. 
 
 
 
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  
 
 Define a new 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  Can be one of the following options: 
 
 
 
[ 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  Can be one of the following options: 
 
 
 
[ 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  
 
 Define a new table from the results of a query. 
 
 
 
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ] AS query 
 
 
 
 
 
 CREATE TABLESPACE  
 
 Define a new tablespace. 
 
 
 
CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory' 
 
 
 
 
 
 CREATE TRIGGER  
 
 Define a new trigger. 
 
 
 
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE func_name ( arguments ) 
 
 
 
 
 
 CREATE TYPE  
 
 Define a new data 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 a new database user account. 
 
 
 
CREATE USER name [ [ WITH ] option [ ... ] ] 
 
 
 
  option  Can be one of the following options: 
 
 
 
SYSID uid | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP group_name [, ...] | VALID UNTIL 'abs_time' 
 
 
 
 
 
 CREATE VIEW  
 
 Define a view. 
 
 
 
CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query 
 
 
 
 
 
 DEALLOCATE  
 
 Delete a prepared query. 
 
 
 
DEALLOCATE [ PREPARE ] plan_name 
 
 
 
 
 
 DECLARE  
 
 Define a cursor. 
 
 
 
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ] CURSOR [ { WITH | WITHOUT } HOLD ] FOR query [ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ] 
 
 
 
 
 
 DELETE  
 
 Delete a row in a table. 
 
 
 
DELETE FROM [ ONLY ] table [ WHERE condition ] 
 
 
 
 
 
 DROP AGGREGATE  
 
 Delete a user-defined aggregation function. 
 
 
 
DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP CAST  
 
 Delete a user-defined type conversion. 
 
 
 
DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP CONVERSION  
 
 Delete a user-defined transcoding. 
 
 
 
DROP CONVERSION name [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP DATABASE  
 
 Delete a database. 
 
 
 
DROP DATABASE name 
 
 
 
 
 
 DROP DOMAIN  
 
 Delete a user-defined domain. 
 
 
 
DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP FUNCTION  
 
 Delete a function. 
 
 
 
DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP GROUP  
 
 Delete a user group. 
 
 
 
DROP GROUP name 
 
 
 
 
 
 DROP INDEX  
 
 Delete an index. 
 
 
 
DROP INDEX name [, ...] [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP LANGUAGE  
 
 Delete a process language. 
 
 
 
DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP OPERATOR  
 
 Delete an operator. 
 
 
 
DROP OPERATOR name ( { left_type | NONE }, { right_type | NONE } ) [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP OPERATOR CLASS  
 
 Delete an operator table. 
 
 
 
DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP ROLE  
 
 Delete a database role. 
 
 
 
DROP ROLE [ IF EXISTS ] _name_ [, ...] 
 
 
 
 
 
 DROP RULE  
 
 Delete a rewrite rule. 
 
 
 
DROP RULE name ON relation [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP SCHEMA  
 
 Delete a pattern. 
 
 
 
DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP SEQUENCE  
 
 Delete a sequence. 
 
 
 
DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP TABLE  
 
 Delete a table. 
 
 
 
DROP TABLE name [, ...] [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP TABLESPACE  
 
 Delete a tablespace. 
 
 
 
DROP TABLESPACE tablespace_name 
 
 
 
 
 
 DROP TRIGGER  
 
 Delete a trigger definition. 
 
 
 
DROP TRIGGER name ON table [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP TYPE  
 
 Delete a user-defined data type. 
 
 
 
DROP TYPE name [, ...] [ CASCADE | RESTRICT ] 
 
 
 
 
 
 DROP USER  
 
 Delete a database user account. 
 
 
 
DROP USER name 
 
 
 
 
 
 DROP VIEW  
 
 Delete a view. 
 
 
 
DROP VIEW name [, ...] [ CASCADE | RESTRICT ] 
 
 
 
 
 
 END  
 
 Commit the current transaction. 
 
 
 
END [ WORK | TRANSACTION ] 
 
 
 
 
 
 EXECUTE  
 
 Execute a prepared query. 
 
 
 
EXECUTE plan_name [ (parameter [, ...] ) ] 
 
 
 
 
 
 EXPLAIN  
 
 Displays the execution plan for a statement. 
 
 
 
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement 
 
 
 
 
 
 FETCH  
 
 Fetch rows from the query with cursors. 
 
 
 
FETCH [ direction { FROM | IN } ] cursor_name 
 
 
 
  direction  Can be one of the following options: 
 
 
 
NEXT PRIOR FIRST LAST ABSOLUTE count RELATIVE count count ALL FORWARD FORWARD count FORWARD ALL BACKWARD BACKWARD count BACKWARD ALL 
 
 
 
 
 
 GRANT  
 
 Define access rights. 
 
 
 
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  
 
 Create a new row in the table, that is, insert data. 
 
 
 
INSERT INTO table [ ( column [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query } 
 
 
 
 
 
 LISTEN  
 
 Monitor a notice. 
 
 
 
LISTEN name 
 
 
 
 
 
 LOAD  
 
 Load or reload a shared library file. 
 
 
 
LOAD 'filename' 
 
 
 
 
 
 LOCK  
 
 Lock a table. 
 
 
 
LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ] 
 
 
 
  lock_mode  Can be one of the following options: 
 
 
 
ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE 
 
 
 
 
 
 MOVE  
 
 Locate a cursor. 
 
 
 
MOVE [ direction { FROM | IN } ] cursor_name 
 
 
 
 
 
 NOTIFY  
 
 Generate a notification. 
 
 
 
NOTIFY name 
 
 
 
 
 
 PREPARE  
 
 Create a prepared query. 
 
 
 
PREPARE plan_name [ (data_type [, ...] ) ] AS statement 
 
 
 
 
 
 REINDEX  
 
 Rebuild the index. 
 
 
 
REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ] 
 
 
 
 
 
 RELEASE SAVEPOINT  
 
 Delete a previously defined SavePoint. 
 
 
 
RELEASE [ SAVEPOINT ] savepoint_name 
 
 
 
 
 
 RESET  
 
 Restore a runtime parameter value to its default value. 
 
 
 
RESET name RESET ALL 
 
 
 
 
 
 REVOKE  
 
 Remove access rights. 
 
 
 
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  
 
 Exit the current transaction. 
 
 
 
ROLLBACK [ WORK | TRANSACTION ] 
 
 
 
 
 
 ROLLBACK TO SAVEPOINT  
 
 Roll back to a SavePoint. 
 
 
 
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name 
 
 
 
 
 
 SAVEPOINT  
 
 Define a new SavePoint in the current transaction. 
 
 
 
SAVEPOINT savepoint_name 
 
 
 
 
 
 SELECT  
 
 Fetch several rows from a table or view. 
 
 
 
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  You can have the following options: 
 
 
 
[ 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  
 
 Modify run-time parameters. 
 
 
 
SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT } SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT } 
 
 
 
 
 
 SET CONSTRAINTS  
 
 Sets the constraint checking mode for the current transaction. 
 
 
 
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE } 
 
 
 
 
 
 SET SESSION AUTHORIZATION  
 
 Sets the session user identifier and current user identifier for the current session. 
 
 
 
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT RESET SESSION AUTHORIZATION 
 
 
 
 
 
 SET TRANSACTION  
 
 Start a transaction block. 
 
 
 
SET TRANSACTION transaction_mode [, ...] SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...] 
 
 
 
 Where  transaction_mode  Is one of − 
 
 
 
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY 
 
 
 
 
 
 SHOW  
 
 Displays the value of the run-time parameter. 
 
 
 
SHOW name SHOW ALL 
 
 
 
 
 
 START TRANSACTION  
 
 Start a transaction block. 
 
 
 
START TRANSACTION [ transaction_mode [, ...] ] 
 
 
 
  transaction_mode  Can be one of the following options: 
 
 
 
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED } READ WRITE | READ ONLY 
 
 
 
 
 
 TRUNCATE  
 
 Empty a table or group of tables. 
 
 
 
TRUNCATE [ TABLE ] name 
 
 
 
 
 
 UNLISTEN  
 
 Stop listening for notification messages. 
 
 
 
UNLISTEN { name | * } 
 
 
 
 
 
 UPDATE  
 
 Update the rows in a table. 
 
 
 
UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...] [ FROM from_list ] [ WHERE condition ] 
 
 
 
 
 
 VACUUM  
 
 Garbage collection and optionally analyze a database. 
 
 
 
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] 
 
 
 
 
 
 VALUES  
 
 Calculates a row or group of rows. 
 
 
 
VALUES ( _expression_ [, ...] ) [, ...] [ ORDER BY _sort_expression_ [ ASC | DESC | USING _operator_ ] [, ...] ] [ LIMIT { _count_ | ALL } ] [ OFFSET _start_ [ ROW | ROWS ] ] [ FETCH { FIRST | NEXT } [ _count_ ] { ROW | ROWS } ONLY ] 
 
 
 
 For more information, refer to the SQL syntax in the manual:  http://www.runoob.com/manual/PostgreSQL/sql-syntax.html  . 
 
 
  
                
                

                    

                        

                            
                                文章链接
                            
                            ：
                            5.5. PostgreSQL syntax 
                        

                    

                

            
        
        

            

    
        
        

            

                

                    
                        
                            
                                Web框架 
                            
                            
                        
                             946
                        
                    
                    
                    
                        

                            
 
 
 

                        

                    
                

                
                

            

        
    
    
        
        

            

                

                    
                        
                            
                                《地理信息系统原理、技术与方法》 
                            
                            
                        
                             97
                        
                    
                    
                    
                        

                            
 
 
 最近几年来，地理信息系统无论是在理论上还是应用上都处在一个飞速发展的阶段。 GIS被应用于多个领域的建模和决策支持，如城市管理、区划、环境整治等等，地理信息成为信息时代重要的组成部分之一； “数字地球”概念的提出，更进一步推动了作为其技术支撑的GIS的发展。 与此同时，一些学者致力于相关的理论研究，如空间感知、空间数据误差、空间关系的形式化等等。 这恰好说明了地理信息系统作为应用技术和学科的两个方面，并且这两个方面构成了相互促进的发展过程。 
 
 

                        

                    
                

                
                

            

        
    
    
        
        

            

                

                    
                        
                            
                                WEB基础 
                            
                            
                        
                             289