データベースクエリ

危険

データベースをいじると月が燃える可能性があることを覚えておいてください。可能であればデータベースを直接変更することを避け、最新のバックアップを常に取得してください。

ヒント

docker exec -it immich_postgres psql --dbname=<DB_DATABASE_NAME> --username=<DB_USERNAME> コマンドを実行してコンテナを介して直接データベースに接続します。

(<DB_DATABASE_NAME> と <DB_USERNAME> を .envファイルで取得した値に置き換えてください)。

ファイル

名前

ノート

列"originalFileName"は、アップロード時の拡張子を含めたファイル名です。

オリジナルファイル名による検索
SELECT * FROM "asset" WHERE "originalFileName" = 'PXL_20230903_232542848.jpg';
SELECT * FROM "asset" WHERE "originalFileName" LIKE 'PXL_%'; -- PXL_で始まるすべてのファイル
SELECT * FROM "asset" WHERE "originalFileName" LIKE '%_2023_%'; -- 中間に'_2023_'を含むすべてのファイル

パスによる検索
SELECT * FROM "asset" WHERE "originalPath" = 'upload/library/admin/2023/2023-09-03/PXL_2023.jpg';
SELECT * FROM "asset" WHERE "originalPath" LIKE 'upload/library/admin/2023/%';

ID

IDによる検索
SELECT * FROM "asset" WHERE "id" = '9f94e60f-65b6-47b7-ae44-a4df7b57f0e9';

部分的なIDによる検索
SELECT * FROM "asset" WHERE "id"::text LIKE '%ab431d3a%';

チェックサム

ノート

sha1sum <filename>コマンドを使用して特定のファイルのチェックサムを計算できます。

チェックサムによる検索（SHA-1）
SELECT encode("checksum", 'hex') FROM "asset";
SELECT * FROM "asset" WHERE "checksum" = decode('69de19c87658c4c15d9cacb9967b8e033bf74dd1', 'hex');
SELECT * FROM "asset" WHERE "checksum" = '\x69de19c87658c4c15d9cacb9967b8e033bf74dd1'; -- 代替表記

一致するチェックサム（SHA-1）を持つ重複ファイルの検索（削除済みファイルを除外）
SELECT T1."checksum", array_agg(T2."id") ids FROM "asset" T1
  INNER JOIN "asset" T2 ON T1."checksum" = T2."checksum" AND T1."id" != T2."id" AND T2."deletedAt" IS NULL
  WHERE T1."deletedAt" IS NULL GROUP BY T1."checksum";

メタデータ

ライブ写真
SELECT * FROM "asset" WHERE "livePhotoVideoId" IS NOT NULL;

説明による検索
SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE TRIM("asset_exif"."description") <> ''; -- 説明があるすべてのファイル
SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."description" ILIKE '%検索する文字列%'; -- 文字列による検索

メタデータなし
SELECT "asset".* FROM "asset_exif"
  LEFT JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."assetId" IS NULL;

サイズ < 100,000バイト、最小から最大へ
SELECT * FROM "asset"
  JOIN "asset_exif" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."fileSizeInByte" < 100000
  ORDER BY "asset_exif"."fileSizeInByte" ASC;

種類

種類による検索
SELECT * FROM "asset" WHERE "asset"."type" = 'VIDEO';
SELECT * FROM "asset" WHERE "asset"."type" = 'IMAGE';

種類ごとの件数
SELECT "asset"."type", COUNT(*) FROM "asset" GROUP BY "asset"."type";

ユーザーごとの種類ごとの件数
SELECT "user"."email", "asset"."type", COUNT(*) FROM "asset"
  JOIN "user" ON "asset"."ownerId" = "user"."id"
  GROUP BY "asset"."type", "user"."email" ORDER BY "user"."email";

ユーザーごとのタグごとの件数
SELECT "t"."value" AS "tag_name", "u"."email" as "user_email", COUNT(*) AS "number_assets" FROM "tag" "t"
  JOIN "tag_asset" "ta" ON "t"."id" = "ta"."tagsId" JOIN "asset" "a" ON "ta"."assetsId" = "a"."id" JOIN "user" "u" ON "a"."ownerId" = "u"."id"
  WHERE "a"."visibility" != 'hidden'
  GROUP BY "t"."value", "u"."email" ORDER BY "number_assets" DESC;

ユーザー

すべてのユーザーを一覧表示
SELECT * FROM "user";

アセットIDから所有者情報を取得
SELECT "user".* FROM "user" JOIN "asset" ON "user"."id" = "asset"."ownerId" WHERE "asset"."id" = 'fa310b01-2f26-4b7a-9042-d578226e021f';

人物

人物を削除し、関連付けられた顔から解除
DELETE FROM "person" WHERE "name" = 'PersonNameHere';

システム

設定

カスタム設定
SELECT "key", "value" FROM "system_metadata" WHERE "key" = 'system-config';

（設定ファイルを使用していない場合のみ使用）

ファイルプロパティ

サムネイルがないファイル
SELECT * FROM "asset" WHERE "asset"."previewPath" IS NULL OR "asset"."thumbnailPath" IS NULL;

移動履歴の失敗記録
SELECT * FROM "move_history";

Postgres内部

DB_PASSWORDの変更
ALTER USER <DB_USERNAME> WITH ENCRYPTED PASSWORD 'newpasswordhere';

ファイル​

名前​

ID​

チェックサム​

メタデータ​

種類​

タグ​

ユーザー​

人物​

システム​

設定​

ファイルプロパティ​

Postgres内部​