Adatok beolvasása és kivitele
Feladatok
Töltds le a következő SQL fájlokat
https://ebiok.science.unideb.hu/sql/
Olvasd be ezeket, vagy legalább az egyiket a saját adatbázis szerveredbe...
createdb danubefish
createuser -delP mahatma
psql -U mahatma
psql: FATAL: Peer authentication failed for user "mahatma"
psql -h localhost -U mahatma
Password for user mahatma:
psql: FATAL: database "mahatma" does not exist
psql -h localhost -U mahatma danubefish
touch .pgpass
chmod 600 .pgpass
echo "localhost:5432:danubefish:mahatma:gandhi" >> .pgpass
psql: FATAL: ...
psql -h localhost -U mahatma danubefish -c "CREATE EXTENSION postgis"
psql -h localhost -U mahatma danubefish < danubefish_dump.sql
INSERT ...
\COPY bioregio FROM 'bioregio.dump.csv' WITH CSV HEADER DELIMETER ','
# két mező beolvasása egy csv fájlból
\COPY my_table (x2, x5) FROM PROGRAM 'csvcut -c 1,8 dump.csv' WITH (FORMAT CSV, HEADER)
psql -h localhost -U mahatma danubefish
SELECT species, township, collector FROM danubefish WHERE township ILIKE '%ű%' ORDER BY township DESC;
SELECT species, 'Budapset' as township, collector FROM danubefish WHERE township ILIKE '%ű%' ORDER BY township DESC;
CREATE VIEW faj_varos AS SELECT species,township,collector,date
FROM danubefish WHERE township ILIKE '%ű%' ORDER BY township DESC;
SELECT date, collector, township, species FROM faj_varos LIMIT 2 OFFSET 10;
CREATE TABLE a_varos AS SELECT species, township, collector, date FROM danubefish WHERE township ILIKE 'a%';
\COPY (SELECT date, collector, township, species FROM danubefish WHERE township ILIKE 'b%') TO 'x.csv' WITH CSV;
psql -h localhost -U mahatma danubefish -t -A -c "SELECT * from faj_varos limit 10"
psql -h localhost -U mahatma danubefish -t -A -c "SELECT * from faj_varos limit 10" > w1.csv
# SQL parancsok végrehajtása fájlból
cat q.sql
SELECT * FROM danubefish LIMIT 5;
psql -h localhost -U mahatma danubefish -t -A < q.sql
# SQL parancsok végrehajtása fájlból, eredmények kiírása csv fájlba
psql -h localhost -U mahatma danubefish -t -A < q.sql > w2.csv