Hologres:PostgreSQL client

Visit the official website of PostgreSQL, download the installation package of version 11 or later based on your operating system, and then install the client as prompted.

• If you use the Windows operating system, perform the following operations:

  1. Go to the System Properties dialog box. On the Advanced tab, click Environment Variables in the lower-right corner.

  2. Set the Path variable to the bin subdirectory of the installation directory.

  3. Click OK.

• If you use the macOS operating system, you do not need to set environment variables in most cases. If you need to set environment variables, see Setting Up Your Environment.

Open the PostgreSQL client and execute the required SQL statements based on your operating system to connect to Hologres. The syntax of the SQL statements is the same as that used to connect to a PostgreSQL database.

• Execute the following SQL statement if you use the Linux operating system:

  psql -h <Endpoint> -p <Port> -U <AccessKey ID> -d <Database>

  Enter the AccessKey secret of your Alibaba Cloud account as prompted.

  

• Execute the following SQL statement if you use the macOS operating system:

  PGUSER=<AccessKey ID> PGPASSWORD=<AccessKey Secret> psql -p <Port> -h <Endpoint> -d <Database>

  

• Execute the following SQL statements if you use the Windows operating system:

  Server [localhost]: Endpoint
  Database [postgres]: Database
  Port [5432]: Port
  Username [postgres]: <AccessKey ID>
  Password of the <AccessKey ID> user: <AccessKey Secret> 

Examples

• If you log on to the Hologres console with an Alibaba Cloud account, execute the following statement to use the PostgreSQL client to connect to a Hologres instance:

  PGUSER="xxx" PGPASSWORD="xxx" psql -h hgpostcn-cn-xxx-cn-hangzhou.hologres.aliyuncs.com -p 80 -d demo

  

• Log on to the Hologres console with a custom account

  • Assume that the username of the custom account is abc, as shown in the following figure.

  • Execute the following statement to use the PostgreSQL client to connect to a Hologres instance:

    PGUSER="BASIC\$abc" PGPASSWORD="xxx" psql -h hgpostcn-cn-xxx-cn-hangzhou.hologres.aliyuncs.com -p 80 -d demo

    

After you purchase a Hologres instance, a database named postgres is automatically created. However, limited resources are allocated to this database. We recommend that you use this database for O&M management and create another database for business purposes.

• Syntax:

  CREATE Database <DatabaseName>;

  Replace DatabaseName with the name of the database that you want to create.

• Example:

  -- Create a database named test. 
  CREATE Database test; 

You can execute standard PostgreSQL statements to develop data by using the PostgreSQL client.

BEGIN;
CREATE TABLE nation (
  n_nationkey bigint NOT NULL,
  n_name text NOT NULL,
  n_regionkey bigint NOT NULL,
  n_comment text NOT NULL,
  PRIMARY KEY (n_nationkey)
);
CALL SET_TABLE_PROPERTY('nation', 'bitmap_columns', 'n_nationkey,n_name,n_regionkey');
CALL SET_TABLE_PROPERTY('nation', 'dictionary_encoding_columns', 'n_name,n_comment');
CALL SET_TABLE_PROPERTY('nation', 'time_to_live_in_seconds', '31536000');
COMMIT;

INSERT INTO nation VALUES
(11,'zRAQ', 4,'nic deposits boost atop the quickly final requests?  quickly regula'),
(22,'RUSSIA', 3  ,'requests against the platelets use never according to the quickly regular pint'),
(2,'BRAZIL',  1 ,'y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special '),
(5,'ETHIOPIA',  0 ,'ven packages wake quickly. regu'),
(9,'INDONESIA', 2  ,'slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull'),
(14,'KENYA',  0  ,'pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t'),
(3,'CANADA',  1 ,'eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold'),
(4,'EGYPT', 4 ,'y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d'),
(7,'GERMANY', 3 ,'l platelets. regular accounts x-ray: unusual, regular acco'),
(20 ,'SAUDI ARABIA',  4 ,'ts. silent requests haggle. closely express packages sleep across the blithely');

SELECT * FROM nation;

You can develop data in different ways based on your business requirements. Examples:

• Accelerate queries of MaxCompute data. For more information, see Create a foreign table in Hologres to accelerate queries on MaxCompute data.

• Write data to Hologres in real time by using Realtime Compute for Apache Flink. For more information, see Hologres result table.