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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.