In persistent-postgresql defining the sqltype of a field as "timestamp without time zone" causes remigrations

[nmk]

When using persistent with PostgreSQL and having the following definition

createdAt UTCTime "sqltype=timestamp without time zone" default=now()

causes a

Migrating: ALTER TABLE "memberships" ALTER COLUMN "created_at" TYPE timestamp without time zone

every time the migrations are run. The type of the field in the DB is set correctly the first time, I would expect it not to issue an ALTER statement every time.

Is this a (quoting?) problem with my field definition or is persistent not getting the correct column type when calculating the necessary migrations?

[snoyberg]

This is likely a quoting or capitalization problem. Two ways of debugging this are:

• Add some trace statements into the migration code in the persistent-postgresql package to see what Persistent thinks is in your database
• Analyze the metadata tables referenced from persistent-postgresql to see what the column looks like

[nmk]

It turns out in PostgreSQL timestamp and timestamp without time zone are synonyms and the udt_name of such a column is reported simply as timestamp, see first note on http://www.postgresql.org/docs/current/static/datatype-datetime.html. So one option is to define the sqltype= as timestamp, the other is to add an alias in sqlShowType such as

showSqlType (SqlOther (T.toLower -> "timestamp")) = "TIMESTAMP WITHOUT TIME ZONE"

in Database.Persist.Postgresql.

[snoyberg]

The latter seems like it could cause problems for people using timestamp right now. I'd recommend changing your schema to use timestamp instead.

On Wed, Dec 9, 2015, 12:50 PM Nickolay Kolev notifications@github.com wrote:

It turns out in PostgreSQL timestamp and timestamp without time zone are synonyms and the udt_name of such a column is reported simply as timestamp, see first note on http://www.postgresql.org/docs/current/static/datatype-datetime.html. So one option is to define the sqltype= as timestamp, the other is to add an alias in sqlShowType such as

showSqlType (SqlOther (T.toLower -> "timestamp")) = "TIMESTAMP WITHOUT TIME ZONE"

in Database.Persist.Postgresql.

— Reply to this email directly or view it on GitHub https://github.com/yesodweb/persistent/issues/514#issuecomment-163187026 .