Re: Self-referential records

From: Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Self-referential records
Date: 2010-01-24 14:51:14
Message-ID: 20100124145114.GA9660@tux
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Xi Shen <davidshen84(at)googlemail(dot)com> wrote:

> > To handle that you can set the constzraint deferrable, initially
> > deferred:
> >
> > test=# CREATE TABLE refers ( id SERIAL  PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id INTEGER NOT NULL, FOREIGN KEY (parent_id) REFERENCES refers(id) deferrable initially deferred);
> > NOTICE:  CREATE TABLE will create implicit sequence "refers_id_seq" for serial column "refers.id"
> > NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "refers_pkey" for table "refers"
> > CREATE TABLE
> > Zeit: 25,599 ms
> > test=*# insert into refers (name, parent_id) values ('xxx',0);
> > INSERT 0 1
> > Zeit: 0,662 ms
>
> to Andreas,
>
> this 'deferrable' thing is really cool. but i have a question. at this
> point, where i insert this text, if i select the refers table, what
> would the parent_id looks like?

0, as i inserted:

test=*# insert into refers (name, parent_id) values ('xxx',0);
INSERT 0 1
Zeit: 0,636 ms
test=*# select * from refers;
id | name | parent_id
----+------+-----------
1 | xxx | 0
(1 Zeile)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Robert Haas 2010-01-24 14:54:21 Re: [HACKERS] Sugerencia de opcion
Previous Message Xi Shen 2010-01-24 14:48:03 Re: Self-referential records