Re: [SQL] Outer joins
(damn gmail, sorry about priv mail earlier) Stephan Szabo <[EMAIL PROTECTED]>: I think putting a test in an ON clause associated with the join (using something like ... left join object_values ov1 ON(ov.obj_id = ov1.obj_id and ov1.att_id=8) rather than where will consider both as part of the join and null extend even if there are obj_id matches if none of those have att_id=8. Thanks, that worked! 2006/4/28, Tom Lane <[EMAIL PROTECTED]>: Emils <[EMAIL PROTECTED]> writes: > The table structure is: > object_values > == This isn't an outer-join problem, it's a crosstab problem. Try the crosstab functions in contrib/tablefunc. Thanks, I looked into it, unfortunately I might have to have also a solution for where installing contrib stuff is not possible. Emils ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Ignore, test of an alias
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Moving around in a SQL database
Florian, Thank you so much for your time. You have given me a whole lot to think about. I suspect you have defined most of the important aspects of the solution to my problem. I will go and implement this. Mit freundlichen Grüssen, Willem ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Multi-Column Constraints and Null Values
I have a problem in a number of my tables. I'd like to add uniqueness
constraints across multiple columns, but some of the columns are
nullable. This gives me trouble since when I create a constraint on
columns A and B.. I'd like the constraint to be enforced such that
you couldn't insert values ("value for A", null) twice. I understand
why the constraints work this way, but I'm wondering if anyone knows
of a workaround.
Feel free to spare me any "don't use nullable columns" responses. I
of course am aware that is an option. It's just one I'm hoping to
avoid as this is a legacy database and it would be a mammoth effort
to accomplish.
Thank you in advance!
Phill
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL]Linked List
I have a table that I created that implements a linked list. I am not an expert SQL developer and was wondering if there are known ways to traverse the linked lists. The table contains many linked lists based upon the head of the list and I need to extract all of the nodes that make up a list. The lists are simple with a item and a link to the history item so it goes kind of like: 1, 0 3, 1 7, 3 9, 7 ... Any suggestions would be helpful, or I will have to implement the table differently. Thanks Ray Madigan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Multi-Column Constraints and Null Values
On Thu, 27 Apr 2006, Phillip Tornroth wrote:
I have a problem in a number of my tables. I'd like to add uniqueness
constraints across multiple columns, but some of the columns are nullable.
This gives me trouble since when I create a constraint on columns A and B..
I'd like the constraint to be enforced such that you couldn't insert values
("value for A", null) twice. I understand why the constraints work this way,
but I'm wondering if anyone knows of a workaround.
Phill,
You likely want a multicolumn unique index created like so:
CREATE UNIQUE INDEX foo_A_B_unique_idx ON foo (A,B);
See the docs here:
http://www.postgresql.org/docs/current/interactive/indexes-unique.html
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [SQL] Multi-Column Constraints and Null Values
On Sat, Apr 29, 2006 at 13:14:36 -0700,
Jeff Frost <[EMAIL PROTECTED]> wrote:
> On Thu, 27 Apr 2006, Phillip Tornroth wrote:
>
> >I have a problem in a number of my tables. I'd like to add uniqueness
> >constraints across multiple columns, but some of the columns are nullable.
> >This gives me trouble since when I create a constraint on columns A and
> >B.. I'd like the constraint to be enforced such that you couldn't insert
> >values ("value for A", null) twice. I understand why the constraints work
> >this way, but I'm wondering if anyone knows of a workaround.
> >
>
> Phill,
>
> You likely want a multicolumn unique index created like so:
>
> CREATE UNIQUE INDEX foo_A_B_unique_idx ON foo (A,B);
>
> See the docs here:
> http://www.postgresql.org/docs/current/interactive/indexes-unique.html
Creating a multicolumn key is a simpler way of doing the same thing.
However, either way of doing the above doesn't completely solve his problem.
To block multiple entries where one of the columns is null he needs to
add extra checks. One way to do this is to add a partial index for each column
with the condition that the other column IS NULL. This will cover everything
but the case where both columns are null. If this case isn't allowed, then
a row constraint can be used to block it. If it is allowed, the only thing that
comes to mind is making an SQL function that counts the number of rows where
both columns are null and calling that function in a row constraint and
checking that the result is <= 1.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Multi-Column Constraints and Null Values
Bruno Wolff III <[EMAIL PROTECTED]> writes: > To block multiple entries where one of the columns is null he needs to > add extra checks. One way to do this is to add a partial index for each column > with the condition that the other column IS NULL. This will cover everything > but the case where both columns are null. If this case isn't allowed, then > a row constraint can be used to block it. If it is allowed, the only thing > that > comes to mind is making an SQL function that counts the number of rows where > both columns are null and calling that function in a row constraint and > checking that the result is <= 1. Nah, it's easy: CREATE UNIQUE INDEX fooi ON foo ((0)) WHERE col1 IS NULL AND col2 IS NULL; So you'd need a total of four unique indexes (3 of 'em partial) to enforce the condition for two columns. The real problem with this is that it doesn't scale well for more than two columns :-( regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
