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

user_15543494 0
Viewed 241

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.

quoted-identifier postgresql sql
2 Answers

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.

user_3303150

You should definitely chose another name for your table. LIKE is a reserved command, and it is considered a bad practice to use it, although possible by using ", e.g.

CREATE TABLE public."like" (id int);
INSERT INTO public."like" VALUES (42);
SELECT * FROM "public.like"

EDIT: As pointed out by @a_horse_with_no_name, specifying a schema in temporary tables won't work (check db<>fiddle), so only the table name should be between double quotes as corrected in the snippet above. For temporary tables just omit the schema:

CREATE TEMPORARY TABLE "like" (id int);
INSERT INTO "like" VALUES (42);
SELECT * FROM "like"

Demo: db<>fiddle

user_22753880
Related