[SQL] it's not NULL, then what is it?

2009-06-30 Thread Tena Sakai
Hi Everybody,

I have a table called gallo.sds_seq_reg_shw,
which is like:

  canon=# \d gallo.sds_seq_reg_shw
   Table "gallo.sds_seq_reg_shw"
Column  |  Type   | Modifiers 
  --+-+---
   name | text| 
   response | text| 
   n| integer | 
   source   | text| 
   test | text| 
   ref  | text| 
   value| real| 
   pvalue.term  | real| 
   stars.term   | text| 
   gtclass.test | text| 
   fclass.test  | text| 
   gtclass.ref  | text| 
   fclass.ref   | text| 
   markerid | integer | 
   maf  | real| 
   chromosome   | text| 
   physicalposition | integer | 
   id   | text| 
   ctrast   | text| 
  
I am intereseted in the column maf (which is real):

  canon=# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf asc;
   maf 
  -
   0.000659631
   0.000659631
   0.000659631
   0.000659631
.
  (trunacated for the interest of breivity)
.

Another way to look at this column is:

  canon=# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf desc;
   maf 
  -
  
  
  
.
  (trunacated for the interest of breivity)
.

These rows shown are blanks, as far as I can tell.
But...

  canon=# select count(maf) from gallo.sds_seq_reg_shw;
   count 
  ---
   67284
  (1 row)
  
  canon=# select count(maf) from gallo.sds_seq_reg_shw
  canon-#  where maf ISNULL;
   count 
  ---
   0
  (1 row)
  
  canon=# 
  canon=# select count(maf) from gallo.sds_seq_reg_shw
  canon-#  where maf NOTNULL;
   count 
  ---
   67284
  (1 row)

My confusion is that if they are real and not null,
what are they?  How would I construct a query to do
something like:

 select count(maf)
   from gallo.sds_seq_reg_shw
  where maf ISBLANK;

Thank you in advance.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


Re: [SQL] it's not NULL, then what is it?

2009-06-30 Thread Tena Sakai
Hi Rob,

> Maybe something like

> select ']' || maf::text || '[' -- just to see where the value
> start/stops

It prints many (1,132,691 to be exact) lines consisting of 7 space
characters followed by many lines like:
 ]0.0106383[
 ]0.0106383[
 ]0.0106383[


> or

> select length(maf::text)

This results in many lines of 7 space characters, followed by a
bunch of 9's, 10's, 8's...

> but I suspect you're getting NAN or something unprintable in your
> environment?

Yes, me too.  But,

  canon=# select maf
  canon-#   from gallo.sds_seq_reg_shw
  canon-#  where maf = NAN;
  ERROR:  column "nan" does not exist
  LINE 3:  where maf = NAN;
   ^
 
What can I put to the right of equal sign to make the query work?

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


-Original Message-
From: Rob Sargent [mailto:robjsarg...@gmail.com]
Sent: Tue 6/30/2009 3:24 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
 
Tena Sakai wrote:
>
> Hi Everybody,
>
> I have a table called gallo.sds_seq_reg_shw,
> which is like:
>
>   canon=# \d gallo.sds_seq_reg_shw
>Table "gallo.sds_seq_reg_shw"
> Column  |  Type   | Modifiers
>   --+-+---
>name | text|
>response | text|
>n| integer |
>source   | text|
>test | text|
>ref  | text|
>value| real|
>pvalue.term  | real|
>stars.term   | text|
>gtclass.test | text|
>fclass.test  | text|
>gtclass.ref  | text|
>fclass.ref   | text|
>markerid | integer |
>maf  | real|
>chromosome   | text|
>physicalposition | integer |
>id   | text|
>ctrast   | text|
>  
> I am intereseted in the column maf (which is real):
>
>   canon=# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf asc;
>maf
>   -
>0.000659631
>0.000659631
>0.000659631
>0.000659631
> .
>   (trunacated for the interest of breivity)
> .
>
> Another way to look at this column is:
>
>   canon=# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf desc;
>maf
>   -
>  
>  
>  
> .
>   (trunacated for the interest of breivity)
> .
>
> These rows shown are blanks, as far as I can tell.
> But...
>
>   canon=# select count(maf) from gallo.sds_seq_reg_shw;
>count
>   ---
>67284
>   (1 row)
>  
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf ISNULL;
>count
>   ---
>0
>   (1 row)
>  
>   canon=#
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf NOTNULL;
>count
>   ---
>67284
>   (1 row)
>
> My confusion is that if they are real and not null,
> what are they?  How would I construct a query to do
> something like:
>
>  select count(maf)
>from gallo.sds_seq_reg_shw
>   where maf ISBLANK;
>
> Thank you in advance.
>
> Regards,
>
> Tena Sakai
> tsa...@gallo.ucsf.edu
>
Maybe something like

select ']' || maf::text || '[' -- just to see where the value
start/stops

or

select length(maf::text)

but I suspect you're getting NAN or something unprintable in your 
environment?



Re: [SQL] it's not NULL, then what is it?

2009-06-30 Thread Tena Sakai
Hi Edward,

> Just out of curiosity did you try maf = 0?

Yes, and this is what I get:

  canon=# select maf
  canon-#   from gallo.sds_seq_reg_shw
  canon-#  where maf = 0;
   maf 
  -
  (0 rows)

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


-Original Message-
From: pgsql-sql-ow...@postgresql.org on behalf of Edward W. Rouse
Sent: Tue 6/30/2009 3:22 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
 
Just out of curiosity did you try maf = 0?

 

Edward W. Rouse

 

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of Tena Sakai
Sent: Tuesday, June 30, 2009 6:03 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] it's not NULL, then what is it?

 

Hi Everybody,

I have a table called gallo.sds_seq_reg_shw,
which is like:

  canon=# \d gallo.sds_seq_reg_shw
   Table "gallo.sds_seq_reg_shw"
Column  |  Type   | Modifiers
  --+-+---
   name | text|
   response | text|
   n| integer |
   source   | text|
   test | text|
   ref  | text|
   value| real|
   pvalue.term  | real|
   stars.term   | text|
   gtclass.test | text|
   fclass.test  | text|
   gtclass.ref  | text|
   fclass.ref   | text|
   markerid | integer |
   maf  | real|
   chromosome   | text|
   physicalposition | integer |
   id   | text|
   ctrast   | text|
 
I am intereseted in the column maf (which is real):

  canon=# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf asc;
   maf
  -
   0.000659631
   0.000659631
   0.000659631
   0.000659631
.
  (trunacated for the interest of breivity)
.

Another way to look at this column is:

  canon=# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf desc;
   maf
  -
 
 
 
.
  (trunacated for the interest of breivity)
.

These rows shown are blanks, as far as I can tell.
But...

  canon=# select count(maf) from gallo.sds_seq_reg_shw;
   count
  ---
   67284
  (1 row)
 
  canon=# select count(maf) from gallo.sds_seq_reg_shw
  canon-#  where maf ISNULL;
   count
  ---
   0
  (1 row)
 
  canon=#
  canon=# select count(maf) from gallo.sds_seq_reg_shw
  canon-#  where maf NOTNULL;
   count
  ---
   67284
  (1 row)

My confusion is that if they are real and not null,
what are they?  How would I construct a query to do
something like:

 select count(maf)
   from gallo.sds_seq_reg_shw
  where maf ISBLANK;

Thank you in advance.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu 




Re: [SQL] it's not NULL, then what is it?

2009-06-30 Thread Tena Sakai
Hi Steve,

> I believe count will only count not-null anyway
> so this will always return zero.

Understood.  But that doesn't help me...
What I need is a query expression that I can substitute
for isblabla below:

  select maf
from gallo.sds_seq_reg_shw
   where maf isblabla;

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


-Original Message-
From: Steve Crawford [mailto:scrawf...@pinpointresearch.com]
Sent: Tue 6/30/2009 3:39 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
 
...
>
>
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf ISNULL;
>count
>   ---
>0
>   (1 row)
>
I believe count will only count not-null anyway so this will always 
return zero. Try count(*) instead of count(maf). Here's an example:

st...@[local]=> select * from barr;
LOG:  duration: 0.226 ms
 a | b
---+---
 a | b
 c | d
   | e
(3 rows)

st...@[local]=> select coalesce(a, 'a is null'), coalesce(b, 'b is 
null') from barr;
LOG:  duration: 0.283 ms
 coalesce  | coalesce
---+--
 a | b
 c | d
 a is null | e
(3 rows)

st...@[local]=> select count(a) from barr;
LOG:  duration: 0.339 ms
 count
---
 2
(1 row)

st...@[local]=> select count(*) from barr where a isnull;
LOG:  duration: 0.350 ms
 count
---
 1
(1 row)





Re: [SQL] it's not NULL, then what is it?

2009-06-30 Thread Tena Sakai
Hi Osvaldo,

> Try:
> SELECT count(*) FROM gallo.sds_seq_reg_shw;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;

> Don't use count(maf), use count(*).

Indeed!

  canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw;
count  
  -
   4645647
  (1 row)

  canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
count  
  -
   4578363
  (1 row)

  canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;
   count 
  ---
   67284
  (1 row)

$ dc
4578363 67284 + p q
4645647
$ 

Many thanks, Osvald.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu




-Original Message-
From: Osvaldo Kussama [mailto:osvaldo.kuss...@gmail.com]
Sent: Tue 6/30/2009 6:49 PM
To: Tena Sakai
Subject: Re: [SQL] it's not NULL, then what is it?
 
2009/6/30 Tena Sakai :
> Hi Everybody,
>
> I have a table called gallo.sds_seq_reg_shw,
> which is like:
>
>   canon=# \d gallo.sds_seq_reg_shw
>    Table "gallo.sds_seq_reg_shw"
>     Column  |  Type   | Modifiers
>   --+-+---
>    name | text    |
>    response | text    |
>    n    | integer |
>    source   | text    |
>    test | text    |
>    ref  | text    |
>    value    | real    |
>    pvalue.term  | real    |
>    stars.term   | text    |
>    gtclass.test | text    |
>    fclass.test  | text    |
>    gtclass.ref  | text    |
>    fclass.ref   | text    |
>    markerid | integer |
>    maf  | real    |
>    chromosome   | text    |
>    physicalposition | integer |
>    id   | text    |
>    ctrast   | text    |
>
> I am intereseted in the column maf (which is real):
>
>   canon=# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf asc;
>    maf
>   -
>    0.000659631
>    0.000659631
>    0.000659631
>    0.000659631
>     .
>   (trunacated for the interest of breivity)
>     .
>
> Another way to look at this column is:
>
>   canon=# select maf from gallo.sds_seq_reg_shw
>   canon-#  order by maf desc;
>    maf
>   -
>
>
>
>     .
>   (trunacated for the interest of breivity)
>     .
>
> These rows shown are blanks, as far as I can tell.
> But...
>
>   canon=# select count(maf) from gallo.sds_seq_reg_shw;
>    count
>   ---
>    67284
>   (1 row)
>
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf ISNULL;
>    count
>   ---
>    0
>   (1 row)
>
>   canon=#
>   canon=# select count(maf) from gallo.sds_seq_reg_shw
>   canon-#  where maf NOTNULL;
>    count
>   ---
>    67284
>   (1 row)
>
> My confusion is that if they are real and not null,
> what are they?  How would I construct a query to do
> something like:
>
>  select count(maf)
>    from gallo.sds_seq_reg_shw
>   where maf ISBLANK;
>


Try:
SELECT count(*) FROM gallo.sds_seq_reg_shw;
SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;

Don't use count(maf), use count(*).

Osvaldo



Re: [SQL] it's not NULL, then what is it?

2009-06-30 Thread Tena Sakai
Hi Tom,

> What platform are you running on exactly?

It is redhat linux running on Dell hardware.
uname -a returns:
Linux vixen.egcrc.org 2.6.9-78.0.1.ELsmp #1 SMP Tue Jul 22 18:01:05 EDT 2008 
x86_64 x86_64 x86_64 GNU/Linux

> And for that matter, what PG version is this?

It is 8.3.6.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Tue 6/30/2009 7:17 PM
To: Tena Sakai
Cc: Edward W. Rouse; pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it? 
 
"Tena Sakai"  writes:
>>> My confusion is that if they are real and not null,
>>> what are they?

Good question.  So far as I can see these must be some value that
sprintf() is printing as spaces; but not NaN and not Infinity because
float4out checks for those first.  I would argue that this must be
a bug in sprintf.  What platform are you running on exactly?  And
for that matter, what PG version is this?

regards, tom lane



Re: [SQL] it's not NULL, then what is it?

2009-07-01 Thread Tena Sakai
Hi Rob,

> So they were null,

Yes!

> and null turns out to be a seven-character blank string!?

I don't understand how that happens.  Mr Tom Lane
hinted that it might be a bug in sprintf...

> Btw, you can change the displayed value of null with
> \pset null nil
> and you will seem 4+ million 'nil's in your output

That is an excellent trick/skill!

  canon=# \pset null nil
  Null display is "nil".
  canon=# 
  canon=# select maf from gallo.sds_seq_reg_shw
  canon-#  order by maf desc
  canon-#  limit 10;
   maf 
  -
   nil
   nil
   nil
   nil
   nil
   nil
   nil
   nil
   nil
   nil
  (10 rows)

  canon=# 

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu

-Original Message-
From: Rob Sargent [mailto:robjsarg...@gmail.com]
Sent: Wed 7/1/2009 9:36 AM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it?
 
So they were null, and null turns out to be a seven-character blank string!?

Btw, you can change the displayed value of null with
\pset null nil
and you will seem 4+ million 'nil's in your output


Tena Sakai wrote:
>
> Hi Osvaldo,
>
> > Try:
> > SELECT count(*) FROM gallo.sds_seq_reg_shw;
> > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> > SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;
>
> > Don't use count(maf), use count(*).
>
> Indeed!
>
>   canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw;
> count 
>   -
>4645647
>   (1 row)
>
>   canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> count 
>   -
>4578363
>   (1 row)
>
>   canon=# SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT 
> NULL;
>count
>   ---
>67284
>   (1 row)
>
> $ dc
> 4578363 67284 + p q
> 4645647
> $
>
> Many thanks, Osvald.
>
> Regards,
>
> Tena Sakai
> tsa...@gallo.ucsf.edu
>
>
>
>
> -Original Message-
> From: Osvaldo Kussama [mailto:osvaldo.kuss...@gmail.com]
> Sent: Tue 6/30/2009 6:49 PM
> To: Tena Sakai
> Subject: Re: [SQL] it's not NULL, then what is it?
>
> 2009/6/30 Tena Sakai :
> > Hi Everybody,
> >
> > I have a table called gallo.sds_seq_reg_shw,
> > which is like:
> >
> >   canon=# \d gallo.sds_seq_reg_shw
> >Table "gallo.sds_seq_reg_shw"
> > Column  |  Type   | Modifiers
> >   --+-+---
> >name | text|
> >response | text|
> >n| integer |
> >source   | text|
> >test | text|
> >ref  | text|
> >value| real|
> >pvalue.term  | real|
> >stars.term   | text|
> >gtclass.test | text|
> >fclass.test  | text|
> >gtclass.ref  | text|
> >fclass.ref   | text|
> >markerid | integer |
> >maf  | real|
> >chromosome   | text|
> >physicalposition | integer |
> >id   | text|
> >ctrast   | text|
> >
> > I am intereseted in the column maf (which is real):
> >
> >   canon=# select maf from gallo.sds_seq_reg_shw
> >   canon-#  order by maf asc;
> >maf
> >   -
> >0.000659631
> >0.000659631
> >0.000659631
> >0.000659631
> > .
> >   (trunacated for the interest of breivity)
> > .
> >
> > Another way to look at this column is:
> >
> >   canon=# select maf from gallo.sds_seq_reg_shw
> >   canon-#  order by maf desc;
> >maf
> >   -
> >
> >
> >
> > .
> >   (trunacated for the interest of breivity)
> > .
> >
> > These rows shown are blanks, as far as I can tell.
> > But...
> >
> >   canon=# select count(maf) from gallo.sds_seq_reg_shw;
> >count
> >   ---
> >67284
> >   (1 row)
> >
> >   canon=# select count(maf) from gallo.sds_seq_reg_shw
> >   canon-#  where maf ISNULL;
> >count
> >   ---
> >0
> >   (1 row)
> >
> >   canon=#
> >   canon=# select count(maf) from gallo.sds_seq_reg_shw
> >   canon-#  where maf NOTNULL;
> >count
> >   ---
> >67284
> >   (1 row)
> >
> > My confusion is that if they are real and not null,
> > what are they?  How would I construct a query to do
> > something like:
> >
> >  select count(maf)
> >from gallo.sds_seq_reg_shw
> >   where maf ISBLANK;
> >
>
>
> Try:
> SELECT count(*) FROM gallo.sds_seq_reg_shw;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NULL;
> SELECT count(*) FROM gallo.sds_seq_reg_shw WHERE maf IS NOT NULL;
>
> Don't use count(maf), use count(*).
>
> Osvaldo
>




Re: [SQL] it's not NULL, then what is it?

2009-07-01 Thread Tena Sakai
Many thanks, Tom.

I wish I had known "\pset null nil" trick.
It would have saved a few unnecessary emails.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Wed 7/1/2009 10:42 AM
To: Tena Sakai
Cc: Rob Sargent; pgsql-sql@postgresql.org
Subject: Re: [SQL] it's not NULL, then what is it? 
 
"Tena Sakai"  writes:
>> So they were null,

> Yes!

>> and null turns out to be a seven-character blank string!?

> I don't understand how that happens.  Mr Tom Lane
> hinted that it might be a bug in sprintf...

Well, that was before I read the messages where it turned out that they
were actually nulls after all.

regards, tom lane



[SQL] please help me on regular expression

2010-02-02 Thread Tena Sakai
Hi everybody,

I need a bit of help on postgres reqular expression.
With a table of the following definition:

   Table "tsakai.pheno"
  Column   |   Type| Modifiers
---+---+---
 subjectid | integer   | not null
 height| character varying | not null
 race  | character varying | not null
 blood | character varying | not null

I want to catch entries in height column that includes a
decimal point.  Here's my attempt:

 select subjectid, height
   from tsakai.pheno
  where height ~ '[:digit:]+.[:digit:]+';

Which returns 0 rows, but if I get rid of where clause,
I get rows like:

 subjectid | height
---+
 55379 | 70.5
 55383 | 69
 55395 | 70
 56173 | 71
 56177 | 65.5
 56178 | 70
   .  .
   .  .

And when I escape that dot after first plus sign with a backslash,
like this:
  where height ~ '[:digit:]+\.[:digit:]+';
then I get complaint:

WARNING:  nonstandard use of escape in a string literal
LINE 3: where height ~ '[:digit:]+\.[:digit:]+';
   ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.

From there, it was a downward spiral descent...

Please help.

Thank you.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


Re: [SQL] please help me on regular expression

2010-02-02 Thread Tena Sakai
Thank you kindly, Pavel.

Regards,

Tena Sakai


On 2/2/10 12:38 PM, "Pavel Stehule"  wrote:

> 2010/2/2 Tena Sakai :
>> Hi everybody,
>> 
>> I need a bit of help on postgres reqular expression.
>> With a table of the following definition:
>> 
>>           Table "tsakai.pheno"
>>  Column   |       Type        | Modifiers
>> ---+---+---
>>  subjectid | integer           | not null
>>  height    | character varying | not null
>>  race      | character varying | not null
>>  blood     | character varying | not null
>> 
>> I want to catch entries in height column that includes a
>> decimal point.  Here's my attempt:
>> 
>>  select subjectid, height
>>   from tsakai.pheno
>>  where height ~ '[:digit:]+.[:digit:]+';
>> 
>> Which returns 0 rows, but if I get rid of where clause,
>> I get rows like:
>> 
>>  subjectid | height
>> ---+
>>     55379 | 70.5
>>     55383 | 69
>>     55395 | 70
>>     56173 | 71
>>     56177 | 65.5
>>     56178 | 70
>>       .      .
>>       .      .
>> 
>> And when I escape that dot after first plus sign with a backslash,
>> like this:
>>  where height ~ '[:digit:]+\.[:digit:]+';
>> then I get complaint:
>> 
>> WARNING:  nonstandard use of escape in a string literal
>> LINE 3: where height ~ '[:digit:]+\.[:digit:]+';
>>                       ^
>> HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
>> 
>> From there, it was a downward spiral descent...
>> 
> 
> you have to use a prefix 'E' - E'some string with \backslash'
> 
> for your case the reg. expr could be
> 
> postgres=# select '70.5' ~ e'\\d+\.\\d+';
>  ?column?
> --
>  t
> (1 row)
> 
> http://www.postgresql.org/docs/8.1/static/functions-matching.html
> 
> or
> 
> postgres=# select '70.5' ~ e'[[:digit:]]+\.[[:digit:]]+';
>  ?column?
> --
>  t
> (1 row)
> 
> Regards
> Pavel Stehule
>> Please help.
>> 
>> Thank you.
>> 
>> Regards,
>> 
>> Tena Sakai
>> tsa...@gallo.ucsf.edu
>> 
>> 
>> 
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>> 


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


Re: [SQL] please help me on regular expression

2010-02-03 Thread Tena Sakai
Hi,

Thanks for your reply.
Indeed, why not?

Tena Sakai
tsa...@gallo.ucsf.edu


On 2/3/10 3:38 AM, "msi77"  wrote:

> Why not to use
> 
> select subjectid, height
>   from tsakai.pheno
>  where height like '%.%';
> 
> ?
> 
>> Hi everybody,
>> I need a bit of help on postgres reqular expression.
>> With a table of the following definition:
>> Table "tsakai.pheno"
>> Column | Type | Modifiers
>> ---+---+---
>> subjectid | integer | not null
>> height | character varying | not null
>> race | character varying | not null
>> blood | character varying | not null
>> I want to catch entries in height column that includes a
>> decimal point. Here's my attempt:
>> select subjectid, height
>> from tsakai.pheno
>> where height ~ '[:digit:]+.[:digit:]+';
>> Which returns 0 rows, but if I get rid of where clause,
>> I get rows like:
>> subjectid | height
>> ---+
>> 55379 | 70.5
>> 55383 | 69
>> 55395 | 70
>> 56173 | 71
>> 56177 | 65.5
>> 56178 | 70
>> . .
>> . .
>> And when I escape that dot after first plus sign with a backslash,
>> like this:
>> where height ~ '[:digit:]+\.[:digit:]+';
>> then I get complaint:
>> WARNING: nonstandard use of escape in a string literal
>> LINE 3: where height ~ '[:digit:]+\.[:digit:]+';
>> ^
>> HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
>> From there, it was a downward spiral descent...
>> Please help.
>> Thank you.
>> Regards,
>> Tena Sakai
>> tsa...@gallo.ucsf.edu
>> 
> 
> Здесь спама нет http://mail.yandex.ru/nospam/sign


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


Re: [SQL] please help me on regular expression

2010-02-03 Thread Tena Sakai
Thank you, Dirk.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


On 2/3/10 11:43 AM, "Dirk Jagdmann"  wrote:

> Be careful when working with backslashes and regular expressions for
> the proper (double) escaping!
> 
> # select '70a5' ~ e'\\d+\.\\d+';
>  ?column?
> --
>  t
> (1 row)
> 
> # select '70a5' ~ e'\\d+\\.\\d+';
>  ?column?
> --
>  f
> (1 row)
> 
> # select '70.5' ~ e'\\d+\\.\\d+';
>  ?column?
> --
>  t


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql


[SQL] field separator problem

2007-10-03 Thread Tena Sakai
Hi Everybody,

I am having a problem with field separator.  Maybe
someone can assist me.

But first thing firtst: I am running postgresql 8.2.4
on redhat, dell 64 bit machine:

I issue from psql \f (or "\pset fieldsep ','") and
psql appears to accept what I want:

  canon=# \f ','
  Field separator is ",".

canon is the name of database, but when I issue a select
command, it still uses '|' as separator.  What am I
doing wront?

Regards,

Tena Sakai
[EMAIL PROTECTED]




Re: [SQL] field separator problem

2007-10-03 Thread Tena Sakai
many thanks, Michael!  I appreciate it.

Regards,

Tena


-Original Message-
From: Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent: Wed 10/3/2007 5:54 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] field separator problem
 
On Wed, Oct 03, 2007 at 05:13:48PM -0700, Tena Sakai wrote:
> I issue from psql \f (or "\pset fieldsep ','") and
> psql appears to accept what I want:
> 
>   canon=# \f ','
>   Field separator is ",".
> 
> canon is the name of database, but when I issue a select
> command, it still uses '|' as separator.  What am I
> doing wront?

fieldsep applies only to unaligned mode (\a or \pset format unaligned).

-- 
Michael Fuhr



[SQL] what's wrong with my date comparison?

2007-10-16 Thread Tena Sakai
Hi Everybody,

I have a table with a column of timestamp type.  It is
known to postgres like this:

 name| character varying   | not null
 value   | character varying   | not null
 datecreated | timestamp without time zone | not null

when I do query

 select name, value, datecreated
   from mytable
  where datecreated > 2007-10-02;

it reports:

   name   |value |   datecreated   
--+--+-
 al_qual  | 0| 2007-08-09 00:06:06.742
 srehquan | 3| 2007-08-09 00:06:06.742
 complete | 1| 2007-08-09 00:06:06.743
 al_quan  | 0.315924933  | 2007-08-09 00:06:06.742
 bsa_qual | 0| 2007-08-09 00:06:06.743
 bsl_qual | 2| 2007-08-09 00:06:06.743
 sh_qual  | 0| 2007-08-09 00:06:06.742
. ..   .
. ..   .

I don't understand why it thinks August is greater than
October.  Can someone please elucidate what is going on?

Regards,

Tena Sakai
[EMAIL PROTECTED]



Re: [SQL] what's wrong with my date comparison?

2007-10-16 Thread Tena Sakai
Oooops!  I got it.
I was missing quotes.
It must have evaluated 2007-10-02 and used it as a
numerical constant 1995.

Sorry about commotion.

Tena


-Original Message-
From: [EMAIL PROTECTED] on behalf of Tena Sakai
Sent: Tue 10/16/2007 10:57 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] what's wrong with my date comparison?
 
Hi Everybody,

I have a table with a column of timestamp type.  It is
known to postgres like this:

 name| character varying   | not null
 value   | character varying   | not null
 datecreated | timestamp without time zone | not null

when I do query

 select name, value, datecreated
   from mytable
  where datecreated > 2007-10-02;

it reports:

   name   |value |   datecreated   
--+--+-
 al_qual  | 0| 2007-08-09 00:06:06.742
 srehquan | 3| 2007-08-09 00:06:06.742
 complete | 1| 2007-08-09 00:06:06.743
 al_quan  | 0.315924933  | 2007-08-09 00:06:06.742
 bsa_qual | 0| 2007-08-09 00:06:06.743
 bsl_qual | 2| 2007-08-09 00:06:06.743
 sh_qual  | 0| 2007-08-09 00:06:06.742
. ..   .
. ..   .

I don't understand why it thinks August is greater than
October.  Can someone please elucidate what is going on?

Regards,

Tena Sakai
[EMAIL PROTECTED]




[SQL] postgres bogged down beyond tolerance

2007-11-14 Thread Tena Sakai
Hi Everybody,

The postgres server I have (on redhat linux with recent
Dell hardware) is running terribly slow.

A job it should have gotten done in less than 1 hour
took 7.5 hours last night.  I checked kernel parameter
shmmax and it was set as 33554432.  I "fixed" it as
suggested by the manual:
http://www.postgresql.org/docs/8.2/static/kernel-resources.html

Namely, I shutdown the database, issued two commands:
  /sbin/sysctl -w kernel.shmmax=134217728
  /sbin/sysctl -w kernel.shmall=2097152
and rebooted the computer.

After it came up, I checked the shmmax and it is set
as 33554432.  Which surprised me.  Since I used -w
flag, I thought it should've written to /etc/sysctl.conf,
but there is no such entry at all and the data of this
file is from 2006.

Can somebody please give me a tip, insight as to what I
am missing, doing wrong?

Here's tail of serverlog file in my data directory:

[2007-11-14 08:53:48.062 PST] LOG:  unexpected EOF on client connection
[2007-11-14 08:53:59.001 PST] LOG:  unexpected EOF on client connection
[2007-11-14 08:54:10.782 PST] LOG:  unexpected EOF on client connection
[2007-11-14 08:54:22.557 PST] LOG:  unexpected EOF on client connection
[2007-11-14 08:54:34.282 PST] LOG:  unexpected EOF on client connection
[2007-11-14 09:13:36.444 PST] LOG:  unexpected EOF on client connection
[2007-11-14 09:13:43.637 PST] LOG:  unexpected EOF on client connection
[2007-11-14 09:17:16.242 PST] LOG:  unexpected EOF on client connection
[2007-11-14 09:39:22.841 PST] ERROR:  
relation "msysconf" does not exist
[2007-11-14 09:39:22.842 PST] STATEMENT:  SELECT Config, nValue FROM MSysConf

Many thanks in advance.

Regards,

Tena Sakai
[EMAIL PROTECTED]



Re: [SQL] Bit string help, please

2008-03-06 Thread Tena Sakai
Hi Ed,

I tried exactly what you did and it works for me.
My postgres is 8.3.0 running on redhat advanced
server.  This is what it told me:

prompt=# SELECT ('1' || repeat('0', 7))::bit varying;
  varbit  
--
 1000
(1 row)

prompt=#

Regards,

Tena Sakai
[EMAIL PROTECTED]



-Original Message-
From: [EMAIL PROTECTED] on behalf of [EMAIL PROTECTED]
Sent: Thu 3/6/2008 10:24 AM
To: [EMAIL PROTECTED]
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Bit string help, please
 
I tried this real quick at the psql command prompt, and unfortunately it
doesn't work:

mydb=# select ('1' || repeat('0',7))::bit varying;
ERROR:  cannot cast type text to bit varying

I appreciate the try though.  Any other ideas?  I am using PostgreSQL
8.2.3.1.  I don't know if that matters too much.

Thanks,
Ed
 

-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 06, 2008 12:14 AM
To: Tyrrill, Ed
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Bit string help, please

[EMAIL PROTECTED] wrote:
> 
> insert into table1 values( DEFAULT, B'1'::bit( n ) );
> 
> Where n is one of the parameters to the PL/pgSQL function, but that
> doesn't work.  PostgreSQL doesn't like having a variable for the bit
> string length.  Does anyone have any ideas how I could achieve this?

Try casting from a string:
   SELECT ('1' || repeat('0', n-1))::bit varying;


-- 
   Richard Huxton
   Archonet Ltd


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-sql



[SQL] compare 2 tables in sql

2008-03-19 Thread Tena Sakai
Hi Everybody,

Is there a sql way to compare (in a diff/cmp sense)
2 tables?  For example,

  create table foo as
  [select bla bla bla];

  create table moo as
  [select bla bla bla];

How would I go about knowing foo and moo are identical
(or not)?  Any pointer would be appreciated.

Tena


Re: [SQL] compare 2 tables in sql

2008-03-19 Thread Tena Sakai
Fantastic!  Many thanks.

Regards,

Tena Sakai


-Original Message-
From: [EMAIL PROTECTED] on behalf of Jonah H. Harris
Sent: Wed 3/19/2008 3:39 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] compare 2 tables in sql
 
On Wed, Mar 19, 2008 at 1:56 PM, Tena Sakai <[EMAIL PROTECTED]> wrote:
>  Is there a sql way to compare (in a diff/cmp sense)
>  2 tables?  For example,

SELECT * FROM foo
EXCEPT
SELECT * FROM moo;

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | [EMAIL PROTECTED]
Edison, NJ 08837 | http://www.enterprisedb.com/

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



Re: [SQL] compare 2 tables in sql

2008-03-20 Thread Tena Sakai
Hi Jonah,

> Still, you should UNION the result of
> both exceptions into a single result set.

Great suggestion.  Many thanks.

Regards,

Tena Sakai
[EMAIL PROTECTED]


-Original Message-
From: Jonah H. Harris [mailto:[EMAIL PROTECTED]
Sent: Thu 3/20/2008 12:21 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] compare 2 tables in sql
 
On Thu, Mar 20, 2008 at 1:44 PM, Tena Sakai <[EMAIL PROTECTED]> wrote:
>  Just a postscript.  It is important to check
>  both ways.  Because (sometimes) vice versa is
>  not necessarily true.  Case in point below:

Yes, I'm well aware of that.  Still, you should UNION the result of
both exceptions into a single result set.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | [EMAIL PROTECTED]
Edison, NJ 08837 | http://www.enterprisedb.com/



Re: [SQL] compare 2 tables in sql

2008-03-20 Thread Tena Sakai
Hi Jonah,

Just a postscript.  It is important to check
both ways.  Because (sometimes) vice versa is
not necessarily true.  Case in point below:


blitzen=> select * from foo
blitzen-> except
blitzen->select * from moo;
 alleleid | markerid | value | datecreated | datereplaced 
--+--+---+-+--
(0 rows)

blitzen=> 
blitzen=> select * from moo
blitzen-> except
blitzen->select * from foo;
  some_id | anothrid | value |   datecreated   |datereplaced 
--+--+---+-+-
  2892473 |  2810329 | t | 2008-03-12 14:37:18.165 | 3000-01-01 12:00:00
(1 row)


Regards,

Tena Sakai
[EMAIL PROTECTED]


-Original Message-
From: Jonah H. Harris [mailto:[EMAIL PROTECTED]
Sent: Wed 3/19/2008 3:39 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] compare 2 tables in sql
 
On Wed, Mar 19, 2008 at 1:56 PM, Tena Sakai <[EMAIL PROTECTED]> wrote:
>  Is there a sql way to compare (in a diff/cmp sense)
>  2 tables?  For example,

SELECT * FROM foo
EXCEPT
SELECT * FROM moo;

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | [EMAIL PROTECTED]
Edison, NJ 08837 | http://www.enterprisedb.com/



Re: [SQL] How to find double entries

2008-04-15 Thread Tena Sakai
Hi,

In a recent linux magazine article (http://www.linux-mag.com/id/5679)
there was a mentioning of Full-Text Search Integration.  Which I know
nothing about, but sounded interesting to me.  You might want to
check it out.

Regards,

Tena Sakai
[EMAIL PROTECTED]


-Original Message-
From: [EMAIL PROTECTED] on behalf of Andreas
Sent: Tue 4/15/2008 8:15 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] How to find double entries
 
Hi,

how can I find double entries in varchar columns where the content is 
not 100% identical because of a spelling error or the person considered 
it "looked nicer" that way?

I'd like to identify and then merge records of e.g.   'google', 'gogle', 
'guugle' 

Then I want to match abbrevations like  'A-Company Ltd.', 'a company 
ltd.', 'A-Company Limited'

Is there a way to do this?
It would be OK just to list candidats up to be manually checked afterwards.


Regards
Andreas

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



Re: [SQL] Enumerated (enum) types

2008-05-27 Thread Tena Sakai
Thank you!

Very educational and started a few wheels turning for
an application.

Would you please comment on enum's sql compatibility
and portability?

Regards,

Tena Sakai
[EMAIL PROTECTED]


-Original Message-
From: [EMAIL PROTECTED] on behalf of Michael Lourant
Sent: Tue 5/27/2008 7:29 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Enumerated (enum) types
 
Enumerated (enum) types are data types that are comprised of a static,
predefined set of values with a specific order. They are equivalent to the
enum types in a number of programming languages. An example of an enum type
might be the days of the week, or a set of status values for a piece of
data.

*Declaration of Enumerated Types*

Enum types are created using the CREATE TYPE command, for example:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Once created, the enum type can be used in table and function definitions
much like any other type:

*Example. Basic Enum Usage*

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
   name text,
   current_mood mood
);

INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood
--+--
 Moe  | happy
(1 row)

*Ordering*

The ordering of the values in an enum type is the order in which the values
were listed when the type was declared. All standard comparison operators
and related aggregate functions are supported for enums. For example:

*Example. Enum Ordering*

INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');
SELECT * FROM person WHERE current_mood > 'sad';
name  | current_mood
---+--
Moe   | happy
Curly | ok
(2 rows)

SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
 name  | current_mood
---+--
 Curly | ok
 Moe   | happy
(2 rows)

SELECT name FROM person
 WHERE current_mood = (SELECT MIN(current_mood) FROM person);
 name
---
 Larry
(1 row)

*Type Safety*

Enumerated types are completely separate data types and may not be compared
with each other.

*Example. Lack of Casting*

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (
   num_weeks int,
   happiness happiness
);
INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
ERROR:  invalid input value for enum happiness: "sad"
SELECT person.name, holidays.num_weeks FROM person, holidays
 WHERE person.current_mood = holidays.happiness;
ERROR:  operator does not exist: mood = happiness

If you really need to do something like that, you can either write a custom
operator or add explicit casts to your query:

*Example. Comparing Different Enums by Casting to Text*

SELECT person.name, holidays.num_weeks FROM person, holidays
 WHERE person.current_mood::text = holidays.happiness::text;
 name | num_weeks
--+---
 Moe  | 4
(1 row)

*Implementation Details*

An enum value occupies four bytes on disk. The length of an enum value's
textual label is limited by the NAMEDATALEN setting compiled into
PostgreSQL; in standard builds this means at most 63 bytes.

Enum labels are case sensitive, so 'happy' is not the same as 'HAPPY'.
Spaces in the labels are significant, too.

*An Alternative Way To Do The Same*

Instead of using an enum type we can set up a CHECK CONSTRAINT - this tells
postgresql to make sure that the value we are entering is valid.

CREATE TABLE person (
 personid int not null primary key,
 favourite_colour varchar(255) NOT NULL,
 CHECK (favourite_colour IN ('red', 'blue', 'yellow', 'purple'))
);

INSERT INTO person(personid, favourite_colour) VALUES (1, 'red');
INSERT 0 1

Now for something not in the list:

INSERT INTO person(personid, favourite_colour) VALUES (2, 'green');
ERROR: new row for relation "person" violates check constraint
"person_favourite_colour_check"


-- 
Michael Lourant
"All you need is love"



Re: [SQL] Results with leading zero

2008-06-16 Thread Tena Sakai
Hi,

Not to say which is better or worse, I find the use
of lpad() interesting as well:

select problem_id,
   lpad (cast(solution_count as varchar), 9, '0')
  from problem;

Regards,

Tena Sakai
[EMAIL PROTECTED]


-Original Message-
From: [EMAIL PROTECTED] on behalf of novice
Sent: Sun 6/15/2008 11:48 PM
To: A. Kretschmer
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Results with leading zero
 
Perfect!
Thank you very mcuh :)

2008/6/16 A. Kretschmer <[EMAIL PROTECTED]>:
> am  Mon, dem 16.06.2008, um 11:48:01 +1000 mailte novice folgendes:
>> I have a table
>>
>> CREATE TABLE problem (
>>   problem_id  integer,
>>   solution_count integer
>>   );
>>
>> INSERT INTO problem VALUES (1001, 4);
>> INSERT INTO problem VALUES (1012, 11);
>>
>> SELECT * from problem;
>>
>>  problem_id | solution_count
>> +---
>>1001 | 4
>>1012 |11
>> (2 rows)
>>
>>
>> Is there a way I could write a query to produce the following?  I will
>> need the leading zero for solution < 10
>>
>>  problem_id | solution
>> -+
>> 1001 | 01
>> 1001 | 02
>
> My previous answer was a little bit wrong (no leading zero for solution
> < 10), sorry. But no problem:
>
> select problem_id, to_char(generate_Series(1,solution_count),'09') as 
> solution_count from problem ;
>
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>



-- 
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



[SQL] a bit confused about distinct() function

2009-03-29 Thread Tena Sakai
Hi Everybody,

I am a bit confused about distinct() function.

I wrote a simple query like this:

 select subjectid, markerid, allele1id, allele2id
  from tsakai.mygenotype2
 where subjectid in (53684, 53688, 53699, 53700, 53704, 53705, 53713, 53714, 
53716, 53724)
   and
   markerid in  (1259501, 1259504, 1260210, 1260211, 1260212, 1260214, 
1260215, 1260238, 1260248, 1260562)
 order
by subjectid;

Here's what I got back:

  subjectid | markerid | allele1id | allele2id
 ---+--+---+---
  53684 |  1260214 |   2521543 |   2521543
  53684 |  1260214 |   2521543 |   2521543
  53684 |  1260215 |   2521537 |   2521538
  53688 |  1260562 |   2522243 |   2522243
  53688 |  1260562 |   2522243 |   2522243
  53699 |  1260562 |   2522243 |   2522243
  53699 |  1260214 |   2521543 |   2521544
  53699 |  1260214 |   2521543 |   2521544
  53704 |  1260215 |   2521537 |   2521537
  53714 |  1260214 |   2521543 |   2521543
 (10 rows)

Which is good, but seeing the duplicate rows in result
made me want to write:

 select distinct (subjectid, markerid, allele1id, allele2id)
   from tsakai.mygenotype2
  where subjectid in (53684, 53688, 53699, 53700, 53704, 53705, 53713, 53714, 
53716, 53724)
and
markerid in  (1259501, 1259504, 1260210, 1260211, 1260212, 1260214, 
1260215, 1260238, 1260248, 1260562)
  order
 by subjectid;

and what I got back was:
 ERROR:  could not identify an ordering operator for type record
 HINT:  Use an explicit ordering operator or modify the query.

Could somebody give me a tip as to what I could do
to get what I want?  Ie., I want get back is:

  subjectid | markerid | allele1id | allele2id
 ---+--+---+---
  53684 |  1260214 |   2521543 |   2521543
  53684 |  1260215 |   2521537 |   2521538
  53688 |  1260562 |   2522243 |   2522243
  53699 |  1260562 |   2522243 |   2522243
  53699 |  1260214 |   2521543 |   2521544
  53704 |  1260215 |   2521537 |   2521537
  53714 |  1260214 |   2521543 |   2521543

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


Re: [SQL] a bit confused about distinct() function

2009-03-29 Thread Tena Sakai
Many thanks, Osvaldo.

Regards,

Tena Sakai


-Original Message-
From: Osvaldo Kussama [mailto:osvaldo.kuss...@gmail.com]
Sent: Sun 3/29/2009 10:44 AM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] a bit confused about distinct() function
 
2009/3/29 Tena Sakai :
> Hi Everybody,
>
> I am a bit confused about distinct() function.
>
> I wrote a simple query like this:
>
>  select subjectid, markerid, allele1id, allele2id
>   from tsakai.mygenotype2
>  where subjectid in (53684, 53688, 53699, 53700, 53704, 53705, 53713, 53714,
> 53716, 53724)
>    and
>    markerid in  (1259501, 1259504, 1260210, 1260211, 1260212, 1260214,
> 1260215, 1260238, 1260248, 1260562)
>  order
>     by subjectid;
>
> Here's what I got back:
>
>   subjectid | markerid | allele1id | allele2id
>  ---+--+---+---
>   53684 |  1260214 |   2521543 |   2521543
>   53684 |  1260214 |   2521543 |   2521543
>   53684 |  1260215 |   2521537 |   2521538
>   53688 |  1260562 |   2522243 |   2522243
>   53688 |  1260562 |   2522243 |   2522243
>   53699 |  1260562 |   2522243 |   2522243
>   53699 |  1260214 |   2521543 |   2521544
>   53699 |  1260214 |   2521543 |   2521544
>   53704 |  1260215 |   2521537 |   2521537
>   53714 |  1260214 |   2521543 |   2521543
>  (10 rows)
>
> Which is good, but seeing the duplicate rows in result
> made me want to write:
>
>  select distinct (subjectid, markerid, allele1id, allele2id)
>    from tsakai.mygenotype2
>   where subjectid in (53684, 53688, 53699, 53700, 53704, 53705, 53713,
> 53714, 53716, 53724)
>     and
>     markerid in  (1259501, 1259504, 1260210, 1260211, 1260212, 1260214,
> 1260215, 1260238, 1260248, 1260562)
>   order
>  by subjectid;
>
> and what I got back was:
>  ERROR:  could not identify an ordering operator for type record
>  HINT:  Use an explicit ordering operator or modify the query.
>
> Could somebody give me a tip as to what I could do
> to get what I want?  Ie., I want get back is:
>
>   subjectid | markerid | allele1id | allele2id
>  ---+--+---+---
>   53684 |  1260214 |   2521543 |   2521543
>   53684 |  1260215 |   2521537 |   2521538
>   53688 |  1260562 |   2522243 |   2522243
>   53699 |  1260562 |   2522243 |   2522243
>   53699 |  1260214 |   2521543 |   2521544
>   53704 |  1260215 |   2521537 |   2521537
>   53714 |  1260214 |   2521543 |   2521543
>


Try:
SELECT DISTINCT subjectid, markerid, allele1id, allele2id
  FROM tsakai.mygenotype2
 WHERE subjectid IN (53684, 53688, 53699, 53700, 53704, 53705, 53713,
53714, 53716, 53724)
   AND markerid IN  (1259501, 1259504, 1260210, 1260211, 1260212,
1260214, 1260215, 1260238, 1260248, 1260562)
ORDER BY subjectid;

Osvaldo



Re: [SQL] a bit confused about distinct() function

2009-03-29 Thread Tena Sakai
Many thanks, Tom.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Sun 3/29/2009 10:49 AM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] a bit confused about distinct() function 
 
"Tena Sakai"  writes:
> I am a bit confused about distinct() function.

Your confusion is in thinking that DISTINCT is a function.  It is not;
it's just a keyword that modifies SELECT.  Write

select distinct subjectid, markerid, allele1id, allele2id
from tsakai.mygenotype2
...

The other syntax was being read as an implicit row constructor, like

select distinct row(subjectid, markerid, allele1id, allele2id)

which actually will work in PG 8.4, but does not in existing releases;
and you likely wouldn't like the output format anyway, since it would
just be one composite column.

regards, tom lane



[SQL] How would I get rid of trailing blank line?

2009-04-02 Thread Tena Sakai
Hi Everybody,

I am using postgres 8.3.4 on linux.
I often use a line like:
  psql -tf query.sql mydatabase > query.out

-t option gets rid of the heading and count
report at the bottom.  There is a blank line
at the bottom, however.  Is there any way to
have psql not give me that blank line?

Thank you for your help.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


Re: [SQL] How would I get rid of trailing blank line?

2009-04-02 Thread Tena Sakai
Hi Tom,

I am a bit surprised to hear that that '\n'
is there unconditionally.  But I am sure
there are more pressing things for you to
work on.  It's something I can live with.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Thu 4/2/2009 4:01 PM
To: Tena Sakai
Cc: pgsql-sql@postgresql.org; pgsql-hack...@postgresql.org
Subject: Re: [SQL] How would I get rid of trailing blank line? 
 
"Tena Sakai"  writes:
> I often use a line like:
>   psql -tf query.sql mydatabase > query.out

> -t option gets rid of the heading and count
> report at the bottom.  There is a blank line
> at the bottom, however.  Is there any way to
> have psql not give me that blank line?

Doesn't look like it --- the final fputc('\n', fout); seems to be
done unconditionally in all the output formats.  I wonder if we should
change that?  I'm afraid it might break programs that are used to it :-(

regards, tom lane



Re: [HACKERS] [SQL] How would I get rid of trailing blank line?

2009-04-02 Thread Tena Sakai
Hi Andrew,

> Right. There's a simple pipeline way to get rid of it:
>   psql -t -f query.sql | sed -e '$d' > query.out

Hi Scott,

> Tired of those blank lines in your text files?  Grep them away:
> psql -tf query.sql mydatabase | grep -v "^$" > query.out

Thank you Both.

Regards,

Tena Sakai
tsa...@gallo.ucsf.edu


-Original Message-
From: Andrew Dunstan [mailto:and...@dunslane.net]
Sent: Thu 4/2/2009 6:34 PM
To: Tom Lane
Cc: Tena Sakai; pgsql-sql@postgresql.org; pgsql-hack...@postgresql.org
Subject: Re: [HACKERS] [SQL] How would I get rid of trailing blank line?
 


Tom Lane wrote:
> "Tena Sakai"  writes:
>   
>> I often use a line like:
>>   psql -tf query.sql mydatabase > query.out
>> 
>
>   
>> -t option gets rid of the heading and count
>> report at the bottom.  There is a blank line
>> at the bottom, however.  Is there any way to
>> have psql not give me that blank line?
>> 
>
> Doesn't look like it --- the final fputc('\n', fout); seems to be
> done unconditionally in all the output formats.  I wonder if we should
> change that?  I'm afraid it might break programs that are used to it :-(
>
>
>   

Right. There's a simple pipeline way to get rid of it:

psql -t -f query.sql | sed -e '$d' > query.out


cheers

andrew