Обсуждение: INT array field referencing same table

Поиск
Список
Период
Сортировка

INT array field referencing same table

От
"Gareth Kirwan"
Дата:
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@thermeoneurope.com
Tel: +44 (0) 1293 864 303
Thermeon Europe e-Card: gbjk

Re: INT array field referencing same table

От
"Gareth Kirwan"
Дата:
Correction:    The [] should have been after the INT, not the related :-)
Otherwise, as posted.
 
Gareth
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gareth Kirwan
Sent: 23 October 2002 10:27
To: pgsql-admin@postgresql.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@thermeoneurope.com
Tel: +44 (0) 1293 864 303
Thermeon Europe e-Card: gbjk

Re: INT array field referencing same table

От
"Gareth Kirwan"
Дата:
Further 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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of Gareth Kirwan
Sent: 23 October 2002 10:27
To: pgsql-admin@postgresql.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@thermeoneurope.com
Tel: +44 (0) 1293 864 303
Thermeon Europe e-Card: gbjk

Re: INT array field referencing same table

От
Stephan Szabo
Дата:
On Wed, 23 Oct 2002, Gareth Kirwan wrote:

> 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?

You can't tell it that. The types on both sides of a foreign key
constraint need to be of comparable types (in this case defined as
having an = operator).  int[] and int aren't comparable.  There's
occasionally been talk about making type[]->type foreign keys
use element in array rather than equality, but since type[] could
be a multidimensional array I'm not sure that's well defined.

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