Re: Reference and arrays

On Mar 18, 2005, at 13:35, Cima wrote:

> im using postgresql 8.0.1 and i want to be able to do the following:
>   
> create table t1 (name varchar(10), age int2);
>  
> create table t2 (position varchar(20), worker t1[]);
>  
> meaning i want to be  able to declare worker as an array of t1. if
> that is possible how do i insert into values t2.
>  after that can i make a reference to another table using the array.
> something like this:
>  
> create table t3 (month varchar(20), bday reference t1[]);
>  
> or  something as close to this as possible.

Cima,

It sounds like you're may not be using a relational database to its
full advantage. Relational databases are optimized to handling
relations (i.e., tables) of attributes, rather than arbitrary
collections of values and arrays. Most of the power of the dbms will be
available to you if you work with simpler table definitions (i.e., no
arrays). (An exception to this is when the data *only* makes sense as a
collection, such as a mathematical array that is being manipulated as a
whole).

I'd approach this a bit differently. Here's how I rewrote your table
definitions.

CREATE TABLE t1 (
     name VARCHAR(10)
     , age INT2
);

CREATE TABLE t1_5 (
     position VARCHAR(20)
);

CREATE TABLE t2 (
     name VARCHAR(10) REFERENCES t1 (name)
     , position VARCHAR(20) REFERENCES t1_5 (position)
);

I've added a table1_5 for holding information about positions in
general, and written t2 to hold information that shows which worker has
which position. (If a worker can only have one position, you don't even
need t2: just add position VARCHAR(20) REFERENCES t1_5 (position) to
t1. You'll still need t1_5, of course.)

If you need to find all workers who have a given position (which I
gather is what you're trying to represent with t2), you can easily
return this with a query such as

SELECT name
FROM t2
WHERE position = foo;

I'm not quite sure what your t3 is supposed to represent. I'd probably
add a "birthday" column to t1, so t1 will essentially be

CREATE TABLE t1 (
     name VARCHAR(10)
     , age INT2
     , birthday DATE
);

However, age can be calculated if you have the birthday (and you know
the current date), so really all you need is

CREATE TABLE t1 (
     name VARCHAR(10)
     , birthday DATE
);

Then you can get current ages using

SELECT name
     , AGE (birthday)
FROM t1;

or if you want only years:

SELECT name
     , EXTRACT ('years' FROM AGE(birthday)) AS age
FROM t1;

If you want to find all the workers who have a birthday in a given
month, you can use a query like

SELECT name
     , EXTRACT('month' FROM birthday) AS birthday_month
FROM t1;

You can find more information about the date and time functions in the
online documentaion:
http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

You might want to take a look at normalization, which is involved in
constructing tables efficiently for relational databases. There are a
lot of tutorials online that can help you with that.

Hope this helps.

Michael Glaesemann
grzm myrealbox com