I am new to PSQL so I am sorry if it’s a silly mistake, but I am having the following problem.
I am trying to make a list that show the user and the places that they haven’t gone to in at least 6 months.
Here is the code that I am using:
SELECT pk_user_id,pk_place_id,
AGE('2023-05-26',date) AS test
FROM visit
WHERE test > 6;
I also tried this one:
SELECT pk_user_id,pk_place_id,
AGE('2023-05-26',date) AS test
FROM visit
HAVING test > 6;
And here is the code for the table:
CREATE SCHEMA code
CREATE TABLE code.place (
pk_place_id VARCHAR(8),
place_name VARCHAR (50),
CONSTRAINT pk_place_id PRIMARY KEY (pk_place_id)
);
CREATE TABLE code.user (
pk_user_id VARCHAR(3),
user_name VARCHAR (50),
CONSTRAINT pk_user_id PRIMARY KEY (pk_user_id)
);
CREATE TABLE code.visit (
pk_user_id VARCHAR(3),
pk_place_id VARCHAR(8),
data DATE,
CONSTRAINT pk_user_id FOREIGN KEY (pk_user_id) REFERENCES code.user,
CONSTRAINT pk_place_id FOREIGN KEY (pk_place_id) REFERENCES code.place
);
The problem is that when I use this code it says that the column test doesn’t exist.
2
Answers
Ramin. I would like to thank you for your answer, I also manage to solve it using the following method:
On
WHERE
clause or onHAVING
clause you cannot use alias of fields. For using aliases onWHERE
clause or onHAVING
clause you must use subquery.And not recommended using having without group by.
So, correct syntax:
or