R, Python 분석과 프로그래밍의 친구 (by R Friend)

이번 포스팅에서는 psql 과 SQL Query 를 이용해서 PostgreSQL, Greenplum DB로부터 

(1) 모든 Databases 조회하기 (list all databases in PostgreSQL, Greenplum)

(2) 모든 Schemas 조회하기 (list all schemas in PostgreSQL, Greenplum) 

(3) 모든 Tables 조회하기 (list all tables in PostgreSQL, Greenplum)

(4) 모든 Views 조회하기 (list all views in PostgreSQL, Greenplum)

(5) 모든 Columns 조회하기 (list all columns in PostgreSQL, Greenplum)

하는 방법을 소개하겠습니다.

Database의 메타 정보를 가지고 있는 information_schema 나 pg_catalog 테이블을 조회해서 원하는 정보를 얻는 방법인데요, 보통 데이터 전처리와 분석을 시작할 때 종종 사용하는 편이어서 알아두면 편리합니다. 

(1) 모든 Databases 조회하기 (list all databases in PostgreSQL, Greenplum)

----------------------------------------------------------------------
-- List all DBs, Schemas, Tables, Views, Columns
----------------------------------------------------------------------

-- (1) List all Databases

-- (1-1) using psql
$ \l

-- or alternatively
$ \list


-- (1-2) SQL query
SELECT datname 
FROM pg_database;

(2) 모든 Schemas 조회하기 (list all schemas in PostgreSQL, Greenplum) 

-- (2) List all Schemas

-- (2-1) using psql
$ \dn


-- (2-2) using SQL query
SELECT schema_name
FROM information_schema.schemata;

--schema_name       |
--------------------+
--madlib            |
--gp_toolkit        |
--information_schema
--public            |
--pg_catalog        |
--pg_bitmapindex    |
--pg_aoseg          |
--pg_toast          |


-- or alternatively
SELECT nspname
 FROM pg_catalog.pg_namespace;

(3) 모든 Tables 조회하기 (list all tables in PostgreSQL, Greenplum)

-- (3) Tables 

-- (3-1) using psql
-- listing all tables
$ \dt

-- listing tables using pattern matching
$ \dt pubic.ab*



-- (3-2) using SQL query
-- listing all tables
SELECT 
    table_schema
    , table_name
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

-- or alternatively
SELECT
	schemaname
	, tablename
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');


-- listing tables using pattern matching
SELECT 
	schemaname
	, tablename
FROM pg_catalog.pg_tables
WHERE schemaname='public'
	AND tablename LIKE 'ab%'
;

--schemaname|tablename       |
------------+----------------+
--public    |abalone_array   |
--public    |abalone_predict |
--public    |abalone_external|
--public    |abalone         |
--public    |abalone_split   |

(4) 모든 Views 조회하기 (list all views in PostgreSQL, Greenplum)

-- (4) List all Views

-- (4-1) using psql
-- $ \dv


-- (4-2) using SQL query
SELECT 
	table_schema
	, table_name AS view_name
FROM information_schema.VIEWS
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

--table_schema|view_name                             |
--------------+--------------------------------------+
--public      |geography_columns                     |
--public      |geometry_columns                      |
--public      |plcontainer_refresh_config            |
--public      |plcontainer_show_config               |
--public      |raster_columns                        |
--public      |raster_overviews                      |


-- or alternatively
SELECT 
	schemaname
	, viewname 
FROM pg_catalog.pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, viewname;

(5) 모든 Columns 조회하기 (list all columns in PostgreSQL, Greenplum)

-- (5) List all Columns

-- (5-1) using psql
$ \d+ table_name


--  (5-2) using SQL query
SELECT 
	table_schema 
	, table_name 
	, column_name
	, data_type
FROM information_schema.columns 
WHERE table_schema = 'public'
	AND table_name = 'abalone'
ORDER BY ordinal_position
;

--table_schema|table_name|column_name   |data_type   
--------------+----------+--------------+----------------+
--public      |abalone   |id            |bigint          |
--public      |abalone   |sex           |text            |
--public      |abalone   |length        |double precision|
--public      |abalone   |diameter      |double precision|
--public      |abalone   |height        |double precision|
--public      |abalone   |whole_weight  |double precision|
--public      |abalone   |shucked_weight|double precision|
--public      |abalone   |viscera_weight|double precision|
--public      |abalone   |shell_weight  |double precision|
--public      |abalone   |rings         |integer         |

이번 포스팅이 많은 도움이 되었기를 바랍니다. 

행복한 데이터 과학자 되세요!  :-)