Error When Adding Features to Map From Database Connection

[MaxwellMiller_]

ArcGIS Pro 3.5.4

I have a PostgreSQL database with the PostGIS extension that I have connected to successfully and want to add features to the current map. I right click and select 'Add to Current Map' and I get the following error message. 

'Attribute column not found[ERROR; column "county" does not exist Line 1: select * from (select COUNTY, BOOK, MAP, PARCEL, SPLIT, ...'

I read somewhere on the ESRI forums that both the database column names need to be in lowercase. I would recommend that the error message be improved to detail that fact.

[MaxwellMiller_ · Accepted solution]

I am commenting to show a solution for those who have the privileges to add functions and are comfortable with PostgreSQL.

 Within psql (PostgreSQL terminal) or pg Admin 4, create the following function:

CREATE OR REPLACE FUNCTION rename_columns_to_lowercase(
    p_schema_name TEXT,
    p_table_name TEXT
)
RETURNS VOID AS $$
DECLARE
    column_rec RECORD;
BEGIN
    FOR column_rec IN
        SELECT
            c.column_name
        FROM
            information_schema.columns c
        WHERE
            c.table_schema = p_schema_name
            AND c.table_name = p_table_name
            AND c.column_name <> lower(c.column_name)
    LOOP
        EXECUTE format('ALTER TABLE %I.%I RENAME COLUMN %I TO %I',
            p_schema_name,
            p_table_name,
            column_rec.column_name,
            lower(column_rec.column_name));
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Now, you can call on that function to make all of the columns lowercase within a given table:

# Input in the form
# SELECT rename_columns_to_lowercase('schema', 'table');
SELECT rename_columns_to_lowercase('public', 'cv_parcels_county_extent')

After refreshing the connection within ArcGIS Pro, you should be able to add your data to the map. You might be prompted to select a primary key from one of your columns before you can add the table.