Skip to main content

Database Queries

danger

Keep in mind that mucking around in the database might set the moon on fire. Avoid modifying the database directly when possible, and always have current backups.

tip

Run docker exec -it immich_postgres psql --dbname=immich --username=<DB_USERNAME> to connect to the database via the container directly.

(Replace <DB_USERNAME> with the value from your .env file).

Assets

note

The "originalFileName" column is the name of the file at time of upload, including the extension.

Find by original filename
SELECT * FROM "assets" WHERE "originalFileName" = 'PXL_20230903_232542848.jpg';
SELECT * FROM "assets" WHERE "originalFileName" LIKE 'PXL_%'; -- all files starting with PXL_
SELECT * FROM "assets" WHERE "originalFileName" LIKE '%_2023_%'; -- all files with _2023_ in the middle
Find by path
SELECT * FROM "assets" WHERE "originalPath" = 'upload/library/admin/2023/2023-09-03/PXL_2023.jpg';
SELECT * FROM "assets" WHERE "originalPath" LIKE 'upload/library/admin/2023/%';
note

You can calculate the checksum for a particular file by using the command sha1sum <filename>.

Find by checksum (SHA-1)
SELECT encode("checksum", 'hex') FROM "assets";
SELECT * FROM "assets" WHERE "checksum" = decode('69de19c87658c4c15d9cacb9967b8e033bf74dd1', 'hex');
SELECT * FROM "assets" WHERE "checksum" = '\x69de19c87658c4c15d9cacb9967b8e033bf74dd1'; -- alternate notation
Find duplicate assets with identical checksum (SHA-1) (excluding trashed files)
SELECT T1."checksum", array_agg(T2."id") ids FROM "assets" T1
INNER JOIN "assets" 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";
Live photos
SELECT * FROM "assets" WHERE "livePhotoVideoId" IS NOT NULL;
By description
SELECT "assets".*, "exif"."description" FROM "exif"
JOIN "assets" ON "assets"."id" = "exif"."assetId"
WHERE TRIM("exif"."description") <> ''; -- all files with a description
SELECT "assets".*, "exif"."description" FROM "exif"
JOIN "assets" ON "assets"."id" = "exif"."assetId"
WHERE "exif"."description" ILIKE '%string to match%'; -- search by string
Without metadata
SELECT "assets".* FROM "exif"
LEFT JOIN "assets" ON "assets"."id" = "exif"."assetId"
WHERE "exif"."assetId" IS NULL;
size < 100,000 bytes, smallest to largest
SELECT * FROM "assets"
JOIN "exif" ON "assets"."id" = "exif"."assetId"
WHERE "exif"."fileSizeInByte" < 100000
ORDER BY "exif"."fileSizeInByte" ASC;
Without thumbnails
SELECT * FROM "assets" WHERE "assets"."previewPath" IS NULL OR "assets"."thumbnailPath" IS NULL;
By type
SELECT * FROM "assets" WHERE "assets"."type" = 'VIDEO';
SELECT * FROM "assets" WHERE "assets"."type" = 'IMAGE';
Count by type
SELECT "assets"."type", COUNT(*) FROM "assets" GROUP BY "assets"."type";
Count by type (per user)
SELECT "users"."email", "assets"."type", COUNT(*) FROM "assets"
JOIN "users" ON "assets"."ownerId" = "users"."id"
GROUP BY "assets"."type", "users"."email" ORDER BY "users"."email";
Failed file movements
SELECT * FROM "move_history";

Users

List all users
SELECT * FROM "users";

System Config

Custom settings
SELECT "key", "value" FROM "system_metadata" WHERE "key" = 'system-config';

(Only used when not using the config file)

Persons

Delete person and unset it for the faces it was associated with
DELETE FROM "person" WHERE "name" = 'PersonNameHere';

Postgres internal

Change DB_PASSWORD
ALTER USER <DB_USERNAME> WITH ENCRYPTED PASSWORD 'newpasswordhere';