sqlpostgresqlquoted-identifier

ERROR: syntax error at or near "." LINE 4: ON like.takerId = frame.likeId;

i have a table whose name is like. But whenever i have to select data from like, i was getting this error, i figured it out public.like..but when i try to join two tables

SELECT *
FROM frame
INNER JOIN public.like
ON like.takerId = frame.likeId;

i get this error

ERROR:  syntax error at or near "."
LINE 4: ON like.takerId = frame.likeId;

i also use public prefix but it throws

ERROR:  column like.takerid does not exist
LINE 4: ON public.like.takerId = frame.likeId;
           ^
HINT:  Perhaps you meant to reference the column "like.takerId".

even if it is saying column like.takerid does not exist , then why it gives me HINT: Perhaps you meant to reference the column "like.takerId". I dont know, i think it is problem with like table name, like is a sql syntax, and it assumes like and a sql syntax and throwing me error. Should I change my table name? Or is there any way to make sql case sensetive or how can i tell sql to ignore like. public.like is not working for joining table.

Solution

  • As like is a reserved keyword, you need to use double quotes for each occurance of it (unless it's prefixed with the schema name as you found out)

    SELECT *
FROM frame
  JOIN public.like ON "like".takerId = frame.likeId;

    Or

    SELECT *
FROM frame
  JOIN "like" ON "like".takerId = frame.likeId;

    Or use an alias

    SELECT *
FROM frame f
  JOIN "like" l ON l.takerId = f.likeId;

    But in the long run you should find a different name for the table that does not require quoting.