pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy this query does not work?
3+ messages / 3 participants
[nested] [flat]
* Why this query does not work?
@ 2003-05-12 17:55 Katka a Daniel Dunajsky <daniel_katka@hotmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Katka a Daniel Dunajsky @ 2003-05-12 17:55 UTC (permalink / raw)
To: pgsql-sql
This works:
service0=# SELECT DISTINCT TO_CHAR(session_start, 'Month') as start,
service0-# TO_CHAR(session_start, 'Month') as
end
service0-# FROM user_sessions
service0-# WHERE TO_CHAR(session_start, 'YYYY') = '2003';
start | end
-----------+-----------
April | April
(1 row)
Why this does not gives correct result?
service0=#
service0=# SELECT DISTINCT machine_id
service0-# FROM user_sessions
service0-# WHERE TO_CHAR(session_start, 'Month') = 'April';
machine_id
------------
(0 rows)
When this does have no problem?
service0=# SELECT DISTINCT machine_id
service0-# FROM user_sessions
service0-# WHERE TO_CHAR(session_start, 'Month') = (SELECT
DISTINCT TO_CHAR(session_start, 'Month')
service0(# FROM
user_sessions);
machine_id
------------
123
(1 row)
Thank you for your time.
DanielD
_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why this query does not work?
@ 2003-05-12 21:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Katka a Daniel Dunajsky <daniel_katka@hotmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-05-12 21:53 UTC (permalink / raw)
To: Katka a Daniel Dunajsky <daniel_katka@hotmail.com>; +Cc: pgsql-sql
"Katka a Daniel Dunajsky" <daniel_katka@hotmail.com> writes:
> Why this does not gives correct result?
You've missed the trailing blanks in TO_CHAR's result.
Probably you should be using FMMonth not Month as the format specifier.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Why this query does not work?
@ 2003-05-13 08:12 A.Bhuvaneswaran <bhuvansql@myrealbox.com>
parent: Katka a Daniel Dunajsky <daniel_katka@hotmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: A.Bhuvaneswaran @ 2003-05-13 08:12 UTC (permalink / raw)
To: Katka a Daniel Dunajsky <daniel_katka@hotmail.com>; +Cc: pgsql-sql
> service0=#
> service0=# SELECT DISTINCT machine_id
> service0-# FROM user_sessions
> service0-# WHERE TO_CHAR(session_start, 'Month') = 'April';
>
> machine_id
> ------------
> (0 rows)
>
Since to_char function pads space to the value returned. The length of the
return value of to_char function is 9. It is mentioned in the document,
please refer the below link for details:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-formatting.html
You can use rtrim to get rid of your problem.
=> select distinct machine_id from user_sessions where rtrim (to_char
(session_start, 'Month')) = 'April';
regards,
bhuvaneswaran
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-05-13 08:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-12 17:55 Why this query does not work? Katka a Daniel Dunajsky <daniel_katka@hotmail.com>
2003-05-12 21:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-05-13 08:12 ` A.Bhuvaneswaran <bhuvansql@myrealbox.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox