Concatentation of 2 Columns in PostgreSQL While Ignoring NULL Values

Question

I am trying to concatenate 2 columns (one is a CHAR(7) and the other is CHAR(6)), add an _ separator between the 2 and limit the result to 10,000 records. I also want to take a random sample.

I have read a number of excellent posts on here including:

Combine two columns and add into one new column

The above post as well as others I have looked at had great starting points for me but I have not yet managed to deliver the expected result.

First I tried:

SELECT osnrth1m||'_'||oseast1m FROM postzon
ORDER BY RANDOM()
LIMIT 10000

While it delivered the correct result of the form '1234567_123456' (ignore the quotes) there were a number of results that were '_'.

The linked post above said that this was because using the concatenation operator (i.e attribute1||attribute2) does not account for NULL values.

I then tried to use the COALESCE function:

SELECT COALESCE(osnrth1m,'')||'_'||COALESCE(oseast1m,'') FROM postzon
ORDER BY RANDOM()
LIMIT 10000;

But this also produced some results that were just underscores as per previous.

I then tried the CONCAT_WS function:

SELECT CONCAT_WS('_',osnrth1m,oseast1m) FROM postzon
ORDER BY RANDOM()
LIMIT 10000;

But unfortunately again some rows returned with just an underscore.

Finally I tried messing about with CASE - I couldn't work out a way to write the query so that NULL values were ignored. In fact the last one I tried specifically says if NULL then show result as NULL (that's what I think it's saying anyway!):

SELECT CASE WHEN (osnrth1m,oseast1m) IS NULL THEN NULL
        ELSE CONCAT_WS('_',osnrth1m,oseast1m) 
        END
        FROM postzon
ORDER BY RANDOM()
LIMIT 10000;

I'm sorry if I'm missing something glaringly obvious but could anyone suggest a way achieve this that will just skip NULL values completely?

Something along the lines of:

SELECT CASE WHEN (osnrth1m,oseast1m) IS NULL THEN IGNORE/DROP/EXCLUDE
            ELSE CONCAT_WS('_',osnrth1m,oseast1m) 
            END
            FROM postzon
    ORDER BY RANDOM()
    LIMIT 10000;

Is there a PostgreSQL operator like IGNORE or DROP or EXCLUDE?

For each of the queries above I tried including the following line after the SELECT statement:

WHERE (osnrth1m IS NOT NULL OR oseast1m IS NOT NULL) 

but this didn't seem to change the results.

Any help would be gratefully received!

Thanks

Answer 1

You need both osnrth1m and oseast1m to be not null. This is why you should use and but not or:

WHERE (osnrth1m IS NOT NULL AND oseast1m IS NOT NULL)

And the query is:

SELECT
        CONCAT_WS('_',osnrth1m,oseast1m)
    FROM postzon
    WHERE (osnrth1m IS NOT NULL AND oseast1m IS NOT NULL)
    ORDER BY RANDOM()
    LIMIT 10000;

If there a empty osnrth1m and oseast1m values which also need to be excluded then:

SELECT
            CONCAT_WS('_',osnrth1m,oseast1m)
        FROM postzon
        WHERE
            osnrth1m IS NOT NULL AND
            oseast1m IS NOT NULL AND
            osnrth1m <> '' AND
            oseast1m <> ''
        ORDER BY RANDOM()
        LIMIT 10000;