This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
postgres:copy_command [2013/07/25 22:31] mh |
postgres:copy_command [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 12: | Line 12: | ||
world=# select count(code2) from countries where name in (select state from membership_raw); | world=# select count(code2) from countries where name in (select state from membership_raw); | ||
count | count | ||
- | ------- | + | ----- |
- | 44 | + | |
- | (1 row) | + | |
- | es stimmen also 7 Ländernamen nicht überein | + | world=# create table countries_x_organisations (code character(3), organisation_id integer); |
- | world=# select count(state) from membership_raw where state not in (select name from countries); | + | world=# insert into organisations (id, name) values (nextval('world_seq'), 'Euro'); |
- | count | + | world=# insert into organisations (id, name) values (nextval('world_seq'), 'EU'); |
- | ------- | + | |
- | 7 | + | |
- | (1 row) | + | |
- | world=# select state from membership_raw where state not in (select name from countries); | + | world=# insert into countries_x_organisations (code, organisation_id) |
- | state | + | world-# select code, (select id from organisations where name = 'EU') from countries where name in (select state from membership_raw where eu = 'EU'); |
- | ------------------ | + | |
- | Bosnia and Herz. | + | |
- | Kazakhstan | + | |
- | Kosovo | + | |
- | Montenegro | + | |
- | Russia | + | |
- | Serbia | + | |
- | Vatican City | + | |
- | (7 rows) | + | |
- | zb: | ||
- | world=# select name from countries where name like 'Ka%'; | ||
- | name | ||
- | ----------- | ||
- | Kazakstan | ||
- | (1 row) | ||
+ | world=# select c.name, o.name from countries c join countries_x_organisations cxo | ||
+ | on cxo.code = c.code join organisations o on o.id = cxo.organisation_id; | ||
- | world=# alter table membership_raw add column alt_state text; | + | </code> |
- | ALTER TABLE | + | |
- | world=# update membership_raw set alt_state = 'Bosnia and Herzegovina' where state = 'Bosnia and Herz.'; | ||
- | world=# select distinct eu from membership_raw; | ||
- | eu | ||
- | ----------- | ||
- | EaP | ||
- | EU | ||
- | Candidate | ||
- | CEFTA | ||
- | EFTA | ||
- | x | ||
- | Applied | ||
- | BRU | ||
- | PCA | ||
- | (9 rows) | ||
- | |||
- | world=# select eu, count(eu) from membership_raw group by eu order by count(eu); | ||
- | eu | count | ||
- | -----------+------- | ||
- | Applied | 1 | ||
- | PCA | 1 | ||
- | BRU | 2 | ||
- | CEFTA | 2 | ||
- | EFTA | 3 | ||
- | x | 4 | ||
- | EaP | 5 | ||
- | Candidate | 5 | ||
- | EU | 28 | ||
- | (9 rows) | ||
- | |||
- | |||
- | </code> |