Consultas de Base de Datos

peligro

Ten en cuenta que manipular la base de datos podría causar desastres. Evita modificar la base de datos directamente siempre que sea posible y asegúrate de tener copias de seguridad actualizadas.

consejo

Ejecuta docker exec -it immich_postgres psql --dbname=<DB_DATABASE_NAME> --username=<DB_USERNAME> para conectarte directamente a la base de datos a través del contenedor.

(Reemplaza <DB_DATABASE_NAME> y <DB_USERNAME> con los valores de tu archivo .env).

Activos

Nombre

nota

La columna "originalFileName" es el nombre del archivo en el momento de la carga, incluida la extensión.

Buscar por nombre original de archivo

SELECT * FROM "asset" WHERE "originalFileName" = 'PXL_20230903_232542848.jpg';
SELECT * FROM "asset" WHERE "originalFileName" LIKE 'PXL_%'; -- todos los archivos que comienzan con PXL_
SELECT * FROM "asset" WHERE "originalFileName" LIKE '%_2023_%'; -- todos los archivos con _2023_ en el medio

Buscar por ruta

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

Buscar por ID

SELECT * FROM "asset" WHERE "id" = '9f94e60f-65b6-47b7-ae44-a4df7b57f0e9';

Buscar por ID parcial

SELECT * FROM "asset" WHERE "id"::text LIKE '%ab431d3a%';

Checksum

nota

Puedes calcular el checksum de un archivo específico usando el comando sha1sum <filename>.

Buscar por checksum (SHA-1)

SELECT encode("checksum", 'hex') FROM "asset";
SELECT * FROM "asset" WHERE "checksum" = decode('69de19c87658c4c15d9cacb9967b8e033bf74dd1', 'hex');
SELECT * FROM "asset" WHERE "checksum" = '\x69de19c87658c4c15d9cacb9967b8e033bf74dd1'; -- notación alternativa

Buscar activos duplicados con checksum idénticos (SHA-1) (excluyendo archivos eliminados)

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";

Metadatos

Fotos en vivo

SELECT * FROM "asset" WHERE "livePhotoVideoId" IS NOT NULL;

Por descripción

SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE TRIM("asset_exif"."description") <> ''; -- todos los archivos con una descripción
SELECT "asset".*, "asset_exif"."description" FROM "asset_exif"
  JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."description" ILIKE '%string to match%'; -- búsqueda por cadena

Sin metadatos

SELECT "asset".* FROM "asset_exif"
  LEFT JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."assetId" IS NULL;

tamaño < 100,000 bytes, de menor a mayor

SELECT * FROM "asset"
  JOIN "asset_exif" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."fileSizeInByte" < 100000
  ORDER BY "asset_exif"."fileSizeInByte" ASC;

Tipo

Por tipo

SELECT * FROM "asset" WHERE "asset"."type" = 'VIDEO';
SELECT * FROM "asset" WHERE "asset"."type" = 'IMAGE';

Conteo por tipo

SELECT "asset"."type", COUNT(*) FROM "asset" GROUP BY "asset"."type";

Conteo por tipo (por usuario)

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";

Etiquetas

Conteo por etiqueta

SELECT "t"."value" AS "tag_name", COUNT(*) AS "number_assets" FROM "tag" "t"
  JOIN "tag_asset" "ta" ON "t"."id" = "ta"."tagsId" JOIN "asset" "a" ON "ta"."assetsId" = "a"."id"
  WHERE "a"."visibility" != 'hidden'
  GROUP BY "t"."value" ORDER BY "number_assets" DESC;

Conteo por etiqueta (por usuario)

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;

Usuarios

Listar todos los usuarios

SELECT * FROM "user";

Obtener información del propietario mediante el ID de activo

SELECT "user".* FROM "user" JOIN "asset" ON "user"."id" = "asset"."ownerId" WHERE "asset"."id" = 'fa310b01-2f26-4b7a-9042-d578226e021f';

Personas

Eliminar persona y desvincularla de las caras asociadas

DELETE FROM "person" WHERE "name" = 'PersonNameHere';

Sistema

Configuración

Configuraciones personalizadas

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

(Solo se usa cuando no se utiliza el archivo de configuración)

Propiedades del archivo

Sin miniaturas

SELECT * FROM "asset" WHERE "asset"."previewPath" IS NULL OR "asset"."thumbnailPath" IS NULL;

Movimientos de archivos fallidos

SELECT * FROM "move_history";

Interno de Postgres

Cambiar DB_PASSWORD

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