Available Commands and SQL Statements
This section describes the SQL statements, which are client commands available with the Database as a Service.
Client Commands
The list of commands available with this Database as a Service is shown below.
Tip: For details on each client command, refer to the PostgreSQL documentation, "PostgreSQL Client Applications."
| Command Name | Purpose |
|---|---|
| clusterdb | Clusters a database (physically reorders the tables, based on the index information) |
| createdb |
Creates a new database Note: You cannot specify the -E option.
|
| createuser | Defines a new user account |
| dropdb | Removes a database |
| dropuser | Removes a user account |
| ecpg | Uses an embedded SQL C preprocessor |
| pg_config | Provides information about the installed version |
| pg_dump | Extracts a database into a script file or other archive file |
| pg_dumpall | Extracts a database cluster into a script file |
| pg_isready | Checks the connection status of a server (*1) |
| pg_recvlogical | Controls the logical decoding of a stream (*1) |
| pg_restore | Restores a database from an archive file created by pg_dump |
| psql | Executes a command interactively |
| reindexdb | Reindexes a database |
| vacuumdb | Garbage-collects and analyzes a database |
Note 1: Not usable when the database engine is Symfoware Server.
SQL Statements
The list of SQL statements available with this Database as a Service is shown below.
Tip: For details on each SQL statement syntax, refer to the PostgreSQL documentation, "SQL Commands."
| SQL Statement | Purpose |
|---|---|
| ABORT | Aborts the current transaction |
| ALTER AGGREGATE | Changes the definition of an aggregate function |
| ALTER COLLATION | Changes the definition of a collation |
| ALTER CONVERSION | Changes the definition of a conversion |
| ALTER DATABASE | Changes a database (Note 1) |
| ALTER DEFAULT PRIVILEGES | Defines default access privileges |
| ALTER DOMAIN | Changes the definition of a domain |
| ALTER EVENT TRIGGER | Changes the definition of an event trigger (*2) |
| ALTER EXTENSION | Changes the definition of an extension |
| ALTER FOREIGN TABLE | Changes the definition of an external table (*2) (*3) |
| ALTER FUNCTION | Changes the definition of a function (Note 1) |
| ALTER GROUP | Changes a role name or membership |
| ALTER INDEX | Changes the definition of an index |
| ALTER LARGE OBJECT | Changes the definition of a large object |
| ALTER MATERIALIZED VIEW | Changes the definition of a materialized view (*2) |
| ALTER OPERATOR | Changes the definition of an operator |
| ALTER OPERATOR CLASS | Changes the definition of an operator class |
| ALTER OPERATOR FAMILY | Changes the definition of an operator family |
| ALTER POLICY | Changes rows of the definition of a security policy (*2) |
| ALTER ROLE | Changes a database role (Note 1) |
| ALTER RULE | Changes the definition of a rule (*2) |
| ALTER SCHEMA | Changes the definition of a schema |
| ALTER SEQUENCE | Changes the definition of a sequence generator |
| ALTER SERVER | Changes the definition of an external server (*2) (*3) |
| ALTER TABLE | Changes the definition of a table |
| ALTER TABLESPACE | Changes the definition of a tablespace |
| ALTER TEXT SEARCH CONFIGURATION | Changes the definition of a text search configuration |
| ALTER TEXT SEARCH DICTIONARY | Changes the definition of a text search dictionary |
| ALTER TEXT SEARCH PARSER | Changes the definition of a text search parser |
| ALTER TEXT SEARCH TEMPLATE | Changes the definition of a text search template |
| ALTER TRIGGER | Changes the definition of a trigger |
| ALTER TYPE | Changes the definition of a type |
| ALTER USER | Changes a database role |
| ALTER USER MAPPING | Changes the definition of a user map (*2) (*3) |
| ALTER VIEW | Changes the definition of a view |
| ANALYZE | Collects statistics about a database |
| BEGIN | Starts a transaction block |
| CHECKPOINT | Forces a transaction log checkpoint |
| CLOSE | Closes a cursor |
| CLUSTER | Clusters a table according to an index |
| COMMENT | Defines or changes the comment of an object |
| COMMIT | Commits the current transaction |
| COMMIT PREPARED | Commits a transaction that was earlier prepared for two-phase commit |
| COPY | Copies data between a client and a database table (Note 4) |
| CREATE AGGREGATE | Defines a new aggregate function (Note 5) |
| CREATE CAST | Defines a new cast (Note 5) |
| CREATE COLLATION | Defines a new collation |
| CREATE CONVERSION | Defines a new encoding conversion (Note 5) |
| CREATE DATABASE | Creates a new database (Note 6) |
| CREATE DOMAIN | Defines a new domain |
| CREATE EVENT TRIGGER | Defines a new event trigger (*2) |
| CREATE EXTENSION | Installs an extension |
| CREATE FOREIGN TABLE | Defines a new external table (*2) (*3) |
| CREATE FUNCTION | Defines a new function (Note 1) (Note 7) |
| CREATE GROUP | Defines a new database role |
| CREATE INDEX | Defines a new index |
| CREATE MATERIALIZED VIEW | Defines a new materialized view (*2) |
| CREATE OPERATOR | Defines a new operator (Note 5) |
| CREATE OPERATOR CLASS | Defines a new operator class (Note 5) |
| CREATE OPERATOR FAMILY | Defines a new operator family |
| CREATE POLICY | Defines new rows of a security policy in a table (*2) |
| CREATE ROLE | Defines a new database role |
| CREATE RULE | Defines a new rewrite rule |
| CREATE SCHEMA | Defines a new schema |
| CREATE SEQUENCE | Defines a new sequence generator |
| CREATE SERVER | Defines a new external server (*2) (*3) |
| CREATE TABLE | Define a new table |
| CREATE TABLE AS | Defines a new table from the results of a query |
| CREATE TABLESPACE | Defines a new tablespace (Note 8) |
| CREATE TEXT SEARCH CONFIGURATION | Defines a new text search configuration |
| CREATE TEXT SEARCH DICTIONARY | Defines a new text search dictionary |
| CREATE TEXT SEARCH PARSER | Defines a new text search parser (Note 5) |
| CREATE TEXT SEARCH TEMPLATE | Defines a new text search template (Note 5) |
| CREATE TRIGGER | Defines a new trigger (Note 5) |
| CREATE TYPE | Defines a new data type (Note 5) |
| CREATE USER | Defines a new database role |
| CREATE USER MAPPING | Defines a new user map of an external server (*2) (*3) |
| CREATE VIEW | Defines a new view |
| DEALLOCATE | Deallocates a prepared statement |
| DECLARE | Defines a cursor |
| DELETE | Deletes rows of a table |
| DISCARD | Discards session state |
| DO | Executes an anonymous code block |
| DROP AGGREGATE | Removes a defined aggregate function |
| DROP CAST | Removes a defined cast |
| DROP COLLATION | Removes a defined collation |
| DROP CONVERSION | Removes a defined conversion |
| DROP DATABASE | Removes a defined database |
| DROP DOMAIN | Removes a defined domain |
| DROP EVENT TRIGGER | Removes a defined event trigger (*2) |
| DROP EXTENSION | Removes a defined extension |
| DROP FOREIGN TABLE | Removes a defined external table (*2) (*3) |
| DROP FUNCTION | Removes a defined function |
| DROP GROUP | Removes a defined database role |
| DROP INDEX | Removes a defined index |
| DROP MATERIALIZED VIEW | Removes a defined materialized view (*2) |
| DROP OPERATOR | Removes a defined operator |
| DROP OPERATOR CLASS | Removes a defined operator class |
| DROP OPERATOR FAMILY | Removes a defined operator family |
| DROP OWNED | Removes database objects owned by a defined database role |
| DROP POLICY | Deletes rows of a security policy from a table (*2) |
| DROP ROLE | Removes a defined database role |
| DROP RULE | Removes a defined rewrite rule |
| DROP SCHEMA | Removes a defined schema |
| DROP SEQUENCE | Removes a defined sequence |
| DROP SERVER | Deletes the descriptor of an external server (*2) (*3) |
| DROP TABLE | Removes a defined table |
| DROP TABLESPACE | Removes a defined tablespace |
| DROP TEXT SEARCH CONFIGURATION | Removes a defined text search configuration |
| DROP TEXT SEARCH DICTIONARY | Removes a defined text search dictionary |
| DROP TEXT SEARCH PARSER | Removes a defined text search parser |
| DROP TEXT SEARCH TEMPLATE | Removes a defined text search template |
| DROP TRIGGER | Removes a defined trigger |
| DROP TYPE | Removes a defined data type |
| DROP USER | Removes a defined database role |
| DROP USER MAPPING | Removes the user map of an external server (*2) (*3) |
| DROP VIEW | Removes a defined view |
| END | Commits the current transaction |
| EXECUTE | Executes a prepared statement |
| EXPLAIN | Shows the execution plan of a query statement |
| FETCH | Retrieves rows from a table using a cursor |
| GRANT | Defines access privileges |
| IMPORT FOREIGN SCHEMA | Imports a table definition from an external server (*2) |
| INSERT | Creates new rows in a table |
| LISTEN | Listens for a notification |
| LOCK | Locks a table |
| MOVE | Positions a cursor |
| NOTIFY | Generates a notification |
| PREPARE | Prepares a statement for execution |
| PREPARE TRANSACTION | Prepares the current transaction for two-phase commit |
| REASSIGN OWNED | Changes the ownership of database objects owned by a database role |
| REFRESH MATERIALIZED VIEW | Converts the content of a materialized view (*2) |
| REINDEX | Rebuilds indexes |
| RELEASE SAVEPOINT | Destroys a previously defined savepoint |
| RESET | Restores the value of a run-time parameter to the default value (Note 1) |
| REVOKE | Removes access privileges |
| ROLLBACK | Aborts the current transaction |
| ROLLBACK PREPARED | Cancels a transaction that was earlier prepared for two-phase commit |
| ROLLBACK TO SAVEPOINT | Rolls back to a savepoint |
| SAVEPOINT | Defines a new savepoint within the current transaction |
| SECURITY LABEL | Defines or changes a security label applied to an object |
| SELECT | Retrieves rows from a table or view |
| SELECT INTO | Defines a new table from the results of a query |
| SET | Changes a run-time parameter (Note 1) |
| SET CONSTRAINTS | Sets constraint check timing for the current transaction |
| SET ROLE | Sets the user identifier of the current session |
| SET SESSION AUTHORIZATION | Sets the session user identifier and the user identifier of the current session |
| SET TRANSACTION | Sets the characteristics of the current transaction |
| SHOW | Shows the value of a run-time parameter |
| START TRANSACTION | Starts a transaction block |
| TRUNCATE | Empties a table or set of tables |
| UNLISTEN | Stops listening for a notification |
| UPDATE | Updates rows of a table |
| VACUUM | Garbage-collects and optionally analyzes a database |
| VALUES | Computes a set of rows |
Note 1: When you specify configuration_parameter, you cannot specify a parameter that takes a directory path as a value.
Note 2: Not usable when the database engine is Symfoware Server.
Note 3: You can only use postgres_fdw as an external wrapper.
Note 4: You cannot specify any file names for FROM and TO. Specify STDIN or STDOUT.
Note 5: If you specify a function, you can only specify a function that is implemented in SQL or the plpgsql language.
Note 6: You cannot specify the ENCODING clause.
Note 7: You can only specify the languages SQL, internal, or plpgsql for LANGUAGE.
Note 8: The directory specified in the LOCATION clause is generated under /userdata/tblspc automatically. You do not have to prepare the directory in advance. In the LOCATION clause, you can specify a path within a length of 958 bytes.
Note:
-
You cannot use the following SQL statements that are available in PostgreSQL:
- ALTER FOREIGN DATA WRAPPER
- ALTER LANGUAGE
- ALTER SYSTEM
- CREATE FOREIGN DATA WRAPPER
- CREATE LANGUAGE
- DROP FOREIGN DATA WRAPPER
- DROP LANGUAGE
- LOAD
-
The operation for parallel searches of the database service is the same as for PostgreSQL.
-
In the database service it is not possible to use the "CPU resource availability checking" function provided by the product version of Enterprise Postgres.
The "CPU resource availability checking" function is the function that controls server slowdown due to excessive parallel searches.
-