Received: from dukas.upc.es (dukas.upc.es [147.83.2.62]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3BBSL836524 for ; Wed, 11 Apr 2001 07:28:21 -0400 (EDT) (envelope-from amanda@labtie.mmt.upc.es) Received: from selene.upc.es (selene.upc.es [147.83.83.65]) by dukas.upc.es (8.10.1/8.10.1) with ESMTP id f3BBNwS05901 for ; Wed, 11 Apr 2001 13:23:58 +0200 (MET DST) Received: from batch.upc.es (root@batch.upc.es [147.83.92.19]) by selene.upc.es (8.8.8/8.8.6) with ESMTP id NAA12491 for ; Wed, 11 Apr 2001 13:28:15 +0200 (MET DST) Received: from labtie.mmt.upc.es (amanda@tester2.upc.es [147.83.92.52]) by batch.upc.es (8.9.3/8.9.3/Debian/GNU) with ESMTP id NAA26217 for ; Wed, 11 Apr 2001 13:28:15 +0200 Message-ID: <3AD44079.FCFE4583@labtie.mmt.upc.es> Date: Wed, 11 Apr 2001 13:31:05 +0200 From: Amanda Riera Organization: CTTC X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.17 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql Subject: Concatenate fields Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200104/152 X-Sequence-Number: 2500 Hi all, using PgSQL 7.0.3 I would like concatenate some fields to have all information in just one field. I'm doing this below: CREATE TABLE bill_2col AS SELECT bill.bill_id, (trim(text(bill.bill_number)) || ' | ' || trim(text(provider.company)) || ' | ' || trim(to_char(bill.issue_date,'MM/DD/YY')) || ' | ' || trim(to_char(bill.amount,'9999999.99')) || ' pts') AS billdesc FROM bill, provider WHERE bill.provider_id = provider.provider_id ORDER BY bill.bill_id; When it finds some empty field, it makes all the new field empty, no matters if the other are empty or not. Here you have the result table bill_id | billdesc ---------+--------------------------------------------------------------- 0 | Unknown | Unknown | 01/01/00 | .00 pts 1 | 98018097 | SUMI Informática | 12/22/99 | 1823520.00 pts 2 | 3 | 99018089 | PISTA CERO S.L | 12/01/99 | 1949380.00 pts 4 | 99018089 | DataSystem Informática | 12/01/99 | 1949380.00 pts 5 | 6 |