Try   HackMD

使用schema搭配json應對table欄位增減

前言

在我們開發系統的過程中,會對table增加欄位或是刪除欄位,為了讓開發測試過程平順,我們可以使用一些技巧來應對.

測試情境

假設我們有一個 schema 放基本資料,隨著系統演進又增加一個 schmea,用來放新的table.

基本資料 schmea 與 table

create schema s1103basic; create table s1103basic.合演片 ( 番號 text not null primary key , 女優 text[] , 發行日期 date ); insert into s1103basic.合演片 values ('IPX-497', '{"初川みなみ", "坂道みる", "楓カレン"}', date '2020-07-13'), ('IPZZ-034', '{"桜空もも", "伊藤舞雪", "楪カレン"}', date '2023-5-9'); select * from s1103basic.合演片; 番號 | 女優 | 發行日期 ----------+--------------------------------+------------ IPX-497 | {初川みなみ,坂道みる,楓カレン} | 2020-07-13 IPZZ-034 | {桜空もも,伊藤舞雪,楪カレン} | 2023-05-09

能不能加兩個欄位

能不能加兩個欄位,然後那個日期欄位不要.在發展的過程中,增加減少欄位時常見到.這時候我們可以應用schema與search_path.

create schema s1103new; create table s1103new.合演片 ( 番號 text not null primary key , 女優 text[] , 注目女優 text , 推薦指數 int ); insert into s1103new.合演片(番號, 女優) select 番號, 女優 from s1103basic.合演片; update s1103new.合演片 set 注目女優 = '初川みなみ' , 推薦指數 = 5 where 番號 = 'IPX-497'; update s1103new.合演片 set 注目女優 = '桜空もも' , 推薦指數 = 5 where 番號 = 'IPZZ-034';

切換search_path

我們可以透過切換 search_path , 來對兩個不同版本的table做查詢

-- 標準的search_path show search_path; search_path ----------------- "$user", public -- 設定增加 s1103basic set search_path="$user", public, s1103basic; -- 查詢時不用指定schema select * from 合演片; 番號 | 女優 | 發行日期 ----------+--------------------------------+------------ IPX-497 | {初川みなみ,坂道みる,楓カレン} | 2020-07-13 IPZZ-034 | {桜空もも,伊藤舞雪,楪カレン} | 2023-05-09 -- 修改為 s1103new; set search_path="$user", public, s1103new; -- 查詢時不用指定schema select * from 合演片; 番號 | 女優 | 注目女優 | 推薦指數 ----------+--------------------------------+------------+---------- IPX-497 | {初川みなみ,坂道みる,楓カレン} | 初川みなみ | 5 IPZZ-034 | {桜空もも,伊藤舞雪,楪カレン} | 桜空もも | 5

但是,不是使用指定欄位比較好嗎?

wildcard的使用,讓我們在開發階段方便.但是有些人會很堅持的不能用,然後會在那裡跟你盧小小一堆的,因為那是麻煩到你不是他,當他自己違反他的說詞時,會說那是隨機應變.在這種情況下,沒必要去跟那些人講道理,他們是不會接受的,典型的有想法,沒做法.這時候我們就要有專業的處理方式.遠山不轉,流水可轉.來變通一下.先來看指定欄位時的情況.

select 番號,女優,注目女優,推薦指數 from 合演片; 番號 | 女優 | 注目女優 | 推薦指數 ----------+--------------------------------+------------+---------- IPX-497 | {初川みなみ,坂道みる,楓カレン} | 初川みなみ | 5 IPZZ-034 | {桜空もも,伊藤舞雪,楪カレン} | 桜空もも | 5 -- 切換到 s1103basic set search_path="$user", public, s1103basic; -- 上面的指令會發生 select 番號,女優,注目女優,推薦指數 from 合演片; LINE 1: select 番號,女優,注目女優,推薦指數 ^ HINT: Perhaps you meant to reference the column "合演片.女優".

可以看到PostgreSQL 很貼心的告訴我們,沒這個欄位.
反向的情況,我就省略了.

變通的作法

with t1 as ( select to_json(合演片) as js from 合演片 ) select js->>'番號' as 番號 , js->>'女優' as 女優 , js->>'發行日期' as 發行日期 , js->>'注目女優' as 注目女優 , js->>'推薦指數' as 推薦指數 from t1; 番號 | 女優 | 發行日期 | 注目女優 | 推薦指數 ----------+--------------------------------------+------------+----------+---------- IPX-497 | ["初川みなみ","坂道みる","楓カレン"] | 2020-07-13 | NULL | NULL IPZZ-034 | ["桜空もも","伊藤舞雪","楪カレン"] | 2023-05-09 | NULL | NULL -- 切換到 s1103new; set search_path="$user", public, s1103new; -- 相同的SQL with t1 as ( select to_json(合演片) as js from 合演片 ) select js->>'番號' as 番號 , js->>'女優' as 女優 , js->>'發行日期' as 發行日期 , js->>'注目女優' as 注目女優 , js->>'推薦指數' as 推薦指數 from t1; 番號 | 女優 | 發行日期 | 注目女優 | 推薦指數 ----------+--------------------------------------+----------+------------+---------- IPX-497 | ["初川みなみ","坂道みる","楓カレン"] | NULL | 初川みなみ | 5 IPZZ-034 | ["桜空もも","伊藤舞雪","楪カレン"] | NULL | 桜空もも | 5

可以看到我們使用了to_json()函數,裡面直接使用了合演片,當我們設定不同的search_path,就會使用不同結構的合演片,轉變為json.
後續我們使用 ->> 運算子取出資料時,沒有對應到的key,不會回報錯誤,會得到null.這樣就避免了,開發階段發生錯誤,會讓過程較為平順.

感謝

感謝 初川みなみ, 坂道みる (現改名為 miru), 楓カレン (楓哥之前改叫田中レモン 現在又改回 楓カレン 人美任性), 桜空もも, 伊藤舞雪, 楪カレン.

結語

PostgreSQL有很多彈性靈活的功能,我們可以多加利用.避免無謂的爭議,讓世界更和平.