I'd like to know what led someone down the path of doing something like DEFAULT 'now()'::timestamp in a CREATE TABLE. Could it be a faulty migration tool that created these and people copy them thinking it's a legitimate syntax?
My thought process on this used to be: Provide a text string of the expression that is then stored within the catalog and eval'd during runtime. If the only thing you are providing is a single literal and not some compound expression it isn't that obvious that you are supposed to provide an unquoted expression - which feels like it should be immediately evaluated - versus something that is a constant. Kinda like dynamic SQL.