Обсуждение: Q: inheritance
hi, I think I misunderstand the concept of inheritance. I was under the impression that inheriting from an existing table inherits all of the parent's columns. But: create table t1 (id serial primary key); create table t2 (num int) inherits (t1); create table t3 (t1 int references t1(id)); insert into t2 (id, num) values (1,1); -- so far , so good -- the next one fails: insert into t3 (t1) values (1); fails with an error (translated from german): insert or update in table "t3" violates foreign key constraint "t3_t1_fkey" DETAIL: key(t1)=(1) is not present in table "t1" but: select * from t2; id | num ----+----- 1 | 1 can anyone explain this behaviour? My database setup relies on inherited tables that share a primary key. cheers, Rüdiger.
Rüdiger Sörensen wrote: > can anyone explain this behaviour? My database setup relies on inherited > tables that share a primary key. I'm afraid inherited tables don't share a primary key. See the manuals for full details. -- Richard Huxton Archonet Ltd
On Thu, Dec 4, 2008 at 8:40 AM, Rüdiger Sörensen <soerense@mpch-mainz.mpg.de> wrote: > insert or update in table "t3" violates foreign key constraint "t3_t1_fkey" > DETAIL: key(t1)=(1) is not present in table "t1" > select * from t2; > id | num > ----+----- > 1 | 1 > can anyone explain this behaviour? Yes, PostgreSQL table inheritance is really just Horizontal Table partitioning with a nifty feature that makes the upper node table behave more like hierarchical UNION ALL views when SELECTed rather than actual tables. So in your case, the record you added to t2 doesn't really exits in t1 so referential integrity throws an error in t3. > My database setup relies on inherited > tables that share a primary key. In this case, I would recommend you use a vertically partitioned table design that simulates what you are trying to achieve. I recently developed a presentation on this subject if you are interested: http://wiki.postgresql.org/images/9/91/Pguswest2008hnd.pdf -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
Greetings!
I was going to make this a question, but I poked around a bit and came
up with an answer, which I'll share here in case anyone else is
interested.
I occasionally need to add test records to a database table. For
example, I want a new charge that is identical to charge 18000, so I
need coils in inventory that match those in charge 18000:
insert into inventory
select * from inventory where charge = 18000
The problem, of course, is that the inventory table has a unique key
constraint that gets violated. So, to do this, I'm going to have to
write an insert query that lists every field in this table (all 62 of
them), except for the primary key, which I'll have to force to something
I know is unique. I would like a database function that would generate
a string that would be a concatenation of all fields in a given table.
Then, I could use the resulting string as the starting point for
building an insert statement that will avoid the key field(s).
So, if I have a table named 'small_table' that contains columns
'column1', 'column2' and 'column3', I would be able to execute:
SELECT get_fields('small_table')
And I would get back:
'column1, column2, column3'.
Here's what I did:
-- Function: list_fields("varchar")
-- DROP FUNCTION list_fields("varchar")
CREATE OR REPLACE FUNCTION list_fields("varchar")
RETURNS "varchar" AS
$BODY$
declare
Tablename ALIAS for $1;
Attributes record;
Result varchar;
begin
Result := '';
FOR Attributes IN SELECT attname FROM pg_attribute
where attrelid = (select oid from pg_class where
relname = Tablename)
and attstattarget <> 0
LOOP
if length(Result) <> 0 then
Result = Result || ', ';
end if;
Result = Result || Attributes.attname;
END LOOP;
raise notice '%', Result;
return Result;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION list_fields("varchar") OWNER TO caps;
GRANT EXECUTE ON FUNCTION list_fields("varchar") TO caps;
GRANT EXECUTE ON FUNCTION list_fields("varchar") TO public;
2008/12/4, Rob Richardson <Rob.Richardson@rad-con.com>:
> ...
> The problem, of course, is that the inventory table has a unique key
> constraint that gets violated. So, to do this, I'm going to have to
> write an insert query that lists every field in this table (all 62 of
> them), except for the primary key, which I'll have to force to something
> I know is unique. I would like a database function that would generate
> a string that would be a concatenation of all fields in a given table.
> Then, I could use the resulting string as the starting point for
> building an insert statement that will avoid the key field(s).
>
Try:
CREATE OR REPLACE FUNCTION list_fields(text) RETURNS text AS
$BODY$
-- all attributes names, except those belonging primary key
SELECT array_to_string(
ARRAY(SELECT pa.attname FROM pg_attribute pa
JOIN pg_class pc ON (pa.attrelid = pc.oid)
WHERE pc.relname = $1
AND pa.attnum > 0
AND pa.attnum <> ALL ((SELECT pco.conkey FROM
pg_constraint pco
WHERE pco.conrelid = pa.attrelid
AND pco.contype =
'p')::smallint[])),
',');
$BODY$
LANGUAGE SQL STABLE;
Osvaldo
Hi Osvaldo,
Your list_fields function looked interesting to me so I tried it out and
it only worked for one of the five or so tables in the database I was
connected to at the time.
More concerning is the fact that I can't seem to drop it. I'm told it
doesn't exist, and then I use it to prove (to myself) that it does.
Here's it not working:
itidb=> select list_fields('joblistings');
-[ RECORD 1 ]-
list_fields |
Here's it working:
itidb=> select list_fields('joblist');
-[ RECORD 1 ]--------------------------------------------------
list_fields | full_name,username,password,recruiter,subscribed,...
Here's me trying to drop it, only to be told it doesn't exist:
itidb=> drop function list_fields();
ERROR: function list_fields() does not exist
And here's it working again!
itidb=> select list_fields('joblist');
-[ RECORD 1 ]--------------------------------------------------
list_fields | full_name,username,password,recruiter,subscribed,...
I'm noticing some very strange behaviour this evening (see thread
'Unique constaint violated without being violated').
Is my database corrupted or are there some vital database maintenance
tasks I've neglected to do?
I'm starting to get worried now.
Sebastian
On 06/12/2008 16:02, Sebastian Tennant wrote:
> Here's it working:
>
> itidb=> select list_fields('joblist');
<snip>
> Here's me trying to drop it, only to be told it doesn't exist:
>
> itidb=> drop function list_fields();
> ERROR: function list_fields() does not exist
You need to specify the argument types as well, so this -
drop function list_fields(varchar); -- or whatever it is
- ought to work.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
Quoth Raymond O'Donnell <rod@iol.ie>:
> On 06/12/2008 16:02, Sebastian Tennant wrote:
>> Here's it working:
>>
>> itidb=> select list_fields('joblist');
>
> <snip>
>
>> Here's me trying to drop it, only to be told it doesn't exist:
>>
>> itidb=> drop function list_fields();
>> ERROR: function list_fields() does not exist
>
> You need to specify the argument types as well, so this -
>
> drop function list_fields(varchar); -- or whatever it is
>
> - ought to work.
>
> Ray.
Man, am I'm feeling geriatric tonight! Thanks for clearing that up for
me Ray.
I guess this is what comes of working under pressure on a Saturday night
when I should be out having a quiet drink with a few friends.
Sebastian