Database Reference
In-Depth Information
We create an instance of a chicken without adding it to the
chicken
table itself; hence
we're able to repeat
id
with impunity. We take our array of two chickens, stuff them into
one duck, and add it to the
ducks
table. We take the duck we added and stuff it into the
turkeys
table.
Finally, let's see what we have in our turkey:
SELECT
*
FROM
turkeys
;
output
------------------------
id | ducks
---+-----------------------
1 | {"(1,\"{(1),(1)}\")"}
We can also replace subelements of our turducken. This next example replaces our
second chicken in our first turkey with a different chicken:
UPDATE
turkeys
SET
ducks
[
1
].
chickens
[
2
]
=
ROW
(
3
)::
chickens
WHERE
id
=
1
RETURNING
*
;
output
--------------
id | ducks
---+-----------------------
1 | {"(1,\"{(1),(3)}\")"}
We used the
RETURNING
clause as discussed in
“Returning Affected Records to the
User” on page 128
to output the changed record.
PostgreSQL internally keeps track of object dependencies. The
ducks.chickens
column
is dependent on the
chickens
table. The
turkeys.ducks
column is dependent on the
ducks
table. You won't be able to drop the
chickens
table without specifying
CASCADE
or first dropping the
ducks.chickens
column. If you do a
CASCADE
, the
ducks.chick
ens
column will be gone, and without warning, your turkeys will have no chickens in
their ducks.
Building Custom Data Types
Although you can easily create composite types just by creating a table, at some point,
you'll probably want to build your own from scratch. For example, let's build a complex
number data type with the following statement:
CREATE
TYPE
complex_number
AS
(
r
double
precision
,
i
double
precision
);
We can then use this complex number as a column type:
CREATE
TABLE
circuits
(
circuit_id
serial
PRIMARY
KEY
,
ac_volt
complex_number
);
We can then query our table with statements such as:
SELECT
circuit_id
,
(
ac_volt
).
*
FROM
circuits
;