CODEWITHSUNDEEP
sqlpostgresqlreserved-words
Sana
Sana

Reputation: 563

Filtering columns with SQL reserved keywords as column name in PostgreSQL

A set of data is uploaded by my colleague and I have to filter the data by date. Unfortunately my colleague used reserved words of year and month for uploading the data so the data in database looks like this:

babyname     year  month  gender
-----------  ----  -----  ------
Sarah        2018  2      f
Jack         2016  5      m
James        2017  7      m
Susan        2017  1      f

I am going to filter baby girls name who were born from or after April 2017. I wrote following query but it does not filter the data by date at all:

SELECT * FROM babytable
WHERE
gender='f' 
AND
(("year"=2017 AND "month">3) OR "year"=2018);

Would you please let me know what is my mistake. Many Thanks

Upvotes: 0

Views: 1115

Answers (2)

S-Man
S-Man

Reputation: 23676

demo: db<>fiddle

SELECT 
    * 
FROM 
    births 
WHERE
    gender = 'f' 
    AND ((year = 2017 AND month > 4) OR (year > 2017))

Alternatively you could convert the year and month columns into dates before comparing:

SELECT 
    * 
FROM 
    births 
WHERE
    gender = 'f' 
    AND to_date(year || '-' || month, 'YYYY-MM') > '2017-04-30'

Upvotes: 1

Seth Nabarro
Seth Nabarro

Reputation: 111

No need to make the column names into strings, your WHERE gender = 'f' condition has the correct format. Try:

SELECT * FROM babytable
WHERE
gender='f' 
AND
((year=2017 AND month>3) OR year=2018);

Upvotes: 1

Related Questions