Error processing request: error: there is no unique or exclusion constraint matching the ON CONFLICT specification

[mbridgnell]

Seems like everything is working except the data being added to the database. The App receives and saves the image. Not sure what I'm doing wrong I followed the install instructions exactly. I can see the images being saved in the ALPR folder.

POST /api/plate-reads
Received plate read data: {
ai_dump: [ { api: 'alpr', found: [Object] } ],
Image: blablabla
camera: 'LPR',
ALERT_PATH: 'LPR.2025.04.09_12.39.37.4320761.3-1.jpg',
ALERT_CLIP: '@547101994949703',
timestamp: '2025-04-09T20:52:05.765Z'
}
Database connection established
[FileStorage] Successfully saved image
Error processing request: error: there is no unique or exclusion constraint matching the ON CONFLICT specification
at <unknown> (/app/node_modules/pg/lib/client.js:535:17)
at async p (/app/.next/server/app/api/plate-reads/route.js:1:6064)
at async te.do (/app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:18:17826)
at async te.handle (/app/node_modules/next/dist/compiled/next-server/app-route.runtime.prod.js:18:22492)
at async doRender (/app/node_modules/next/dist/server/base-server.js:1455:42)
at async responseGenerator (/app/node_modules/next/dist/server/base-server.js:1814:28)
at async NextNodeServer.renderToResponseWithComponentsImpl (/app/node_modules/next/dist/server/base-server.js:1824:28)
at async NextNodeServer.renderPageComponent (/app/node_modules/next/dist/server/base-server.js:2240:24)
at async NextNodeServer.renderToResponseImpl (/app/node_modules/next/dist/server/base-server.js:2278:32) {
length: 148,
severity: 'ERROR',
code: '42P10',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'plancat.c',
line: '870',
routine: 'infer_arbiter_indexes'
}

[jonnoman]

I had the same issue with new docker install, but managed to solve it by adding unique index to plate_number column in plates table and also adding a sequence to id column in plate_reads table.
It appears that the database wasn't completely configured correctly during installation for some reason.

[jonnoman]

Try the following, works on Linux, should be pretty similar in Windows
Get the docker container ID by running "docker ps -a"
Then execute shell on the Postgres docker by running "docker exec -u 0 -it bash"
Replace with the ID of the container you got from running the previous command.
Now you should be in the Docker shell, and should run the following to configure the database:
"psql -d postgres -U postgres -f /docker-entrypoint-initdb.d/schema.sql"
Run the commands without the quotes.

[mbridgnell]

Thanks, worked perfectly.

[tinker3433]

Replace What with the ID of the container?

[tinker3433]

Microsoft Windows [Version 10.0.26100.3476]
(c) Microsoft Corporation. All rights reserved.

C:\Users\BI Server>docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9bd699f7e71b algertc/alpr-dashboard:latest "docker-entrypoint.s…" 4 days ago Up 10 hours 0.0.0.0:3000->3000/tcp alpr-app-1
417fbbdfa897 postgres:13 "docker-entrypoint.s…" 4 days ago Up 10 hours (healthy) 0.0.0.0:5432->5432/tcp alpr-db-1

C:\Users\BI Server>docker exec -u 0 -it bash
docker: 'docker exec' requires at least 2 arguments

Usage: docker exec [OPTIONS] CONTAINER COMMAND [ARG...]

See 'docker exec --help' for more information

[jonnoman]

@tinker3433 Sorry, my markup caused the container ID text to be removed.
9bd699f7e71b - This is the container ID, so your command should be
docker exec -u 0 -it 9bd699f7e71b bash
and then run
psql -d postgres -U postgres -f /docker-entrypoint-initdb.d/schema.sql

[tinker3433]

Thanks!
That got me closer, but still no joy.
I am guessing it may be a difference between Linux and Windows

Microsoft Windows [Version 10.0.26100.3476]
(c) Microsoft Corporation. All rights reserved.

C:\Windows\System32>docker ps -a
CONTAINER ID   IMAGE                           COMMAND                  CREATED      STATUS                  PORTS                    NAMES
9bd699f7e71b   algertc/alpr-dashboard:latest   "docker-entrypoint.s…"   4 days ago   Up 24 hours             0.0.0.0:3000->3000/tcp   alpr-app-1
417fbbdfa897   postgres:13                     "docker-entrypoint.s…"   4 days ago   Up 24 hours (healthy)   0.0.0.0:5432->5432/tcp   alpr-db-1

C:\Windows\System32>docker exec -u 0 -it 9bd699f7e71b bash
root@9bd699f7e71b:/app# psql -d postgres -U postgres -f /docker-entrypoint-initdb.d/schema.sql
bash: psql: command not found
root@9bd699f7e71b:/app#

[jonnoman]

I misled you, you need the shell of the postgres docker, not the alpr-dashboard one. The command should be:
docker exec -u 0 -it 417fbbdfa897 bash

[tinker3433]

In the folder you created for the install routine to run in, if you dig down into "Storage" you will find folders for "images" and "thumbnails".
FWIW: I gave up on getting it to work, at least for now. incomplete instructions and the creator seems to be tied up with other things.
Clearly it used to work, but the recent updates seem to have broken it.

[Janhaus]

Ah ok - I'm using the docker image so it's not an actual folder for me..

[tinker3433]

I am using the docker image also
the folder/directory mentioned in the instructions below is the one i am refering to

Windows
Create a new directory wherever you would like to store your ALPR data. Open PowerShell with administrator priveleges and cd into your new install directory.

Paste in the below commands. After that, everything will be set up automatically!

Set-ExecutionPolicy RemoteSigned
irm https://raw.githubusercontent.com/algertc/ALPR-Database/main/install.ps1 | iex

[thewolfman56]

Are you deploying on unraid? The requirements for unraid are slightly different for reasons I do not understand.

ANYTHING related to a relation, exclusion, etc. missing is a database issue. The install script has worked perfectly fine for most users, but if you are having issues, the above comments are very good suggestions. If those do not fix your installation, I would recommend deleting all docker images and volumes related to this app (delete everything if you don't have any other important apps).

I've tried ,y nest to make the manual install as OS agnostic as possible, but if you are still having trouble there is this legacy tutorial available.

I am going to make video tutorials soon.

I am having this issue in unRAID. Would love to see the video tutorial.

[Janhaus]

I thought I posted it here but I didn't - I figured out the issue for me, you have to run these commands:
psql -d postgres -U postgres -f /docker-entrypoint-initdb.d/schema.sql
psql -d postgres -U postgres -f /docker-entrypoint-initdb.d/migrations.sql
and adjust some settings in BI to get it to work. Full details in the ipcamtalk thread