Filtering columns with SQL reserved keywords as column name in PostgreSQL

Question

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

The query correctly returns Sarah - what exactly is your question? Correctly quoting column names that use (non-reserved) keywords as you did will not change the result: rextester.com/PJGVS72570 — user330315
– user330315, Commented Sep 17, 2018 at 14:30
You can simplify and do where gender = 'f' and ("year", "month") >= (2017,4). — jarlh
– jarlh, Commented Sep 17, 2018 at 14:36

Seth Nabarro · Accepted Answer · 2018-09-17 14:30:21Z

1

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);

answered Sep 17, 2018 at 14:30

Seth Nabarro

1214 bronze badges

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

4 Comments

jarlh Over a year ago

Syntax error... YEAR and MONTHS are reserved words, so these columns need to be delimited.

user330315 Over a year ago

@jarlh: no, they don't need to be quoted. They are keywords, but they non-reserved keywords: rextester.com/PJGVS72570

jarlh Over a year ago

@a_horse_with_no_name, they aren't? Sorry for jumping to conclusions. (According to the ANSI/ISO SQL spec they are reserved.)

user330315 Over a year ago

@jarlh: see here: postgresql.org/docs/current/static/sql-keywords-appendix.html

S-Man · Accepted Answer · 2018-09-17 16:00:49Z

1

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'

edited Sep 17, 2018 at 16:00

answered Sep 17, 2018 at 15:11

S-Man

24k9 gold badges51 silver badges78 bronze badges

Collectives™ on Stack Overflow

Filtering columns with SQL reserved keywords as column name in PostgreSQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related