topic Re: Cannot register postgres table in Geodatabase Questions

Cannot register postgres table

RiccardoKlinger — Fri, 07 Oct 2022 13:21:19 GMT

Dear ESRI Community,

We have some tables in our postgres database. the tables were creted like:

CREATE TABLE IF NOT EXISTS gd_base.polygon
(
objectid integer NOT NULL,
name character varying(255) COLLATE pg_catalog."default",
shape geometry(Polygon,32632) NOT NULL,
status integer NOT NULL,
created_user character varying(255) COLLATE pg_catalog."default",
created_date timestamp without time zone,
last_edited_user character varying(255) COLLATE pg_catalog."default",
last_edited_date timestamp without time zone,
)

Unfortunately we face an issue as it states:

After, I have dropped the NOT NULL constraint using dataGrip and retried the same process it was resulting in the same issue. But the column was again marked with "NOT NULL".

After that I also removed the objectid column from the table and rerun the process, leaving the field to be used as objectid empty.

The same problem appeared. We are lost...

Tried with ArcGIS Por 2.9/2.8 Arcmap 10.8.1, ArcPY running on postgres12 with postgis and sde extension.

Re: Cannot register postgres table

RiccardoKlinger — Fri, 07 Oct 2022 13:56:17 GMT

If someone faces similar problems:

We've removed the "NOT NULL" constraint from the shape column and it is now working. We can register our tables with an already defined objectid column and with no defined objectid column.

Re: Cannot register postgres table

RiccardoKlinger — Fri, 07 Oct 2022 14:05:39 GMT

the answer was given in this thread:

https://community.esri.com/t5/geodatabase-questions/register-view-with-geodatabase-not-null-constraint/m-p/1027571#M6966

Re: Cannot register postgres table

VinceAngelo — Mon, 10 Oct 2022 01:41:29 GMT

If you use objectid serial NOT NULL in the table declaration, ArcGIS will know how to deal with it at registration.

- V

Re: Cannot register postgres table

RiccardoKlinger — Mon, 10 Oct 2022 06:31:14 GMT

Hi @VinceAngelo , yes, it will recognize it, which is a desired behaviour. But still: if the SHAPE column is set to NOT NULL, the process itself is not working and giving the above error message. It would be VERY good to rewrite the error message and tell the user:

SHAPE column must not have a NOT NULL constraint

instead of:

The column must not have a NOT NULL constraint