Thread: 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
from1 for 7) AS stryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth
ORDERBY 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)
ASstryearmonth, COUNT(id) AS countofnumber FROM (SELECT * from downloads ) AS foo GROUP BY stryearmonth ORDER BY
stryearmonthASC</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...