I have below 2 tables.

old_name    old_value
Tom1        100
Kate1       80
Jim1        70

new_name   new_value
Tom2       70
Kate2      100
Jim2       80

I want to make a new table look like

old_name    old_value       new_name    new_value
Tom1        100             Tom2        70
Kate1       80              Kate2       100
Jim1        70              Jim2        80

How to do that in Postgres?

  • 1
    How do the two tables relate to each other? By firstname/nickname? – bma Nov 19 '13 at 2:23
  • I have amended the table, they are related by the name (e.g. Tom1 = Tom2) – Xianlin Nov 19 '13 at 3:01
  • What is the relationship between the 1 and 2 appended to the names? Are the names without the 1/2 suffix unique? For example, can there only be a Tom1 and Tom2, but no Tom3, Tom4, etc? And is the 1 suffix the only suffix in the first table, and 2 is the only suffix in the second table? – bma Nov 19 '13 at 3:07
  • Tom1 = Tom2, there is not Tom3 or Tom4, Tom1 and Tom2 are unique – Xianlin Nov 19 '13 at 4:00

Use some pattern matching / string manipulation in the JOIN condition. I use left() in my example:

FROM   tbl_old o
JOIN   tbl_new n ON left(n.new_name, -1)
                  = left(o.old_name, -1)

This assumes that all names have exactly one pending character (digit or no, doesn't matter).

left(string, -1) (with a negative length parameter) effectively trims the last letter from string. Should be fastest. Requires Postgres 9.1.
In older version substitute with substring() or one of many other ways ...

  • +1 Nice use of LEFT(). Simple and clean. – bma Nov 20 '13 at 4:13

Your Answer

