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
– The Impaler, Commented Dec 20, 2018 at 20:20
select articles.author, articles.slug from articles join log on articles.slug = substring(log.path, 10, 28) — Lukasz Szozda
– Lukasz Szozda, Commented Dec 20, 2018 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 — user330315
– user330315, Commented Dec 20, 2018 at 20:30

sticky bit · Accepted Answer · 2018-12-20 20:27:26Z

2

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.

answered Dec 20, 2018 at 20:27

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

syntax error when using substring in a join expression with PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related