whatthecode
whatthecode

Reputation: 25

Invalid input syntax for integer but type is date?

I'm trying to use a query to get date type value but it doesn't work...

tables:

Create table Clients1(
    login char(30) primary key,
    password varchar(30),
    date_ad date,
    name_c varchar(30),
    adress varchar(30),
    card_c varchar(30)
);

Create table clients_pay(
    payment_ID char(15) primary key,
    login char(15)
);

The following error appears:

22P02: invalid input syntax for integer: "2019/12/02"

My query is as follows:

SELECT DISTINCT login, COUNT(payment_ID) AS p 
FROM Clients1 
NATURAL INNER JOIN Payments1 
NATURAL INNER JOIN clients_pay 
GROUP BY login 
HAVING COUNT(payment_ID) >='2019/12/02';

Since it's type date why it appears as integer?

I'm trying to see which clients can use service at date 2019/12/02? (They can if payed)

Thank you

Upvotes: 1

Views: 783

Answers (1)

TH58PZ700U
TH58PZ700U

Reputation: 43

It looks like you want to have a WHERE clause limiting the payment_ids returned by a date, such as:

SELECT login, COUNT(payment_ID) AS p 
FROM clients1 
INNER JOIN clients_pay 
ON clients_pay.login = Clients1.login
WHERE date_ad >='2019/12/02'
GROUP BY login ;

I also made some assumptions about the intention of your query and your data model, please edit your question to clarify those things if this doesn't meet your needs. Also note, Postgres identifiers are coerced to lowercase by default, unless you specify the casing by putting double quotes around the identifier during object creation. That practice is highly discouraged, though.

Upvotes: 1

Related Questions