跳至主要内容

[PgSQL] 紀錄 SQL 相關筆記

記錄一些遇到的筆記

WHERE: Boolean​

同時取得 Null, False 資料​

Column type 為 bool 然後又可以是 Null 時，想同時搜尋 False 、 Null 資料。

Bool Column: ignored

以下方式取得失敗
select * from foo where ignored != true;
select * from foo where ignored not in (true);
要用以下方式
select * from foo where ignored = false or ignored isnull;

Alter Table​

Alter Column set not null, drop not null​

alter table commission.shipping_ignored_items 
add column ignored_reason_id int8 NOT null;
-- error

alter table commission.shipping_ignored_items 
alter column ignored_reason_id type int8 null;
-- success

alter table commission.shipping_ignored_items 
alter column ignored_reason_id drop not null;

alter table commission.forced_order_audits 
alter column ignored_reason_id drop not null;
-- success

alter table commission.forced_order_audits 
alter column ignored_reason_id set not null;

Alter drop column​

alter table commission.shipping_ignored_items 
drop column ignored_reason_id;

相關的 cli​

pg_dump 匯出 PostgreSQL Data 到 .sql​

pg_dump
pg_dump -U postgres -h 172.20.1.144 -d clinico_dev -n tt_dbuser1 > dump-clinico_dev-data-tt_dbuser1-202305191749.sql

pg_dump -U postgres -h 172.20.1.144 -d clinico_dev -n public > dump-clinico_dev-data-202305191645.sql

pg_restore 匯入資料到 PostgreSQL Data​

pg_restore
pg_restore -U postgres -h 172.20.1.144 -d clinico_dev < dump-clinico_dev-202305191624.sql