I think this is rather a formatting issue. I have a database with a geometry Column that sometimes contains points. Normally, it will be None/Null.
When I format the SQL query in psychopg2 and python (for inserting entries), it looks like this:
insert_sql = '''
INSERT INTO "post" (post_guid, post_latlng)
VALUES (%s,ST_GeomFromText(%s,4326))
ON CONFLICT (post_guid)
DO UPDATE SET
post_latlng = COALESCE(EXCLUDED.post_latlng, "post".post_latlng),
'''
cursor.execute(insert_sql,(post_guid,photo_latlng))
However, this does not work if photo_latlng = None
I tried:
if geoinformation_available:
photo_latlng = "POINT(%s %s)" % (photo_longitude,photo_latitude) #WKTconversion
else:
photo_latlng = None
if not photo_latlng == None:
photo_latlng = "ST_GeomFromText(" + photo_latlng + ",4326)"
insert_sql = '''
INSERT INTO "post" (post_guid, post_latlng)
VALUES (%s,%s)
ON CONFLICT (post_guid)
DO UPDATE SET
post_latlng = COALESCE(EXCLUDED.post_latlng,"post".post_latlng)
'''
cursor.execute(insert_sql,(post_guid,photo_latlng))
.. but it gives me a psycopg2.InternalError: parse error - invalid geometry (HINT: "ST" <-- parse error at position 2 within geometry). I also doubt that this will work with COALESCE -> only fill row if entry is not Null.