psql_commands.txt

---------------------------------------------------------------------------
-- 실용 SQL
-- by Anthony DeBarros
----------------------------------------------------------------------------


-- 로컬 서버 내 데이터베이스에 psql 연결

psql -d [database name] -U [username]
psql -d analysis -U postgres

-- 원격 서버 내 데이터베이스에 psql 연결

psql -d [database name] -U [username] -h [host name]
psql -d analysis -U postgres -h example.com

-- 사용자명과 데이터베이스명 변경하기

\c [database name] [user name]
\c test
\c test yourname

-- 패스워드 파일 형식 (윈도우: pgpass.conf, 맥/리눅스: .pgpass)
hostname:port:database:username:password


-- 코드 18-1: psql에 한 줄짜리 쿼리 입력하기

SELECT county_name FROM us_counties_pop_est_2019 ORDER BY county_name LIMIT 3;

-- 코드 18-2: psql에 쿼리를 여러 줄로 입력

SELECT county_name
FROM us_counties_pop_est_2019
ORDER BY county_name
LIMIT 3;

-- 코드 18-3: psql 프롬프트에서 열린 괄호 확인

CREATE TABLE wineries (
id bigint,
winery_name text
);

-- 코드 18-4: 스크롤할 수 있는 결과를 받는 쿼리

SELECT county_name FROM us_counties_pop_est_2019 ORDER BY county_name;

-- 코드 18-5 및 18-6: grades 테이블 쿼리의 일반 결과 및 확장 결과 (\x로 확장 여부 결정)

SELECT * FROM grades ORDER BY student_id, course_id;

-- 코드 18-7: \copy를 사용해 데이터 가져오기

DELETE FROM state_regions;

\copy state_regions FROM 'C:\YourDirectory\state_regions.csv' WITH (FORMAT CSV, HEADER);


-- 코드 18-8: COPY로 psql에 데이터 가져오기

DELETE FROM state_regions;

psql -d analysis -U postgres -c "COPY state_regions FROM STDIN WITH (FORMAT CSV, HEADER);" < C:\YourDirectory\state_regions.csv


-- 코드 18-9: 쿼리 결과를 파일에 저장하기

-- psql 세팅 입력
\pset format csv

-- 쿼리
SELECT * FROM grades ORDER BY student_id, course_id;

-- psql이 결과를 출력하도록 설정
-- 이때 윈도우는 슬래시를 사용해야 함
\o 'C:/YourDirectory/query_output.csv'

-- 쿼리 실행 후 결과 확인
SELECT * FROM grades ORDER BY student_id, course_id;


-- 작업을 도와주는 추가 커맨드라인 도구


-- createdb: box_office라는 데이터베이스 만들기

createdb -U postgres -e box_office

-- PostgreSQL에 shapefile 불러오기

-- 15장의 미국 인구조사 shapefile 불러오기
shp2pgsql -I -s 4269 -W Latin1 tl_2019_us_county.shp us_counties_2019_shp | psql -d analysis -U postgres

-- 15장의 Santa Fe 도로 및 수로 정보 shapefile 불러오기
shp2pgsql -I -s 4269 tl_2019_35049_roads.shp santafe_roads_2019 | psql -d analysis -U postgres
shp2pgsql -I -s 4269 tl_2019_35049_linearwater.shp santafe_linearwater_2019 | psql -d analysis -U postgres