Re: [SQL] NULLL comparison in multiple column unique index
On Thu, 02 Jan 2003 17:19:52 -0600, "Brian Walker"
<[EMAIL PROTECTED]> wrote:
>create table test1 (name varchar(64),num1 int,num2 int);
>create unique index idx1 on test1(name,num1);
>insert into idx1 values ('row3',null,22);
>insert into idx1 values ('row3',null,23);
>
>This is allowed to happen. In Microsoft SQL the second insert will
>fail because of the unique index. This looks like in MSSQL for the
>unique index checks that NULL is equal to NULL so the unique check
>fails. In PostgreSQL NULL != NULL so the unique check passes because
>even though the name is the same the "num1" field is different..
This is just one more issue where Postgres is standard compliant and
MS is not. Your problem has been discussed before:
http://archives.postgresql.org/pgsql-novice/2002-09/msg00062.php
>Does anybody have any ideas on how I can work around this difference?
Also read the other messages of that thread; thus you should get an
idea of possible solutions.
You wrote:
>In PostgreSQL NULL != NULL
While accurate enough for the context you used it in, it is not
completely exact. NULL = NULL is neither TRUE nor FALSE, it is
UNKNOWN. The same holds for NULL != NULL. Try
SELECT * FROM anytable WHERE NULL = NULL;
SELECT * FROM anytable WHERE NULL != NULL;
to illustrate this; you get 0 rows in both cases, even in MSSQL ;-).
What's relevant here is that NULL = NULL doesn't evaluate to TRUE,
which explains why rows containing NULL cannot violate a unique
constraint.
Servus
Manfred
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Query
Regards . I hope someone can help me in this query. Have a field in a table that needs to check if another table has the value that is being inserted. Ex: table A - id int constraint pkey_id primary key, table B - id int constraint fkey_A_B references A, Here comes my doubt: table C - id int constraint fkey_A_C references A check (if exists B.id = C.id) How can i build this expression so, when I insert a tupple in table C the field will check in the table A(ok, because is a foreign key) and also in table B I have tried : check (select count(b.id) from B b where b.id = id) <> 0) but doesn´t work .. I can use trigger here, but i don´t know if is the best solution . Thanks, Pedro Igor
Re: [SQL] Query
On Fri, 3 Jan 2003, Pedro Igor wrote: > Regards . > > I hope someone can help me in this query. > Have a field in a table that needs to check if another table has the value that is >being inserted. > Ex: > table A > - id int constraint pkey_id primary key, > > > table B > - id int constraint fkey_A_B references A, > > Here comes my doubt: > > table C > - id int constraint fkey_A_C references A check (if exists B.id > = C.id) > > How can i build this expression so, when I insert a tupple in > table C the field will check in the table A(ok, because is a foreign > key) and also in table B If you only want insert(/update) time checks (ie that deleting from B doesn't violate) then a trigger should work. I think a check constraint to a function that does the work will work as well. Subsellects in check constraints are a nasty problem that hasn't been solved yet (since those do imply the inverse constraint). Of course if you want two foreign keys you should be able to do that as well. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Compiling pl/pgsql functions?
Through preliminary examination, it appears stored procedures using pl/pgsql in PostgreSQL 7.3 are interpreted, rather than compiled? When we insert a stored function into PostgreSQL, the syntax check is not done until execution, and only on the branch/path of execution, not on the entire function. Is there any way to precompile the pl/pgsql functions so that we know if there's a missing object dependency upon function creation, rather than at runtime? Thanks in advance. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL to determine Tablenames, Primarykeys & Foreignkeys]
Thanks to previous help I am using: $sql = "SELECT " . "ic.relname AS index_name, " . "bc.relname AS tab_name, " . "ta.attname AS column_name, " . "i.indisunique AS unique_key, " . "i.indisprimary AS primary_key " . "FROM " . "pg_class bc, " . "pg_class ic, " . "pg_index i, " . "pg_attribute ta, " . "pg_attribute ia " . "WHERE " . "bc.oid = i.indrelid " . "AND ic.oid = i.indexrelid " . "AND ia.attrelid = i.indexrelid " . "AND ta.attrelid = bc.oid " . "AND bc.relname = '" . $tablename . "' " . "AND ta.attrelid = i.indrelid " . "AND ta.attnum = i.indkey[ia.attnum-1] " . "ORDER BY " . "index_name, tab_name, column_name"; which provides primary keys 100%. I have written the following to obtain tablenames: $sql = "SELECT " . "ic.relname " . "FROM " . "pg_class ic " . "WHERE " . "ic.relname not like 'pg%' " . "AND ic.relname not like '%pk' " . "AND ic.relname not like '%idx' "; which I am not certain is complete but appears to work. Could anyone help me with the SQL to retrieve Foreign Keys for a particular Table? Much appreciated. Kevin Gordon ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
