Re: [SQL] How to collect text-fields from multiple rows ?
Andreas wrote on 16.10.2010 05:23: Hi, how can I collect text-fields from multiple rows into one output row? I'd like to do an equivalent to the aggregate function SUM() only for text. The input is a select that shows among other things a numerical column where I would like to group by. The text column of all rows in a group should get concatenated into 1 text devided by a '\n'. Even better would be if I could add a second text colum per line as topic. Input e.g. select group_nr::integer, memo::text, topic::text ... 1, 'bla ', 'weather' 2, 'yada..', 'weather' 2, 'talk talk..', 'cooking' 2, 'words words, ...', 'poetry' 3, Output: 1, 'weather\nbla...' 2, 'weather\nyada..\ncooking\ntalk talk..\npoetry\nwords words, ...' If you are on 9.0: SELECT group_nr, string_agg(memo||'--'||topic, '--') FROM the_table_with_no_name GROUP BY group_nr; On 8.x you need to user array_agg() SELECT group_nr, array_to_string(array_agg(memo||'--'||topic),'--') FROM the_table_with_no_name GROUP BY group_nr; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] SIMILAR TO
Hi guys, I use the following code to detect alphanumeric strings: IF _my_variable SIMILAR TO '^[a-zA-Z0-9]+$' THEN // do stuff here END IF; In pg8.4 this worked perfectly. I upgraded to pg9.0 and it no longer works. From the release notes it appears that the behavior of SIMILAR TO has changed in pg9.0. My question is, how do I modify my code so that it works in 9.0? Thanks in advance. Rommel. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SIMILAR TO
Rommel the iCeMAn writes: > I use the following code to detect alphanumeric strings: > IF _my_variable SIMILAR TO '^[a-zA-Z0-9]+$' THEN > // do stuff here > END IF; > In pg8.4 this worked perfectly. I upgraded to pg9.0 and it no longer > works. From the release notes it appears that the behavior of SIMILAR > TO has changed in pg9.0. My question is, how do I modify my code so > that it works in 9.0? Drop the ^ and $; they are incorrect for SIMILAR TO. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SIMILAR TO
Thank you very much. I figured it was something simple but I am not proficient at regular expressions. Thanks again. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Saturday, October 16, 2010 10:30 AM To: Rommel the iCeMAn Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] SIMILAR TO Rommel the iCeMAn writes: > I use the following code to detect alphanumeric strings: > IF _my_variable SIMILAR TO '^[a-zA-Z0-9]+$' THEN > // do stuff here > END IF; > In pg8.4 this worked perfectly. I upgraded to pg9.0 and it no longer > works. From the release notes it appears that the behavior of SIMILAR > TO has changed in pg9.0. My question is, how do I modify my code so > that it works in 9.0? Drop the ^ and $; they are incorrect for SIMILAR TO. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
