Re: Group by clause - Postgres 9.2.4

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.