Lists: | pgsql-general |
---|
From: | Andreas Fromm <Andreas(dot)Fromm(at)physik(dot)uni-erlangen(dot)de> |
---|---|
To: | List pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | add constraints to views |
Date: | 2003-09-20 10:19:53 |
Message-ID: | 3F6C29C9.5040607@physik.uni-erlangen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Please consider the following situation.
CREATE TABLE test_table (
~ id SERIAL PRIMARY_KEY,
~ tag BOOLEAN,
~ field1 INTEGER
);
CREATE VIEW test_view (
~ SELECT * FROM test_table WHERE tag
);
Now I want ad a NOT NULL constraint to the view on field1. I tryed the
following, but neither works.
ALTER TABLE test_view ADD CONSTRAINT isit CHECK ( field1 IS NOT NULL);
ALTER TABLE test_view ALTER field1 SET NOT NULL;
How can I do this?
Regards
Andreas
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Debian - http://enigmail.mozdev.org
iD8DBQE/bCnEPkvkZVZzNY0RAgiBAKChAPbr+RV1bTYX5+2vnCg/KU6k5ACfeGmd
/tbh47tLhPee5mAkFLzODZU=
=LDWj
-----END PGP SIGNATURE-----
From: | Gaetano Mendola <mendola(at)bigfoot(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org"(at)svr1(dot)postgresql(dot)org |
Cc: | Andreas Fromm <Andreas(dot)Fromm(at)physik(dot)uni-erlangen(dot)de> |
Subject: | Re: add constraints to views |
Date: | 2003-09-20 11:32:26 |
Message-ID: | 3F6C3ACA.3020409@bigfoot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Andreas Fromm wrote:
> CREATE VIEW test_view (
> ~ SELECT * FROM test_table WHERE tag
> );
>
> Now I want ad a NOT NULL constraint to the view on field1. I tryed the
> following, but neither works.
what does mean add a null constrain to a view ? Do you mean
filter out the records with the field1 null ?
CREATE OR REPLACE test_view AS
SELECT *
FROM test_table
WHERE field1 IS NOT NULL AND
tag;
Regards
Gaetano Mendola
From: | Andreas Fromm <Andreas(dot)Fromm(at)physik(dot)uni-erlangen(dot)de> |
---|---|
To: | List pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: add constraints to views |
Date: | 2003-09-20 14:02:28 |
Message-ID: | 3F6C5DF4.10006@physik.uni-erlangen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Gaetano Mendola wrote:
| Andreas Fromm wrote:
|
|> CREATE VIEW test_view (
|> ~ SELECT * FROM test_table WHERE tag
|> );
|>
|> Now I want ad a NOT NULL constraint to the view on field1. I tryed the
|> following, but neither works.
|
|
| what does mean add a null constrain to a view ? Do you mean
| filter out the records with the field1 null ?
|
| CREATE OR REPLACE test_view AS
| SELECT *
| FROM test_table
| WHERE field1 IS NOT NULL AND
| tag;
|
No, I mean that the view behaves like a table with the same columns as
table, but that restricts to records on wich tag is set. To insert a
record to this "special" table it requires to have field1 set. In other
words: A record of table is a record of view if tag is set. If tag is
set, then field1 has also have to have a value. If tag is not set, it
may have a value for field1, but will not show up in the view. Of course
I could achive this be triggers, but I thought it could be possible to
do via constraints on the view.
Regards
Andreas
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
Comment: Using GnuPG with Debian - http://enigmail.mozdev.org
iD8DBQE/bF30PkvkZVZzNY0RAqh8AJ0ZwagGrOhyuma/7gARKl1l35/wOACfVj9d
xYvyd2Pet25drqcv4vBE5eg=
=jXUQ
-----END PGP SIGNATURE-----
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andreas Fromm <Andreas(dot)Fromm(at)physik(dot)uni-erlangen(dot)de> |
Cc: | List pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: add constraints to views |
Date: | 2003-09-20 17:14:07 |
Message-ID: | 25410.1064078047@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Andreas Fromm <Andreas(dot)Fromm(at)physik(dot)uni-erlangen(dot)de> writes:
> No, I mean that the view behaves like a table with the same columns as
> table, but that restricts to records on wich tag is set. To insert a
> record to this "special" table it requires to have field1 set. In other
> words: A record of table is a record of view if tag is set. If tag is
> set, then field1 has also have to have a value. If tag is not set, it
> may have a value for field1, but will not show up in the view. Of course
> I could achive this be triggers, but I thought it could be possible to
> do via constraints on the view.
Constraints on a view are meaningless --- it has no real rows to
constrain. Put the constraints on the underlying table.
regards, tom lane