topic Re: Postgresql trigger to maintain coloumn uniqueness in ArcGIS Field Maps Questions

Postgresql trigger to maintain coloumn uniqueness

yockee — Mon, 01 Dec 2025 07:24:34 GMT

I am writing a trigger in Postgresql database so that one particular coloumn can maintain its uniqueness.

The idea is to check the data before they are entered. The data are entered via Field Map.

So, I write a Function below :

CREATE OR REPLACE FUNCTION xxx_check_unique()
RETURNS TRIGGER
AS
$$
BEGIN
IF (SELECT EXISTS(SELECT FROM scema1.chamber where id = new.id)) = 'false' THEN
RETURN new; //entry the new record
else
RAISE EXCEPTION SQLSTATE '90001' USING MESSAGE = 'DUP';
return null; //do not entry due to duplication
end if;
END;

CREATE OR REPLACE TRIGGER xx_check_unique
BEFORE update of id ON scema1.chamber
FOR EACH ROW EXECUTE PROCEDURE xxx_check_unique();

CREATE OR REPLACE TRIGGER xx_check_unique
BEFORE insert ON scema1.chamber
FOR EACH ROW EXECUTE PROCEDURE xxx_check_unique();

That trigger works using Field Map if I enter an ID that's never been entered before.

If the ID entered has existed in the record, then an error comes up :

Then, if I want to entry another data, *i have to repeat the process from the very beginning, with all empty coloumns*. I f I just change the ID into another unique ID, the same error message comes up just like the picture above.

So, how can I employ such a trigger.

FYI, this trigger works in Desktop without error.

Thanks

Re: Postgresql trigger to maintain coloumn uniqueness

CodyPatterson — Mon, 01 Dec 2025 14:07:44 GMT

Hey @yockee

Are these IDs bound by any certain restriction? Be it like a GUID or similar? When a feature is inserted, you could have a calculated field on Field Maps create a GUID using the GUID() function, or something like

return count($layer) + 1;

That would return a much different ID but still something unique, what are your restrictions currently?

Cody

Re: Postgresql trigger to maintain coloumn uniqueness

yockee — Tue, 02 Dec 2025 00:48:44 GMT

Hi @CodyPatterson ,

The only restriction is that it has to be unique. This ID is based on another layer underneath it (the ID of the layer underneath is already unique) and the data have to be entered manually. I have to run this mechanism of entering it manually because I have to do so : the layer underneath is a polyline, whereas the one that I am working is a point layer. One of the points inside a polyline must have the ID of that polyline.

The trigger above somehow actually works in Field Map not in a way it is intended. If I enter an ID that is already existed and filled in all other text boxes (there are around 12 questionaires), press SUBMIT, and then that error (that error in the pic above) comes up. After the error, I just *can not simply change* the ID to another unique value and then press SUBMIT again. The same error comes up.

But, if I do the entry from the beginning (with all 12 text boxes emptied out), and put in a unique ID, it will work without error. This is pains as I have to fill in all the text boxes again.

I have overcome this problem of re-entering-data-from-beginning by employing Attribute Validation, set in Arcgis Pro, below. But this method is rather process heavy because all data are copied into Datastore. If only a few records this method will work fine.

(PS: I am curious, where does this validation is stored ? i presume in Arcgis Server )

:::
if (id == null || id == "") {
return true;
}

var fs = FeatureSetByName(
$datastore,
"db_city.scema1.data1",
["oid_code ", "objectid"],
false
);

var oid_self = $feature.objectid;

var others = Filter(fs, "oid_code = @id AND objectid <> @oid_self");

if (Count(others) > 0) {
return {
"errorMessage": "id already existed"
};
}

Re: Postgresql trigger to maintain coloumn uniqueness

JeffSilberberg — Wed, 03 Dec 2025 17:21:20 GMT

@yockee

I believe you will find that your PostgreSQL function is broken -

IF (SELECT EXISTS(SELECT FROM scema1.chamber where id = new.id)) = 'false' THEN

Should probably be

IF NOT EXISTS (SELECT 1 FROM schema1.chamber WHERE id = NEW.id) THEN

As EXISTS returns a Bool, not a Text Value, which you have specified. Also, your RAISE EXCEPTION does not look correct, but maybe, it could just be a style thing.

Re: Postgresql trigger to maintain coloumn uniqueness

yockee — Thu, 11 Dec 2025 03:00:10 GMT

Hi @JeffSilberberg The code you provide gives out the same result.

Anyway, now I find out behaviour in Field Map this :

1. I enter duplicate value. press SUBMIT

2. Error comes up "Unable to submit. Service error code 500. bla bla"

3. Then without exiting, I edit the value again. Now type in a non-duplicate value. press SUBMIT again

4. Error comes up "Unable to submit. Service error code 500. bla bla"

5. Then I press SUBMIT again straight away. Success.

6. I check the database. The record is there with objectID x+3

What I want is, when I press SUBMIT for the second time (step #3), the record will be inserted without error.

The trigger itself is not a problem when executed using database query run from PGADMIN.

Thanks

FYI, Anyway, these 3 codes give out the same result in postgres:

1.IF (SELECT EXISTS(SELECT FROM scema1.chamber where id = new.id)) = 'false' THEN

2.IF (SELECT EXISTS(SELECT 1 FROM scema1.chamber where id = new.id)) = 'false' THEN

3.IF NOT EXISTS (SELECT 1 FROM schema1.chamber WHERE id = NEW.id) THEN

Re: Postgresql trigger to maintain coloumn uniqueness

JeffSilberberg — Thu, 11 Dec 2025 03:39:58 GMT

Use ChatGPT to check your logic and syntax. You have a number of issues here, including that you can not compare a Boolean to a String.

but there are several PostgreSQL syntax issues:

1. schema1 is spelled wrong (scema1)

2. EXISTS(...) returns a boolean, not text

You should compare it to TRUE/FALSE, not the string 'false'.

3. PostgreSQL does NOT use // for comments

Use -- or /* */.

**4. RETURN NULL is correct, but must come after raising the exception (which already stops execution)**

5. Trigger functions must be in LANGUAGE plpgsql

CREATE OR REPLACE FUNCTION xxx_check_unique() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN -- If no record exists with the same ID, allow insert/update IF NOT EXISTS (SELECT 1 FROM schema1.chamber WHERE id = NEW.id) THEN RETURN NEW; ELSE -- Raise custom error RAISE EXCEPTION SQLSTATE '90001' USING MESSAGE = 'DUP: id already exists in schema1.chamber'; RETURN NULL; END IF; END; $$;

Re: Postgresql trigger to maintain coloumn uniqueness

yockee — Thu, 11 Dec 2025 06:11:14 GMT

@JeffSilberberg Yes.. shouldn't be "scema". I just want to show that the table reside in a schema. Thanks

Anyway, I did try the code you give me. Still the same behaviour on the Field Map, just like what I have described above.

The problem is not with the trigger, but how the Field Map reacts to the trigger.

Re: Postgresql trigger to maintain coloumn uniqueness

yockee — Fri, 12 Dec 2025 09:30:53 GMT

Here are some results that I pulled from Arcgis Server Log:

=== else RAISE EXCEPTION SQLSTATE '90001' USING MESSAGE = 'DUP'; return null;
SEVERE Dec 12, 2025, 14:36:02 An error occurred. /test_1.MapServer
SEVERE Dec 12, 2025, 14:36:02 Error: Underlying DBMS error [ERROR: 504 INSERT CONTEXT: PL/pgSQL function test() line 17 at RAISE (schema1.test_1)::SQLSTATE=P0001] [test_1]. Test01/test_1.MapServer

===else RAISE EXCEPTION SQLSTATE '90001' USING MESSAGE = 'DUP'; return 0;
Error: Underlying DBMS error [ERROR: cannot return non-composite value from function returning composite type CONTEXT: PL/pgSQL function test() line 19 at RETURN (schema1.test_1)::SQLSTATE=42804] [test_1].

===else RAISE EXCEPTION SQLSTATE '90001' USING MESSAGE = 'DUP';
Error: Underlying DBMS error [ERROR: control reached end of trigger procedure without RETURN CONTEXT: PL/pgSQL function test() (schema1.test_1)::SQLSTATE=2F005] [test_1].

===Without raise dan Without return
No error message

Re: Postgresql trigger to maintain coloumn uniqueness

JeffSilberberg — Fri, 12 Dec 2025 14:39:20 GMT

Sorry, I don't do much with Field Maps, and have nothing else to offer here.

Re: Postgresql trigger to maintain coloumn uniqueness

yockee — Mon, 26 Jan 2026 03:19:55 GMT

I come to a conclusion that somehow the Field Map can not handle such error in a correct manner.

Allow me to summarize the trouble after a trigger is implemented:

1. First, entry an *incorrect* record (data that has already existed)

2. The response from the Field Map : "Error.."

3. Second, entry a *correct* data.

4. The response from the Field Map : "Error.."

5. Repeat step 3 by directly hitting the Save button

6. The response from the Field Map : "Success !"

So, for my case above, the second entry (step number 3), the Field Map still regard it as error. The subsequent entry (step number 5), is success. The trigger is not a problem.

It seems that the Field Map still regard the second entry (step number 3) incorrect due to the first entry (step number 1). Perhaps there is a cache that has not been cleared. Or maybe something else.

I can not confirm the exact cause.

thanks