PostGIS & Python SQL Query formatting: Create Geometry only for entries that are not NULL

Question

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.

Answer 1

photo_latlng will never be null as is always contains at least "POINT( )". You need to validate that the coordinates are or not null. If they are null, you insert a null geometry, else, you construct the proper geometry.

newgeom = "NULL"
if not photo_longitude == None and  not photo_latitude== None:
   newgeom = " ST_SetSRID(ST_MakePoint(%s %s), 4326) " % (photo_longitude,photo_latitude) 

#no change to the sql
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)                            
        '''
#use the new geometry/null geometry
cursor.execute(insert_sql,(post_guid,newgeom ))

Answer 2

See above (better) answer from JGH. This was my solution (also works, but kind of ugly):

photo_latlng = "POINT(%s %s)" % (photo_longitude,photo_latitude) #WKTconversion
if photo_latlng == None:
    geometry = "%s"
else:    
    geometry = "ST_GeomFromText(%s,4326)" 
insert_sql = '''
        INSERT INTO "post" (post_guid, post_latlng)
        VALUES (%s,''' + geometry + ''')
        ON CONFLICT (post_guid)
        DO UPDATE SET
            post_latlng = COALESCE(EXCLUDED.post_latlng, "post".post_latlng),                            
        '''
cursor.execute(insert_sql,(post_guid,photo_latlng))