Substring Problem

On Tue, May 13, 2008 at 03:26:57PM +0200, Stefan Schwarzer wrote:

it seems to work with 8.1, but not anymore with 8.3. What is wrong
with this substring request? Or is it some installation issue? Thanks
for any suggestion!
SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS
countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY
stryearmonth ORDER BY stryearmonth ASC

in release notes it says that 8.3 removes some implicit casts. for
example the ones from date to text.

change your substring to:
to_char(date, 'YYYY-MM')
and You should be fine.

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

On Tue, May 13, 2008 at 03:26:57PM +0200, Stefan Schwarzer wrote:

SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS
countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY
stryearmonth ORDER BY stryearmonth ASC

Another way of doing this is by using date_trunc, i.e.:

SELECT date_trunc('month',date) AS yearmonth...

I think it'll still realise it can use indexes (if they're appropriate)
that way.

Sam

<span style="font-family: Verdana">Type casting is required since 8.3, try<br /><br />SELECT substring(date :: varchar from 1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASC<br /><br />Bye...<br />Ludwig<span class="Apple-style-span" style="font-family: arial; font-size: 13px"><pre class="data" style="font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; font-size: 100%"><br /><br /><font class="Apple-style-span" color="#666666" face="verdana" size="3"><span class="Apple-style-span" style="font-size: 11px; white-space: normal">Hi there,</span></font></pre> <pre class="data"><font class="Apple-style-span" color="#666666" face="verdana" size="3"><span class="Apple-style-span" style="font-size:
11px; white-space: normal">it seems to work with 8.1, but not anymore with 8.3. What is wrong with this substring request? Or is it some installation issue? Thanks for any suggestion!</span></font></pre> <pre class="data" style="font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; font-size: 100%"><span class="Apple-style-span" style="color: #666666; font-family: verdana; font-size: 11px; white-space: normal">SELECT substring(date from 1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY stryearmonth ASC</span></pre> <pre class="data" style="font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; font-size: 100%"><font class="Apple-style-span" color="#666666" face="verdana" size="3"><span
class="Apple-style-span" style="font-size: 11px; white-space: normal">It says:</span></font></pre> <pre class="data" style="font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; font-size: 100%"><font class="Apple-style-span" color="#666666" face="verdana" size="3"><span class="Apple-style-span" style="font-size: 11px; white-space: normal"><span class="Apple-style-span" style="color: #000000; font-family: arial; font-size: 13px"><pre class="data" style="font-family: arial,tahoma,verdana,helvetica,sans-serif,serif; font-size: 100%">ERROR: function pg_catalog.substring(date, integer, integer) does not exist LINE 1: SELECT substring(date from 1 for 7) as stryearmonth, COUNT(i...</pre></span></span></font></pre></span></span>

ludwig@kni-online.de wrote:

Type casting is required since 8.3, try

SELECT substring(date :: varchar from 1 for 7) AS stryearmonth,

^^^^^^

sorry but this hurts and should not recommended. I think depesz
approach with to_string() and the correct format string is the
better solution. (think of datetyle oddities) - one of the reasons
I believe most of the implicit casts have gone ayway.

COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP
BY stryearmonth ORDER BY stryearmonth ASC

btw, whats the reason for the subselect?

T.

COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo
GROUP BY stryearmonth ORDER BY stryearmonth ASC

btw, whats the reason for the subselect?

Oh, right, looks a bit stupid like this. It's eventually being filled
with something more useful, upon the user's request. It's dynamically
(via PHP) extended to fulfill certain rules...