Database Reference
In-Depth Information
DELETE USING
Often, when you delete data from a table, you'll want to delete the data based on its
presence in another set of data. You can use the table or queries you added to the
USING
clause in the
WHERE
clause of the delete to control what gets deleted. Multiple items can
be included, separated by commas.
Example 7-14
deletes all records from
census.facts
that correspond to a fact type of
short_name = 's01'
.
Example 7-14. DELETE USING
DELETE
FROM
census
.
facts
USING
census
.
lu_fact_types
As
ft
WHERE
facts
.
fact_type_id
=
ft
.
fact_type_id
AND
ft
.
short_name
=
's01'
;
The standards-compliant way would be to use a clunkier
IN
expression in the
WHERE
clause.
Returning Affected Records to the User
The
RETURNING
clause is supported by ANSI SQL standards but not commonly found
in other relational databases. We show an example of it in
Example 7-30
, where we
return the records deleted.
RETURNING
can also be used for
INSERT
and
UPDATE
. For
inserts into tables with serial keys,
RETURNING
is invaluable because it returns the key
value of the new rows—something you don't know prior to the query execution. Al‐
though
RETURNING
is often accompanied by
*
for all fields, you can limit the fields as we
do in
Example 7-15
.
Example 7-15. Returning changed records of an UPDATE with RETURNING
UPDATE
census
.
lu_fact_types
AS
f
SET
short_name
=
replace
(
replace
(
lower
(
f
.
fact_subcats
[
4
]),
' '
,
'_'
),
':'
,
''
)
WHERE
f
.
fact_subcats
[
3
]
=
'Hispanic or Latino:'
AND
f
.
fact_subcats
[
4
]
>
''
RETURNING
fact_type_id
,
short_name
;
fact_type_id | short_name
--------------+-------------------------------------------------
96 | white_alone
97 | black_or_african_american_alone
98 | american_indian_and_alaska_native_alone
99 | asian_alone
100 | native_hawaiian_and_other_pacific_islander_alone
101 | some_other_race_alone
102 | two_or_more_races
Composite Types in Queries
PostgreSQL automatically creates data types of all tables. Because data types derived
from tables contain other data types, they are often called
composite data types
, or just
composites
. The first time you see a query with composites, you might be surprised. In
