Invalid input syntax for integer but type is date?

Question

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

Note what you are comparing to the date: it is a COUNT, which is always an integer. You want to compare a date-valued column with the date. — antlersoft
– antlersoft, Commented Feb 18, 2020 at 21:33
What are you trying to do? You should explain the purpose of your query so one can help fixing it. — GMB
– GMB, Commented Feb 18, 2020 at 21:36

TH58PZ700U · Accepted Answer · 2020-02-18 22:13:43Z

1

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.

answered Feb 18, 2020 at 22:13

TH58PZ700U

432 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Invalid input syntax for integer but type is date?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related