使用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有很多彈性靈活的功能,我們可以多加利用.避免無謂的爭議,讓世界更和平.