pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCHECK col A not NULL if col B='x'
6+ messages / 4 participants
[nested] [flat]
* CHECK col A not NULL if col B='x'
@ 2004-09-28 14:02 T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 2 replies; 6+ messages in thread
From: T E Schmitz @ 2004-09-28 14:02 UTC (permalink / raw)
To: pgsql-sql
Hello,
Is it possible to set up a table CHECK, which ensures that column A is
NOT NULL if column B = 'x' ?
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CHECK col A not NULL if col B='x'
@ 2004-09-28 14:12 Martin Marques <martin@bugs.unl.edu.ar>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 2 replies; 6+ messages in thread
From: Martin Marques @ 2004-09-28 14:12 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
El Mar 28 Sep 2004 11:02, T E Schmitz escribió:
> Hello,
>
> Is it possible to set up a table CHECK, which ensures that column A is
> NOT NULL if column B = 'x' ?
CONSTRAINT constraint_name ]
CHECK (expression)
CHECK (expression)
The CHECK clause specifies an expression producing a Boolean result which new
or updated rows must satisfy for an insert or update operation to succeed. A
check constraint specified as a column constraint should reference that
column's value only, while an expression appearing in a table constraint may
reference multiple columns.
So I would say that it should be:
CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL)
(use a logical table to build the correct logical expression)
--
11:05:01 up 16 days, 1:23, 4 users, load average: 1.26, 0.70, 1.04
-----------------------------------------------------------------
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-----------------------------------------------------------------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CHECK col A not NULL if col B='x'
@ 2004-09-28 14:15 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: T E Schmitz <mailreg@numerixtechnology.de>
1 sibling, 0 replies; 6+ messages in thread
From: Alexander M. Pravking @ 2004-09-28 14:15 UTC (permalink / raw)
To: T E Schmitz <mailreg@numerixtechnology.de>; +Cc: pgsql-sql
On Tue, Sep 28, 2004 at 03:02:02PM +0100, T E Schmitz wrote:
> Hello,
>
> Is it possible to set up a table CHECK, which ensures that column A is
> NOT NULL if column B = 'x' ?
Sure.
fduch@~=# CREATE TABLE test (
fduch@~(# a integer check (case when b = 'x' then a is not null else true end),
fduch@~(# b text);
CREATE TABLE
fduch@~=# INSERT INTO test VALUES (null, '123');
INSERT 107538 1
fduch@~=# INSERT INTO test VALUES (null, 'x');
ERROR: new row for relation "test" violates check constraint "test_a"
fduch@~=# INSERT INTO test VALUES (1, 'x');
INSERT 107539 1
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CHECK col A not NULL if col B='x'
@ 2004-09-28 14:38 T E Schmitz <mailreg@numerixtechnology.de>
parent: Martin Marques <martin@bugs.unl.edu.ar>
1 sibling, 0 replies; 6+ messages in thread
From: T E Schmitz @ 2004-09-28 14:38 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Hola Martin!
Martin Marques wrote:
> El Mar 28 Sep 2004 11:02, T E Schmitz escribió:
>
>>Is it possible to set up a table CHECK, which ensures that column A is
>>NOT NULL if column B = 'x' ?
>
> CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL)
This is brilliant. Only detected this today. Don't know how I managed to
overlook the CHECK constraints ;-)
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CHECK col A not NULL if col B='x'
@ 2004-09-28 15:01 T E Schmitz <mailreg@numerixtechnology.de>
parent: Martin Marques <martin@bugs.unl.edu.ar>
1 sibling, 1 reply; 6+ messages in thread
From: T E Schmitz @ 2004-09-28 15:01 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Hello again,
Martin Marques wrote:
> El Mar 28 Sep 2004 11:02, T E Schmitz escribió:
>
>>Is it possible to set up a table CHECK, which ensures that column A is
>>NOT NULL if column B = 'x' ?
>
>
> CONSTRAINT somename CHECK (B <> 'x' OR A IS NOT NULL)
I noticed a table constraint can be added via ALTER TABLE. Is it correct
that a column constraint cannot be added via the ALTER TABLE other than
by dropping and adding the column? (I am using psql 7.4.2.)
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: CHECK col A not NULL if col B='x'
@ 2004-09-28 22:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-09-28 22:36 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
T E Schmitz <mailreg@numerixtechnology.de> writes:
> I noticed a table constraint can be added via ALTER TABLE. Is it correct
> that a column constraint cannot be added via the ALTER TABLE other than
> by dropping and adding the column? (I am using psql 7.4.2.)
There is no difference between table and column constraints in PG. Just
write it as a table constraint in ALTER TABLE.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-09-28 22:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-28 14:02 CHECK col A not NULL if col B='x' T E Schmitz <mailreg@numerixtechnology.de>
2004-09-28 14:12 ` Martin Marques <martin@bugs.unl.edu.ar>
2004-09-28 14:38 ` T E Schmitz <mailreg@numerixtechnology.de>
2004-09-28 15:01 ` T E Schmitz <mailreg@numerixtechnology.de>
2004-09-28 22:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-28 14:15 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
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