0

I have a Postgres DB with some very bad schema design, I'm trying to join two tables with and ID and a field which has the ID but with a couple of characters at the beginning, how would I join these?

eg.

TABLE1
------
Field name: ID
Value: 1000

TABLE2
------
Field name: NUMBER
Value: WO-1000

so basically I need it to say something like:

JOIN TABLE2 ON (TABLE1.ID = 'WO-' + TABLE2.NUMBER)

Thanks

Mankind1023
  • 7,198
  • 16
  • 56
  • 86

1 Answers1

1

Use || operator to concatenate two values in Postgres. Try this.

JOIN TABLE2 ON (TABLE1.ID = 'WO-' ||  TABLE2.NUMBER)

If your Number column is of Integer type then cast it to text.

JOIN TABLE2 ON (TABLE1.ID = 'WO-' ||  cast(TABLE2.NUMBER as text)) --or TABLE2.NUMBER::text

Check here for more info

Community
  • 1
  • 1
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • The `||` is not specific to Postgres - this is what has been defined in the SQL standard 30 years ago –  Nov 04 '15 at 17:15
  • @a_horse_with_no_name - Yep that's a sql standard. Was just mentioning its a way to concatenate :) – Pரதீப் Nov 04 '15 at 18:28