Skip site navigation (1) Skip section navigation (2)

Re: INT array field referencing same table

From: "Gareth Kirwan" <gbjk(at)thermeoneurope(dot)com>
To: <gbjk(at)thermeoneurope(dot)com>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: INT array field referencing same table
Date: 2002-10-23 09:38:42
Message-ID: KMEELFAKJGHIGGNIDGHDEEADJPAA.gbjk@thermeoneurope.com (view raw or flat)
Thread:
Lists: pgsql-admin
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

In response to

Responses

pgsql-admin by date

Next:From: Stephan SzaboDate: 2002-10-23 13:10:18
Subject: Re: INT array field referencing same table
Previous:From: Gareth KirwanDate: 2002-10-23 09:29:55
Subject: Re: INT array field referencing same table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group