Data Query, Update and Delete

SELECT

Overview

The SELECT statement is used to retrieve data from a table or view. The syntax is as follows:

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT | SKIP LOCKED ] [...] ]
 
where from_item can be one of:
 
    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
                [ TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] ]
    [ LATERAL ] ( select ) [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item join_type from_item { ON join_condition | USING ( join_column [, ...] ) [ AS join_using_alias ] }
    from_item NATURAL join_type from_itemfrom_item CROSS JOIN from_itemand grouping_element can be one of:
 
    ( )
    expression
    ( expression [, ...] )
    ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
    CUBE ( { expression | ( expression [, ...] ) } [, ...] )
    GROUPING SETS ( grouping_element [, ...] )
 
and with_query is:with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( select | values | insert | update | delete )
        [ SEARCH { BREADTH | DEPTH } FIRST BY column_name [, ...] SET search_seq_col_name ]
        [ CYCLE column_name [, ...] SET cycle_mark_col_name [ TO cycle_mark_value DEFAULT cycle_mark_default ] USING cycle_path_col_name ]
 
TABLE [ ONLY ] table_name [ * ]

For more syntax options regarding SELECT, refer to SELECT.

Examples

Refer to the table below for the SELECT test.

tacnode=> \d t1
                                 Table "public.t1"
  Column  |     Type     | Collation |  Nullable  |                 Default
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
tacnode=> SELECT * FROM t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  2 | testname   |     |
  3 | updatename |  11 | f
  4 | updatename |  11 | f
  5 | testname4  |  22 | m
 11 | updatename |  11 | f
(6 rows)

Retrieve the data from the table that satisfies the given criteria.

tacnode=> SELECT * FROM t1 WHERE gender='m';
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  5 | testname4  |  22 | m
(2 rows)

Select specific fields to query

tacnode=> SELECT id, name FROM t1 WHERE gender='m';
 id |    name
----+------------
  1 | updatename
  5 | testname4
(2 rows)

A subquery, an inner or nested query, is embedded within a PostgreSQL query and can be placed in a WHERE clause. It is utilized to return data that serves as a condition for the main query, helping to refine the data retrieval process. Subqueries can be employed with various statements, including SELECT, INSERT, UPDATE, and DELETE, as well as with operators such as =, <, >, <=, IN, and others.

Subqueries must adhere to these rules:

They need to be contained within parentheses.
A subquery should consist of only one column in the SELECT clause, unless the main query includes multiple columns for comparison with the subquery's selected column.
While ORDER BY cannot be utilized in a subquery, it can be applied in the main query, functioning similarly to GROUP BY in a subquery.
Subqueries that return multiple rows can only interact with various value operators, including IN, EXISTS, NOT IN, ANY / SOME, and ALL.
The BETWEEN operator is not allowable within a subquery, although it can be employed inside a subquery.

Subqueries are primarily utilized in SELECT statements, following this basic syntax:

SELECT column1, column2, ... colulmnN
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column1, column2, ... colulmnN
      FROM table1 [, table2 ]
      [WHERE])

Note: While subqueries are primarily utilized in SELECT, they also apply in INSERT, UPDATE, and DELETE.

Example:

Utilize the test table and data provided for SubQuery testing.

tacnode=> \d t1
                                 Table "public.t1"
  Column  |     Type     | Collation |  Nullable  |                 Default
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
tacnode=> SELECT * FROM t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  2 | aa         |  11 | m
  3 | bb         |  11 | m
  4 | ee         |  11 | f
  5 | ff         |  15 | f
  6 | gg         |  18 | m
(6 rows)
 
tacnode=> \d t2
              Table "public.t2"
  Column  |     Type     | Collation |  Nullable  |                 Default
-------+----------+----------+----------+------
 id    | integer  |          | not null |
 score | smallint |          |          |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (id ASC)
 
tacnode=> SELECT * FROM t2;
 id | score
----+-------
  1 |    80
  2 |    90
  3 |    85
  4 |    88
  5 |    96
  6 |    89
(6 rows)

Retrieve the names of all IDs from table t1 that have a score of 90 or higher in table t2.

tacnode=> SELECT t1.id, t1.name FROM t1 WHERE id IN (SELECT id FROM t2 WHERE score>=90);
 id | name
----+------
  5 | ff
  2 | aa
(2 rows)

With Query (CTE)

The WITH query is an advanced SQL feature in PostgreSQL, commonly known as CTE (Common Table Expressions). This feature allows you to define an auxiliary statement within a complex query, similar to a temporary table created in the query. When dealing with intricate query statements, using WITH can significantly simplify the SQL, enhancing readability.

With Query syntax:

WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
), top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Here’s a detailed example illustrating how to use the query. Below are the definitions and data for the test table:

tacnode=> \d t1
                                 Table "public.t1"
  Column  |     Type     | Collation |  Nullable  |                 Default
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
tacnode=> SELECT * FROM t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  2 | aa         |  11 | m
  3 | bb         |  11 | m
  4 | ee         |  11 | f
  5 | ff         |  15 | f
  6 | gg         |  18 | m
(6 rows)

USING With Query in SELECT.

tacnode=> WITH t AS (SELECT * FROM t1 WHERE gender='f') SELECT * FROM t WHERE age > 12;
 id | name | age | gender
----+------+-----+--------
  5 | ff   |  15 | f
(1 row)

Benefits of using CTE:

CTE simplifies SQL code and enhances readability.
It is lighter than the views.

DELETE

Overview

The DELETE command removes data from a table. You can target particular rows for deletion using the defined WHERE condition. If no WHERE condition is provided, all the data in the table will be deleted.

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    [ USING from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Refer to the DELETE documentation for additional usage syntax.

Examples

Conduct a 'DELETE' test for the table below.

tacnode=> \d t2
                                  Table "public.t2"
  Column  |     Type     | Collation |  Nullable  |                 Default
--------+--------------+----------+----------+-----------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::regclass)::integer
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (id ASC)
 
tacnode=> select * from t2;
 id |    name    | age | gender
----+------------+-----+--------
 10 | t2testname |  22 | f
 11 | t2testname |  22 | f
 12 | t2testname |  22 | f
(3 rows)

Remove a record from the table.

-- Remove specific record
tacnode=> delete from t2 where id=11;
DELETE 1
tacnode=> select * from t2;
 id |    name    | age | gender
----+------------+-----+--------
 10 | t2testname |  22 | f
 12 | t2testname |  22 | f
(2 rows)

Remove all data from the entire table.

-- Remove all data
tacnode=> delete from t2;
DELETE 2
tacnode=> select * from t2;
 id | name | age | gender
----+------+-----+--------
(0 rows)

Utilize DELETE USING to reference other tables in a delete statement, streamlining complex delete operations that involve multiple tables in one SQL query.

-- Remove all products from the product table where quantity in stock is 0
DELETE FROM product
USING stock
    WHERE product.id = stock.product_id AND stock.quantity = 0;

Notices

To enable multi-version concurrency control and TimeTravel features, MVCC attaches a timestamp to each record as a version marker. The deletion process does not instantly remove the old data from the disk. Instead, the old data is purged through asynchronous tasks only after the expired MVCC version. Consequently, storage space increases after a delete operation is performed but decreases once the asynchronous compaction is finished.

UPDATE

Overview

To change data in a table, utilize the UPDATE statement. Here’s the syntax for UPDATE:

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
    SET { column_name = { expression | DEFAULT } |
          ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

To learn more about the UPDATE usage syntax, refer to UPDATE.

Examples

Perform an 'UPDATE' test using the table provided below.

tacnode=> \d t1
                                 Table "public.t1"
  Column  |     Type     | Collation |  Nullable  |                 Default
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
tacnode=> select * from t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | testname1  |  11 | m
  2 | testname   |     |
  3 | testname3  |  33 | f
  4 | testname4  |  22 | f
  5 | testname4  |  22 | m
 11 | t2testname |  22 | f
(6 rows)

Revise a row of data in the table.

tacnode=> update t1 set name='updatename' where id=1;
UPDATE 1
tacnode=> select * from t1 where id=1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
(1 row)

Update multiple rows that fulfill the specified conditions.

tacnode=> select * from t1 where gender='f';
 id |    name    | age | gender
----+------------+-----+--------
  3 | testname3  |  33 | f
  4 | testname4  |  22 | f
 11 | t2testname |  22 | f
(3 rows)
 
tacnode=> update t1 set name='updatename',age=11 where gender='f';
UPDATE 3
tacnode=> select * from t1 where gender='f';
 id |    name    | age | gender
----+------------+-----+--------
  3 | updatename |  11 | f
  4 | updatename |  11 | f
 11 | updatename |  11 | f
(3 rows)

Notices

To implement multi-version concurrency control and TimeTravel features, MVCC attaches timestamp information to each record as a version indicator. The update operation does not instantly remove the old data from the disk. Instead, the old data is purged through asynchronous tasks once the MVCC version expires. As a result, storage space increases following the execution of an update operation, and it only decreases after the asynchronous compaction is finalized.

MERGE

Overview

The MERGE INTO statement allows you to combine records by integrating INSERT, UPDATE, and DELETE operations into a single command. It executes the appropriate actions depending on the matching conditions between the source and target tables. With MERGE INTO, you can efficiently maintain data consistency while synchronizing information across two tables.

Usage Scenarios

Data Warehouse ETL: Combine incremental data into data warehouse tables.
Data Synchronization: Align data across various systems or databases.
Data Deduplication: Remove duplicates and refresh data in a table according to defined criteria.

Syntax:

MERGE INTO target_table_name [[AS] target_alias]
USING source_table_name [[AS] source_alias]
ON ( condition )
WHEN MATCHED THEN
    UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
    INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);

Parameters:

target_table_name: The table that will be updated or have data inserted into
source_table_name: The table that supplies the data
condition: Defines the matching criteria between the target and source tables
WHEN MATCHED THEN: Perform an UPDATE operation when the matching criteria is satisfied
WHEN NOT MATCHED THEN: Perform an INSERT operation when the matching criteria is not satisfied

Examples

MERGE INTO customers c
USING (SELECT *
        FROM new_customer_data) AS nc
ON c.id = nc.id
WHEN MATCHED THEN
    UPDATE
    SET name  = nc.name,
        email = nc.email
WHEN NOT MATCHED THEN
    INSERT (id, name, email)
    VALUES (nc.id, nc.name, nc.email);

For additional syntax regarding MERGE, refer to MERGE.

TRUNCATE

Overview

The ’TRUNCATE' command removes all entries from a table. Its syntax is as follows:

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

For additional syntax on TRUNCATE, refer to TRUNCATE.

Examples

Refer to the table below to conduct the TRUNCATE test.

        Table "public.t1"
  Column  |     Type     | Collation |  Nullable  |                 Default
--------+--------------+----------+----------+--------------------------------------
 id     | integer      |          | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |          |          |
 age    | smallint     |          |          |
 gender | character(1) |          |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id ASC)
 
tacnode=> SELECT * FROM t1;
 id |    name    | age | gender
----+------------+-----+--------
  1 | updatename |  11 | m
  2 | testname   |     |
  3 | updatename |  11 | f
  4 | updatename |  11 | f
  5 | testname4  |  22 | m
 11 | updatename |  11 | f
(6 rows)

Truncate the table.

tacnode=> TRUNCATE TABLE t1;
TRUNCATE TABLE
tacnode=> SELECT * FROM t1;
 id | name | age | gender
----+------+-----+--------
(0 row)

Notices

TRUNCATE functions like DELETE, yet it performs more quickly since it skips scanning the table.

VIEW

Overview

A view is fundamentally a stored query statement within a database. It functions as a virtual table without holding actual data. When we create a view, we assign it a name, allowing us to query it similarly to a table. A view does not exist physically, meaning a query executes each time the view is accessed. This approach is advantageous because it preserves only the query statement, alleviating the issues of constant data writing during frequent SQL calls.

Syntax:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name< [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

To explore additional syntax for VIEW, please check out CREATE VIEW, ALTER VIEW, and DROP VIEW.

Examples

Refer to the table below for conducting tests related to views.

tacnode=> \d t1
 id     | integer      |           | not null | nextval('t1_id_seq'::text::regclass)
 name   | text         |           |          |
 age    | smallint     |           |          |
 gender | character(1) |           |          |
 
tacnode=> SELECT * FROM t1;
  1 | updatename |  11 | m
  2 | aa         |  11 | m
  3 | bb         |  11 | m
  4 | ee         |  11 | f
  5 | ff         |  15 | f
  6 | gg         |  18 | m

Creating a View

You can add several tables in a SELECT statement, similar to how you would in a SQL SELECT query.

tacnode=> CREATE VIEW testview AS SELECT id, name, age, gender FROM t1 WHERE gender='m';
CREATE VIEW
tacnode=> SELECT * FROM testview;
  1 | updatename |  11 | m
  2 | aa         |  11 | m
  3 | bb         |  11 | m
  6 | gg         |  18 | m

Modify a view

tacnode=> \dv
 public | testview | view | tacadmin
 
tacnode=> ALTER VIEW testview RENAME TO testview1;
ALTER VIEW
 
tacnode=> \dv
 public | testview1 | view | tacadmin

Deleting a View

Remove the view by dropping it. Here’s an example:

tacnode=> DROP VIEW testview;
DROP VIEW

Query all views

You can access all user-defined view information via information_schema.tables or by using \dv.

tacnode=> SELECT * FROM information_schema.tables WHERE table_type = 'VIEW';
 tacnode    | public       | testview   | VIEW       | NO                 |       1
 
 tacnode=> \dv
 public | testview | view | tacadmin

Data Query, Update and Delete

On this page