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:11] mh |
postgres:copy_command [2014/09/10 21:22] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | [[europa.csv]] | ||
| + | <code> | ||
| world=# create table membership_raw | world=# create table membership_raw | ||
| (State text, GNI text, wage text, UN text, CoE text, OSCE text, EU text, EEA text, CU text, Schengen text, euro text, OECD text, WTO text, ESA text, NATO text, ICC text, VWP text); | (State text, GNI text, wage text, UN text, CoE text, OSCE text, EU text, EEA text, CU text, Schengen text, euro text, OECD text, WTO text, ESA text, NATO text, ICC text, VWP text); | ||
| - | |||
| - | [[europa.csv]] | ||
| world=# \encoding latin1 | world=# \encoding latin1 | ||
| 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 | + | world=# insert into countries_x_organisations (code, organisation_id) |
| - | (1 row) | + | world-# select code, (select id from organisations where name = 'EU') from countries where name in (select state from membership_raw where eu = 'EU'); |
| + | |||
| + | |||
| + | 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=# select state from membership_raw where state not in (select name from countries); | + | </code> |
| - | state | + | |
| - | ------------------ | + | |
| - | Bosnia and Herz. | + | |
| - | Kazakhstan | + | |
| - | Kosovo | + | |
| - | Montenegro | + | |
| - | Russia | + | |
| - | Serbia | + | |
| - | Vatican City | + | |
| - | (7 rows) | + | |
postgres/copy_command.1374783060.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
