How to empty a field with the "character varying" type in PostgreSQL?

Question

Trying to clear data in a column of the "character varying type", I send the following PostgreSQL request in PHP:

pg_query("UPDATE ".$database['prefix']."products SET file='' WHERE id=".$id) or die(pg_last_error());

The field is not truely cleared, but two single quotes are written in it.

In the database, I tried replacing ''::character varying by NULL::character varying, but it does not change anything.

What should be changed so that the field can be emptied?

score 8 · Answer 1 · answered Mar 02 '12 at 09:48

8

The field is not truely cleared, but two single quotes are written in it.

No, an empty string (a string of length zero) will be written.

If you want to "remove" any contents you need to set the column to NULL

UPDATE products
   SET file = NULL
WHERE ...

answered Mar 02 '12 at 09:48

1

Thank you. I just tried and that's correct: with NULL the field is truely emptied. As concerning the two single quotes displayed for an empty string, it seems being the representation chosen by pgAdmin to display empty strings in "Edit Data" grids. – user1244689 Mar 02 '12 at 09:55
There is here an interesting debate whether it is better to use NULL or empty strings: http://stackoverflow.com/questions/167952/null-or-empty-string-to-represent-no-data-in-table-column – user1244689 Mar 02 '12 at 10:19
@user1244689: being also a long time Oracle user I prefer NULL (as Oracle does not have empty strings anyway...) – Mar 02 '12 at 10:35

1 Answers1