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

Question

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.

I would recommend `likes`. Plurals are much less likely to interfere with reserved words and keywords. And, tables contain multiple entities, so a plural is appropriate. — Gordon Linoff, Apr 12 '21 at 12:03

score 2 · Accepted Answer · answered Apr 12 '21 at 05:53

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.

Jim Jones · Answer 2 · 2021-04-12T06:37:39.760

1

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

edited Apr 12 '21 at 06:37

answered Apr 12 '21 at 05:48

Jim Jones

18,404
3
35
44

@a_horse_with_no_name I thought so at first, but it threw an error: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=2cd7a9a1ae9b1990174b78bcfe3c98e8 – Jim Jones Apr 12 '21 at 05:55
@a_horse_with_no_name ah, now I see the issue... it does not work with `TEMPORARY tables :-P https://dbfiddle.uk/?rdbms=postgres_13&fiddle=4822425a145eb66a873d5640bc487d78 – Jim Jones Apr 12 '21 at 05:58
you can't specify a schema for temporary tables. – Apr 12 '21 at 06:00
1

@a_horse_with_no_name thanks for pointing out! It is amazing how simple things can get tricky.. I just corrected it. +1 – Jim Jones Apr 12 '21 at 06:04

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

2 Answers2