Skip site navigation (1) Skip section navigation (2)

Re: Problems with substring

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: "Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za>,pgsql-novice(at)postgresql(dot)org
Subject: Re: Problems with substring
Date: 2002-09-10 15:34:23
Message-ID: web-1642956@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
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___________________________

In response to

pgsql-novice by date

Next:From: Duncan Adams (DNS)Date: 2002-09-10 15:39:37
Subject: Re: Problems with substring
Previous:From: Terry YaptDate: 2002-09-10 15:32:04
Subject: pl/pgsql and returns timestamp type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group