RE: Immutable way to cast timestamp TEXT to DATE? (for index)

Поиск
Список
Период
Сортировка
От Kevin Brannen
Тема RE: Immutable way to cast timestamp TEXT to DATE? (for index)
Дата
Msg-id MN2PR19MB25758307FDE5FD98439DF1BEA48E0@MN2PR19MB2575.namprd19.prod.outlook.com
обсуждение исходный текст
Ответ на Re: Immutable way to cast timestamp TEXT to DATE? (for index)  (Ken Tanzer <ken.tanzer@gmail.com>)
Список pgsql-general
Дерево обсуждения

From: Ken Tanzer <ken.tanzer@gmail.com>

On Fri, Jan 4, 2019 at 2:54 PM Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

>>>>> "Ken" == Ken Tanzer <ken.tanzer@gmail.com> writes:

 Ken> Hi. I've got a text field in a table that holds this style of
 Ken> timestamp:

 Ken> 2014-10-23T00:00:00

You can't make this a field of type "timestamp" rather than text?

 

> I actually can't, or rather don't want to.  The underlying data this is drawn from is actually a date field, but this particular table keeps a history of what we actually transmitted to another organization, and I want to keep it as an exact replication of what we sent.

 

 

If it’s not too painful, add another column to your table of type DATE, and on INSERT shove your “timestamp” into that, converting/casting as needed, then index that. So at the cost of an extra 4 bytes per row, you can have both your “transmission” value and an indexable value.

 

Kevin

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

В списке pgsql-general по дате отправления: