Postgres "ERROR: syntax error at or near" but no apparent syntax error

Question

I'm running into kind of an odd problem. I'm trying to make a view in PostgreSQL, but I'm getting the error mentioned in my title. From what I can tell I don't have any syntax errors, but Postgres is still throwing an error. What am I doing wrong?

Here's the error I'm running into:

ERROR:  syntax error at or near "FROM"
LINE 3: period, start, "end" FROM Periods,

Here's the view and the corresponding table I'm running into the issue with:

CREATE VIEW ReservationsView AS
SELECT code, date FROM Reservations,
period, start, "end" FROM Periods,
room FROM Rooms,
name FROM Users
FROM Reservations A
INNER JOIN Periods B
ON A.room = B.room
INNER JOIN Rooms C
ON A.room = C.room
INNER JOIN Users D
ON A."user" = D."user"
ORDER BY A.code DESC;

CREATE TABLE Reservations (
    code SERIAL PRIMARY KEY, 
    abbr VARCHAR(5), 
    room INT, 
    date DATE NOT NULL, 
    period CHAR(1), 
    "user" INT, 
    FOREIGN KEY (abbr, room) REFERENCES Rooms (abbr, room), 
    FOREIGN KEY (period) REFERENCES Periods (period),
    FOREIGN KEY ("user") REFERENCES Users ("user")
);

CREATE TABLE Periods (
    period CHAR(1) PRIMARY KEY, 
    start TIME NOT NULL, 
    "end" TIME NOT NULL
);

At first glance, the SELECT statement in your view has several syntax errors. You can have only one `FROM` keyword in a `SELECT` query. You can have any number of other tables referenced with types of `JOIN`, but all the joined tables are introduced with a single `FROM` keyword. — Bill Karwin, Apr 22 '23 at 22:43
Out of curiosity, how did you come up with the query format with multiple `FROM` keywords? Did you see it in any examples or tutorials or documentation? Was it something ChatGPT showed you? — Bill Karwin, Apr 22 '23 at 22:45
It's worked for me in the past, I didn't know it wasn't a thing. I'm not sure where I got the idea. As for the SELECT, I wasn't aware I was only allowed to have one FROM for each keyword. I've also edited the question to include where I'm getting the error. — FinMartinez, Apr 22 '23 at 23:30
Indeed, the error says that it didn't understand your syntax as soon as it got to the second `FROM` keyword. That matches the standard syntax for `SELECT` queries. I suggest you study the [documentation](https://www.postgresql.org/docs/current/sql-select.html) or some tutorial on writing queries. — Bill Karwin, Apr 22 '23 at 23:39

Erwin Brandstetter · Accepted Answer · 2023-04-23T00:05:00.817

"No apparent syntax error" :)

CREATE VIEW reservations_view AS
SELECT a.code, a.date
     , b.period, b.start, b."end"
     , c.room
     , d.name
FROM   reservations a
JOIN   periods b USING (room)
JOIN   rooms   c USING (room)
JOIN   users   d ON d."user" = a."user"
ORDER  BY a.code DESC;

Start reading the manual here.

And don't use reserved words like "end" or "user" as identifier, that's asking for trouble. Use legal, lower-case, unquoted names to make your life with Postgres easier. See:

Are PostgreSQL column names case-sensitive?

And don't use the obsolete data type char(N). See:

Postgres "ERROR: syntax error at or near" but no apparent syntax error

1 Answers1