Обсуждение: psql question

Hi,

The following 'SELECT' query fails in psql, but it works
in Oracle sqlplus ...

  mydb=# create table emp
  mydb-# (firstname varchar(255),lastname varchar(255),empno int);
  CREATE
  mydb=# insert into emp (firstname,lastname,empno) values ('John','Doe',1234);

  INSERT 44968 1
  mydb=# select empno from emp where ((firstname,lastname) IN
  mydb-# (('John','Doe'),('Joe','Blow')));
  ERROR:  parser: parse error at or near "'"
  ERROR:  parser: parse error at or near "'"

Whereas in sqlplus:

  SQL> select empno from emp where ((firstname,lastname) IN
    2  (('John','Doe'),('Joe','Blow')));

       EMPNO
  ----------
        1234

Can anyone tell me what syntax _will_ work in psql?

Many thx,
Mark

select empno from emp where firstname || lastname in ('JohnDoe', 'JoeBlow');

comes to mind...  If one of firstname or lastname is null, the result of the concatenation will be null and will not
matchanything, so you might want to use a case statement to catch that case and use an empty string. 

Are you stuck with that kind of construct?  It seems kind of... odd.

Ian

>>> Mark Tooker <mtooker@magma.ca> 11/08/02 04:10PM >>>
Hi,

The following 'SELECT' query fails in psql, but it works
in Oracle sqlplus ...

  mydb=# create table emp
  mydb-# (firstname varchar(255),lastname varchar(255),empno int);
  CREATE
  mydb=# insert into emp (firstname,lastname,empno) values ('John','Doe',1234);

  INSERT 44968 1
  mydb=# select empno from emp where ((firstname,lastname) IN
  mydb-# (('John','Doe'),('Joe','Blow')));
  ERROR:  parser: parse error at or near "'"
  ERROR:  parser: parse error at or near "'"

Whereas in sqlplus:

  SQL> select empno from emp where ((firstname,lastname) IN
    2  (('John','Doe'),('Joe','Blow')));

       EMPNO
  ----------
        1234

Can anyone tell me what syntax _will_ work in psql?

Many thx,
Mark

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On Fri, 2002-11-08 at 18:10, Mark Tooker wrote:
>   SQL> select empno from emp where ((firstname,lastname) IN
>     2  (('John','Doe'),('Joe','Blow')));
>
>        EMPNO
>   ----------
>         1234

Cool, I'll have to try that sometime. Didn't know you could make tuples
out of values like that.

> Can anyone tell me what syntax _will_ work in psql?

I think you need to use and/or to duplicate that.

select    empno
from      emp
where     (firstname = 'John' and lastname = 'Doe')
   or     (firstname = 'Joe' and lastname = 'Blow');

Not as terse, but it works.

Scott