Updated on 2025-03-13 GMT+08:00

Unique

Description

Deduplicates lower-layer data. During the execution, it traverses all input data, filters duplicate records, and retains only the unique record.

Typical Scenarios

Disable the enable_hashagg parameter and perform the query with DISTINCT.

Examples

Example: Perform the query with DISTINCT.

-- Prepare data.
gaussdb=# CREATE TABLE t1 (id INT , number INT);
CREATE TABLE
gaussdb=# INSERT INTO t1 VALUES(generate_series(1,50), 1);
INSERT 0 50
gaussdb=# INSERT INTO t1 VALUES(generate_series(1,50), 2);
INSERT 0 50

-- Execution result.
gaussdb=# SET enable_hashagg = off;
SET
gaussdb=# EXPLAIN SELECT DISTINCT t1.id FROM t1;
                            QUERY PLAN                            
------------------------------------------------------------------
 Unique  (cost=150.43..161.18 rows=200 width=4)
   ->  Sort  (cost=150.43..155.80 rows=2149 width=4)
         Sort Key: id
         ->  Seq Scan on t1  (cost=0.00..31.49 rows=2149 width=4)
(4 rows)

-- Drop.
gaussdb=# DROP TABLE t1;