syntax error when using substring in a join expression with PostgreSQL

Question

I'm working with postgresql using the Python DB API.

The substring expression I'm using works fine when on its own, but when I put it in the context of a join command I get a syntax error, and I can't find a solution to this in any tutorial or other question.

The expression I'm using is

    select substring(path, 10, 28) 'my_substr' from log limit 3") 

and it gives the result

    [('',), ('candidate-is-jerk',), ('goats-eat-googles',)]

which is what I want. (It trims off /articles/ in this path.)

The context I want to place it in is a join expression, like this:

    select articles.author, articles.slug
    from articles 
    join log on articles.slug = substring(path, 10, 28) 'my_substr'
    from log 

If I didn't need a substring, the expression would be

    join log on articles.slug = log.path,

but as it is, the 'path' won't match 'slug', but with the substring in place it does.

I've tried using brackets, using "as" before 'my_substr', but all give the same syntax error.

How can I create this expression with the proper syntax?

Answer 1

You cannot and don't need to alias column expression in expressions other than a list of columns for a SELECT.

You also have a stray FROM in there.

Try:

SELECT articles.author,
       articles.slug
       FROM articles 
            INNER JOIN log
                       ON articles.slug = substring(log.path, 10, 28);

And finally, if you use aliases, don't enclose them in single quotes ('), these are for string literals in SQL. If you need to have special characters in the alias use double quotes ("), otherwise no quotes (and nothing else) at all.