[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?
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?
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?
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?
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?
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?
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?
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
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
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
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
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
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
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?
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?
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
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
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
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
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
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
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
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
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
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
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
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
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?
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?
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?
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