default quoting syntax #134

Closed
BJTerry opened this Issue Jun 20, 2013 · 8 comments

4 participants

@BJTerry

In PostgreSQL, if you create a UTCTime column using Persistent, the table is created as "timestamp without time zone". In the Yesod book they give the example of using "now()" as a column default for UTCTime, but this doesn't work correctly, because using now() on a timestamp without time zone column uses the local time, stripped of time zone information, while all the other entries created by Persistent are UTCTimes stripped of time zone information. This can cause subtle bugs if you try to use raw queries that use default values for that column. If you ONLY use Persistent to create rows this doesn't come up because only real UTCTimes are inserted into the column.

Either the documentation should change to say that you should never use now() or LOCAL_TIMESTAMP for column defaults for UTCTime columns, or the column type should be changed to timestamp with time zone so that properly formatted times can be compared within the column.

@BJTerry

It seems to also be possible to work around this by setting the appropriate default other than now(). In this case timezone('utc'::text,now())

Note that in you have to use exactly this formulation because as far as I can tell defaults in Persistent can't have spaces in them.

@snoyberg
Yesod Web Framework member

You can include whitespace in an attribute if you surround the whole thing with double quotes.

@BJTerry

That might be a separate bug then, because, for example adding a space after the comma like this:

created UTCTime default="timezone('utc'::text, now())"

Gives me the error

Migrating: ALTER TABLE "user" ALTER COLUMN "created" SET DEFAULT "timezone('utc'::text,
<interactive>: SqlError {sqlState = "42601", sqlNativeError = 7, sqlErrorMsg = "execute: PGRES_FATAL_ERROR: ERROR:  unterminated quoted identifier at or near \"\"timezone('utc'::text,\"\nLINE 1: ...R TABLE \"user\" ALTER COLUMN \"created\" SET DEFAULT \"timezone(...\n                                                             ^\n"}
Exit code: ExitFailure 1

Is that the proper way of quoting?

@snoyberg
Yesod Web Framework member

No, it should be

created UTCTime "default=timezone('utc'::text, now())"
@BJTerry

Ah, I see. Thanks, that formulation never occurred to me for some reason.

@meteficha
Yesod Web Framework member

Don't worry, it is an unfamiliar syntax.

@gregwebs gregwebs added the Postgres label Aug 4, 2014
@gregwebs gregwebs added SQL and removed Postgres labels Aug 29, 2014
@gregwebs
Yesod Web Framework member

it seems like the syntax would be better as default=""

@gregwebs gregwebs changed the title from UTCTime tables are created as timestamp without time zone in PostgreSQL to default quoting syntax Aug 29, 2014
@gregwebs
Yesod Web Framework member

I think default="quoted" works now.

@gregwebs gregwebs closed this May 12, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment