Thread: WHERE parent IN (0,-1)
Hi, I'm pretty new to Postgres, so please excuse my ignorance. I have a sql script that uses WHERE parent IN (0,-1), this works fine with MySQL, Access, & MS-SQL Server, unfortunately Postgres does not like this statement, I can get around the problem (I think) by using WHERE parent IN (0,'-1'). Is this valid? Is there another way to do this that works with Postgres and the others? (They dont like the ' around -1) Thanks, Drew
> Hi, > > I'm pretty new to Postgres, so please excuse my ignorance. > > I have a sql script that uses WHERE parent IN (0,-1), this works fine with > MySQL, Access, & MS-SQL Server, unfortunately Postgres does not like this > statement, I can get around the problem (I think) by using WHERE parent IN > (0,'-1'). > > Is this valid? > > Is there another way to do this that works with Postgres and the others? > (They dont like the ' around -1) > > Thanks, > > Drew > > > yes, that's a good workaround. Added to TODO list: * select * from pg_class where oid in (0,-1); -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> I can get around the problem (I think) by using WHERE parent IN
>> (0,'-1').
> yes, that's a good workaround. Added to TODO list:
> * select * from pg_class where oid in (0,-1);
It's a grammar problem. in_expr_nodes and not_in_expr_nodes expect the
elements of the IN-list to be AexprConst ... I wonder why not a_expr
instead? Might be a reduce conflict, but I bet we could at least use
b_expr. Thomas, any comments?
regards, tom lane
> >> I can get around the problem (I think) by using WHERE parent IN
> >> (0,'-1').
> It's a grammar problem. in_expr_nodes and not_in_expr_nodes expect
> the elements of the IN-list to be AexprConst ... I wonder why not
> a_expr instead? Might be a reduce conflict, but I bet we could at
> least use b_expr. Thomas, any comments?
The immediate problem is with handling the minus sign; I should be
able to fix that (I had changed the handling of minus signs to get
better behavior with cases like "- 1" and "2-1" in other contexts). My
recollection is that SQL92 allows only lists of constants, and there
may have been shift/reduce problems with doing more than that. Will
poke at it in between getting Bruce's man pages going ;)
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
>> It's a grammar problem. in_expr_nodes and not_in_expr_nodes expect
>> the elements of the IN-list to be AexprConst ... I wonder why not
>> a_expr instead? Might be a reduce conflict, but I bet we could at
>> least use b_expr. Thomas, any comments?
Indeed, there is no reduce conflict created by using a_expr, so I went
ahead and committed it. Also tidied the list-generating code a bit.
Stuff like
select * from int4_tbl where f1 not in (0,123455+1);
seems to work fine now.
Drew, if you don't want to wait around for 6.6 to fix this, you
should be able to just change the occurrences of AexprConst to a_expr
in the productions for in_expr_nodes: and not_in_expr_nodes: in
src/backend/parser/gram.y. I wouldn't advise trying to copy the current
gram.y into 6.5, since there are a bunch of other changes in it already...
regards, tom lane
> >> It's a grammar problem. in_expr_nodes and not_in_expr_nodes expect
> >> the elements of the IN-list to be AexprConst ... I wonder why not
> >> a_expr instead? Might be a reduce conflict, but I bet we could at
> >> least use b_expr. Thomas, any comments?
> Indeed, there is no reduce conflict created by using a_expr, so I went
> ahead and committed it. Also tidied the list-generating code a bit.
Great. Did you fix all the places where IN (values) is allowed? I was
doing that, but will merge your fixes...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California