pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oliver Elphick <olly@lfix.co.uk>
To: Christopher Sawtell <csawtell@xtra.co.nz>
Cc: Gary MacMinn <G.MacMinn@nca.gov.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Merging two columns into one
Date: Fri, 23 Jun 2000 06:53:37 +0100
Message-ID: <200006230553.e5N5rde14918@linda.lfix.co.uk> (raw)
In-Reply-To: <csawtell@xtra.co.nz> <00062317123201.15938@berty>
References: <s9536097.001@syd_aux.nca.gov.au>
	<00062317123201.15938@berty>

Christopher Sawtell wrote:
  >On Fri, 23 Jun 2000, Gary MacMinn wrote:
  >> Hi All,
  >> 
  >> I have two columns in a table (areacode and phone number) that I'd like to
      > merge
  >into one (phone number) containing both sets of info. Could anyone suggest a
  >simple way of achieving this?
  >
  >export the data to a file using the copy command,
  >remove the delimiter with the unix command 'tr -d'
  >import the file, now minus the delimeter using the copy command.

Surely tr will remove ALL the delimiters, so this is not helpful
unless these columns are the only ones in the table.  You would
have to use awk or perl to process the exported file and delete
the correct delimiter.

For an SQL solution, how about:

  SELECT col1, col2,..., areacode || phone as phone, colx, coly,...
     INTO new_table
     FROM table;

Then you can drop the old table and recreate it with the correct
columns and import the data into it from new_table.  (You could
just rename new_table if it doesn't need to be created with
constraints.)

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Train up a child in the way he should go, and when he 
      is old, he will not depart from it."    Proverbs 22:6 





view thread (7+ messages)  latest in thread

Message-ID: <200006230553.e5N5rde14918@linda.lfix.co.uk>
Permalink:  ../200006230553.e5N5rde14918@linda.lfix.co.uk/
Also on:    postgresql.org/message-id/200006230553.e5N5rde14918@linda.lfix.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: olly@lfix.co.uk, csawtell@xtra.co.nz, G.MacMinn@nca.gov.au
  Subject: Re: Merging two columns into one
  In-Reply-To: <200006230553.e5N5rde14918@linda.lfix.co.uk>
  References: <s9536097.001@syd_aux.nca.gov.au> <00062317123201.15938@berty> <200006230553.e5N5rde14918@linda.lfix.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox