I am trying to bring in data into arcmap from my PostgreSql + Postgis database. The data are points (using ST_Geometry POINTZ types) containing various properties/attibutes. So I successfully connected to my database by creating a new query layer, but many common sql query I put in the 'query' section gives me errors. Below is a screenshot of the query layer dialog.
Here are some errors I get when performing the sql queries
select * from firstdb.public.purcell7 -> attribute conversion error
select geom from firstdb.public.purcell7 -> at least one fixed-point or string data type column must be present to generate unique identifer
But if I do
select id from firstdb.public.purcell7 the validate button does not produce any errors
and in arcmap in the table of contents I get
Then if I right-click the firstdb I can 'Add Table' and select the entire table I want (which is what the sql command : select * from firstdb.public.purcell7 should have done but didn't successfully do). Then I select the 'Display XY Data' option for that table where the x and y fields are automatically filled in my x, y columns of the data and hit 'ok' and I get the following warning:
Now the table I am trying to import has OIDs setup, there is a spatial index associated with the table, and there is a proper ST_Geometry associated with the data (POINTZ type).
How do I properly bring in my spatial data from my postgresql + postgis database so that I can view, query, select the data in arcmap?