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

[Janhaus]

I have the same setup, and same issue.
BlueIris on Windows, CodeProjectAI & ALPR Dashboard, both using the supplied docker images.
@tinker3433 - how did you access a folder to see saved images?

I ran the steps suggested and here's the results:

root@xxx:/# psql -d postgres -U postgres -f /docker-entrypoint-initdb.d/schema.sql
SET
SET
SET
SET
SET
set_config

(1 row)

SET
SET
SET
SET
CREATE EXTENSION
COMMENT
CREATE EXTENSION
COMMENT
psql:/docker-entrypoint-initdb.d/schema.sql:58: ERROR: function "update_updated_at_column" already exists with same argument types
ALTER FUNCTION
SET
SET
psql:/docker-entrypoint-initdb.d/schema.sql:77: ERROR: relation "known_plates" already exists
ALTER TABLE
psql:/docker-entrypoint-initdb.d/schema.sql:93: ERROR: relation "plate_notifications" already exists
ALTER TABLE
psql:/docker-entrypoint-initdb.d/schema.sql:108: ERROR: relation "plate_notifications_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
psql:/docker-entrypoint-initdb.d/schema.sql:140: ERROR: relation "plate_reads" already exists
ALTER TABLE
psql:/docker-entrypoint-initdb.d/schema.sql:155: ERROR: relation "plate_reads_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
psql:/docker-entrypoint-initdb.d/schema.sql:175: ERROR: relation "plate_tags" already exists
ALTER TABLE
psql:/docker-entrypoint-initdb.d/schema.sql:190: ERROR: relation "plates" already exists
ALTER TABLE
psql:/docker-entrypoint-initdb.d/schema.sql:196: ERROR: relation "idx_plates_occurrence_count" already exists
psql:/docker-entrypoint-initdb.d/schema.sql:207: ERROR: relation "tags" already exists
ALTER TABLE
psql:/docker-entrypoint-initdb.d/schema.sql:216: ERROR: relation "devmgmt" already exists
ALTER TABLE
psql:/docker-entrypoint-initdb.d/schema.sql:222: ERROR: relation "devmgmt" does not exist
LINE 1: INSERT INTO devmgmt (id, update1)
^
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
psql:/docker-entrypoint-initdb.d/schema.sql:440: ERROR: function "update_plate_occurrence_count" already exists with same argument types
ALTER FUNCTION
psql:/docker-entrypoint-initdb.d/schema.sql:444: ERROR: trigger "plate_reads_count_trigger" for relation "plate_reads" already exists
root@xxx:/#

[Janhaus]

Getting a diff error now:

[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