Postgresql trigger to maintain coloumn uniqueness

[yockee]

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

[CodyPatterson]

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

[yockee]

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

[JeffSilberberg]

@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. 

[yockee]

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

[JeffSilberberg]

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

[yockee]

@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.

[yockee]

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

[JeffSilberberg]

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