Menu
I've tried a few variations, but from my reading of the the documentation this pattern should work '' || val1 || val1 ... yet my result is an empty column ...
thedb=# \d buildings_propertyvalue;
Table "public.buildings_propertyvalue"
Column | Type | Modifiers
-----------+------------------------+----------------------------------------------------------------------
id | integer | not null default nextval('buildings_propertyvalue_id_seq'::regclass)
prop_id | integer | not null
place_id | integer | not null
float_val | double precision |
int_val | integer |
char_val | character varying(255) |
text_val | text |
thedb=# select * from buildings_propertyvalue limit 10;
id | prop_id | place_id | float_val | int_val | char_val | text_val
-----+---------+----------+-----------+---------+----------+----------
798 | 3 | 170 | | 831 | |
2 | 46 | 180 | | 0 | |
733 | 2 | 180 | 40 | | |
737 | 10 | 180 | | 0 | |
740 | 5 | 345 | 100 | | |
742 | 10 | 345 | | 0 | |
11 | 2 | 170 | 50 | | |
744 | 11 | 345 | 0 | | |
746 | 14 | 345 | | | 52 |
749 | 46 | 348 | | 0 | |
(10 rows)
thedb=# select prop_id, place_id, '' || float_val || int_val || char_val || text_val as val from buildings_propertyvalue limit 10;
prop_id | place_id | val
---------+----------+-----
3 | 170 |
46 | 180 |
2 | 180 |
10 | 180 |
5 | 345 |
10 | 345 |
2 | 170 |
11 | 345 |
14 | 345 |
46 | 348 |
(10 rows)
392
The PostgreSQL concatenate operator ( || ) is used to concatenate two or more strings and non strings.
In PostgreSQL, the CONCAT function is used to concatenate two or more strings into one. Let's analyze the above syntax: The CONCAT function accepts a list of string convertible arguments. A string in this context means any of the following data types: char, varchar, or text.
SELECT *, CONCAT(FIRSTNAME, LASTNAME) AS FIRSTNAME FROM demo_table; Output: Here, we can see that FIRSTNAME and LASTNAME is concatenated but there is no space between them, If you want to add space between the FIRSTNAME and LASTNAME then add space(' ') in CONCAT() function. This method will change the original table.
To combine first and last names, use the CONCATENATE function or the ampersand (&) operator.
Concatenating a NULL with a non-empty string yields a NULL
Since your *_val columns are nullable, it's probably what is happening.
Try this:
'' || COALESCE(float_val::TEXT, '') || COALESCE(int_val::TEXT, '') || COALESCE(char_val, '') || COALESCE(text_val, '')
or, if you can only have at most one non-null value, just this:
COALESCE(float_val::TEXT, int_val::TEXT, char_val, text_val, '')
Note that in PostgreSQL, unlike some other engines, TEXT has no downsides compared to VARCHAR. There is no point in separating TEXT and VARCHAR data.
197
Concatenating a NULL value to any other value produces a NULL. It looks like some of the columns being concatenated are nullable, so you will need to wrap a COALESCE function around them to force an empty string or some other placeholder value when they're NULL.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
