Other PostgreSQL statements - Hologres - Alibaba Cloud Documentation Center

Hologres is compatible with PostgreSQL. Apart from the PostgreSQL statements that are used to create, query, and update tables and schemas, Hologres supports other PostgreSQL statements. This topic describes these PostgreSQL statements.

The following table describes the PostgreSQL statements that are supported by Hologres. For more information, see official PostgreSQL documentation.


PostgreSQL statement	Description
ALTER TABLE	Modifies a table. Hologres allows you to execute only the following PostgreSQL statements to modify a table: ALTER TABLE ALTER PARTITION TABLE ALTER FOREIGN TABLE
ALTER ROLE	Modifies a role.
ANALYZE	Updates statistics.
BEGIN	Starts a transaction. The `BEGIN` statement must be used together with a DDL statement.
COMMIT	Commits a transaction. The `COMMIT` statement must be used together with a DDL statement.
CREATE DATABASE	Creates a database.
CREATE EXTENSION	Installs an extension.
CREATE FOREIGN DATA WRAPPER	Creates a foreign data wrapper (FDW).
CREATE FOREIGN TABLE	Creates a foreign table. Hologres supports only foreign tables that are sourced from MaxCompute.
CREATE GROUP	Creates a user group.
CREATE SERVER	Creates an external server.
CREATE TABLE	Creates a table. Hologres does not support all the commands of the `CREATE TABLE` statement. The following commands are not supported: UNLOGGED TEMP IF NOT EXISTS LIKE CHECK DEFAULT GENERATED UNIQUE EXCLUDE FOREIGN KEY DEFERRABLE WITH OIDS GLOBAL LOCAL Hologres supports only list partitions. You can specify only a list of discrete values of the STRING type for the partition key in the description for each partition.
CREATE VIEW	Creates a view.
CREATE USER	Creates a user.
CREATE USER MAPPING	Creates a user mapping.
DROP DATABASE	Deletes a database.
DROP FOREIGN DATA WRAPPER	Deletes an FDW.
DROP FOREIGN TABLE	Deletes a foreign table.
DROP GROUP	Deletes a user group.
DROP OWNED	Deletes the database objects that are owned by a database role.
DROP ROLE	Deletes a role.
DROP SERVER	Deletes an external server.
DROP TABLE	Deletes a table.
DROP USER	Deletes a user.
DROP USER MAPPING	Deletes a user mapping.
END	Stops a transaction. The `END` statement must be used together with a DDL statement.
EXPLAIN	Views an execution plan.
INSERT	Inserts data.
ROLLBACK	Rolls back a transaction.
SELECT	Hologres does not support all the commands of the `SELECT` statement. The following commands are not supported: WITH RECURSIVE TABLESAMPLE LOCKING ONLY
SET	Sets a Grand Unified Configuration (GUC) parameter. For more information about GUC parameters, see GUC parameters.
CALL set_table_property('<tablename>', 'time_to_live_in_seconds', '<values>');	Specifies the time to live (TTL) of the data in the table. The TTL of the data in the table must be accurate to seconds and must be a non-negative integer or floating-point number. Take note of the following items: If you do not specify the TTL, the data in the table is retained for 100 years by default. In Hologres V1.3.24 and later, the minimum TTL can be set to 1 day, which is equivalent to 86,400 seconds. The system counts the TTL from the time when the data is first written to the table, rather than the time when the data is last modified. If the TTL expires, the data of the table is deleted at an unspecified point in time. The table is still retained. You can separately execute this statement to modify the TTL of the data in the table. Different from the `DELETE FROM` statement, this statement consumes few resources and does not lock the table. However, this statement does not ensure the consistency of expired data. In this case, the following situations may occur: You may fail to read the expired data or may read the expired data that was not last modified. If you modify or delete the expired data, the operation may fail due to unexpected causes. For example, the data of a primary key column is duplicate. If you specify the TTL of the data in the table, make sure that the expired data will not be read or modified, or make sure that you do not need to ensure the consistency of the expired data.