Re: INT array field referencing same table

Gareth StationeryFurther information:

I've now tried this ( I would have tried it first but the server was having
it's nappy changed.
It seems to have a problem with the notion that the individual items of the
array are the references to the id, rather than the array itself.
How can I tell it this?
Am I missing something in the documentation that tells me how to do this?

Raw Error:
ERROR: Unable to identify an operator '=' for types 'integer[]' and
'integer'
You will have to retype this query using an explicit cast

I'll attempt to cast the related while I send this out ( I think I'll start
off trying related::INT )

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org
[mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of Gareth Kirwan
Sent: 23 October 2002 10:27
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] INT array field referencing same table

I just wanted to check that this is the right syntax for a table.
CREATE TABLE articles (
id INT PRIMARY KEY DEFAULT nextval('articles_seq'),
category INT,
type INT,
title VARCHAR(50),
description TEXT,
link INT,
date timestamp,
related INT [] ,
CONSTRAINT articles_categories_const FOREIGN KEY (category)
REFERENCES a_categories (id) ON DELETE CASCADE,
CONSTRAINT articles_types_const FOREIGN KEY (type) REFERENCES
a_types (id) ON DELETE CASCADE,
CONSTRAINT articles_links_const FOREIGN KEY (link) REFERENCES
a_links (id) ON DELETE CASCADE,
CONSTRAINT articles_related_const FOREIGN KEY (related) REFERENCES
articles (id)
);
The bit that I'm curious about is the related field.
Can I do it like this / Should I do it like this.
The array of integers should reference the id of the same table.

If anyone has any more general comments about the design, They'd be
welcome too :-)

Should I index the date field if i expect it to be searched upon regularly
???

Thanks

Regards

Gareth Kirwan
Programming & Development,
Thermeon Europe Ltd,
gbjk(at)thermeoneurope(dot)com
Tel: +44 (0) 1293 864 303
Thermeon Europe e-Card: gbjk