Thread

1. left() in postgres

   Peter Nixonn <listuser@peternixon.net> — 2002-10-25T16:31:17Z

   Hi Guys
   
   I am trying to do a query similar to the following:
   
   select left(field,3) from table;
    
   Now this works in Mysql/Access etc etc, but not in postgres
   (I am running PostgreSQL 7.2)
   
   Can anyone suggest how to do this in postgres? I have a database with many
   millions of records, and this functionality is essential for me.
   
   PS. I did do a google search etc before posting.
   
   Regards
   
   -- 
   
   Peter Nixon
   http://www.peternixon.net/
   PGP Key: http://www.peternixon.net/public.asc
   
   

2. Re: left() in postgres

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-25T16:36:24Z

   Try substring().
   
   ---------------------------------------------------------------------------
   
   Peter Nixon wrote:
   > Hi Guys
   > 
   > I am trying to do a query similar to the following:
   > 
   > select left(field,3) from table;
   >  
   > Now this works in Mysql/Access etc etc, but not in postgres
   > (I am running PostgreSQL 7.2)
   > 
   > Can anyone suggest how to do this in postgres? I have a database with many
   > millions of records, and this functionality is essential for me.
   > 
   > PS. I did do a google search etc before posting.
   > 
   > Regards
   > 
   > -- 
   > 
   > Peter Nixon
   > http://www.peternixon.net/
   > PGP Key: http://www.peternixon.net/public.asc
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 3: if posting/reading through Usenet, please send an appropriate
   > subscribe-nomail command to majordomo@postgresql.org so that your
   > message can get through to the mailing list cleanly
   > 
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 359-1001
     +  If your life is a hard drive,     |  13 Roberts Road
     +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
   
   

3. Re: left() in postgres

   Peter Nixonn <listuser@peternixon.net> — 2002-10-25T17:14:24Z

   I guess I don't understand how to use it properly. can anyone give me some
   more help. When I fire off the following query (at a database of radius
   records)
   
   
   SELECT substring('CalledStationId' from 2 for 3) FROM calls WHERE
   h323connecttime BETWEEN DATE'YESTERDAY' AND (DATE'YESTERDAY' + INTERVAL'1
   min');
   
   I get:
   
    substring
   -----------
    all
    all
    all
    all
    all
    all
    all
    all
   (8 rows)
   
   
   I if what I want is the behaviour mysql gives with left. ie.
   
   SELECT left(CalledStationId, 3) FROM calls WHERE h323connecttime BETWEEN
   DATE'YESTERDAY' AND (DATE'YESTERDAY' + INTERVAL'1 min');
   
   result
   -----------
   222
   222
   223
   234
   444
   777
   777
   778
   (8 rows)
   
   
   ie a list of area codes (which I then want to group on to give a sum per
   area code)
   
   Can someone suggest show me a cluestick here please?
   
   -Peter
   
   On Fri, 25 Oct 2002 12:36:24 -0400 (EDT)
   Bruce Momjian <pgman@candle.pha.pa.us> wrote:
   
   > 
   > Try substring().
   > 
   > ------------------------------------------------------------------------
   > ---
   > 
   > Peter Nixon wrote:
   > > Hi Guys
   > > 
   > > I am trying to do a query similar to the following:
   > > 
   > > select left(field,3) from table;
   > >  
   > > Now this works in Mysql/Access etc etc, but not in postgres
   > > (I am running PostgreSQL 7.2)
   > > 
   > > Can anyone suggest how to do this in postgres? I have a database with
   > > many millions of records, and this functionality is essential for me.
   > > 
   > > PS. I did do a google search etc before posting.
   

4. Re: left() in postgres

   Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-25T17:25:20Z

   You want to use double-quotes, not single quotes:
   
   > SELECT substring("CalledStationId" from 2 for 3) FROM calls WHERE
   > h323connecttime BETWEEN DATE'YESTERDAY' AND (DATE'YESTERDAY' + INTERVAL'1
   > min');
   
   ---------------------------------------------------------------------------
   
   Peter Nixon wrote:
   -- Start of PGP signed section.
   > I guess I don't understand how to use it properly. can anyone give me some
   > more help. When I fire off the following query (at a database of radius
   > records)
   > 
   > 
   > SELECT substring('CalledStationId' from 2 for 3) FROM calls WHERE
   > h323connecttime BETWEEN DATE'YESTERDAY' AND (DATE'YESTERDAY' + INTERVAL'1
   > min');
   > 
   > I get:
   > 
   >  substring
   > -----------
   >  all
   >  all
   >  all
   >  all
   >  all
   >  all
   >  all
   >  all
   > (8 rows)
   > 
   > 
   > I if what I want is the behaviour mysql gives with left. ie.
   > 
   > SELECT left(CalledStationId, 3) FROM calls WHERE h323connecttime BETWEEN
   > DATE'YESTERDAY' AND (DATE'YESTERDAY' + INTERVAL'1 min');
   > 
   > result
   > -----------
   > 222
   > 222
   > 223
   > 234
   > 444
   > 777
   > 777
   > 778
   > (8 rows)
   > 
   > 
   > ie a list of area codes (which I then want to group on to give a sum per
   > area code)
   > 
   > Can someone suggest show me a cluestick here please?
   > 
   > -Peter
   > 
   > On Fri, 25 Oct 2002 12:36:24 -0400 (EDT)
   > Bruce Momjian <pgman@candle.pha.pa.us> wrote:
   > 
   > > 
   > > Try substring().
   > > 
   > > ------------------------------------------------------------------------
   > > ---
   > > 
   > > Peter Nixon wrote:
   > > > Hi Guys
   > > > 
   > > > I am trying to do a query similar to the following:
   > > > 
   > > > select left(field,3) from table;
   > > >  
   > > > Now this works in Mysql/Access etc etc, but not in postgres
   > > > (I am running PostgreSQL 7.2)
   > > > 
   > > > Can anyone suggest how to do this in postgres? I have a database with
   > > > many millions of records, and this functionality is essential for me.
   > > > 
   > > > PS. I did do a google search etc before posting.
   -- End of PGP section, PGP failed!
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 359-1001
     +  If your life is a hard drive,     |  13 Roberts Road
     +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
   
   

5. Re: left() in postgres

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-10-25T17:26:01Z

   On Fri, 25 Oct 2002, Peter Nixon wrote:
   
   > I guess I don't understand how to use it properly. can anyone give me some
   > more help. When I fire off the following query (at a database of radius
   > records)
   >
   >
   > SELECT substring('CalledStationId' from 2 for 3) FROM calls WHERE
   
   I think you'd want something like:
   substring(CalledStationId from 1 for 3)
   
   Putting it in single quotes makes it a literal, you were asking for
   positions 2-4 in the literal string 'CalledStationId' => 'all'.
   
   
   
   

6. Re: left() in postgres

   PD Miller <millerp@caribdata.co.uk> — 2002-10-25T17:26:58Z

   At 20:14 +0300 25/10/02, Peter Nixon wrote:
   >I guess I don't understand how to use it properly. can anyone give me some
   >more help. When I fire off the following query (at a database of radius
   >records)
   >
   >SELECT substring('CalledStationId' from 2 for 3) FROM calls WHERE
   >h323connecttime BETWEEN DATE'YESTERDAY' AND (DATE'YESTERDAY' + INTERVAL'1
   >min');
   
   Change this:
   
   SELECT substring('CalledStationId' from 2 for 3)
                     ^               ^
   
   to this:
   
   SELECT substring(CalledStationId from 2 for 3)
   
   In the example you have given, CalledStationID is a literal instead 
   of a column.
   
   The syntax for substr would be:
   
   SELECT substr(CalledStationId', 2, 3)
   
   Regards
   
   Paul Miller
   -- 
   -
   Carib Data Limited
   
   <mailto:millerp@caribdata.co.uk>
   <http://www.caribdata.co.uk>
   
   

7. Re: left() in postgres

   David Blood <david@matraex.com> — 2002-10-25T20:25:03Z

   I was going to suggest this and decided to do a little testing before
   and found that the left function was much slower that just using the
   substr() function.  I have not used function much.  Is it often the case
   that functions are slower when doing simple things like providing
   syntactic sugar?
   
   David Blood
   Matraex, Inc
   
   
   -----Original Message-----
   From: pgsql-general-owner@postgresql.org
   [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Gyorgy Molnar
   Sent: Friday, October 25, 2002 3:58 PM
   To: Bruce Momjian; Peter Nixon
   Cc: pgsql-general@postgresql.org
   Subject: Re: [GENERAL] left() in postgres
   
   Dear Peter,
   
   Yes, indeed the substring() is a very good soloution. Actually it is the
   only suitable function in the PG standard library that can help you.
   Based on the substring we could make our own left() and right()
   functions
   using pgplsql.  Comapre to substring(), they will only provide you a
   more
   convinient interface, nothing more nothing less.
   
   The two functions:
   
   DROP FUNCTION left(TEXT, INTEGER);
   CREATE FUNCTION left(TEXT, INTEGER) RETURNS TEXT AS '
   DECLARE
   BEGIN
       RETURN substr($1, 1, $2);
   END;
   ' LANGUAGE 'plpgsql';
   
   DROP FUNCTION right(TEXT, INTEGER);
   CREATE FUNCTION right(TEXT, INTEGER) RETURNS TEXT AS '
   DECLARE
   BEGIN
       RETURN substr($1, char_length($1) - $2 + 1, $2);
   END;
   ' LANGUAGE 'plpgsql';
   
   After when you are installed the plpgsql language extension and created
   the
   two function above, we can use them in exactly the same way you did it
   in
   MySQL.
   
   For example:
   
   sms=> select lastmsg, left(lastmsg, 2), right(lastmsg, 2) from usrprf
   limit
   5;
               lastmsg            | left | right
   -------------------------------+------+-------
    2002-07-29 18:50:20.237055-04 | 20   | 04
    2002-07-23 16:45:12.936491-04 | 20   | 04
    2002-08-01 12:18:23.278126-04 | 20   | 04
    2002-09-07 16:31:41.096087-04 | 20   | 04
    2002-08-23 09:21:42.927549-04 | 20   | 04
   (5 rows)
   
   If you think the preformance is not good or you don't want to use the
   plpgsql, I can make the same function for you in "C". It is very easy
   based
   on the PG-examples, but to install a "C" extension is more difficult.
   
   Kind Regards,
   Yuri
   
   ----- Original Message -----
   From: "Bruce Momjian" <pgman@candle.pha.pa.us>
   To: "Peter Nixon" <listuser@peternixon.net>
   Cc: <pgsql-general@postgresql.org>
   Sent: Friday, October 25, 2002 9:36 AM
   Subject: Re: [GENERAL] left() in postgres
   
   
   >
   > Try substring().
   >
   >
   ------------------------------------------------------------------------
   --
   -
   >
   > Peter Nixon wrote:
   > > Hi Guys
   > >
   > > I am trying to do a query similar to the following:
   > >
   > > select left(field,3) from table;
   > >
   > > Now this works in Mysql/Access etc etc, but not in postgres
   > > (I am running PostgreSQL 7.2)
   > >
   > > Can anyone suggest how to do this in postgres? I have a database
   with
   many
   > > millions of records, and this functionality is essential for me.
   > >
   > > PS. I did do a google search etc before posting.
   > >
   > > Regards
   > >
   > > --
   > >
   > > Peter Nixon
   > > http://www.peternixon.net/
   > > PGP Key: http://www.peternixon.net/public.asc
   > >
   > > ---------------------------(end of
   broadcast)---------------------------
   > > TIP 3: if posting/reading through Usenet, please send an appropriate
   > > subscribe-nomail command to majordomo@postgresql.org so that your
   > > message can get through to the mailing list cleanly
   > >
   >
   > --
   >   Bruce Momjian                        |  http://candle.pha.pa.us
   >   pgman@candle.pha.pa.us               |  (610) 359-1001
   >   +  If your life is a hard drive,     |  13 Roberts Road
   >   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
   19073
   >
   > ---------------------------(end of
   broadcast)---------------------------
   > TIP 3: if posting/reading through Usenet, please send an appropriate
   > subscribe-nomail command to majordomo@postgresql.org so that your
   > message can get through to the mailing list cleanly
   
   
   ---------------------------(end of broadcast)---------------------------
   TIP 5: Have you checked our extensive FAQ?
   
   http://www.postgresql.org/users-lounge/docs/faq.html
   
   
   

8. Re: left() in postgres

   Gyorgy Molnar <yuri@powercom.com.sg> — 2002-10-25T21:57:58Z

   Dear Peter,
   
   Yes, indeed the substring() is a very good soloution. Actually it is the
   only suitable function in the PG standard library that can help you.
   Based on the substring we could make our own left() and right() functions
   using pgplsql.  Comapre to substring(), they will only provide you a more
   convinient interface, nothing more nothing less.
   
   The two functions:
   
   DROP FUNCTION left(TEXT, INTEGER);
   CREATE FUNCTION left(TEXT, INTEGER) RETURNS TEXT AS '
   DECLARE
   BEGIN
       RETURN substr($1, 1, $2);
   END;
   ' LANGUAGE 'plpgsql';
   
   DROP FUNCTION right(TEXT, INTEGER);
   CREATE FUNCTION right(TEXT, INTEGER) RETURNS TEXT AS '
   DECLARE
   BEGIN
       RETURN substr($1, char_length($1) - $2 + 1, $2);
   END;
   ' LANGUAGE 'plpgsql';
   
   After when you are installed the plpgsql language extension and created the
   two function above, we can use them in exactly the same way you did it in
   MySQL.
   
   For example:
   
   sms=> select lastmsg, left(lastmsg, 2), right(lastmsg, 2) from usrprf limit
   5;
               lastmsg            | left | right
   -------------------------------+------+-------
    2002-07-29 18:50:20.237055-04 | 20   | 04
    2002-07-23 16:45:12.936491-04 | 20   | 04
    2002-08-01 12:18:23.278126-04 | 20   | 04
    2002-09-07 16:31:41.096087-04 | 20   | 04
    2002-08-23 09:21:42.927549-04 | 20   | 04
   (5 rows)
   
   If you think the preformance is not good or you don't want to use the
   plpgsql, I can make the same function for you in "C". It is very easy based
   on the PG-examples, but to install a "C" extension is more difficult.
   
   Kind Regards,
   Yuri
   
   ----- Original Message -----
   From: "Bruce Momjian" <pgman@candle.pha.pa.us>
   To: "Peter Nixon" <listuser@peternixon.net>
   Cc: <pgsql-general@postgresql.org>
   Sent: Friday, October 25, 2002 9:36 AM
   Subject: Re: [GENERAL] left() in postgres
   
   
   >
   > Try substring().
   >
   > --------------------------------------------------------------------------
   -
   >
   > Peter Nixon wrote:
   > > Hi Guys
   > >
   > > I am trying to do a query similar to the following:
   > >
   > > select left(field,3) from table;
   > >
   > > Now this works in Mysql/Access etc etc, but not in postgres
   > > (I am running PostgreSQL 7.2)
   > >
   > > Can anyone suggest how to do this in postgres? I have a database with
   many
   > > millions of records, and this functionality is essential for me.
   > >
   > > PS. I did do a google search etc before posting.
   > >
   > > Regards
   > >
   > > --
   > >
   > > Peter Nixon
   > > http://www.peternixon.net/
   > > PGP Key: http://www.peternixon.net/public.asc
   > >
   > > ---------------------------(end of broadcast)---------------------------
   > > TIP 3: if posting/reading through Usenet, please send an appropriate
   > > subscribe-nomail command to majordomo@postgresql.org so that your
   > > message can get through to the mailing list cleanly
   > >
   >
   > --
   >   Bruce Momjian                        |  http://candle.pha.pa.us
   >   pgman@candle.pha.pa.us               |  (610) 359-1001
   >   +  If your life is a hard drive,     |  13 Roberts Road
   >   +  Christ can be your backup.        |  Newtown Square, Pennsylvania
   19073
   >
   > ---------------------------(end of broadcast)---------------------------
   > TIP 3: if posting/reading through Usenet, please send an appropriate
   > subscribe-nomail command to majordomo@postgresql.org so that your
   > message can get through to the mailing list cleanly