Thread: constraints on composite types
This fails on 8.0.3 (syntax error at or near "." at character): CREATE TYPE ct AS ( foo INTEGER, bar INTEGER ); CREATE TABLE t1 ( attr ct, CONSTRAINT uq UNIQUE (attr.foo) ); Should it be possible? From reading http://www.postgresql.org/docs/current/static/rowtypes.html it looks like almost everything else works. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991
Roman Neuhauser wrote: > This fails on 8.0.3 (syntax error at or near "." at character): > > CREATE TYPE ct AS ( > foo INTEGER, > bar INTEGER > ); > > CREATE TABLE t1 ( > attr ct, > CONSTRAINT uq UNIQUE (attr.foo) > ); > > Should it be possible? From reading > http://www.postgresql.org/docs/current/static/rowtypes.html it looks > like almost everything else works. You might get somewhere with: CREATE OR REPLACE FUNCTION testfunc(ct) RETURNS int AS 'SELECT $1.foo;' LANGUAGE SQL IMMUTABLE; CREATE UNIQUE INDEX t1_b_uniq ON t1 (testfunc(b)); Seems to work on 8.1beta - haven't tried on version 8, but if the syntax is accepted I don't see why not. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> You might get somewhere with:
> CREATE OR REPLACE FUNCTION testfunc(ct) RETURNS int AS
> 'SELECT $1.foo;'
> LANGUAGE SQL IMMUTABLE;
> CREATE UNIQUE INDEX t1_b_uniq ON t1 (testfunc(b));
The point is that "attr.foo" is an expression, not a column name, and
the SQL spec allows UNIQUE and PRIMARY KEY only on bare column names.
I don't believe you need the function -- this should be enough:
CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo));
regards, tom lane
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > >>You might get somewhere with: > > >>CREATE OR REPLACE FUNCTION testfunc(ct) RETURNS int AS >>'SELECT $1.foo;' >>LANGUAGE SQL IMMUTABLE; > > >>CREATE UNIQUE INDEX t1_b_uniq ON t1 (testfunc(b)); > > > The point is that "attr.foo" is an expression, not a column name, and > the SQL spec allows UNIQUE and PRIMARY KEY only on bare column names. > I don't believe you need the function -- this should be enough: > > CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo)); I got: Relation "attr" does not exist (on 8.1 beta) -- Richard Huxton Archonet Ltd
On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote: > I don't believe you need the function -- this should be enough: > > CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo)); I was expecting that to work too, but it doesn't: ERROR: relation "attr" does not exist -- Michael Fuhr
# mike@fuhr.org / 2005-09-09 09:10:30 -0600:
> On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote:
> > I don't believe you need the function -- this should be enough:
> >
> > CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo));
>
> I was expecting that to work too, but it doesn't:
>
> ERROR: relation "attr" does not exist
The manual says something to the effect of (table.col).subcol,
I'll need that schema-qualified as well, IOW (schema.table.col).subcol
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
Richard Huxton <dev@archonet.com> writes:
> Tom Lane wrote:
>> I don't believe you need the function -- this should be enough:
>>
>> CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo));
> I got: Relation "attr" does not exist (on 8.1 beta)
Sorry, make that
CREATE UNIQUE INDEX t1_b_uniq ON t1 (((attr).foo));
regards, tom lane
On Fri, Sep 09, 2005 at 05:20:58PM +0200, Roman Neuhauser wrote: > # mike@fuhr.org / 2005-09-09 09:10:30 -0600: > > On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote: > > > I don't believe you need the function -- this should be enough: > > > > > > CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo)); > > > > I was expecting that to work too, but it doesn't: > > > > ERROR: relation "attr" does not exist > > The manual says something to the effect of (table.col).subcol, > I'll need that schema-qualified as well, IOW (schema.table.col).subcol This works: CREATE UNIQUE INDEX t1_b_uniq ON t1 (((attr).foo)); -- Michael Fuhr