MathJax

MathJax-2

MathJax-3

Google Code Prettify

置頂入手筆記

EnterproseDB Quickstart — 快速入門筆記

由於考慮採用 EnterpriseDB 或是直接用 PostgreSQL 的人,通常需要一些入手的資料。這邊紀錄便提供相關快速上手的簡單筆記 ~ 這篇筆記以 資料庫安裝完畢後的快速使用 為目標,基本紀錄登入使用的範例:

2017年4月10日 星期一

停止卡住的 Postgres Session 的 SQL 指令

在 Postgres 遇到有奇怪的 Session 執行「好像」卡很久的 SQL 查詢指令,想要先把它停下來,再找人來罵一罵~,怎麼處理呢?

2017年4月4日 星期二

在 Postgres 把同一欄的資料,併成同一列的逗號分隔值呈現

雖然不清楚這種功能要作什麼,還是紀錄一下~

在 Oracle 資料庫裡面,要把同一欄位的不同筆資料,用逗號分隔的方式輸出,有以下兩種方法
(一) 用 XML 處理,比較像繞遠路
SQL> select DEPARTMENT_ID, 
   rtrim (xmlagg (xmlelement (e, FIRST_NAME || ',')).extract ('//text()'), ',') enames 
 from EMPLOYEES 
 group by DEPARTMENT_ID;

DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
           10
Jennifer

           20
Michael,Pat

           30
Den,Karen,Guy,Sigal,Shelli,Alexander


DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
           40
Susan

           50
Matthew,Douglas,Donald,Kevin,Alana,Vance,Samuel,Britney,Sarah,Randall,Timothy,Jennifer,Kelly,Anthony,Julia,Alexis,Nandita,Girard,Martha,Jean,Winston,Peter,Randall,Curtis,Trenna,Joshua,John,Stephen,Renske,Hazel,Ki,Michael,Jason,TJ,James,Mozhe,Laura,Steven,James,Irene,Julia,Kevin,Shanta,Payam,Adam


DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
           60
Alexander,Diana,Valli,David,Bruce

           70
Hermann

           80
John,Charles,Jack,Jonathon,Alyssa,Ellen,Sundita,Elizabeth,William,Tayler,Harrison,Lisa,Amit,Sundar,David,Mattea,Danielle,Clara,Sarath,Louise,Lindsey,Allan,Patri

DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
ck,Janette,Oliver,Nanette,Christopher,Peter,David,Peter,Eleni,Gerald,Alberto,Karen
           90
Steven,Lex,Neena
          100
Nancy,Luis,Jose Manuel,Ismael,John,Daniel

DEPARTMENT_ID
-------------
ENAMES
--------------------------------------------------------------------------------
          110
Shelley,William

Kimberely

12 rows selected.

或是
(二) 用 listagg() 函數處理
SQL> SELECT
   DEPARTMENT_ID,
   listagg (FIRST_NAME, ',') 
WITHIN GROUP 
(ORDER BY FIRST_NAME) FIRST_NAME 
FROM EMPLOYEES 
GROUP BY DEPARTMENT_ID;


DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
           10
Jennifer

           20
Michael,Pat

           30
Alexander,Den,Guy,Karen,Shelli,Sigal


DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
           40
Susan

           50
Adam,Alana,Alexis,Anthony,Britney,Curtis,Donald,Douglas,Girard,Hazel,Irene,James,James,Jason,Jean,Jennifer,John,Joshua,Julia,Julia,Kelly,Kevin,Kevin,Ki,Laura,Martha,Matthew,Michael,Mozhe,Nandita,Payam,Peter,Randall,Randall,Renske,Samuel,Sarah,Shanta,Stephen,Steven,TJ,Timothy,Trenna,Vance,Winston


DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
           60
Alexander,Bruce,David,Diana,Valli

           70
Hermann
           80
Alberto,Allan,Alyssa,Amit,Charles,Christopher,Clara,Danielle,David,David,Eleni,Elizabeth,Ellen,Gerald,Harrison,Jack,Janette,John,Jonathon,Karen,Lindsey,Lisa,Lou

DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
ise,Mattea,Nanette,Oliver,Patrick,Peter,Peter,Sarath,Sundar,Sundita,Tayler,William
           90
Lex,Neena,Steven
          100
Daniel,Ismael,John,Jose Manuel,Luis,Nancy

DEPARTMENT_ID
-------------
FIRST_NAME
--------------------------------------------------------------------------------
          110
Shelley,William

Kimberely

12 rows selected.

上面這兩方式在 EDB 都還沒支援(截至 9.6),那應該如何處理?

Postgres 輸入/輸出中文日期

如何從 PostgreSQL 資料庫裡面的 Timestamp 資料型態,變出中文日期格式?