Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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
up vote 1 down vote accepted

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 ...

share|improve this answer
+1 Nice use of LEFT(). Simple and clean. – bma Nov 20 '13 at 4:13

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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