PostgreSQL compare with substring doesn't work

Question

I want to select from 2 table where 2nd id is equal with first 4 character from first id

SELECT a.*, b.*, substring(a.my_id from 1 for 4)::integer as number
   FROM table1 as a
INNER Join table2 as b ON(b.id_2=number) where my_id = 101
                                 ^
It produces an error here        |

ERROR: column "number" does not exist

SQL state: 42703

Character: 189

Aliases from the SELECT list don't work in FROM list. Move substring(a.my_id from 1 for 4)::integer to your ON condition. — LisMorski
– LisMorski, Commented Jul 25, 2012 at 10:37

score 3 · Accepted Answer · 2012-07-25 11:36:51Z

3

You can't use an alias like that, you need a derived table:

select *
from (
   SELECT t1.*, 
          substring(t1.my_id::text from 1 for 4)::integer as number
   FROM table1 t1
) as a 
inner join table2 as b ON (b.id_2 = a.number) 
where my_id = 101

Storing a number that is used as a foreign key as a part in a varchar column is a really, really ugly design. That number should be a column of its own in table1.

edited Jul 25, 2012 at 11:36

answered Jul 25, 2012 at 10:37

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Alex Over a year ago

In this case it gives me ERROR: missing FROM-clause entry in subquery for table "a" inside 2nd select

user330315 Over a year ago

@Alex: sorry too much copy & paste (but with a little bit of thinking you could have fixed that yourself)

Alex Over a year ago

however it did not solve my problem ( function pg_catalog.substring (integer, integer, integer) does not exist

user330315 Over a year ago

@Alex: What datatype is my_id?

user330315 Over a year ago

@Alex: you don't like reading manuals, do you? The substring is a function that works on strings not on numbers. See my edit. Do re-think your design. Using only parts of a number as a reference to another table is absolutely insane.

Collectives™ on Stack Overflow

PostgreSQL compare with substring doesn't work

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related