Hey Daniel,
DB> (make-date nil nil nil) #<DATE 30-03-2010> DB> (day-of-week *) 1
I was puzzled for a bit because make-date isn't in the simple-date library. But it did indeed contain that bug -- it always returned the correct answer minus one. That goes to show that in all these years, no one has actually used that function. I've pushed a fix to the (new, git) repository, but if you want a more well-designed and well-tested date library, I'd suggest local-time [1], the latest release even has cl-postgres glue included (src/integration/cl-postgres.lisp).
Best, Marijn