2

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?

8
  • I have tried running a select statement using your where clause? Do you get any results?
    – Jamie
    Commented Jan 3, 2013 at 19:16
  • yes I do get results from my select statement. Commented Jan 3, 2013 at 19:44
  • needs COMMIT; command postgresql.org/docs/7.4/static/sql-commit.html
    – Mapperz
    Commented Jan 3, 2013 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. Commented Jan 3, 2013 at 21:59
  • 2
    shouldn't it work without the EXPLAIN ANALYZE part
    – underdark
    Commented Jan 3, 2013 at 23:19

1 Answer 1

2

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.

1
  • 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... Commented Jan 4, 2013 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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