Postgresql script to insert a point into a table which accepts NULL values

Question

I am having trouble inserting point values into my Postgresql database. I have enabled the postgis extension and set the column to type geometry(Point,4326). I have some data that I want to store in my database, this data can have NULL values in this column. The following SQL script works when there are no NULL values:

INSERT INTO tweets(id, content, location, retweet_count, favorite_count, happened_at, author_id, country_id)
VALUES(%s, %s, '%s', %s, %s, %s, %s, %s) RETURNING id;

The third value, location, is the point type. Is there a surefire way to guarantee both NULL and point values will be recorded?

Update: After finding a piece of code online I updated my code accordingly:

if location is not None:
 sql = """INSERT INTO tweets(id, content, location, retweet_count, favorite_count, happened_at, author_id, country_id)
 VALUES(%s, %s, ST_SetSRID(ST_MakePoint(%s, %s),4326), %s, %s, %s, %s, %s) RETURNING id;"""
 data = (tweet_id, content, location[0], location[1], retweet_count, favorite_count, happened_at, author_id, country_id,)
else:
 sql = """INSERT INTO tweets(id, content, location, retweet_count, favorite_count, happened_at, author_id, country_id)
 VALUES(%s, %s, %s, %s, %s, %s, %s, %s) RETURNING id;"""
 data = (tweet_id, content, location, retweet_count, favorite_count, happened_at, author_id, country_id,)

After which I can select date from the database like this:

select * from tweets where location = 'POINT(3.576 6.6128)'::geography;

• Jim Jones: is the column location declared as not null?
• Michael Kročka: It is not declared as not null.

Answer 1

If the column location isn't set as not null, there shouldn't be a problem to add null values.

CREATE TEMPORARY TABLE t (geom geometry(point,4326));
INSERT INTO t VALUES (null),('SRID=4326;POINT(1 2)');
SELECT * FROM t;
 geom 
----------------------------------------------------
 
 0101000020E6100000000000000000F03F0000000000000040
(2 Zeilen)

Keep in mind that an empty string '' isn't a valid value! Insert either a valid geometry or null.

• Michael Kročka: An error pops up: parse error - invalid geometry LINE 3: xyz', 'NULL', 0, 1, 'Thu Aug 06 06:59:54... ^ HINT: "NU" <-- parse error at position 2 within geometry
• Jim Jones: you're writing 'NULL' with single quotes. It should be NULL WITHOUT quotes :-)
• Michael Kročka: Well yes, as stated in the SQL query in the question, this is the only way it accepts point values. If the '' quotes are removed, the point values can't be inserted.
• Michael Kročka: Which makes me think what would be the ideal form (string, tuple, etc.) of the point?
• Jim Jones: well, you have to provide the single quotes in the variable you're using to store the geometry, not hard code it in the insert statement. So, either your variable has a valid point or a null value.