Re: named generic constraints [feature request]

So last time I checked this wasn't possible (at least not that anyone
has told me). I'd like to be able to create constraints that aren't
tied to a specific table/column.

I think that the syntax would look something like this

CREATE CONSTRAINT empty CHECK (VALUE = '\0' );

this should allow us to do thinks like

CREATE TABLE users (
username TEXT NOT empty
);

the example from create domain (modified) is also pretty good

CREATE CONSTRAINT zip CHECK(
VALUE ~ '^\\d{5}$'
OR VALUE ~ '^\\d{5}-\\d{4}$'
);

--
Caleb Cushing

http://xenoterracide.blogspot.com

Hello

do you know domains? It is very similar to your proposal.

http://www.postgresql.org/docs/8.2/static/sql-createdomain.html

Regards
Pavel Stehule

2009/11/23 Caleb Cushing <xenoterracide(at)gmail(dot)com>:
> So last time I checked this wasn't possible (at least not that anyone
> has told me). I'd like to be able to create constraints that aren't
> tied to a specific table/column.
>
> I think that the syntax would look something like this
>
> CREATE CONSTRAINT empty CHECK (VALUE = '\0' );
>
> this should allow us to do thinks like
>
> CREATE TABLE users (
>              username TEXT NOT empty
> );
>

constraint cannot be part of expression.

CREATE OR REPLACE FUNCTION emptystr(text)
RETURNS bool AS $$
SELECT $1 <> ''; -- it is SQL not C
$$ LANGUAGE sql;

CREATE TABLE users(
username TEXT CHECK (NOT emptystr(username)),
...

p.s. Is it related to ANSI SQL?

Regards
Pavel Stehule

> the example from create domain (modified)  is also pretty good
>
> CREATE CONSTRAINT zip CHECK(
>   VALUE ~ '^\\d{5}$'
> OR VALUE ~ '^\\d{5}-\\d{4}$'
> );
>
> --
> Caleb Cushing
>
> http://xenoterracide.blogspot.com
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Mon, Nov 23, 2009 at 4:17 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> Hello
>
> do you know domains? It is very similar to your proposal.
>

obviously since I cited it.

> constraint cannot be  part of  expression.
>
why not? NOT NULL is a contraint, UNIQUE is a contstraint.

> CREATE OR REPLACE FUNCTION emptystr(text)
> RETURNS bool AS $$
>  SELECT $1 <> ''; -- it is SQL not C
> $$ LANGUAGE sql;
>
> CREATE TABLE users(
>  username TEXT CHECK (NOT emptystr(username)),
>  ...

this is probably the 'best' current solution. however, I'd like to be
able to not have to name the column for every constraint. and domains
only seem right if it's something, like a zip code, that has a very
specific set of rules, that is in reality it's own type. where
specifying something like 'empty' feels as generic (and arbitrary?) as
null. empty is not the only example (I'm sure), just the best I can
think of.

> p.s. Is it related to ANSI SQL?

not to my knowledge (can't say that it isn't though, I've never read
the standard).
--
Caleb Cushing

http://xenoterracide.blogspot.com

2009/11/23 Caleb Cushing <xenoterracide(at)gmail(dot)com>:
> On Mon, Nov 23, 2009 at 4:17 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> Hello
>>
>> do you know domains? It is very similar to your proposal.
>>
>
> obviously since I cited it.
>
>> constraint cannot be  part of  expression.
>>
> why not? NOT NULL is a contraint, UNIQUE is a contstraint.

yes - but you are defined constraint empty - not "not empty". for
example - there are not a constraint "NOT UNIQUE". I thing, so this
isn't workable. Constrainst are hard coded - it uses keywords. Your
new syntax is redundant - there are not any special value to using
CHECK clause and functions.

Regards
Pavel Stehule

>
>> CREATE OR REPLACE FUNCTION emptystr(text)
>> RETURNS bool AS $$
>>  SELECT $1 <> ''; -- it is SQL not C
>> $$ LANGUAGE sql;
>>
>> CREATE TABLE users(
>>  username TEXT CHECK (NOT emptystr(username)),
>>  ...
>
> this is probably the 'best' current solution.  however, I'd like to be
> able to not have to name the column for every constraint. and domains
> only seem right if it's something, like a zip code, that has a very
> specific set of rules, that is in reality it's own type. where
> specifying something like 'empty' feels as generic (and arbitrary?) as
> null. empty is not the only example (I'm sure), just the best I can
> think of.
>
>> p.s. Is it related to ANSI SQL?
>
> not to my knowledge (can't say that it isn't though, I've never read
> the standard).
> --
> Caleb Cushing
>
> http://xenoterracide.blogspot.com
>

On mån, 2009-11-23 at 12:50 -0500, Caleb Cushing wrote:
> and domains
> only seem right if it's something, like a zip code, that has a very
> specific set of rules, that is in reality it's own type.

A domain is not really its own type, it's a domain over its base type.
Hence the name.

> where
> specifying something like 'empty' feels as generic (and arbitrary?) as
> null.

The problem with your empty constraint is that it's data type specific,
and therefore the operator is also different depending on context. So
either you create a "named generic constraint" for every data type you
are interested in (in that case, see domains), or the thing could at
best work as a text substitution mechanism, which is something that SQL
typically doesn't do.

> empty is not the only example (I'm sure), just the best I can
> think of.

I doubt that there are any really good examples that cannot be solved
with the current facilities.

> CREATE OR REPLACE FUNCTION emptystr(text)
> RETURNS bool AS $$
>  SELECT $1 <> ''; -- it is SQL not C
> $$ LANGUAGE sql;
>
> CREATE TABLE users(
>  username TEXT CHECK (NOT emptystr(username)),

although I'm not going to continue discussing the request. this code
as the opposite desired effect. it should be

SELECT $1 = ''; -- you have a double negative

--
Caleb Cushing

http://xenoterracide.blogspot.com

Caleb,

I can understand why you want this. However, it would be tricky to
implement because of data typing, and is fairly easily worked around
using either domains or functions. So I don't think anyone is going to
want to add it to the TODO list, sorry.

Of course, Postgres is fully hackable if you *really* want it.

--Josh Berkus

2009/11/24 Caleb Cushing <xenoterracide(at)gmail(dot)com>:
>> CREATE OR REPLACE FUNCTION emptystr(text)
>> RETURNS bool AS $$
>>  SELECT $1 <> ''; -- it is SQL not C
>> $$ LANGUAGE sql;
>>
>> CREATE TABLE users(
>>  username TEXT CHECK (NOT emptystr(username)),
>
> although I'm not going to continue discussing the request. this code
> as the opposite desired effect. it should be
>
> SELECT $1 = ''; -- you have a double negative

no -

"--" is line comment in SQL - it same like "//" in C++

Regards
Pavel Stehule

>
> --
> Caleb Cushing
>
> http://xenoterracide.blogspot.com
>

> no -
>
> "--" is line comment in SQL - it same like "//" in C++

sorry didn't see this was updated. I know -- is a comment

I mean in sql <> means NOT your function name is emptystr which
implies it looks for an emptystr and returns true if the string is
found to be empty (at least in my mind). so if you want to create a
contrstraint of not empty you'd write NOT emptystr(col) however the
way you wrote it would only return true if the string was NOT <> empty
which is a double negative meaning that it is empty thereby rejecting
all but empty strings.

my final function that I wrote ended up looking like this (note: I
didn't specify to include whitespace in my original explanation.

CREATE OR REPLACE FUNCTION empty(TEXT)
RETURNS bool AS $$
SELECT $1 ~ '^[[:space:]]*$';
$$ LANGUAGE sql
IMMUTABLE;
COMMENT ON FUNCTION empty(TEXT)
IS 'Find empty strings or strings containing only whitespace';

which I'm using like this (note: this is not the full table)

CREATE TABLE users (
user_name TEXT NOT NULL
UNIQUE
CHECK ( NOT empty( user_name ))
);

I still wish I could write,something like

CREATE CONSTRAINT empty CHECK ( VALUE NOT ~ '^[[:space:]]*$';)

CREATE TABLE users (
user_name TEXT NOT NULL
UNIQUE
CHECK ( NOT empty )
);
CREATE TABLE roles (
role_name TEXT NOT NULL
UNIQUE
CHECK ( NOT empty)
);
--
Caleb Cushing

http://xenoterracide.blogspot.com

2009/12/7 Caleb Cushing <xenoterracide(at)gmail(dot)com>:
>> no -
>>
>> "--" is line comment in SQL - it same like "//" in C++
>
> sorry didn't see this was updated. I know -- is a comment
>
> I mean in sql <> means NOT your function name is emptystr which
> implies it looks for an emptystr and returns true if the string is
> found to be empty (at least in my mind). so if you want to create a
> contrstraint of not empty you'd write NOT emptystr(col) however the
> way you wrote it would only return true if the string was NOT <> empty
> which is a double negative meaning that it is empty thereby rejecting
> all but empty strings.
>
> my final function that I wrote ended up looking like this (note: I
> didn't specify to include whitespace in my original explanation.
>
>
>
>
> CREATE OR REPLACE FUNCTION empty(TEXT)
> RETURNS bool AS $$
>  SELECT $1 ~ '^[[:space:]]*$';
>     $$ LANGUAGE sql
>        IMMUTABLE;
> COMMENT ON FUNCTION empty(TEXT)
>        IS 'Find empty strings or strings containing only whitespace';
>
> which I'm using like this (note: this is not the full table)
>
>  CREATE TABLE users (
>        user_name       TEXT    NOT NULL
>                                UNIQUE
>                                CHECK ( NOT empty( user_name ))
> );
>
> I still wish I could write,something like
>
> CREATE CONSTRAINT empty CHECK ( VALUE NOT ~ '^[[:space:]]*$';)
>
> CREATE TABLE users (
>        user_name       TEXT    NOT NULL
>                                UNIQUE
>                                CHECK ( NOT empty )
> );
>  CREATE TABLE roles (
>    role_name       TEXT    NOT NULL
>                                UNIQUE
>                                CHECK ( NOT empty)

I understand. But I don't see any significant benefit for this
non-standard feature. You safe a few chars. I thing so it is useless.

Regards
Pavel Stehule

> );
> --
> Caleb Cushing
>
> http://xenoterracide.blogspot.com
>