default values in inheritance hierarchies

Started by Miroslav Šimulčíkalmost 14 years ago3 messages
#1Miroslav Šimulčík
simulcik.miro@gmail.com

Hi,

I have two tables defined for example like this:

create table a1 (id serial primary key, data text);
create table a2 (id serial primary key, data2 integer) inherits (a1);

The point is, that I want to have two tables with inheritance relation
between them, but each with its own id column (overriden in child table).

Then I execute this sequence of commands:

insert into a1(data) values('abc');
insert into a2(data2, data) values(123, 'def');
update a1 set id = default, data = 'ghi';

I need new IDs on each update, because I store old rows in another table to
keep history of changes. Problem is that update uses values from sequence
belonging to table a1 when updating rows in table a2.

So the content of tables after operations is:

select * from only a1;
id | data
----+------
2 | ghi

select * from a2;
id | data | data2
----+------+-------
3 | ghi | 123

The following sequence of commands ends up wit

#2Miroslav Šimulčík
simulcik.miro@gmail.com
In reply to: Miroslav Šimulčík (#1)
Re: default values in inheritance hierarchies

Sorry, I have accidentaly sent incomplet mail.

Here is the rest:

The following sequence of commands will end up with error, because of
duplicate ID in table a2:

insert into a2(data2, data) values(456, 'jkl');
insert into a2(data2, data) values(789, 'mno');

Is there any way how to force UPDATE to use default value of ID column from
table a2 when updating rows in this table, without using triggers?

Thank you

Best regards
Miroslav Simulcik

2012/2/27 Miroslav Šimulčík <simulcik.miro@gmail.com>

Show quoted text

Hi,

I have two tables defined for example like this:

create table a1 (id serial primary key, data text);
create table a2 (id serial primary key, data2 integer) inherits (a1);

The point is, that I want to have two tables with inheritance relation
between them, but each with its own id column (overriden in child table).

Then I execute this sequence of commands:

insert into a1(data) values('abc');
insert into a2(data2, data) values(123, 'def');
update a1 set id = default, data = 'ghi';

I need new IDs on each update, because I store old rows in another table
to keep history of changes. Problem is that update uses values from
sequence belonging to table a1 when updating rows in table a2.

So the content of tables after operations is:

select * from only a1;
id | data
----+------
2 | ghi

select * from a2;
id | data | data2
----+------+-------
3 | ghi | 123

The following sequence of commands ends up wit

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Miroslav Šimulčík (#2)
Re: default values in inheritance hierarchies

=?ISO-8859-2?Q?Miroslav_=A9imul=E8=EDk?= <simulcik.miro@gmail.com> writes:

Is there any way how to force UPDATE to use default value of ID column from
table a2 when updating rows in this table, without using triggers?

No. The update is expanded using the default expression applicable to
the table that is named in the update command (ie, the parent).

regards, tom lane