Konvertierung von varchar(16) auf timestamp

Hallo,

ich habe da ein mehr oder weniger großer Problem. Ich muss Daten von Oracle 8i testweise auf PostgreSQL 8.4 portieren. Unter Oracle 8i gab es noch keinen timestamp, so dass man sich mit varchar(16) ausgeholfen hat.
Doch wenn schon, dann sollen diese Strings bei der Portierung in einen timestamp umgewandelt werden. Geht ja prinzipiell über die Funktion to_timestamp. Doch handelt es sich hierbei im mehrere Milliarden Datensätze, so dass ein Insert Trigger, welche die Umwandlung übernimmt einiges an Performace kosten wird.
Ich dachte, dass man bei, Create Table statement irgendwie das einzufügende Format für date angeben kann, doch finde ich nichts in der Doku und auch Google verrät nichts in der Hinsicht.

Muss ich das wirklich über einen Trigger machen, oder habt ihr eine andere Idee?

Ich danke schon mal im Voraus ganz herzlich.

mfg
Marcus

Wie kommen denn die Daten rein? Bei der Masse doch sicherlich via COPY, oder?

Andreas

Das Input-Format ist PostgreSQL reichlich egal. Alles, was halbwegs nach einem (gültigen) Timestamp ausschaut, wird akzeptiert.

Näheres findest du in der Dokumentation:
PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Date/Time Types

Intern werden die unterschiedlichen Formate dann alle in ein einheitliches umgewandelt.

Per Insert sollten die Daten rein. Nutze dafür das perlskript vom ora2pg Paket.
Das Format des Quellsystems ist: ‘200312012305123215’ und da würde ich gerne irgendwie bei der Spaltendefinition angeben, was was ist. So geht es jedenfalls nicht. Über to_timestamp funktioniert die Formatanpassung.

mfg
Marcus

Tja, da wirst Du wohl to_timestamp nehmen müssen. Was für ein Formatstring verwendest Du dann eigentlich? Ich müßte ja nun auch raten, was da was ist.

Andreas

‘200312012305123215’ nach 2003-12-01 23:05:12.3215 nach Postgres

Du meinst to_timestamp in einem Insert Trigger?

mfg
Marcus

Wird wohl mit einem Trigger gar nicht gehen, weil der schon vorher über den kapotten Typ meckert. Kannst Du nicht vorher schon den Dump, den Du da reinziehen willst, bearbeiten? Dies dürfte am schnellsten sein. Ansonsten vielleicht über eine Zwischentabelle und aus der via insert into … select … arbeiten.

Andreas

oder in die PG-table in die er importiert einfach eine column einfuegen, die da z.Bleistift

karpottes_timestamp varchar(20), ...

heisst.

in dem BEFORE INSERT trigger (row-level) kann er dann in aller ruhe seinen

NEW.schoener_timestamp = to_timestamp(NEW.karpottes_timestamp);

berechnen

und hernach ggf. die spalte karpottes_timestamp einfach wieder droppen

natuerlich muss er im INSERT statement den karpotten timestamp (text) auch in karpottes_timestamp fuellen - und den schoenen timestamp leer lassen - oder falls not null einfach mit DEFAULT initialisieren … der trigger wirds schon richten …

oder bei (BULK) COPY auch das richtige falsche feld befuellen …

jenachdem was er da gerade verwenden tut

Man könnte natürlich die Tabelle zuerst mal mit VARCHAR erstellen und dann den Datentyp nachträglich ändern. Beim Konvertieren kann man dann eine Funktion angeben, die die Arbeit erledigt.

oder so … das ist aber nicht so aufregend :wink:

Ach … wenn man dafür keinen Index auf die Spalte legt und die Tabelle gleich mit etwas Platz zwischendurch erstellt, dann greift HOT und die Updates passieren in-place. Spart eine Menge Speicherplatz.

Sag mal, wie hoch sollte man den fillfactor da angeben? Es wird ja jede Zeile verändert, ein fillfactor=50 würde also zwar naheliegen, aber die Tabelle in einer Größe von 200% der nötigen Größe erstellen. Dasselbe erreiche ich auch mit fillfactor=100 und einer extra Import-Tabelle, oder?

Oder würde, konkret in diesem Falle, auch ein fillfactor größer 50 funktionieren? (und dabei HOT-Tupels, die ‘grad anfielen’, mit genutzt werden?

PS.: sorry, falls ich Dich im Urlaub stresse …

Andreas

Ich danke euch für die zahlreichen Antworten.
Werde es jetzt über eine zusätzliche Spalte machen in der der String drin steht und diesen dann in ein timestamp umwandeln und in die korrekte Spalte einfügen.

mfg
Marcus

Hmm, das Update findet innerhalb einer einzigen Transaktion statt. Deshalb werden die Tuples noch benötigt, bis die Transaktion abgeschlossen ist. 50% hört sich gut an, sollte man aber mal testen …


PS.: sorry, falls ich Dich im Urlaub stresse …

Ich genieße gerade einen guten Whiskey in der Lobby :wink:

Das sei Dir gegönnt. Ich stelle mir da z.B. einen Tullamore Dew oder Bushmills vor, nicht jedoch ‘Johnny walker red label’ oder vergleichbare billige Getränke vor … und dazu einen schönen Kamin. Aber ich schweife ab…

Andreas