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 ;
Search WWH ::




Custom Search