Help Center/ Data Warehouse Service / Getting Started/ Getting Started with DWS Data Development SQL
Updated on 2025-09-11 GMT+08:00
View PDF

Getting Started with DWS Data Development SQL

This section describes some basic SQL operations in DWS.

Creating, Viewing, and Deleting a Database

  • Run the CREATE DATABASE statement to create a database. 
    1
    		 
    CREATE DATABASE test_db ENCODING 'UTF8' template = template0;
  • Query the database list using the \l meta-command. 
    1
    		 
    \l
  • Querying the database list using the PG_DATABASE system catalog 
    1
    		 
    SELECT datname FROM pg_database;
  • Run the DROP DATABASE statement to delete a database. 
    1
    		 
    DROP DATABASE test_db;

Creating, Viewing, Modifying, and Deleting a Table

  • Run the CREATE TABLE statement to create a table. 
    1
    		 
    CREATE TABLE customer_t1(id INT, name CHAR(40),age TINYINT);
  • Use the PG_GET_TABLEDEF() function to view the table creation statement. Replace customer_t1 with the name of the table to be queried. 
    1
    		 
    SELECT * FROM PG_GET_TABLEDEF('customer_t1');
  • Run the ALTER TABLE statement to modify a table.
    Add a column: 
    1
    		 
    ALTER TABLE customer_t1 ADD (address VARCHAR(100));

    Delete a column:

    1
    		 
    ALTER TABLE customer_t1 DROP COLUMN address;

    Modify a column type:

    1
    		 
    ALTER TABLE customer_t1 MODIFY age INTEGER NOT NULL;
  • Run the DROP TABLE statement to delete a table. 
    1
    		 
    DROP TABLE customer_t1;

Creating, Viewing, and Deleting Indexes

  • Run the CREATE INDEX or ALTER TABLE statement to create a common index. 
    1
    		 
    CREATE INDEX c_id_index on customer_t1(id);

    1
    		 
    ALTER TABLE customer_t1 ADD INDEX c_id_index (id);
  • Use the PG_INDEXES system catalog to view all indexes in a table. 
    1
    		 
    SELECT * FROM pg_indexes WHERE tablename = 'customer_t1';
  • Run the ALTER TABLE or DROP INDEX statement to delete an index. 
    1
    		 
    DROP INDEX c_id_index;

    1
    		 
    ALTER TABLE customer_t1 DROP INDEX c_id_index;

Adding, Deleting, and Modifying Table Data

  • Run the INSERT INTO statement to insert table data. 
    1
    		 
    INSERT INTO customer_t1 VALUES(1001,'user1',22);
  • Run the SELECT statement to query table data. 
    1
    		 
    SELECT * FROM customer_t1;
  • Run the UPDATE statement to update table data. 
    1
    		 
    UPDATE customer_t1 SET id = 1009 WHERE id = '1001';
  • Use the DELETE statement to delete table data. 
    1
    		 
    DELETE FROM customer_t1 WHERE id = '1009';