Date: Tue, 9 Dec 2014 08:05:24 -0700 (MST)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-admin@postgresql.org
Message-ID: <1418137524442-5829763.post@n5.nabble.com>
In-Reply-To: <bd7792db57721722bc94664c4ba2a7e8@mail.gmail.com>
References: <bd7792db57721722bc94664c4ba2a7e8@mail.gmail.com>
Subject: Re: Group by clause - Postgres 9.2.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Khangelani Gama-2 wrote
> I need some help
> 
> 
> 
> I was given the SQL called as  insert into usr_role (select usr_id,
> '501~734' from usr_role where role_id = '501~703' group by usr_id);  to be
> run in a server which will work fine but I need to construct another one
> to
> work the same way for the master table called corp_usr_role. Given the
> following 2 tables, is it correct to have the SQL as insert into
> corp_usr_role (select usr_id,ctry_cde,grp_cde, '501~734'  from
> corp_usr_role where role_id = '501~703' group by
> usr_id,ctry_cde,grp_cde,role_id);
> 
> 
> 
> *First table :*
> 
> 
> 
> *Table "public.corp_usr_role"*
> 
> 
> 
>   Column  | Type | Modifiers
> 
> ----------+------+-----------
> 
> ctry_cde | text | not null
> 
> grp_cde  | text | not null
> 
> usr_id   | text | not null
> 
> role_id  | text | not null
> 
> Indexes:
> 
>     "pk_cur" PRIMARY KEY, btree (ctry_cde, grp_cde, usr_id, role_id)
> 
> 
> 
> 
> 
> *2nd table : *
> 
> 
> 
> *Table "public.usr_role"*
> 
> 
> 
> Column  | Type | Modifiers
> 
> ---------+------+-----------
> 
> usr_id  | text |
> 
> role_id | text |
> 
> Indexes:
> 
>     "idx_usr_role_role" btree (role_id)
> 
>     "idx_usr_role_usr" btree (usr_id)

The only admin aspect to this question is the fact you are using a very
out-of-date release of PostgreSQL.  As for your question it seems like
something you can easily resolve on your own since you supposedly have data
and a way to run queries.  If your run the above query against your data do
you get the results you expect?

I do question whether having a constant in the select-list is something you
really want but since you provide no context and no data...

David J.

P.s. Decided to read more closely...you seem to want to add a new role to
all existing users with a given role.  Whether this makes sense depends on
the data.  Regardless, your corporate query does not do what you want.


--
View this message in context: http://postgresql.nabble.com/Group-by-clause-Postgres-9-2-4-tp5829741p5829763.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin