Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 2 down vote favorite

I've been trying to update a column of integers called level_dept in a table called adminboundaries within the sql query tool on pgadmin3 and the query runs and finishes with a lot of lag, but 0 rows are affected. I didn't receive any syntax errors and I've set the privileges to allows the user role to perform all actions, are there any other particular settings or things I should check that I should know about? 

BEGIN;
EXPLAIN ANALYZE
UPDATE AdminBoundaries
SET "LEVEL_DEPT" = "LEVEL_DEPT" - 1
WHERE "NAME_3" = NULL AND "LEVEL_DEPT" = 3; 
COMMIT;

Error Log:

2013-01-03 15:58:03 EST STATEMENT:  BEGIN;
    EXPLAIN ANALYZE
    UPDATE AdminBoundaries
    SET "LEVEL_DEPT" = "LEVEL_DEPT" - 1
    WHERE "NAME_3" = NULL AND "LEVEL_DEPT" = 3; 
    COMMIT;

Despite trying a few variations of syntax, I think it might be a pgadmin related issue? I'm not sure though. I'm considering changing the postgresql.conf file. I've already enabled seq_scan to be off. There are other parameters I'm considering changing.

Is it necessary to look into the nuances of postgresql as far as dirty reads, commit delays, etc when processing in a table of only 250k rows?

share|improve this question
I have tried running a select statement using your where clause? Do you get any results? – Jamie Jan 3 at 19:16
yes I do get results from my select statement. – Carl Carlson Jan 3 at 19:44
needs COMMIT; command postgresql.org/docs/7.4/static/sql-commit.html – Mapperz♦ Jan 3 at 20:20
didn't work. command has been edited. The message was:Query result with 8 rows discarded. Query returned successfully with no result in 14 ms. – Carl Carlson Jan 3 at 21:59
1  
shouldn't it work without the EXPLAIN ANALYZE part – underdark♦ Jan 3 at 23:19
show 3 more comments

1 Answer

up vote 1 down vote

The problem is with "NAME_3" = NULL, since NULL is not "equal to" NULL. This should instead use the proper SQL construct "NAME_3" IS NULL. See the documentation for more details.

share|improve this answer
I've actually tried that, it still didn't give me any results. But, is it supposed to? It gave me:Query result with 5 rows discarded. Query returned successfully with no result in 16 ms. Yet, I'm not discarding anything here... – Carl Carlson Jan 4 at 15:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.