dokuwiki

This is an old revision of the document!

---

europa.csv

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);

world=# \encoding latin1

world=# copy membership_raw from '/home/mh/world/europa.csv' delimiter ';';

world=# select count(code2) from countries where name in (select state from membership_raw);
 count
-------
    44
(1 row)

es stimmen also 7 Ländernamen nicht überein

world=# select count(state) from membership_raw where state not in (select name from countries);
 count
-------
     7
(1 row)

world=# select state from membership_raw where state not in (select name from countries);
      state
------------------
 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=# alter table membership_raw add column alt_state text;
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)