Thread: domains, types, constraints
I'm having trouble with types and domains. I can create a type, but then when I try to create constraints
using the components of the type, I get syntax errors. I've read through the pdf several times, to no avail.
Things like (value).component, component(value), value::component, for example, all generate errors.
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Andrew Winkler <the_andrew_winkler@yahoo.com> writes:
> I'm having trouble with types and domains. I can create a type, but then when I try to create constraints
> using the components of the type, I get syntax errors. I've read through the pdf several times, to no avail.
> Things like (value).component, component(value), value::component, for example, all generate errors.
This is nearly content-free :-(. Show us exactly what commands you
tried and what errors you got. (Cut and paste from a terminal window
is good.)
regards, tom lane
Thanks for responding. Here's a simple example of the kind of thing I'm trying to do:
postgres=# drop type complex;
DROP TYPE
postgres=# create type complex as (r numeric, i numeric);
CREATE TYPE
postgres=# create domain unitary as complex;
ERROR: "complex" is not a valid base type for a domain
postgres=#
The idea in this example would be to create a domain of complex numbers where
r*r + i*i is not zero.
Are composite types not useable for domains?
----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Winkler <the_andrew_winkler@yahoo.com>
Cc: pgsql-novice@postgresql.org
Sent: Tuesday, January 15, 2008 8:55:34 PM
Subject: Re: [NOVICE] domains, types, constraints
Andrew Winkler <the_andrew_winkler@yahoo.com> writes:
> I'm having trouble with types and domains. I can create a type, but
then when I try to create constraints
> using the components of the type, I get syntax errors. I've read
through the pdf several times, to no avail.
> Things like (value).component, component(value), value::component,
for example, all generate errors.
This is nearly content-free :-(. Show us exactly what commands you
tried and what errors you got. (Cut and paste from a terminal window
is good.)
regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
Andrew Winkler <the_andrew_winkler@yahoo.com> writes:
> postgres=# create type complex as (r numeric, i numeric);
> CREATE TYPE
> postgres=# create domain unitary as complex;
> ERROR: "complex" is not a valid base type for a domain
> Are composite types not useable for domains?
Nope, sorry. We currently only support domains over base types.
I don't think anyone's even thought about doing the above, though
it does seem like a potentially useful thing.
regards, tom lane
What I wish I could do is something like this:
create domain value as ( amount numeric(30,9), expressedIn int references currency );
which I can't do because domains can't qualify composite types.
So what I'm trying to do instead is something like
create type value as ( amount numeric(30,9), expressedIn int);
create table values ( v value, foreign key ( v.expressedIn) references currency);
but all the ways I can think of trying it give me syntax errors. Am I out of luck?
My impression of the way the system is organized is that it's architected towards extensibility, so
I'd be interested in looking into what it would take to support this kind of thing, but since I've just
started browsing the sources, I'm not up to speed.
Since types are created automatically for tables, with table constraints added in, they are in particular
effectively domains of composite type, anyway, so all the necessary code is already there somewhere,
as is the necessary grammar. The big question of course, is whether that code is tightly coupled in its
place...
Any guesses at what I'd be looking at, on a scale from crazy-to-even-be-thinking-about-it to no-big-deal?
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
Can unique only reference columns? Am I not able to do what I'm trying to do here, or am I just being stupid
about how I'm trying to do it?
create type e_mail as (address text, domain text);
CREATE TYPE
create table e_mail_t ( e email, unique((e).address, (e).domain) );
ERROR: syntax error at or near "("
LINE 1: create table e_mail_t ( e email, unique((e).address, (e).dom...
^
create table e_mail_t ( e email, unique(e));
ERROR: data type email has no default operator class for access method "btree"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
ERROR: syntax error at or near ")"
LINE 1: )
____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs
Andrew Winkler <the_andrew_winkler@yahoo.com> writes:
> Can unique only reference columns?
Yup, that's what the SQL spec syntax says.
Instead, use CREATE UNIQUE INDEX.
regards, tom lane