What is the equivalent of:

SELECT 0 as something;

but for strings, is it:

SELECT '' as something;

?

For more context, this is for a UNION ALL query

CodePudding user response：

If you want an "equivalent" of SELECT 0 in string form, then it would be SELECT '0'::int.

If you are looking for a non-null string to represent 'no data,' SELECT '' AS something would suffice

When you call UNION ALL, the expectation is that the columns would align and the data sets would be concatenated along their column orders. Since you want to UNION ALL an integer and char, you will get an error:

edb=# select 0 as something union all select '' as something;
ERROR:  invalid input syntax for type integer: ""
LINE 1: select 0 as something union all select '' as something;
                                               ^

Therefore, in order to UNION ALL, you'll want to cast your 0 as a char:

edb=# select 0::char as something union all select '' as something;
 something 
-----------
 0
 
(2 rows)

Caveat

I'm not sure if this is really what you want to do, but if you're looking to UNION ALL the two sets, that's how you'd do it. However, there's a chance this would open up a can of worms -- is '' going to be considered equivalent to 0? And what will you do with non-zero values? Will you still cast those integers into strings? I think you'll need to think through those implications and try to find a way to sanitize your data.

CodePudding user response：

Your first statement create a column of INT, your second statement creates a column of CHAR (A single character, not a string of characters).

There is no generic string data-type, so you probably want VARCHAR(32) or similar (where you must specify the desired maximum length).

In which case, you would use something like...

SELECT ''::VARCHAR(32) AS something

Which would create a column of VARCHAR(32), with just one row, where that row has an empty string as the value.

This can then be UNIONed with other data-sets containing a single column of VARCHAR(32).