pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMerging two columns into one
7+ messages / 5 participants
[nested] [flat]
* Merging two columns into one
@ 2000-06-23 03:04 Gary MacMinn <G.MacMinn@nca.gov.au>
2000-06-23 05:10 ` Re: Merging two columns into one Christopher Sawtell <csawtell@xtra.co.nz>
2000-06-23 11:35 ` Re: Merging two columns into one D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 2 replies; 7+ messages in thread
From: Gary MacMinn @ 2000-06-23 03:04 UTC (permalink / raw)
To: pgsql-sql
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?
Thanks,
Gary MacMinn
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Merging two columns into one
2000-06-23 03:04 Merging two columns into one Gary MacMinn <G.MacMinn@nca.gov.au>
@ 2000-06-23 05:10 ` Christopher Sawtell <csawtell@xtra.co.nz>
2000-06-23 05:53 ` Re: Merging two columns into one Oliver Elphick <olly@lfix.co.uk>
1 sibling, 1 reply; 7+ messages in thread
From: Christopher Sawtell @ 2000-06-23 05:10 UTC (permalink / raw)
To: Gary MacMinn <G.MacMinn@nca.gov.au>; pgsql-sql
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.
--
Sincerely etc.,
NAME Christopher Sawtell - iOpen Technologies Ltd.
CELL PHONE 021 257 4451
ICQ UIN 45863470
EMAIL chris @ iopen . co . nz, csawtell @ xtra . co . nz
WWW http://www.iopen.co.nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Merging two columns into one
2000-06-23 03:04 Merging two columns into one Gary MacMinn <G.MacMinn@nca.gov.au>
2000-06-23 05:10 ` Re: Merging two columns into one Christopher Sawtell <csawtell@xtra.co.nz>
@ 2000-06-23 05:53 ` Oliver Elphick <olly@lfix.co.uk>
2000-06-23 11:40 ` Re: Merging two columns into one D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 1 reply; 7+ messages in thread
From: Oliver Elphick @ 2000-06-23 05:53 UTC (permalink / raw)
To: Christopher Sawtell <csawtell@xtra.co.nz>; +Cc: Gary MacMinn <G.MacMinn@nca.gov.au>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Merging two columns into one
2000-06-23 03:04 Merging two columns into one Gary MacMinn <G.MacMinn@nca.gov.au>
2000-06-23 05:10 ` Re: Merging two columns into one Christopher Sawtell <csawtell@xtra.co.nz>
2000-06-23 05:53 ` Re: Merging two columns into one Oliver Elphick <olly@lfix.co.uk>
@ 2000-06-23 11:40 ` D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 0 replies; 7+ messages in thread
From: darcy@druid.net @ 2000-06-23 11:40 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Christopher Sawtell <csawtell@xtra.co.nz>; Gary MacMinn <G.MacMinn@nca.gov.au>; pgsql-sql
Thus spake Oliver Elphick
> SELECT col1, col2,..., areacode || phone as phone, colx, coly,...
Although you may want this for easier reading.
SELECT col1, col2,..., (areacode || ' ') || phone as phone, colx, coly,...
Also, I would do "as fullphone" instead so that I don't get confused
between the field name and my generated string but that's mainly a
style issue.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Merging two columns into one
2000-06-23 03:04 Merging two columns into one Gary MacMinn <G.MacMinn@nca.gov.au>
@ 2000-06-23 11:35 ` D'Arcy J.M. Cain <darcy@druid.net>
2000-06-23 15:42 ` Re: Merging two columns into one Jeff Hoffmann <jeff@propertykey.com>
1 sibling, 1 reply; 7+ messages in thread
From: darcy@druid.net @ 2000-06-23 11:35 UTC (permalink / raw)
To: Gary MacMinn <G.MacMinn@nca.gov.au>; +Cc: pgsql-sql
Thus spake Gary MacMinn
> 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?
Oliver showed you how to merge these together with the SQL concatenate
operator but there is another possibility. PostgreSQL could use a
phone number type. I have been meaning to write a user defined type
for some time but never seem to get around to it. Maybe you could
give this a shot. The examples in contrib should get you started.
Once finished you can make the phone # one column and split out parts
with various functions. Don't forget to have a place for country code
and extension.
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Merging two columns into one
2000-06-23 03:04 Merging two columns into one Gary MacMinn <G.MacMinn@nca.gov.au>
2000-06-23 11:35 ` Re: Merging two columns into one D'Arcy J.M. Cain <darcy@druid.net>
@ 2000-06-23 15:42 ` Jeff Hoffmann <jeff@propertykey.com>
0 siblings, 0 replies; 7+ messages in thread
From: Jeff Hoffmann @ 2000-06-23 15:42 UTC (permalink / raw)
To: D'Arcy J.M. Cain <darcy@druid.net>; +Cc: Gary MacMinn <G.MacMinn@nca.gov.au>; pgsql-sql
"D'Arcy J.M. Cain" wrote:
>
> PostgreSQL could use a
> phone number type. I have been meaning to write a user defined type
> for some time but never seem to get around to it. Maybe you could
> give this a shot. The examples in contrib should get you started.
> Once finished you can make the phone # one column and split out parts
> with various functions. Don't forget to have a place for country code
> and extension.
i messed around with one a while back, but i dropped it before i
finished it.
if i recall correctly, it was mostly working, but i don't know if i
broke it again after that or not. i'll put a copy of it on my web site
-- you can download it, clean it up and put it in contrib if you want.
i just tarred up the files, there's no documentation for it & barely any
copyright info in it. put whatever license you want on it if you want
to reuse it.
the files located at:
http://www.potlatch.org/source/phone.tar.gz
--
Jeff Hoffmann
PropertyKey.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Merging two columns into one
@ 2000-06-26 23:09 Gary MacMinn <G.MacMinn@nca.gov.au>
0 siblings, 0 replies; 7+ messages in thread
From: Gary MacMinn @ 2000-06-26 23:09 UTC (permalink / raw)
To: pgsql-sql
All,
Many thanks for your thoughts on the merging of columns. The tr method would have killed all the delimiters as a few people noted. The || method in SQL was the winner, although the final table this has to be done to (4 million records) should kill the system for a while!!!!! Must make sure I've got enough disk space to do it all as well. Maybe doing it a section at a time would be in order.
Thanks to all,
Gary MacMinn
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2000-06-26 23:09 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-23 03:04 Merging two columns into one Gary MacMinn <G.MacMinn@nca.gov.au>
2000-06-23 05:10 ` Christopher Sawtell <csawtell@xtra.co.nz>
2000-06-23 05:53 ` Oliver Elphick <olly@lfix.co.uk>
2000-06-23 11:40 ` D'Arcy J.M. Cain <darcy@druid.net>
2000-06-23 11:35 ` D'Arcy J.M. Cain <darcy@druid.net>
2000-06-23 15:42 ` Jeff Hoffmann <jeff@propertykey.com>
2000-06-26 23:09 Re: Merging two columns into one Gary MacMinn <G.MacMinn@nca.gov.au>
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