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:11] 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 | + | world=# select c.name, o.name from countries c join countries_x_organisations cxo |
- | Kosovo | + | on cxo.code = c.code join organisations o on o.id = cxo.organisation_id; |
- | Montenegro | + | |
- | Russia | + | |
- | Serbia | + | |
- | Vatican City | + | |
- | (7 rows) | + | |
</code> | </code> | ||
+ | |||
+ |