pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDEFAULT Constraint based on table type?
5+ messages / 4 participants
[nested] [flat]
* DEFAULT Constraint based on table type?
@ 2005-11-28 20:22 Announce <truthhurts@insightbb.com>
0 siblings, 3 replies; 5+ messages in thread
From: Announce @ 2005-11-28 20:22 UTC (permalink / raw)
To: pgsql-sql
Lets say I have the following tables.
CREATE TABLE animals(id primary key, name varchar, type varchar);
CREATE TABLE dogs (breed varchar) INHERITS (animals);
CREATE TABLE birds (bool hasFeathers) INHERITS (animals);
Is there a way I can specify a default on the child table that will populate
the 'type' column? For example, if I am inserting a row in table DOGS, I
would always want the default value for column TYPE to be 'DOG'. If I am
inserting into BIRDS.... type 'BIRD'.
I know that I could add individual triggers on each table that set the TYPE
field to a default value on insert but I wanted a more simple solution like
setting a DEFAULT table-constraint.
Also, In java, this could be done on a parent object by overriding a
constructor or method, using the Class object or instanceof. Is there
anyway for a table to "know" it's "class" in this scenario?
Thanks,
-Aaron
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DEFAULT Constraint based on table type?
@ 2005-11-28 21:57 Rod Taylor <pg@rbt.ca>
parent: Announce <truthhurts@insightbb.com>
2 siblings, 0 replies; 5+ messages in thread
From: Rod Taylor @ 2005-11-28 21:57 UTC (permalink / raw)
To: Announce <truthhurts@insightbb.com>; +Cc: pgsql-sql
On Mon, 2005-11-28 at 14:22 -0600, Announce wrote:
> Lets say I have the following tables.
>
> CREATE TABLE animals(id primary key, name varchar, type varchar);
> CREATE TABLE dogs (breed varchar) INHERITS (animals);
> CREATE TABLE birds (bool hasFeathers) INHERITS (animals);
r=# alter table birds alter type set default 'Bird';
ALTER TABLE
r=# \d birds
Table "public.birds"
Column | Type | Modifiers
-------------+-------------------+-----------------------------------
id | integer | not null
name | character varying |
type | character varying | default 'Bird'::character varying
hasfeathers | boolean |
Inherits: animals
r=# \d animals
Table "public.animals"
Column | Type | Modifiers
--------+-------------------+-----------
id | integer | not null
name | character varying |
type | character varying |
Indexes:
"animals_pkey" PRIMARY KEY, btree (id)
--
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DEFAULT Constraint based on table type?
@ 2005-11-28 22:14 Jaime Casanova <systemguards@gmail.com>
parent: Announce <truthhurts@insightbb.com>
2 siblings, 0 replies; 5+ messages in thread
From: Jaime Casanova @ 2005-11-28 22:14 UTC (permalink / raw)
To: Announce <truthhurts@insightbb.com>; +Cc: pgsql-sql
On 11/28/05, Announce <truthhurts@insightbb.com> wrote:
> Lets say I have the following tables.
>
> CREATE TABLE animals(id primary key, name varchar, type varchar);
> CREATE TABLE dogs (breed varchar) INHERITS (animals);
> CREATE TABLE birds (bool hasFeathers) INHERITS (animals);
>
> Is there a way I can specify a default on the child table that will populate
> the 'type' column? For example, if I am inserting a row in table DOGS, I
> would always want the default value for column TYPE to be 'DOG'. If I am
> inserting into BIRDS.... type 'BIRD'.
>
> I know that I could add individual triggers on each table that set the TYPE
> field to a default value on insert but I wanted a more simple solution like
> setting a DEFAULT table-constraint.
>
> Also, In java, this could be done on a parent object by overriding a
> constructor or method, using the Class object or instanceof. Is there
> anyway for a table to "know" it's "class" in this scenario?
>
> Thanks,
>
> -Aaron
>
CREATE TABLE animals(id primary key, name varchar, type varchar);
CREATE TABLE dogs (breed varchar) INHERITS (animals);
ALTER TABLE dogs ALTER COLUMN type SET DEFAULT 'DOG';
CREATE TABLE birds (bool hasFeathers) INHERITS (animals);
ALTER TABLE birds ALTER COLUMN type SET DEFAULT 'BIRD';
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DEFAULT Constraint based on table type?
@ 2005-11-29 10:31 Oliver Elphick <olly@lfix.co.uk>
parent: Announce <truthhurts@insightbb.com>
2 siblings, 1 reply; 5+ messages in thread
From: Oliver Elphick @ 2005-11-29 10:31 UTC (permalink / raw)
To: Announce <truthhurts@insightbb.com>; +Cc: pgsql-sql
On Mon, 2005-11-28 at 14:22 -0600, Announce wrote:
> Lets say I have the following tables.
>
> CREATE TABLE animals(id primary key, name varchar, type varchar);
> CREATE TABLE dogs (breed varchar) INHERITS (animals);
> CREATE TABLE birds (bool hasFeathers) INHERITS (animals);
>
> Is there a way I can specify a default on the child table that will populate
> the 'type' column? For example, if I am inserting a row in table DOGS, I
> would always want the default value for column TYPE to be 'DOG'. If I am
> inserting into BIRDS.... type 'BIRD'.
>
> I know that I could add individual triggers on each table that set the TYPE
> field to a default value on insert but I wanted a more simple solution like
> setting a DEFAULT table-constraint.
>
> Also, In java, this could be done on a parent object by overriding a
> constructor or method, using the Class object or instanceof. Is there
> anyway for a table to "know" it's "class" in this scenario?
SELECT tableoid::regproc, * from animals;
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DEFAULT Constraint based on table type?
@ 2005-11-29 10:33 Oliver Elphick <olly@lfix.co.uk>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Oliver Elphick @ 2005-11-29 10:33 UTC (permalink / raw)
To: Announce <truthhurts@insightbb.com>; +Cc: pgsql-sql
On Tue, 2005-11-29 at 10:31 +0000, Oliver Elphick wrote:
> SELECT tableoid::regproc, * from animals;
regproc should be regclass; sorry.
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-11-29 10:33 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-28 20:22 DEFAULT Constraint based on table type? Announce <truthhurts@insightbb.com>
2005-11-28 21:57 ` Rod Taylor <pg@rbt.ca>
2005-11-28 22:14 ` Jaime Casanova <systemguards@gmail.com>
2005-11-29 10:31 ` Oliver Elphick <olly@lfix.co.uk>
2005-11-29 10:33 ` Oliver Elphick <olly@lfix.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox