1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
|
Transformation Functions
========================
Some data types are implemented in a different enough way that a
transformation function is necessary. This function must be written in
`Common lisp` and is searched in the `pgloader.transforms` package.
Some default transformation function are provided with pgloader, and you can
use the `--load` command line option to load and compile your own lisp file
into pgloader at runtime. For your functions to be found, remember to begin
your lisp file with the following form::
(in-package #:pgloader.transforms)
The provided transformation functions are:
- *zero-dates-to-null*
When the input date is all zeroes, return `nil`, which gets loaded as a
PostgreSQL `NULL` value.
- *date-with-no-separator*
Applies *zero-dates-to-null* then transform the given date into a format
that PostgreSQL will actually process::
In: "20041002152952"
Out: "2004-10-02 15:29:52"
- *time-with-no-separator*
Transform the given time into a format that PostgreSQL will actually
process::
In: "08231560"
Out: "08:23:15.60"
- *tinyint-to-boolean*
As MySQL lacks a proper boolean type, *tinyint* is often used to
implement that. This function transforms `0` to `'false'` and anything
else to `'true`'.
- *bits-to-boolean*
As MySQL lacks a proper boolean type, *BIT* is often used to implement
that. This function transforms 1-bit bit vectors from `0` to `f` and any
other value to `t`..
- *int-to-ip*
Convert an integer into a dotted representation of an ip4. ::
In: 18435761
Out: "1.25.78.177"
- *ip-range*
Converts a couple of integers given as strings into a range of ip4. ::
In: "16825344" "16825599"
Out: "1.0.188.0-1.0.188.255"
- *convert-mysql-point*
Converts from the `astext` representation of points in MySQL to the
PostgreSQL representation. ::
In: "POINT(48.5513589 7.6926827)"
Out: "(48.5513589,7.6926827)"
- *integer-to-string*
Converts a integer string or a Common Lisp integer into a string
suitable for a PostgreSQL integer. Takes care of quoted integers. ::
In: "\"0\""
Out: "0"
- *float-to-string*
Converts a Common Lisp float into a string suitable for a PostgreSQL float::
In: 100.0d0
Out: "100.0"
- *hex-to-dec*
Converts a string containing an hexadecimal representation of a number
into its decimal representation::
In: "deadbeef"
Out: "3735928559"
- *set-to-enum-array*
Converts a string representing a MySQL SET into a PostgreSQL Array of
Enum values from the set. ::
In: "foo,bar"
Out: "{foo,bar}"
- *empty-string-to-null*
Convert an empty string to a null.
- *right-trim*
Remove whitespace at end of string.
- *remove-null-characters*
Remove `NUL` characters (`0x0`) from given strings.
- *byte-vector-to-bytea*
Transform a simple array of unsigned bytes to the PostgreSQL bytea Hex
Format representation as documented at
http://www.postgresql.org/docs/9.3/interactive/datatype-binary.html
- *sqlite-timestamp-to-timestamp*
SQLite type system is quite interesting, so cope with it here to produce
timestamp literals as expected by PostgreSQL. That covers year only on 4
digits, 0 dates to null, and proper date strings.
- *sql-server-uniqueidentifier-to-uuid*
The SQL Server driver receives data fo type uniqueidentifier as byte
vector that we then need to convert to an UUID string for PostgreSQL
COPY input format to process.
- *unix-timestamp-to-timestamptz*
Converts a unix timestamp (number of seconds elapsed since beginning of
1970) into a proper PostgreSQL timestamp format.
- *varbinary-to-string*
Converts binary encoded string (such as a MySQL `varbinary` entry) to a
decoded text, using the table's encoding that may be overloaded with the
*DECODING TABLE NAMES MATCHING* clause.
|
