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?

Is `'my_substr'` an alias for the column? If so, then you don't need it in an update. Just remove it. — The Impaler, Dec 20 '18 at 20:20
`select articles.author, articles.slug from articles join log on articles.slug = substring(log.path, 10, 28)` — Lukasz Szozda, Dec 20 '18 at 20:20
The string constant `my_substr` is completely wrong there. If that is supposed to be an alias: if at all you need to use double quotes for identifiers. Single quotes are only for string constants — , Dec 20 '18 at 20:30

score 2 · Accepted Answer · answered Dec 20 '18 at 20:27

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.

syntax error when using substring in a join expression with PostgreSQL

1 Answers1