To replace NULL values with an empty string, use COALESCE(column_name, ''). You'll need to know the column name and do this for each column name.

To convert a column into text, you can use column_name::TEXT or CAST(column_name AS TEXT). You'll also need to know the column names.

If you just want something quick and dirty that achieves something close to your goal, you can convert the whole row tuple into text:

SELECT my_table::TEXT FROM my_table

In this case, you'll get parentheses around the row which each value separated by a column. It's generally not ideal, and you'll lose the column name information, but could be handy if you want a quick and simple text search for example. (I'm sure there are better solutions to that particular problem.)

Another option, which may depend on the client you're using, is to use COPY (or psql's \copy) and export your data in CSV format to the standard output or to a file. There are more details on this in other answers.

The default CSV value for NULL is the empty string, but you could have quotes or something else using WITH NULL AS ...

You could also use some dynamic PL/pgSQL to create your query. Building the query could look like this:

SELECT 'SELECT '
       || string_agg('COALESCE('
                     || attname::TEXT
                     || '::TEXT, '''')' , ',' ORDER BY attnum)
       || ' FROM my_table'
    INTO q
    FROM pg_attribute WHERE  attrelid = 'my_table'::regclass
     AND attnum > 0 AND NOT attisdropped;

It's then a problem regarding how you want to return those results (since you'd may to know the columns in advance for using record or row types).

Since you're only interested in text, you might as well return each row as a TEXT array:

CREATE OR REPLACE FUNCTION my_function() RETURNS SETOF TEXT[]
AS $$
DECLARE
    q TEXT;
    r TEXT[];
BEGIN
    SELECT 'SELECT ARRAY['
           || string_agg('COALESCE('
                         || attname::TEXT
                         || '::TEXT, '''')' , ',' ORDER BY attnum)
           || '] FROM my_table'
        INTO q
        FROM pg_attribute WHERE  attrelid = 'my_table'::regclass
         AND attnum > 0 AND NOT attisdropped;
    FOR r IN EXECUTE q
    LOOP
        RETURN NEXT r;
    END LOOP;
END
$$ LANGUAGE plpgsql;

SELECT my_function();

Use COALESCE(city, '') - it will coerce NULL string to empty string "". If you have lots of cities and lots of queries to them, you can create an index on coalesced field to foster search.

"SELECT location.id FROM location WHERE COALESCE(location.city, '') = :city "

The types of values need to be consistent; coalescing the empty string to a 0 means that you cannot then compare it to null in the nullif. So either of these works:

# create table tests (orig varchar);
CREATE TABLE

# insert into tests (orig) values ('1'), (''), (NULL), ('0');
INSERT 0 4


# select orig, cast(coalesce(nullif(orig,''),'0') as float) as result from tests;
 orig | result 
------+--------
    1 |      1
      |      0
      |      0
    0 |      0
(4 rows)


# select orig, coalesce(cast(nullif(orig,'') as float),0) as result from tests;
 orig | result 
------+--------
 1    |      1
      |      0
      |      0
 0    |      0
(4 rows)

There is the NULLIF() function:

SELECT NULLIF(var, '');

If var equals the 2nd parameter, you get NULL instead.
The example replaces the empty string '' with NULL.

There is no "empty string" for the type integer. Both parameters must be of compatible type, so sanitize your input in PHP.

If you did not define a column default, you can also just omit the column in the INSERT command and it will be filled with NULL (which is the default DEFAULT).

Check if the parameter is empty in PHP and don't include the column in the INSERT command if it is.

Or use the PHP literal NULL instead like Quassnoi demonstrates here.

The rest only makes sense for string types

To make absolutely sure, nobody can enter an empty string add a CHECK constraint to the table:

ALTER TABLE tr_view
ADD CONSTRAINT tr_view_age_not_empty CHECK (age <> '');

To avoid exceptions caused by this, you could add a trigger that fixes input automatically:

CREATE OR REPLACE FUNCTION trg_tr_view_avoid_empty()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF NEW.age = '' THEN
      NEW.age := NULL;
   END IF;

   IF NEW.month = '' THEN
      NEW.month := NULL;
   END IF;

   RETURN NEW;
END
$func$;

CREATE TRIGGER tr_view_avoid_empty
BEFORE INSERT OR UPDATE ON tr_view
FOR EACH ROW
WHEN (NEW.age = '' OR NEW.month = '')
EXECUTE FUNCTION trg_tr_view_avoid_empty();

You have two options:

1. you change the empty strings in the original data to NULLs and then change the type
2. you handle them while changing the type

The first one would be a simple

UPDATE table SET col=NULL where col='';

but this naturally takes time and is a bit unnecessary since you're changing the type after that.

Second way is to handle the empty strings while converting

ALTER TABLE mapdata
  ALTER COLUMN lat TYPE double precision
    USING (NULLIF(lat, '')::double precision);

The NULLIF will return NULL if the lat column is an empty string, otherwise it'll return the lat column value. This will be a lot more efficient than to first change the source data.