설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 72건, 최근 0 건
   

psql의 유용한 -E 옵션

글쓴이 : PostgresDBA 날짜 : 2014-12-30 (화) 15:55 조회 : 3274
psql 유틸에서 테이블목록을 보여주는 dt 명령어등을 사용시 내부적으로 수행되는 SQL 문을 보고 싶으면 -E 옵션을 붙이면 됩니다.

예제로 살펴보시죠.

[postgres@pgdbserver:/opt/PostgreSQL/9.3/data]$ psql -U scott -d scottdb -E
You are connected to database "scottdb" as user "scott" via socket in "/tmp" at port "5432".
UTF8
Null display is "<NULL>".
Pager usage is off.
psql.bin (9.3.5)
Type "help" for help.

using dumb terminal settings.
scott@[local]:5432:scottdb] 
SQL> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

         List of relations
 Schema |   Name   | Type  | Owner 
--------+----------+-------+-------
 public | bonus    | table | scott
 public | dept     | table | scott
 public | dummy    | table | scott
 public | salgrade | table | scott
 public | zzz      | table | lion
 scott  | emp      | table | scott
 scott  | test     | table | scott
 scott  | x        | table | scott
(8 rows)

scott@[local]:5432:scottdb] 
SQL> 

   

postgresdba.com