Re: Problems with substring

From: "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Problems with substring
Date: 2002-09-10 15:39:37
Message-ID: 034A824BAA3FBA4CA0CBEF1031A02F350E3EDB@zablv02001.vodacom.corp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Thanx to u and Stuart

i was really busting my head on this one.

-----Original Message-----
From: Josh Berkus [mailto:josh(at)agliodbs(dot)com]
Sent: Tuesday, September 10, 2002 5:34 PM
To: Duncan Adams (DNS); pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Problems with substring

Ducan,

> I am using postgres 7.1.3
> on redhat 7.2.

First, please consider upgrading. 7.2.1 has a lot of enhancements
over 7.1.3, and we're testing 7.3.0 now!

> drop view wm_ups_count;
> create view wm_ups_count as
> select
> substring(phase from 1 for 1) as ups,
> sys_key,
> count(substring(phase from 1 for 1)) as phas_count
> from
> power
> where
> sys_key = 10
> group by
> sys_key,
> phase;
^^^^^^^^^^^^^

This is your problem. It should be:
GROUP BY sys_key, substring(phase from 1 for 1);

If you GROUP BY something other than the columns you are displaying,
you will often see "duplicate" rows.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

Browse pgsql-novice by date

  From Date Subject
Next Message Josh Berkus 2002-09-10 15:46:10 Re: pl/pgsql and returns timestamp type
Previous Message Josh Berkus 2002-09-10 15:34:23 Re: Problems with substring