之前有寫一篇Postgresql 的冪等操作探討按我前往,裡面有使用了upsert. 現在對upsert再來做一些探討. 第一個例子 insert into upsert0223a select n, random(1, 10) from generate_series(1, 100) g(n); select last_autoanalyze , n_tup_ins

先直接來看例子 create table gals1 ( id int generated always as identity primary key , name text not null unique ); insert into gals1 (name) values ('小島南') returning *;

前言 正規化是一個重要,但是近年來較少討論,甚至被誤解的議題. 最近時常看到一些人,說到正規化,關聯式資料庫,就是以前磁碟貴,要節省空間與金錢,所以才這樣做.現在儲存科技進步,所以就不需要云云. 正規化主要是做一致性,去冗餘只是為了一致性的過程中,帶來的特性,並不是目的. 還是來看實際的案例 來一個簡化情況的影片網站. 分析與設計 gal 放 av女優 id,名字.

tag處理的一些情況 查詢的情況 常見到使用tag查詢是點單一tag,少數是可以選數個tag,然後再點查詢. 雖然查詢多tag對使用者來說,使用體驗較好,但是使用此方式的網站較少. 一來查詢方式組SQL可能較為麻煩,二來可能也是效能考量. tag的管理 在上篇的例子中,我們有建立一個tags的table,並且有做unique index. 這樣可以確保tag的唯一性,通常用在較為嚴謹的系統,例如物料屬性, 希望不要過於發散,使用勾選的方式等等.

前言 之前有分享過使用trigger 保護的table不能delete或update.按我前往 也有搭配JSON保存資料的方式.按我前往 也有一些其他方式的分享,例如保護並發送telgram訊息的方式.在此不一一列舉.有興趣的朋友,可以看 PostgreSQLTW_HackMD 在HackMD的分享資料. 不久前MySQL User Group Taiwan有一篇有意思的分享.按我前往 會出現一些提示語. 之前分享的方式,大多是純保護,這次增加判斷是否有where 條件. 並使用statement 層級,避免出現過多訊息. 來看展示吧

前言 2024-01-18我在ithelp 有發一篇GREATEST LEAST 介紹的文,按我前往. 裡面還有以前解答問題時,有應用到Oracle使用GREATEST的案例. 目前這對函數(或者說是語法),已經是ANSI標準了.Oracle,PostgreSQL,MySQL都有支援,SQL Server 在2022版有支援.大家可以多加利用.裡面有提到一個問題,其實就是unpivot的功能.unpivot 橫轉直,我在ithelp有個範例,unpivot案例 來看hstore 的方式 -- 先聚合,將多欄位放進hstore select hst from s0118.monthlysales as mtsl join lateral hstore(mtsl) s1(hst)

前言 在BackendTW社群中,有人問了這樣的問題:傳送門 簡言之就是兩筆record的一個欄位要交換. SQL的作法 通常一聽到交換,就很容易聯想到使用變數來中繼或者叫過橋. 我們使用的是資料庫,與程序性語言的方式是不同的. 可以這樣做 target: 勇者(1), 某董(2), 小島南(3), 佐山愛(4)

前言 在處理資料時,timestamp 是很重要的資料.想必大家也都會在建立table時會使用,並且會有類似created_at, updated_at.有的會用一個欄位,只放最新的變動時刻,這樣建立時也包含在內.也有使用兩個欄位分別對應建立與最新的變動時刻. 來看一下MySQL的方式 MySQL有提供很便利語法,可以參考這段例子:傳送門 可以看到最後的例子中,建立與變動的時刻有差異了. MySQL在建立table時,欄位就可以指定 on update, 十分地便利.不愧為貼心小棉襖. PostgreSQL的方式 create table t0105a (

前言 大家都熟知幾個資料庫操作的語法,select,insert,delete與update. 我們來看這幾個語法,先由 update 來入手. 觀察一下 update 之後 table的使用空間 先建立一個 table, 指定不做 autovacuum. create table t231124a ( id int primary key , val int

前言 前文感謝大家的回應.其中Eric Tam大大有提到多名女優合演同一片的情境. 所以特別再補充本篇. 共演的情境 array 的方式 若能夠使用array 裡面的元素都能夠使用references 就能夠很輕鬆的處理了. 可惜這方式,似乎只有討論試做,並未正式的支持. 其實之前的範例是簡化了

前言 昨天發了篇趣味SQL(傳送門). 用來介紹 Hypothetical-Set Aggregate Functions 若不知道有這類函數時,我們可以怎樣做 雖然會麻煩一點,但是可以設法加工. 先來看第一步 with t1 as ( select array_agg(val) as arry

前言 在之前的一些分享中,有一些關於直轉橫的方式,也有固定時段與不固定時段的統計方式. 這篇會對直轉橫統計及統計後直轉橫做一些探討. 直接統計的傳統方式 測試資料 create table t231122a ( id int not null generated always as identity primary key , gal text , dt date

前言 在前一篇(傳送門)中,我們使用了json的函數,搭配動態產生的table,來做到pivot的功能. 在PostgreSQL的資料型態中,還有另一個key-value的hstore.屬於extension. 我們來看一下之前使用array存放tag的例子(傳送門),來做tag統計並轉換. 測試資料與前期準備 select * from videos_array; -[ RECORD 1 ]------------------------------------------- vid_id | 1

前言 ptt database版有一個討論,傳送門 分析 這是常見的直轉橫 pivot 功能.但是這裡會有一個較為不方便的地方,就是需要產生如a1, a2, a3 ....z1, z2, z3,欄位非常多. 一一列舉手動產生,會很無聊,也容易出錯,眼殘就少了,或是重複. 實務上比較常見的是月份之類的,12個比較容易做. 另外 PostgreSQL 要做 pivot 需要使用tablefunc 可以參考文件 https://www.postgresql.org/docs/current/tablefunc.html 裡面也是需要列舉,可以看到使用月份的例子.

前言 ptt database版有一篇 計算多欄位平均 傳送門 測試資料 我將table 簡化,重點在於平均數計算,就使用一個table. create table t231121a ( sid int not null primary key , chi int , eng int

關於密碼的一些事情 最近在BackendTW 有一篇關於密碼的 傳送門 在此先不去討論網站密碼該怎樣存,相信這些對本文的讀者來說,不需要再重複多說了. 個人密碼的管理,有的人會使用密碼管理工具.有的人是可能有幾組常用密碼,這篇的使用者,可能就是這樣情況,這樣當網站的密碼不安全時,就連帶影響了. 現在大家都已經有共識,就是密碼長度要夠長,以提高安全性,但是夠長的卻又不好記,就容易只使用幾組. 所以若我們有比較容易記得的文句當基礎,來產生一串長長的密碼,這樣就很方便了. PostgreSQL 的 Array 又出現了 最近介紹了不少Array的應用,這次還是要使用它. 來看一下例子

這是延伸的問題,之前有固定間隔的,也介紹了date_bin(). 上一次的 這次的 苦工大師 with t1 as ( select case when ts >= timestamp '2023-10-11 09:00:00' and ts < timestamp '2023-10-11 10:00:00' then timestamp '2023-10-11 09:00:00'

前言 在我們開發系統的過程中,會對table增加欄位或是刪除欄位,為了讓開發測試過程平順,我們可以使用一些技巧來應對. 測試情境 假設我們有一個 schema 放基本資料,隨著系統演進又增加一個 schmea,用來放新的table. 基本資料 schmea 與 table create schema s1103basic; create table s1103basic.合演片 (

在許多系統中,會使用tag,或是屬性. 我們將介紹傳統的方式,以及PostgreSQL 的 array 兩種方式. 傳統方式 會建立三個table,一個用來放標的,一個用來放tag,一個用來放中介. create table videos ( vid_id int not null generated always as identity primary key , vid_number text not null );

現有的精度 https://www.postgresql.org/docs/current/datatype-datetime.html 可以看到Resolution timestamp , time, interval 是 1 microsecond, date 是 1 day 驗證看看 select current_timestamp , current_timestamp(6) , current_timestamp(0); result: