我的數據庫中有一個表，其中儲存了我的網站使用的影片文件的路徑。這些文件儲存在 SSD 磁碟中。

path character varying(255)
id serial

路徑將永遠是這樣的：

/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)

以下是一些有效路徑的範例：

/files/client/743052/attachment/2123598/main
/files/client/743052/attachment/2123598/thumbnail
/files/client/1475296/user/3541234/main
/files/client/1475296/user/3541234/thumbnail

問題： 如何創建一個檢查值的觸發器WHEN INSERTED，如果行小於最小路徑 ( /files/client/\d+/(attachment|user)/\d+/(main|thumbnail))，則引發異常？

我正在使用 PostgreSQL 9.1。

更新：

這裡還有一些例子：

/files/client/45345/user/3542341234/main -- PASS
/files/client/45345/user/3542341234/thumbnail -- PASS
/files/client/253623/attachment/35334/main -- PASS
/files/client/253623/attachment/35334/thumbnail -- PASS
/files/client/45312341245/users/12545/main -- WRONG!
/files/client/45312341245/users/12545/thumbnail -- WRONG!
/files/client/45345/attachment/1223545/mains -- WRONG!
/files/client/45345/attachment/1223545/thumbnails -- WRONG!

您可以簡單地使用檢查約束，但是我會親自重組我的架構。本質上，您似乎正在做的是將大量數據序列化到路徑中，然後您想確保它是正確的。從我的角度來看，這有點糟糕。

CREATE TABLE foo (
 id    serial,
 path  text
   CHECK ( path ~ '/files/client/\d+/(attachment|user)/\d+/(main|thumbnail)' )
);
INSERT INTO foo(path) VALUES 
 ('/files/client/743052/attachment/2123598/main'),
 ('/files/client/743052/attachment/2123598/thumbnail'),
 ('/files/client/1475296/user/3541234/main'),
 ('/files/client/1475296/user/3541234/thumbnail');

INSERT INTO foo(path) VALUES ('/STUD/EVAN/beefcake_hotstuff.jpg');
ERROR:  new row for relation "foo" violates check constraint "foo_path_check"
DETAIL:  Failing row contains (6, /STUD/EVAN/beefcake_hotstuff.jpg).

我可能會走相反的方向並在某種程度上正常化。

規範化

規範化看起來像這樣，

CREATE SCHEMA aws;

CREATE TABLE aws.client (
 client_id serial PRIMARY KEY
);
CREATE TABLE aws.attachment (
 client_id       int REFERENCES client,
 attachment_id   int PRIMARY KEY,
 name            text
);
CREATE TABLE aws.user (
 client_id       int REFERENCES client,
 user_id         int PRIMARY KEY,
 name            text
);

SELECT '/files/client' || client_id || '/attachment' || attachment_id || '/' || a.name
FROM aws.attachment AS a
JOIN aws.client AS c USING (client_id);

引用自：https://dba.stackexchange.com/questions/177081