sql - Date column arithmetic in PostgreSQL query

Question

I have two tables that look like this:

CREATE TABLE table1 (user_id int, the_date date);
CREATE TABLE table2 (user_id int, the_date date, something_else real);

I am writing a query that looks like this

CREATE TABLE foo AS 
 SELECT t1.user_id
 , (t1.the_date - (t2.the_date - t1.the_date)::int) start_date
 FROM table1 t1, table2 t2 
 where t1.user_id=t2.user_id
 ;

When I run the above query, I get the following error displayed on the psql console:

ERROR:  syntax error at or near "$1"
LINE 1: ...the_date - (t2.the_date - t1.the_date)::int)  $1 ...

                                                             ^

The second column in the query result is to show a date which is N days BEFORE the date in table1, where N is the difference (in days) between the date in table2 and table1.

Note: table2 will always have has later dates than the dates in table1.

How can I perform this date calculation and store the result as a new column alias in my query?

I am using PG 8.4.

Answer 1

You would need to table-qualify t1.user_id to disambiguate. Plus other adjustments:

CREATE TABLE foo AS 
SELECT user_id, (t1.the_date - (t2.the_date - t1.the_date)) AS start_date
FROM   table1 t1
JOIN   table2 t2 USING (user_id);

• Subtracting two dates yields integer. Cast was redundant.

• Don't omit the AS keyword for column aliases - while it's generally OK to omit AS for table aliases. The manual:

  You can omit AS, but only if the desired output name does not match any PostgreSQL keyword (see Appendix C). For protection against possible future keyword additions, it is recommended that you always either write AS or double-quote the output name.)

• Joining tables with a USING clause only keeps one instance of the joining columns(s) (user_id in this case) in the result set and you don't have to table-qualify it any more.