CODEWITHSUNDEEP
pythonpostgresqlpostgis
Michael Kročka
Michael Kročka

Reputation: 655

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

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;

Upvotes: 0

Views: 1143

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

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.

Upvotes: 1

Related Questions