The content of this page has been automatically translated by AI. If you encounter any problems while reading, you can view the corresponding content in Chinese.
Help & Documentation>Tencent Cloud TCHouse-P>Development Guide>Basic Database Development>SELECT Statement

SELECT Statement

Last updated: 2024-08-22 16:59:53

On this page:

Access Function

postgres=# select md5(random()::text);
Result:
 md5
----------------------------------
 3eb6c0c8f8355f0b0f0cad7a8f0f7491

Data Sorting

Sort by a Column

postgres=# INSERT into tbase (nickname) VALUES('TBase Good');
postgres=# INSERT into tbase (id,nickname) VALUES(1,'The era of TBase distributed database has arrived');
postgres=# select * from tbase order by id;
Result:
 id | nickname
----+-----------------------------
 1  | hello TBase
 1  | The era of TBase distributed database has arrived
 2  | TBase Good
(3 rows)

Sort by First Column

postgres=# select * from tbase order by 1;

Sort by id Ascending, then by nickname Descending

postgres=# select * from tbase order by id, nickname desc;

Random Sort

postgres=# select * from tbase order by random();

Calculate Sort

postgres=# select * from tbase order by md5(nickname);

Sort using Subquery

postgres=# select * from tbase order by (select id from tbase order by random() limit 1);

NULL Value Sort Result Processing

postgres=# insert into tbase values(4,null);
postgres=# select * from tbase order by nickname nulls first;
postgres=# select * from tbase order by nickname nulls last;

Sort by Pinyin

postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by myname;
postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by convert(myname::bytea,'UTF-8','GBK');
postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by convert_to(myname,'GBK');
postgres=# select * from (values ('张三'), ('李四'),('陈五')) t(myname) order by myname collate "zh_CN.utf8";

WHERE Condition Usage

Single Condition Query

postgres=# select * from tbase where id=1;

Multiple Conditions AND

postgres=# select * from tbase where id=1 and nickname like '%h%';

Multiple Conditions OR

postgres=# select * from tbase where id=2 or nickname like '%h%';

ilike Case-insensitive Matching

postgres=# create table t_ilike(id int,mc text);
postgres=# insert into t_ilike values(1,'tbase'),(2,'TBase');
postgres=# select * from t_ilike where mc ilike '%tb%';

WHERE Condition Supports Subqueries

postgres=# select * from tbase where id=(select (random()*2)::integer from tbase order by random() limit 1);

NULL Value Query Method

postgres=# select * from tbase where nickname is null;
postgres=# select * from tbase where nickname is not null;

exists, returns true as long as there is a record

postgres=# create table t_exists1(id int,mc text);
postgres=# insert into t_exists1 values(1,'tbase'),(2,'TBase');
postgres=# create table t_exists2(id int,mc text);
postgres=# insert into t_exists2 values(1,'tbase'),(1,'TBase');
postgres=# select * from t_exists1 where exists(select 1 from t_exists2 where t_exists1.id=t_exists2.id);

Equivalent EXISTS Writing

postgres=# select t_exists1.* from t_exists1, (select distinct id from t_exists2) as t where t_exists1.id=t.id;

Paginated query

By default, starts from the first record and returns one record:
postgres=# select * from tbase limit 1;
Result:
 id | nickname
----+-------------
 1 | hello TBase
(1 row)
Use offset to specify the starting record. 0 indicates starting from the first record and returns 1 record:
postgres=# select * from tbase limit 1 offset 0;
Result:
 id | nickname
----+-------------
 1 | hello TBase
(1 row)
Starts from the 3rd record and returns two records:
postgres=# select * from tbase limit 1 offset 2;
Result:
 id | nickname
----+-----------------
 1 | The era of TBase Distributed Database has arrived
(1 row)
Using order by can obtain an ordered result:
postgres=# select * from tbase order by id limit 1 offset 2;
Result:
 id | nickname
----+-----------
 2 | TBase is good
(1 row)

Merge multiple query results

Do not filter duplicate records:
postgres=# select * from tbase union all select * from t_appoint_col;
Result:
 id | nickname
----+-----------------
 1 | hello TBase
 2 | TBase is good
 1 | The era of TBase Distributed Database has arrived
 1 | hello TBase
(4 rows)
Filter duplicate records:
postgres=# select * from tbase union select * from t_appoint_col;
Result:
 id | nickname
----+-----------------
 1 | The era of TBase Distributed Database has arrived
 1 | hello TBase
 2 | TBase is good
(3 rows)

Returns the intersection of two results

postgres=# select * from t_intersect1 INTERSECT select * from t_intersect2;
Result:
 id | mc
----+-------
 1 | tbase
(1 row)

Returns the difference set of two results

postgres=# select * from t_except1 except select * from t_except2;
Result:
 id | mc
----+-------
 2 | tbase
(1 row)

Usage of any

Only needs to be greater than one of the values to be true:
postgres=# select * from t_any where id > any (select 1 union select 3);
Result:
 id | mc
----+-------
 2 | TBase
(1 row)

Usage of all

Must be greater than all values to be true:
postgres=# select * from t_all where id > all (select 1 union select 2);
Result:
 id | mc
----+-------
 3 | TBase
(1 row)

Aggregate Query

Count records

postgres=# select count(1) from tbase;
Result:
 count
-------
 3
(1 row)

Count distinct values in the record table

postgres=# select count(distinct id) from tbase;
Result:
 count
-------
 2
(1 row)

Sum

postgres=# select sum(id) from tbase;
Result:
 sum
-----
 4
(1 row)

Maximum value

postgres=# select max(id) from tbase;
Result:
 max
-----
 2
(1 row)

Minimum value

postgres=# select min(id) from tbase;
Result:
 min
-----
 1
(1 row)

Average value

postgres=# select avg(id) from tbase;
Result:
        avg
--------------------
 1.3333333333333333
(1 row)

Merge grouped fields into a single string

postgres=# create table t1(f1 int, f2 text, f3 text);
postgres=# insert into t1 values(1,'a','a');
postgres=# insert into t1 values(1,'b','b');
postgres=# insert into t1 values(2,'a','a');
postgres=# select f1, string_agg(f2, ',') from t1 group by f1;
Result:
 f1 | string_agg
----+------------
 1  | a,b
 2  | a
(2 rows)

Deduplication and custom aggregate function

Using DISTINCT and ORDER BY in aggregate functions requires meeting specific conditions. You can use custom functions to achieve more flexible aggregation.

Multi-table join

Inner join

postgres=# select * from tbase inner join t_appoint_col on tbase.id=t_appoint_col.id;
Result:
 id | nickname | id | nickname
----+-----------------------------+----+-------------
 1  | hello TBase               | 1  | hello TBase
 1  | The era of TBase distributed database has arrived | 1  | hello TBase
(2 rows)

Left outer join

postgres=# select * from tbase left join t_appoint_col on tbase.id=t_appoint_col.id;
Result:
 id | nickname   | id | nickname
----+------------+----+-------------
 1  | hello TBase | 1  | hello TBase
 2  | TBase is good    |     |
 1  | The era of TBase distributed database has arrived | 1  | hello TBase
(3 rows)

Right outer join

postgres=# select * from tbase right join t_appoint_col on tbase.id=t_appoint_col.id;
Result:
 id | nickname                     | id | nickname
----+-------------------------------+----+-------------
 1  | The era of TBase distributed database has arrived | 1  | hello TBase
 1  | hello TBase                  | 1  | hello TBase
    |                               5  | Power TBase
(3 rows)

Full join

postgres=# select * from tbase full join t_appoint_col on tbase.id=t_appoint_col.id;
Result:
 id | nickname   | id | nickname
----+------------+----+-------------
 1  | hello TBase | 1  | hello TBase
 2  | TBase is good    |     |
 1  | The era of TBase distributed database has arrived | 1  | hello TBase
    |              | 5  | Power TBase
(4 rows)

Aggregation function concurrent computing

Single-core computing

postgres=# set max_parallel_workers_per_gather to 0;
postgres=# select count(1) from t_count;

Dual-core parallel

postgres=# set max_parallel_workers_per_gather to 2;
postgres=# select count(1) from t_count;

Quad-core parallel

postgres=# set max_parallel_workers_per_gather to 4;
postgres=# select count(1) from t_count;

not in clause includes null condition

postgres=# create table t_not_in(id int, mc text);
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t_not_in values(1, 'tbase'), (2, 'pgxz');
INSERT 0 2
postgres=# select * from t_not_in where id not in (3, 5);
Result:
 id | mc
----+-------
 1 | tbase
 2 | pgxz
(2 rows)
postgres=# select * from t_not_in where id not in (3, 5, null);
Result: No output row


Query data of specific data nodes (dn) only

postgres=# create table t_direct(id int, mc text);
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t_direct values(1, 'tbase'), (3, 'pgxz');
INSERT 0 2
postgres=# EXECUTE DIRECT ON (dn001) 'select * from t_direct';
Result:
 id | mc
----+-------
 1 | tbase
(1 row)
postgres=# EXECUTE DIRECT ON (dn002) 'select * from t_direct';
Result:
 id | mc
----+------
 3 | pgxz
(1 row)
Query data of all nodes:
postgres=# select * from t_direct;
Result:
 id | mc
----+-------
 1 | tbase
 3 | pgxz
(2 rows)

Special Application

Multiple rows to single row

postgres=# create table t_mulcol_tosimplecol(id int, mc text);
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t_mulcol_tosimplecol values(1, 'tbase'), (2, 'TBase');
INSERT 0 2
postgres=# select array_to_string(array(select mc from t_mulcol_tosimplecol), ',');
Result:
 array_to_string
-----------------
 tbase,TBase
(1 row)

Single column to multiple rows

postgres=# create table t_col_to_mulrow(id int, mc text);
NOTICE: Replica identity is needed for shard table, please add to this table through "alter table" command.
CREATE TABLE
postgres=# insert into t_col_to_mulrow values(1, 'tbase,TBase');
INSERT 0 1
postgres=# select regexp_split_to_table((select mc from t_col_to_mulrow where id=1 limit 1), ',');
Result:
 regexp_split_to_table
-----------------------
 tbase
 TBase
(2 rows)

Query the data node of the record

postgres=# select xc_node_id, * from t1;
Result:
 xc_node_id | f1 | f2
------------+----+----
 2142761564 | 1  | 3
 2142761564 | 1  | 3
(2 rows)
Query and map node name:
postgres=# select t1.xc_node_id, pgxc_node.node_name, t1.* from t1, pgxc_node where t1.xc_node_id=pgxc_node.node_id;
Result:
 xc_node_id | node_name | f1 | f2
------------+-----------+----+----
 2142761564 | dn001     | 1  | 3
 2142761564 | dn001     | 1  | 3
(2 rows)

Grouping Sets/Rollup/Cube Usage

GROUP BY Usage
Create Sales Detail Table and Insert Data:
create table t_grouping(id int, dep varchar(20), product varchar(20), num int);
INSERT INTO t_grouping VALUES(1, 'Business Unit 1', 'Mobile Phone', 90);
-- More data inserts ...
Group Summary by Department and Product:
postgres=# select dep, product, sum(num) from t_grouping group by dep, product order by dep, product;
Result:
 dep       | product   | sum
-----------+-----------+-----
 Business Unit 1 | Computer       | 80
 Business Unit 1 | Mobile Phone   | 160
 Business Unit 2 | Computer       | 120
 Business Unit 2 | Mobile Phone   | 50
 Business Unit 3 | Computer       | 80
 Business Unit 3 | Mobile Phone   | 160
Group using Grouping Sets:
postgres=# select dep, product, sum(num) from t_grouping group by grouping sets((dep), (product), ());
Result:
 dep       | product   | sum
-----------+-----------+-----
 Business Unit 1 | (null)         | 240
 Business Unit 2 | (null)      | 170
 Business Unit 3 | (null)      | 240
 Computer      | (null)      | 280
 Mobile Phone      | (null)      | 370
(null)       | (null)      | 650
Using rollup and cube:
postgres=# select dep, product, sum(num) from t_grouping group by rollup((dep), (product));
postgres=# select dep, product, sum(num) from t_grouping group by cube((dep), (product));
Results are the same as grouping sets.


PREPARE preparation usage

Create a prepared statement

postgres=# create table t1(f1 int, f2 int);
CREATE TABLE
postgres=# insert into t1 values(1, 1), (2, 2);
COPY 2
postgres=# PREPARE usrrptplan (int) AS SELECT * FROM t1 WHERE f1=$1;
PREPARE
postgres=# EXECUTE usrrptplan(1);
Result:
 f1 | f2
----+----
 1  | 1
(1 row)

Deallocate a prepared statement

postgres=# DEALLOCATE usrrptplan;
DEALLOCATE
postgres=# EXECUTE usrrptplan(1);
ERROR: prepared statement "usrrptplan" does not exist

中国站 - English