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;

is the column location declared as not null?

Jim Jones
– Jim Jones

2020-09-24 16:41:11 +00:00
Commented Sep 24, 2020 at 16:41 — Jim Jones
– Jim Jones, Commented Sep 24, 2020 at 16:41
It is not declared as not null.

Michael Kročka
– Michael Kročka

2020-09-24 16:45:13 +00:00
Commented Sep 24, 2020 at 16:45 — Michael Kročka
– Michael Kročka, Commented Sep 24, 2020 at 16:45

Jim Jones · Accepted Answer · 2020-09-24 17:38:51Z

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.

edited Sep 24, 2020 at 17:38

answered Sep 24, 2020 at 16:43

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Michael Kročka Over a year ago

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 Over a year ago

you're writing 'NULL' with single quotes. It should be NULL WITHOUT quotes :-)

Michael Kročka Over a year ago

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 Over a year ago

Which makes me think what would be the ideal form (string, tuple, etc.) of the point?

Jim Jones Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related