postgres:copy_command
Differences
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> | ||
postgres/copy_command.1374784303.txt.gz · Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
