Thread: left() in postgres

left() in postgres

From

Peter Nixon

Date:

25 October 2002, 15:31:18

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

Re: left() in postgres

From

Bruce Momjian

Date:

25 October 2002, 15:36:37

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

Re: left() in postgres

From

Peter Nixon

Date:

25 October 2002, 16:14:42

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.

Attachment

msg-32091-49159.dat

Re: left() in postgres

From

Bruce Momjian

Date:

25 October 2002, 16:25:50

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

Re: left() in postgres

From

Stephan Szabo

Date:

25 October 2002, 16:26:11

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'.

Re: left() in postgres

From

PD Miller

Date:

25 October 2002, 16:27:34

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>

Re: left() in postgres

From

"Gyorgy Molnar"

Date:

25 October 2002, 17:59:37

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

Re: left() in postgres

From

"David Blood"

Date:

25 October 2002, 19:27:24

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