Thread: Reference and arrays
hi,
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.
thanx a lot!
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