I can't make a list of user and the places that they can't go if they have been there in t-CodePudding

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.

CodePudding user response：

On WHERE clause or on HAVING clause you cannot use alias of fields. For using aliases on WHERE clause or on HAVING clause you must use subquery. And not recommended using having without group by. So, correct syntax:

SELECT pk_user_id,pk_place_id,
AGE('2023-05-26',date) AS test
FROM visit
WHERE AGE('2023-05-26',date) > 6;

select * from (     
    SELECT pk_user_id, pk_place_id,
    AGE('2023-05-26',date) AS test
    FROM visit
) tb 
WHERE tb.test > 6;

CodePudding user response：

Ramin. I would like to thank you for your answer, I also manage to solve it using the following method:

SELECT pk_place_id, pk_user_id, date
FROM visita
WHERE EXTRACT(year FROM age(NOW(),date))*12   EXTRACT(month FROM age(now(),date)) > 6;