在這主要介紹PostgreSQL IN、NOT IN的用法
IN:數值在這集合裡面。
NOT IN:數值在不再這集合裡面。

如果有一表如下:

1
2
3
4
5
6

price_record
id | price
---+------
1  | 100 
2  | 200
3  | 300

IN:數值在這集合裡面。

1
2

SELECT * FROM PRICE_RECORD PR 
WHERE PR.PRICE IN ('100','200') 

結果:

1
2
3
4

id | price
---+------
1  | 100 
2  | 200

NOT IN:數值在不再這集合裡面。

1
2

SELECT * FROM PRICE_RECORD PR 
WHERE PR.PRICE NOT IN ('100','200') 

結果:

1
2
3

id | price
---+------
3  | 300

(撰寫中。)

參考資料

• https://www.twblogs.net/a/5ee9f9bc29a78f6b0110a168

前言

最近遇到在PostgreSQL取得近幾天資料的問題，於是在思考是否有比較直覺得寫法?
後來發現用Between或>=都可以。

以下有一個table:

tb_record

1
2
3
4
5
6
7

seq | record_date(character varying(8))
---- -----
1   | 20220101
2   | 20220103
3   | 20220305
4   | 20220308
5   | 20220310

比如今天是，20220310則:

取得昨天到今天的資料

between寫法:

1
2
3
4
5
6

SELECT 
    * 
FROM 
    TB_RECORD 
WHERE
    RECORD_DATE BETWEEN TO_CHAR(NOW() - INTERVAL '1 DAY','YYYYMMDD') AND TO_CHAR(NOW(),'YYYYMMDD')

大於等於寫法:

1
2
3
4
5
6

SELECT 
    * 
FROM 
    TB_RECORD 
WHERE
    RECORD_DATE >= TO_CHAR(NOW() - INTERVAL '1 DAY','YYYYMMDD')

結果:

1

5   | 20220310

取得近五天資料

between寫法:

1
2
3
4
5
6

SELECT 
    * 
FROM 
    TB_RECORD 
WHERE
    RECORD_DATE BETWEEN TO_CHAR(NOW() - INTERVAL '5 DAY','YYYYMMDD') AND TO_CHAR(NOW(),'YYYYMMDD')

大於等於寫法:

1
2
3
4
5
6
7

SELECT 
    * 
FROM 
    TB_RECORD 
WHERE
    RECORD_DATE >= TO_CHAR(NOW() - INTERVAL '5 DAY','YYYYMMDD')

結果:

1
2
3

3   | 20220305
4   | 20220308
5   | 20220310

總結

其實就是使用NOW()函數與INTERVAL條件去組日期區間去篩選(O)。
並且搭配一些內部的轉型。
以上面結果就是都轉成YYYYMMDD的字串去比較。

前言

最近碰到一個PostgreSQL搜尋與句的問題。
就是怎樣在PostgreSQL達到類似SQL Server的Outer Apply的寫法。
因為需要在原本一對一的表，再去LEFT JOIN一個一對多資料。
並且把一對多資料取得一個結果存進一對一的表，像是Boolean、數量。

目標:
一張是訂單主表，一張是訂單明細表
想以主表為主，取得有幾張明細表數量。
Table如下:

Table ORDER

1
2
3
4

SEQ
---- 
1
2

Table ORDER_DETAIL

1
2
3
4
5

SEQ  | ORDER_SEQ | NAME 
---------------------------
1    | 1         | 訂單明細1
2    | 2         | 訂單明細2
3    | 2         | 訂單明細3

使用 Lateral :

1
2
3
4
5
6
7
8

SELECT 
    O.SEQ AS ORDER_SEQ,
    RS.ORDER_DETAIL_COUNT
FROM ORDER AS O
LEFT JOIN LATERAL(
    SELECT COUNT(OD.SEQ) AS ORDER_DETAIL_COUNT FROM  ORDER_DETAIL AS OD 
    WHERE   OD.ORDER_SEQ = O.SEQ
) AS RS ON TRUE

結果

1
2
3
4

ORDER_SEQ | ORDER_DETAIL_COUNT
------------------------------
1         | 1
2         | 2

不用 Lateral:

1
2
3
4
5
6

SELECT 
    O.SEQ AS ORDER_SEQ,
    RS.ORDER_DETAIL_COUNT
FROM ORDER AS O
LEFT JOIN ORDER_DETAIL AS OD on OD.ORDER_SEQ = O.SEQ
GROUP BY O.SEQ

結果

1
2
3
4

ORDER_SEQ | ORDER_DETAIL_COUNT
------------------------------
1         | 1
2         | 2

總結

用LATERAL會比較直覺，就不用最後再去GROUP BY。
如果想繼承上面LEFT JOIN表的條件，那LATERAL優勢就會出來，會顯得簡單很多。

參考資料

• https://www.cnblogs.com/ricklz/p/12122808.html

在寫Javascript時會看到??的語法或?.的用法。
在這稍微做解釋:

?.可選鏈:
作用:可以簡化寫法，因為Javascript如果強制叫出不存在的物件會出現錯誤，所以原本寫法都要寫很長去檢查物件屬性是否存在。

1
2
3
4
5

const user = {data:{address:"my address"}}
// 原本寫法 output:my address
const userAddress = user.data && user.data.address
// 替換如下 output:my address
const userAddress = user.data?.address

可以減少巢狀物件取的屬性的程式碼。

??:
作用:左方為null或undefined則會直接回傳右方的值。

1
2
3
4
5

const detail = {data:{address:"my address"}}
// 原本寫法 output:10
const detailAmount = (detail.data.amount && detail.data.amount) ? detail.data.amount : 10 ;
// 替換如下 output:10
const detailAmount = detail.data?.amount ?? 10;

上面因為找不到amount所以接回傳10，有的話則回傳amount

總結

感覺未來的Javascript會充斥著 「??」、「?.」
到時候又配上Javascript函式寫法感覺閱讀性會複雜了。(雖然簡化語法，但學習門檻變高)
看自己手上的專案斟酌吧。

前言

在這篇中介紹如何撰寫一個PostgreSQL的stored procedure。

Procedure模板

以下是一個固定的模板，我們將以此模板來撰寫。

1
2
3
4
5
6
7
8

create [or replace] procedure procedure_name(parameter_list)
language plpgsql
as $$
declare
-- variable declaration
begin
-- stored procedure body
end; $$

• procedure_name : 是prodcedure的名子
• parameter_list : 放傳進參數。可以選擇兩種方式:inout、out
• plpgsql : 使用的prodcedure內的語法，可以改sql或C等等，看自己用哪種方式寫
• $$:可以看到有被各一個$$圍住的區塊就是body，寫語法的區塊就在$$內。

補充:
stored prodcedure沒有回傳值，所以並不會有return expression;的寫法，但你可以加return;讓程式執行到一半停止執行。

POSTGRESQL文件:
A procedure does not have a return value. A procedure can therefore end without a RETURN statement. If a RETURN statement is desired to exit the code early, then NULL must be returned. Returning any other value will result in an error

實作

實際撰寫

目標:
撰寫一個傳送用戶ID回傳用戶資料的procedure。

users資料表:

因為PostgreSQL的stored procedure沒有回傳值，所以就要用參考的方式去拿值。
以下用INOUT refcursor refcursor去當作參考。
IN user_id integer則是要取得資料的使用者id。

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE PROCEDURE get_user_data
(
	IN user_id integer,
	INOUT refcursor refcursor
) 
LANGUAGE 'plpgsql'
AS $$
DECLARE
  str_sql varchar;                                                    
BEGIN
  str_sql := 'SELECT * FROM users WHERE id = '|| user_id ;
  OPEN refcursor FOR EXECUTE str_sql;                             
END;
$$

• || : 是字串的連結符號
  傳進一個user_id跟參考的物件。

建好後可在左方PGADMIN看到

之後可以用call來執行。
建立一個區域，宣告一個RECORD物件用來印資料、一個refcursor物件用來參考
接著跑loop迴圈，將每個out_ref放進rcd，再用raise notice印出。
如果沒資料則用IF判斷式離開。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

DO $$
DECLARE
	rcd RECORD;
	out_ref refcursor;
BEGIN
	call get_user_data(2,out_ref);
	loop
		fetch in out_ref into rcd;
		IF NOT FOUND THEN
			EXIT;
		END IF;
		raise notice 'user_id=%', rcd.id;
		raise notice 'user_name=%', rcd.name;
  	end loop;
	close out_ref;
END;
$$ LANGUAGE plpgsql;

結果:

總結

PostgreSQL的prodcedure並不好寫，因為他沒辦法直接有返回值。要用參考的方式。他不像SQL Server一樣簡單，能直接Select完畢，因為並不具備回傳值。要回傳值就得寫他的function，就不是prodcedure了。

然後，如果想參考SQL Server的prodcedure我之前也有寫一篇:SQL-Server-Stored-Procedure實作範例可以參考。

參考資料

• https://www.postgresqltutorial.com/postgresql-create-procedure/
• https://www.sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure
• https://stackoverflow.com/questions/50894269/how-to-return-result-set-from-postgresql-stored-procedure

前言

最近剛好有要在SQL Server中Left Join N筆結果排序後只取得第一筆的需求。
發現Left Join資料結果會有問題，所以發現了Outer Apply作法能夠達到，因此筆記一下。

實作

需求:
撈出每位使用者還沒完成的最早一筆任務。
並以Priority做排序，如果遇到相同層級則以最早新建的資料為主。

Todos資料表:

Users資料表:

sql:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT 
	A.UserName,
	B.Name TaskName,
	B.Id TaskId,
	B.Priority
FROM Users A
	OUTER APPLY
	(
		SELECT TOP 1 
			Name,Priority,Id
		FROM Todos 
		WHERE 
			A.Id = UserId 
			AND Todos.IsValid = 1 
			AND IsComplete = 0
		ORDER BY Priority ASC,Id ASC 
	) B
WHERE
	A.IsValid = 1

執行結果:

總結

SQL Server有些特殊語法，有時真的得在實作時才會真的碰到。(坑)

前言

因為最近有碰PostgreSQL的需要，因此來整理儲存型別。
此外，發現PostgreSQL的欄位型態具有Alias別名，所以有些型別找不到就要參考文件的Alias。
在以下只列常見的型別，更多請自己參閱參考資料。

常見資料型別

字串

數字

布林

日期時間

總結

在這篇中以一個熟悉SQL Server的角度，列出了平常可能會見到的PostgreSQL資料型態。
因為後者的資料型別彈性高(能夠自己建立物件存進、支援儲存JSON等等)，所以等後面實際碰到再額外補充。
而完整的型別請自行參考網址的參考資料吧!

可能會有人有疑問怎沒SQL Server的NVARCHAR?
Generally you just use a UTF-8 encoded DB, and everything works with no hassle.

參考資料

• https://www.postgresqltutorial.com/postgresql-data-types/
• https://docs.postgresql.tw/the-sql-language/data-types
• https://www.itread01.com/content/1550538018.html
• https://stackoverflow.com/questions/21925802/can-i-use-nvarchar-data-type-in-plpgsql

前言

在開資料時在存字串常會有選擇上的問題，到底該存哪種型別?
而在此就稍微介紹NVARCHAR、VARCHAR的差異。
以SQL Server 2019資料庫為準。

NVARCHAR [ ( n | max ) ]

n表示可變大小字串資料。 n 會定義字串大小 (單位為位元組配對)，且可以是 1 到 4,000 之間的值。max 表示儲存大小上限是 2^30-1 個字元 (2 GB)。

VARCHAR [ ( n | max ) ]

使用 n 以位元組為單位來定義字串大小，該值可以介於 1 到 8,000，或使用 max 來表示資料行大小限制最多為 2^31-1 個位元組 (2 GB) 的儲存體。

總結

使用NVARCHAR來儲存字串會比較好，因為可避免字串因為中英混雜亂碼。

參考資料

https://docs.microsoft.com/zh-tw/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-ver15
https://docs.microsoft.com/zh-tw/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver15
https://ithelp.ithome.com.tw/articles/10213922

在這篇文章中介紹如何用C#產生PDF檔案。
使用套件為IText7。

IText7:https://itextpdf.com/en/products/itext-7/itext-7-core
程式碼範例:https://github.com/yuhsiang237/csharp-notes/tree/master/IText7Sample

先用Nuget安裝:

1

Install-Package itext7

如下，以下為產生一個PDF檔案，並加入標題。
其中標題為中英文混雜。

Program.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

using iText.Kernel.Font;
using iText.Kernel.Pdf;
using iText.Layout;
using iText.Layout.Element;
using iText.Layout.Properties;
using System;

namespace IText7Sample
{
    public class Program
    {
        public static void Main()
        {
            PdfFont sysFont = PdfFontFactory.CreateFont(Environment.CurrentDirectory + "/src/fonts/NotoSansCJKtc-Regular.otf", iText.IO.Font.PdfEncodings.IDENTITY_H);

            // Must have write permissions to the path folder
            PdfWriter writer = new PdfWriter(@"demo.pdf");
            PdfDocument pdf = new PdfDocument(writer);
            Document document = new Document(pdf);
            Paragraph header = new Paragraph("標題 HEADER")
               .SetFont(sysFont)
               .SetTextAlignment(TextAlignment.CENTER)
               .SetFontSize(20);

            document.Add(header);
            document.Close();
        }
    }
}

總結

可以使用此套件來快速產生PDF，如果有中文需求可以像我上面那樣自己去補中文字型即可。

參考資料

• https://itextpdf.com/en/products/itext-7/itext-7-core
• https://github.com/yuhsiang237/csharp-notes/tree/master/IText7Sample