Re: Group by clause - Postgres 9.2.4 - Mailing list pgsql-admin

From David G Johnston
Subject Re: Group by clause - Postgres 9.2.4
Date
Msg-id 1418137524442-5829763.post@n5.nabble.com
Whole thread Raw
In response to Group by clause - Postgres 9.2.4  (Khangelani Gama <kgama@argility.com>)
List pgsql-admin
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.


pgsql-admin by date:

Previous
From:
Date:
Subject: Sessions being killed - out of memory?
Next
From: Peter Eisentraut
Date:
Subject: Re: Problem pg_upgradecluster from 9.1 to 9.3