On Sun, Jan 24, 2010 at 10:36 PM, Andreas Kretschmer
<akretschmer@xxxxxxxxxxxxx> wrote:
> Ovid <curtis_ovid_poe@xxxxxxxxx> wrote:
>
>> Assuming I have the following table:
>>
>> CREATE TABLE refers (
>> id SERIAL PRIMARY KEY,
>> name VARCHAR(255) NOT NULL,
>> parent_id INTEGER NOT NULL,
>> FOREIGN KEY (parent_id) REFERENCES refers(id)
>> );
>> I need to insert two records so that "select * from refers" looks like this:
>>
>> =# select * from refers;
>> id | name | parent_id
>> ----+------+-----------
>> 1 | xxxx | 1
>> 2 | yyy | 2
>
> I think you mean id=1, parent_id=2 and id=2, parent_id=1, or?
>
>>
>> The first record can't be inserted because I don't yet know the parent_id. The second record can be inserted after the first, but I since this is merely a large .sql file that I intend to shove into the PG, I'd much rather declare a variable in the script to get this done. I'm thinking something like the following pseudo-code:
>>
>> INSERT INTO refers (name, parent_id) VALUES ('xxxx', :id);
>> SELECT id INTO :parent_id FROM refers WHERE name='xxxx';
>> INSERT INTO refers (name, parent_id) VALUES ('yyy', :parent_id);
>>
>> Obviously the above is gibberish, but hopefully it makes clear what I'm trying to do :)
>>
>> Oh, and "parent_id" is NOT NULL because I hate the logical inconsistencies associated with NULL values.
>
> 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?
> test=*# insert into refers (name, parent_id) select 'yyy', id from refers where name = 'xxx';
> INSERT 0 1
> Zeit: 0,436 ms
> test=*# update refers set parent_id = (select id from refers where name = 'yyy') where name = 'xxx';
> UPDATE 1
> Zeit: 0,431 ms
> test=*# select * from refers;
> id | name | parent_id
> ----+------+-----------
> 2 | yyy | 1
> 1 | xxx | 2
> (2 Zeilen)
>
>
> The next release 9.0 contains (i hope) writes CTE, with this featue you can do:
>
> 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
> Time: 3,753 ms
> test=*#
>
> with
> t1 as (select nextval('refers_id_seq') as id),
> t2 as (insert into refers (id, name, parent_id) select nextval('refers_id_seq'), 'yyy', t1.id from t1 returning *),
> t3 as (insert into refers (id, name, parent_id) select t1.id, 'xxx', t2.id from t1, t2)
> select true;
> bool
> ------
> t
> (1 row)
>
> Time: 0,853 ms
> test=*# select * from refers;
> id | name | parent_id
> ----+------+-----------
> 2 | yyy | 1
> 1 | xxx | 2
> (2 rows)
>
>
> That's (the two insert's) are now one single statement ;-)
>
>
> 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°
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
to Ovid,
if you are going to create a tree in the table, you should certainly
allow the parent_id be null, otherwise, what would be the parent of
root? if you are meant to create a circle...god, what is that for? may
i have your story?
--
Best Regards,
David Shen
http://twitter.com/davidshen84/
http://meme.yahoo.com/davidshen84/
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
