pgloader Documentation

File: README.md Copy Chapter

pgloader

pgloader is a data loading tool for PostgreSQL, using the COPY command.

Its main advantage over just using COPY or \copy, and over using a
Foreign Data Wrapper, is its transaction behaviour, where pgloader
will keep a separate file of rejected data, but continue trying to
copy good data in your database.

The default PostgreSQL behaviour is transactional, which means that
any erroneous line in the input data (file or remote database) will
stop the entire bulk load for the table.

pgloader also implements data reformatting, a typical example of that
being the transformation of MySQL datestamps 0000-00-00 and
0000-00-00 00:00:00 to PostgreSQL NULL value (because our calendar
never had a year zero).

Version 4 — Clojure/JVM rewrite Copy Chapter

pgloader v4 is a full rewrite in Clojure, distributed as a single
self-contained JAR requiring Java 21 or later. It is a drop-in
replacement for v3 and accepts the same .load file syntax and
command-line flags.

Key improvements over v3:

• Single JAR — no native dependencies, no SBCL, no shared libraries
• JDBC connection strings — use jdbc:mysql://, jdbc:postgresql://,
  jdbc:sqlserver:// etc. everywhere; driver-specific parameters pass
  through unchanged
• Java heap — no more Common Lisp heap exhaustion on large migrations;
  tune with standard -Xmx JVM flag
• SSL via URI — use ?sslmode=require / ?sslmode=disable in the
  connection string instead of --no-ssl-cert-verification

Documentation Copy Chapter

Full documentation is available at
https://pgloader.readthedocs.io/.

$ java -jar pgloader.jar --help
pgloader [ option ... ] SOURCE TARGET
  --help                          Show usage and exit.
  --version                       Display pgloader version and exit.
  --quiet                         Be quiet.
  --verbose                       Be verbose.
  --debug                         Display debug level information.
  --client-min-messages           Filter logs seen at the console (default: info)
  --log-min-messages              Filter logs seen in the logfile (default: info)
  --logfile                       Filename where to send the logs.
  --summary                       Filename where to copy the summary.
  --root-dir                      Output root directory (default: /tmp/pgloader/).
  --list-encodings                List pgloader known encodings and exit.
  --dry-run                       Check connections only, don't load anything.
  --on-error-stop                 Stop on first error instead of continuing.

Usage Copy Chapter

You can either give a command file to pgloader or run it all from the
command line, see the
pgloader quick start
for more details.

$ java -jar pgloader.jar --help
$ java -jar pgloader.jar <file.load>

For example, for a full migration from SQLite:

$ createdb newdb
$ java -jar pgloader.jar ./test/sqlite/sqlite.db postgresql:///newdb

Or for a full migration from MySQL, including schema definition (tables,
indexes, foreign keys, comments) and parallel loading of the corrected data:

$ createdb pagila
$ java -jar pgloader.jar mysql://user@localhost/sakila postgresql:///pagila

INSTALL Copy Chapter

Java (v4 — recommended) Copy Section

pgloader v4 requires Java 21 or later
(Temurin, OpenJDK, or any compatible distribution).

Download the latest pre-release JAR — this URL always points to the most
recent build from the v4-clojure-rewrite branch:

$ curl -L -o pgloader.jar \
    https://github.com/dimitri/pgloader/releases/download/v4-dev/pgloader.jar
$ java -jar pgloader.jar --version

To make it available system-wide:

$ sudo install -m 755 pgloader.jar /usr/local/lib/pgloader.jar
$ echo '#!/bin/sh\nexec java -jar /usr/local/lib/pgloader.jar "$@"' \
    | sudo tee /usr/local/bin/pgloader
$ sudo chmod 755 /usr/local/bin/pgloader
$ pgloader --version

Docker Copy Section

The latest image is built by CI on every push to v4-clojure-rewrite:

$ docker pull ghcr.io/dimitri/pgloader:latest
$ docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version

Debian / Ubuntu (v3) Copy Section

The v3 package is available in the Debian and Ubuntu repositories:

$ apt-get install pgloader

A v4 Debian package is planned.

LICENCE Copy Chapter

pgloader is available under The PostgreSQL
Licence.

File: bundle/README.md Copy Chapter

pgloader source bundle

In order to ease building pgloader for non-lisp users, the bundle
distribution is a tarball containing pgloader and its build dependencies.
See the the following documentation for more details:

https://www.quicklisp.org/beta/bundles.html

The bundle comes with a specific Makefile so that building it is as
simple as the following (which includes testing the resulting binary):

make
LANG=en_US.UTF-8 make test

The compilation might takes a while, it's because SBCL is trying hard to
generate run-time binary code that is fast and efficient. Yes you need to be
in a unicide environment to run the test suite, so that it matches with the
encoding of the test *.load files.

You can then package or use the pgloader binary:

./bin/pgloader --version
./bin/pgloader --help

Note that the SQLite test files are not included in the bundle, for weithing
too much here.

File: clojure/README.md Copy Chapter

pgloader v4 (Clojure)

Clojure-based reimplementation of pgloader, a data loading tool for PostgreSQL.
Supports MySQL, MariaDB, SQLite, CSV, COPY, DBF, Fixed-width, MSSQL, and
PostgreSQL-as-source migration.

Status Copy Chapter

This is an in-progress rewrite. Core migration paths (CSV, MySQL, MariaDB,
SQLite, DBF, PostgreSQL) are fully implemented and covered by integration tests
that run against both v3 and v4 in CI. Known gaps:

• MSSQL: source introspection is implemented; full end-to-end coverage is limited.
• IXF / Redshift: not planned for v4 at this time.
• Built-in transforms: all 19 v3 transforms are ported to pgloader.transforms.

Intentional differences from v3 Copy Chapter

INI config files — not supported Copy Section

v3 supported two INI-related features that are not implemented in v4 and
will not be:

1. --upgrade-config FILE.conf — converted pgloader v2.x INI-format
   configuration files to v3 load file syntax. pgloader v2 has been
   unsupported for many years; this migration path is no longer needed.

2. --context FILE.ini — fed an INI file as a mustache template context
   into a .load file (e.g. {{DBPATH}}). v4 replaces this with standard
   OS environment variables: set DBPATH in the shell environment and
   reference it in the load file as $DBPATH or via ${DBPATH}.

If you relied on --context, move your INI values to environment variables
and reference them in your .load file. No load-file syntax changes are
needed for simple key=value substitution.

Directory structure Copy Chapter

/* Detailed source-code truncated for AI context efficiency. */

Requirements Copy Chapter

• JDK 21+
• Clojure CLI 1.11+
• Docker + Docker Compose v2 (for integration tests)

Build Copy Chapter

clojure -T:build uber       # produces target/pgloader-v4-*.jar
make build                  # same, via Makefile

How to run tests Copy Chapter

All integration tests run entirely inside Docker Compose — no local database
needed.

Unit tests (no Docker) Copy Section

make test-unit
# or directly:
clojure -M:test -m cognitect.test-runner \
  -n pgloader.cast-test -n pgloader.batch-test \
  -n pgloader.ddl-test -n pgloader.load-file.parser-test

Integration tests (Docker Compose) Copy Section

# From clojure/tests/:
make csv           # CSV + COPY + Fixed-width (v4)
make csv-v3        # same tests with pgloader v3
make mysql         # MySQL 8.0 → PostgreSQL
make sqlite        # SQLite → PostgreSQL
make dbf           # DBF → PostgreSQL
make pgsql         # PostgreSQL → PostgreSQL
make mssql         # SQL Server → PostgreSQL
make citus         # Citus distributed tables
make all           # all suites with v4
make all-v3        # all suites with v3

# Regenerate expected baselines (uses v3 as source of truth):
make update-expected-csv
make update-expected-mysql

Each target spins up the required database services, waits for health-checks,
runs tests inside the test-runner container, then cleans up.

Run migrations Copy Chapter

# From LOAD file (after build):
java -jar target/pgloader-v4-*.jar path/to/file.load

# Direct URI migration:
java -jar target/pgloader-v4-*.jar mysql://user:pass@host/db pgsql://user:pass@host/db

# Pipe data via stdin:
cat data.csv | java -jar target/pgloader-v4-*.jar /path/to/file.load

File: docs/ref/archive.rst Copy Chapter

Archive (http, zip)

This command instructs pgloader to load data from one or more files contained
in an archive. Currently the only supported archive format is ZIP, and the
archive might be downloaded from an HTTP URL.

Using advanced options and a load command file Copy Chapter

The command then would be:

::

$ pgloader archive.load

And the contents of the archive.load file could be inspired from the
following:

::

LOAD ARCHIVE
   FROM /Users/dim/Downloads/GeoLiteCity-latest.zip
   INTO postgresql:///ip4r

   BEFORE LOAD
     DO $$ create extension if not exists ip4r; $$,
        $$ create schema if not exists geolite; $$,

     EXECUTE 'geolite.sql'

   LOAD CSV
        FROM FILENAME MATCHING ~/GeoLiteCity-Location.csv/
             WITH ENCODING iso-8859-1
             (
                locId,
                country,
                region     null if blanks,
                city       null if blanks,
                postalCode null if blanks,
                latitude,
                longitude,
                metroCode  null if blanks,
                areaCode   null if blanks
             )
        INTO postgresql:///ip4r?geolite.location
             (
                locid,country,region,city,postalCode,
                location point using (format nil "(~a,~a)" longitude latitude),
                metroCode,areaCode
             )
        WITH skip header = 2,
             fields optionally enclosed by '"',
             fields escaped by double-quote,
             fields terminated by ','

  AND LOAD CSV
        FROM FILENAME MATCHING ~/GeoLiteCity-Blocks.csv/
             WITH ENCODING iso-8859-1
             (
                startIpNum, endIpNum, locId
             )
        INTO postgresql:///ip4r?geolite.blocks
             (
                iprange ip4r using (ip-range startIpNum endIpNum),
                locId
             )
        WITH skip header = 2,
             fields optionally enclosed by '"',
             fields escaped by double-quote,
             fields terminated by ','

   FINALLY DO
     $$ create index blocks_ip4r_idx on geolite.blocks using gist(iprange); $$;

Common Clauses Copy Chapter

Please refer to :ref:common_clauses for documentation about common
clauses.

Archive Source Specification: FROM Copy Chapter

Filename or HTTP URI where to load the data from. When given an HTTP URL the
linked file will get downloaded locally before processing.

If the file is a zip file, the command line utility unzip is used to
expand the archive into files in $TMPDIR, or /tmp if $TMPDIR is unset
or set to a non-existing directory.

Then the following commands are used from the top level directory where the
archive has been expanded.

Archive Sub Commands Copy Chapter

• command [ AND command ... ]

  A series of commands against the contents of the archive, at the moment
  only CSV,'FIXED and DBF commands are supported.

  Note that commands are supporting the clause FROM FILENAME MATCHING
  which allows the pgloader command not to depend on the exact names of
  the archive directories.

  The same clause can also be applied to several files with using the
  spelling FROM ALL FILENAMES MATCHING and a regular expression.

  The whole matching clause must follow the following rule::

  FROM [ ALL FILENAMES | [ FIRST ] FILENAME ] MATCHING

Archive Final SQL Commands Copy Chapter

• FINALLY DO

  SQL Queries to run once the data is loaded, such as CREATE INDEX.

File: docs/ref/copy.rst Copy Chapter

COPY

This commands instructs pgloader to load from a file containing COPY TEXT
data as described in the PostgreSQL documentation.

Using advanced options and a load command file Copy Chapter

The command then would be:

::

$ pgloader copy.load

And the contents of the copy.load file could be inspired from the following:

::

LOAD COPY
     FROM copy://./data/track.copy
          (
            trackid, track, album, media, genre, composer,
            milliseconds, bytes, unitprice
          )
     INTO postgresql:///pgloader
   TARGET TABLE track_full

     WITH truncate

      SET work_mem to '14MB',
          standard_conforming_strings to 'on'

BEFORE LOAD DO
     $$ drop table if exists track_full; $$,
     $$ create table track_full (
          trackid      bigserial,
          track        text,
          album        text,
          media        text,
          genre        text,
          composer     text,
          milliseconds bigint,
          bytes        bigint,
          unitprice    numeric
        );
     $$;

Common Clauses Copy Chapter

Please refer to :ref:common_clauses for documentation about common
clauses.

COPY Formatted Files Source Specification: FROM Copy Chapter

Filename where to load the data from. This support local files, HTTP URLs
and zip files containing a single dbf file of the same name. Fetch such a
zip file from an HTTP address is of course supported.

• inline

  The data is found after the end of the parsed commands. Any number of
  empty lines between the end of the commands and the beginning of the
  data is accepted.

• stdin

  Reads the data from the standard input stream.

• FILENAMES MATCHING

  The whole matching clause must follow the following rule::

  [ ALL FILENAMES | [ FIRST ] FILENAME ]
  MATCHING regexp
  [ IN DIRECTORY '...' ]

  The matching clause applies given regular expression (see above for
  exact syntax, several options can be used here) to filenames. It's then
  possible to load data from only the first match of all of them.

  The optional IN DIRECTORY clause allows specifying which directory to
  walk for finding the data files, and can be either relative to where the
  command file is read from, or absolute. The given directory must exists.

COPY Formatted File Options: WITH Copy Chapter

When loading from a COPY file, the following options are supported:

• delimiter

  Takes a single character as argument, which must be found inside single
  quotes, and might be given as the printable character itself, the
  special value \t to denote a tabulation character, or 0x then an
  hexadecimal value read as the ASCII code for the character.

  This character is used as the delimiter when reading the data, in a
  similar way to the PostgreSQL COPY option.

• null

  Takes a quoted string as an argument (quotes can be either double quotes
  or single quotes) and uses that string as the NULL representation in
  the data.

  This is similar to the null COPY option in PostgreSQL.

• truncate

  When this option is listed, pgloader issues a TRUNCATE command against
  the PostgreSQL target table before reading the data file.

• disable triggers

  When this option is listed, pgloader issues an ALTER TABLE ... DISABLE TRIGGER ALL command against the PostgreSQL target table before copying
  the data, then the command ALTER TABLE ... ENABLE TRIGGER ALL once the
  COPY is done.

  This option allows loading data into a pre-existing table ignoring the
  foreign key constraints and user defined triggers and may result in
  invalid foreign key constraints once the data is loaded. Use with
  care.

• skip header

  Takes a numeric value as argument. Instruct pgloader to skip that many
  lines at the beginning of the input file.

File: docs/ref/csv.rst Copy Chapter

CSV

This command instructs pgloader to load data from a CSV file. Because of
the complexity of guessing the parameters of a CSV file, it's simpler to
instruct pgloader with how to parse the data in there, using the full
pgloader command syntax and CSV specifications as in the following example.

Using advanced options and a load command file Copy Chapter

The command then would be:

::

$ pgloader csv.load

And the contents of the csv.load file could be inspired from the following:

::

LOAD CSV
   FROM 'GeoLiteCity-Blocks.csv' WITH ENCODING iso-646-us
        HAVING FIELDS
        (
           startIpNum, endIpNum, locId
        )
   INTO postgresql://user@localhost:54393/dbname
        TARGET TABLE geolite.blocks
        TARGET COLUMNS
        (
           iprange ip4r using (ip-range startIpNum endIpNum),
           locId
        )
   WITH truncate,
        skip header = 2,
        fields optionally enclosed by '"',
        fields escaped by backslash-quote,
        fields terminated by '\t'

    SET work_mem to '32 MB', maintenance_work_mem to '64 MB';

Common Clauses Copy Chapter

Please refer to :ref:common_clauses for documentation about common
clauses.

CSV Source Specification: FROM Copy Chapter

Filename where to load the data from. Accepts an ENCODING option. Use the
--list-encodings option to know which encoding names are supported.

The filename may be enclosed by single quotes, and could be one of the
following special values:

• inline

  The data is found after the end of the parsed commands. Any number
  of empty lines between the end of the commands and the beginning of
  the data is accepted.

• stdin

  Reads the data from the standard input stream.

• FILENAME MATCHING

  The whole matching clause must follow the following rule::

  text Copy

  [ ALL FILENAMES | [ FIRST ] FILENAME ]
      MATCHING regexp
      [ IN DIRECTORY '...' ]

  The matching clause applies given regular expression (see above
  for exact syntax, several options can be used here) to filenames.
  It's then possible to load data from only the first match of all of
  them.

  The optional IN DIRECTORY clause allows specifying which directory
  to walk for finding the data files, and can be either relative to
  where the command file is read from, or absolute. The given
  directory must exists.

Fields Specifications Copy Chapter

The FROM option also supports an optional comma separated list of field
names describing what is expected in the CSV data file, optionally
introduced by the clause HAVING FIELDS.

Each field name can be either only one name or a name following with
specific reader options for that field, enclosed in square brackets and
comma-separated. Supported per-field reader options are:

• terminated by

  See the description of field terminated by below.

  The processing of this option is not currently implemented.

• date format

  When the field is expected of the date type, then this option allows
  to specify the date format used in the file.

  Date format string are template strings modeled against the
  PostgreSQL to_char template strings support, limited to the
  following patterns:

  • YYYY, YYY, YY for the year part
  • MM for the numeric month part
  • DD for the numeric day part
  • HH, HH12, HH24 for the hour part
  • am, AM, a.m., A.M.
  • pm, PM, p.m., P.M.
  • MI for the minutes part
  • SS for the seconds part
  • MS for the milliseconds part (4 digits)
  • US for the microseconds part (6 digits)
  • unparsed punctuation signs: - . * # @ T / \ and space

  Here's an example of a date format specification::

  text Copy

  column-name [date format 'YYYY-MM-DD HH24-MI-SS.US']

• null if

  This option takes an argument which is either the keyword blanks
  or a double-quoted string.

  When blanks is used and the field value that is read contains
  only space characters, then it's automatically converted to an SQL
  NULL value.

  When a double-quoted string is used and that string is read as the
  field value, then the field value is automatically converted to an
  SQL NULL value.

• trim both whitespace, trim left whitespace, trim right whitespace

  This option allows to trim whitespaces in the read data, either from
  both sides of the data, or only the whitespace characters found on
  the left of the streaing, or only those on the right of the string.

CSV Loading Options: WITH Copy Chapter

When loading from a CSV file, the following options are supported:

• truncate

  When this option is listed, pgloader issues a TRUNCATE command
  against the PostgreSQL target table before reading the data file.

• drop indexes

  When this option is listed, pgloader issues DROP INDEX commands
  against all the indexes defined on the target table before copying
  the data, then CREATE INDEX commands once the COPY is done.

  In order to get the best performance possible, all the indexes are
  created in parallel and when done the primary keys are built again
  from the unique indexes just created. This two step process allows
  creating the primary key index in parallel with the other indexes,
  as only the ALTER TABLE command needs an access exclusive lock
  on the target table.

• disable triggers

  When this option is listed, pgloader issues an ALTER TABLE ... DISABLE TRIGGER ALL command against the PostgreSQL target table
  before copying the data, then the command ALTER TABLE ... ENABLE TRIGGER ALL once the COPY is done.

  This option allows loading data into a pre-existing table ignoring
  the foreign key constraints and user defined triggers and may
  result in invalid foreign key constraints once the data is loaded.
  Use with care.

• skip header

  Takes a numeric value as argument. Instruct pgloader to skip that
  many lines at the beginning of the input file.

• csv header

  Use the first line read after skip header as the list of csv field
  names to be found in the CSV file, using the same CSV parameters as
  for the CSV data.

• date format

  Takes a date format string as argument. When target columns are of a
  PostgreSQL date/time type, this option applies the format to those
  fields by default. A per-field date format specification still takes
  precedence for that field.

  Here's an example of a WITH date format specification::

  WITH date format 'YYYY-MM-DD HH24-MI-SS.US'

• trim unquoted blanks

  When reading unquoted values in the CSV file, remove the blanks
  found in between the separator and the value. That behaviour is the
  default.

• keep unquoted blanks

  When reading unquoted values in the CSV file, keep blanks found in
  between the separator and the value.

• fields optionally enclosed by

  Takes a single character as argument, which must be found inside single
  quotes, and might be given as the printable character itself, the
  special value \t to denote a tabulation character, the special value '
  to denote a single-quote, or 0x then an hexadecimal value read as the
  ASCII code for the character.

  The following options specify the same enclosing character, a single quote::

  fields optionally enclosed by '''
  fields optionally enclosed by '0x27'

  This character is used as the quoting character in the CSV file,
  and defaults to double-quote.

• fields not enclosed

  By default, pgloader will use the double-quote character as the
  enclosing character. If you have a CSV file where fields are not
  enclosed and are using double-quote as an expected ordinary
  character, then use the option fields not enclosed for the CSV
  parser to accept those values.

• fields escaped by

  Takes either the special value backslash-quote or double-quote,
  or any value supported by the fields terminated by option (see
  below). This value is used to recognize escaped field separators
  when they are to be found within the data fields themselves.
  Defaults to double-quote.

• csv escape mode

  Takes either the special value quote (the default) or following
  and allows the CSV parser to parse either only escaped field
  separator or any character (including CSV data) when using the
  following value.

• fields terminated by

  Takes a single character as argument, which must be found inside
  single quotes, and might be given as the printable character itself,
  the special value \t to denote a tabulation character, or 0x then
  an hexadecimal value read as the ASCII code for the character.

  This character is used as the field separator when reading the
  CSV data.

• lines terminated by

  Takes a single character as argument, which must be found inside
  single quotes, and might be given as the printable character itself,
  the special value \t to denote a tabulation character, or 0x then
  an hexadecimal value read as the ASCII code for the character.

  This character is used to recognize end-of-line condition when
  reading the CSV data.

File: docs/ref/dbf.rst Copy Chapter

DBF

This command instructs pgloader to load data from a DBF file. A default
set of casting rules are provided and might be overloaded and appended to by
the command.

Using advanced options and a load command file Copy Chapter

Here's an example with a remote HTTP source and some user defined casting
rules. The command then would be:

::

$ pgloader dbf.load

And the contents of the dbf.load file could be inspired from the following:

::

LOAD DBF
    FROM http://www.insee.fr/fr/methodes/nomenclatures/cog/telechargement/2013/dbf/reg2013.dbf
    INTO postgresql://user@localhost/dbname
    WITH truncate, create table
    CAST column reg2013.region to integer,
         column reg2013.tncc to smallint;

Common Clauses Copy Chapter

Please refer to :ref:common_clauses for documentation about common
clauses.

DBF Source Specification: FROM Copy Chapter

Filename where to load the data from. This support local files, HTTP URLs
and zip files containing a single dbf file of the same name. Fetch such a
zip file from an HTTP address is of course supported.

DBF Loading Options: WITH Copy Chapter

When loading from a DBF file, the following options are supported:

• truncate

  When this option is listed, pgloader issues a TRUNCATE command against
  the PostgreSQL target table before reading the data file.

• disable triggers

  When this option is listed, pgloader issues an ALTER TABLE ... DISABLE TRIGGER ALL command against the PostgreSQL target table before copying
  the data, then the command ALTER TABLE ... ENABLE TRIGGER ALL once the
  COPY is done.

  This option allows loading data into a pre-existing table ignoring the
  foreign key constraints and user defined triggers and may result in
  invalid foreign key constraints once the data is loaded. Use with
  care.

• create table

  When this option is listed, pgloader creates the table using the meta
  data found in the DBF file, which must contain a list of fields with
  their data type. A standard data type conversion from DBF to PostgreSQL
  is done.

• table name

  This options expects as its value the possibly qualified name of the
  table to create.

Default DB3 Casting Rules Copy Chapter

When migrating from DB3 the following Casting Rules are provided::

type C to text using db3-trim-string
type M to text using db3-trim-string
type N to numeric using db3-numeric-to-pgsql-integer
type I to numeric using db3-numeric-to-pgsql-numeric
type L to boolean using logical-to-boolean
type D to date using db3-date-to-pgsql-date

File: docs/ref/fixed.rst Copy Chapter

Fixed Columns

This command instructs pgloader to load data from a text file containing
columns arranged in a fixed size manner.

Using advanced options and a load command file Copy Chapter

The command then would be:

::

$ pgloader fixed.load

And the contents of the fixed.load file could be inspired from the following:

::

LOAD FIXED
     FROM inline
          (
           a from  0 for 10,
           b from 10 for  8,
           c from 18 for  8,
           d from 26 for 17 [null if blanks, trim right whitespace]
          )
     INTO postgresql:///pgloader
   TARGET TABLE fixed
          (
             a, b,
             c time using (time-with-no-separator c),
             d
          )

     WITH truncate

      SET work_mem to '14MB',
          standard_conforming_strings to 'on'

BEFORE LOAD DO
     $$ drop table if exists fixed; $$,
     $$ create table fixed (
         a integer,
         b date,
         c time,
         d text
        );
     $$;

 01234567892008052011431250firstline
    01234562008052115182300left blank-padded
 12345678902008052208231560another line
  2345609872014092914371500                 
  2345678902014092914371520

Note that the example comes from the test suite of pgloader, where we use
the advanced feature FROM inline that allows embedding the source data
within the command file. In most cases a more classic FROM clause loading
the data from a separate file would be used.

Common Clauses Copy Chapter

Please refer to :ref:common_clauses for documentation about common
clauses.

Fixed File Format Source Specification: FROM Copy Chapter

Filename where to load the data from. Accepts an ENCODING option. Use the
--list-encodings option to know which encoding names are supported.

The filename may be enclosed by single quotes, and could be one of the
following special values:

• inline

  The data is found after the end of the parsed commands. Any number
  of empty lines between the end of the commands and the beginning of
  the data is accepted.

• stdin

  Reads the data from the standard input stream.

• FILENAMES MATCHING

  The whole matching clause must follow the following rule::

  text Copy

  [ ALL FILENAMES | [ FIRST ] FILENAME ]
  MATCHING regexp
  [ IN DIRECTORY '...' ]

  The matching clause applies given regular expression (see above
  for exact syntax, several options can be used here) to filenames.
  It's then possible to load data from only the first match of all of
  them.

  The optional IN DIRECTORY clause allows specifying which directory
  to walk for finding the data files, and can be either relative to
  where the command file is read from, or absolute. The given
  directory must exists.

Fields Specifications Copy Chapter

The FROM option also supports an optional comma separated list of field
names describing what is expected in the FIXED data file.

Each field name is composed of the field name followed with specific reader
options for that field. Supported per-field reader options are the
following, where only start and length are required.

• start

  Position in the line where to start reading that field's value. Can
  be entered with decimal digits or 0x then hexadecimal digits.

• length

  How many bytes to read from the start position to read that
  field's value. Same format as start.

Those optional parameters must be enclosed in square brackets and
comma-separated:

• terminated by

  See the description of field terminated by below.

  The processing of this option is not currently implemented.

• date format

  When the field is expected of the date type, then this option allows
  to specify the date format used in the file.

  Date format string are template strings modeled against the
  PostgreSQL to_char template strings support, limited to the
  following patterns:

  • YYYY, YYY, YY for the year part
  • MM for the numeric month part
  • DD for the numeric day part
  • HH, HH12, HH24 for the hour part
  • am, AM, a.m., A.M.
  • pm, PM, p.m., P.M.
  • MI for the minutes part
  • SS for the seconds part
  • MS for the milliseconds part (4 digits)
  • US for the microseconds part (6 digits)
  • unparsed punctuation signs: - . * # @ T / \ and space

  Here's an example of a date format specification::

  text Copy

  column-name [date format 'YYYY-MM-DD HH24-MI-SS.US']

• null if

  This option takes an argument which is either the keyword blanks
  or a double-quoted string.

  When blanks is used and the field value that is read contains only
  space characters, then it's automatically converted to an SQL NULL
  value.

  When a double-quoted string is used and that string is read as the
  field value, then the field value is automatically converted to an
  SQL NULL value.

• trim both whitespace, trim left whitespace, trim right whitespace

  This option allows to trim whitespaces in the read data, either from
  both sides of the data, or only the whitespace characters found on
  the left of the streaing, or only those on the right of the string.

Fixed File Format Loading Options: WITH Copy Chapter

When loading from a FIXED file, the following options are supported:

• date format

  Takes a date format string as argument. When target columns are of a
  PostgreSQL date/time type, this option applies the format to those
  fields by default. A per-field date format specification still takes
  precedence for that field.

  Here's an example of a WITH date format specification::

  WITH date format 'YYYY-MM-DD HH24-MI-SS.US'

• truncate

  When this option is listed, pgloader issues a TRUNCATE command
  against the PostgreSQL target table before reading the data file.

• disable triggers

  When this option is listed, pgloader issues an ALTER TABLE ... DISABLE TRIGGER ALL command against the PostgreSQL target table
  before copying the data, then the command ALTER TABLE ... ENABLE TRIGGER ALL once the COPY is done.

  This option allows loading data into a pre-existing table ignoring
  the foreign key constraints and user defined triggers and may
  result in invalid foreign key constraints once the data is loaded.
  Use with care.

• skip header

  Takes a numeric value as argument. Instruct pgloader to skip that
  many lines at the beginning of the input file.

File: docs/ref/ixf.rst Copy Chapter

IXF

This command instructs pgloader to load data from an IBM IXF file.

Using advanced options and a load command file Copy Chapter

The command then would be:

::

$ pgloader ixf.load

And the contents of the ixf.load file could be inspired from the following:

::

LOAD IXF
    FROM data/nsitra.test1.ixf
    INTO postgresql:///pgloader
  TARGET TABLE nsitra.test1
    WITH truncate, create table, timezone UTC

  BEFORE LOAD DO
   $$ create schema if not exists nsitra; $$,
   $$ drop table if exists nsitra.test1; $$;

Common Clauses Copy Chapter

Please refer to :ref:common_clauses for documentation about common
clauses.

IXF Source Specification: FROM Copy Chapter

Filename where to load the data from. This support local files, HTTP URLs
and zip files containing a single ixf file of the same name. Fetch such a
zip file from an HTTP address is of course supported.

IXF Loading Options: WITH Copy Chapter

When loading from a IXF file, the following options are supported:

• truncate

  When this option is listed, pgloader issues a TRUNCATE command against
  the PostgreSQL target table before reading the data file.

• disable triggers

  When this option is listed, pgloader issues an ALTER TABLE ... DISABLE TRIGGER ALL command against the PostgreSQL target table before copying
  the data, then the command ALTER TABLE ... ENABLE TRIGGER ALL once the
  COPY is done.

  This option allows loading data into a pre-existing table ignoring the
  foreign key constraints and user defined triggers and may result in
  invalid foreign key constraints once the data is loaded. Use with
  care.

• create table

  When this option is listed, pgloader creates the table using the meta
  data found in the DBF file, which must contain a list of fields with
  their data type. A standard data type conversion from DBF to PostgreSQL
  is done.

• table name

  This options expects as its value the possibly qualified name of the
  table to create.

• timezone

  This options allows to specify which timezone is used when parsing
  timestamps from an IXF file, and defaults to UTC. Expected values are
  either UTC, GMT or a single quoted location name such as
  'Universal' or 'Europe/Paris'.

File: docs/ref/mssql.rst Copy Chapter

MS SQL to Postgres

This command instructs pgloader to load data from a MS SQL database.
Automatic discovery of the schema is supported, including build of the
indexes, primary and foreign keys constraints.

Using default settings Copy Chapter

Here is the simplest command line example, which might be all you need:

::

$ pgloader mssql://user@mshost/dbname pgsql://pguser@pghost/dbname

Using advanced options and a load command file Copy Chapter

The command then would be:

::

$ pgloader ms.load

And the contents of the command file ms.load could be inspired from the
following:

::

load database
     from mssql://user@host/dbname
     into postgresql:///dbname

including only table names like 'GlobalAccount' in schema 'dbo'

set work_mem to '16MB', maintenance_work_mem to '512 MB'

before load do $$ drop schema if exists dbo cascade; $$;

Common Clauses Copy Chapter

Please refer to :ref:common_clauses for documentation about common
clauses.

MS SQL Database Source Specification: FROM Copy Chapter

Connection string to an existing MS SQL database server that accepts TCP/IP
connections. pgloader v4 uses the official Microsoft JDBC driver (mssql-jdbc)
so no FreeTDS or ODBC installation is required.

Both pgloader-native and JDBC URL formats are accepted::

mssql://[user[:password]@][host][:port][/dbname]
jdbc:sqlserver://host[:port][;param=value;...]

The JDBC URL format is particularly useful when you need to pass driver-specific
parameters such as encrypt, trustServerCertificate, or
applicationIntent:

.. code-block:: sql

-- Native pgloader URI (encrypt=false by default)
FROM mssql://sa:password@mssql:1433/mydb

-- JDBC URL — no URL-encoding needed for special chars in password,
--            and all MSSQL JDBC properties are available:
FROM jdbc:sqlserver://mssql:1433;databaseName=mydb;user=sa;password=P@$$w0rd;encrypt=false

-- Azure SQL / SQL Server with SSL:
FROM jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;user=admin;password=secret;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net

When using the native mssql:// URI, encrypt=false is set automatically
(matching the previous FreeTDS behaviour). Override it by using a JDBC URL.

MS SQL Database Migration Options: WITH Copy Chapter

When loading from a MS SQL database, the same options as when loading a
MYSQL database are supported. Please refer to the MYSQL section. The
following options are added:

• create schemas

  When this option is listed, pgloader creates the same schemas as found
  on the MS SQL instance. This is the default.

• create no schemas

  When this option is listed, pgloader refrains from creating any schemas
  at all, you must then ensure that the target schema do exist.

MS SQL Database Casting Rules Copy Chapter

CAST
^^^^

The CAST clause overrides or extends the built-in type mapping rules
that pgloader applies when converting an MS SQL schema to PostgreSQL.

The built-in defaults map all character types (char, nchar,
varchar, nvarchar, ntext) to text and discard the length
modifier. Use CAST when you want to preserve types and/or length
modifiers.

User-defined rules are evaluated before the built-in defaults, and the
first matching rule wins.

Synopsis
""""""""

::

CAST cast_rule [, ...]

where cast_rule is:

.. code-block:: text

{ type source_type | column table_name.column_name }
[ guard ... ]
[ to target_type ]
[ option ... ]
[ using function_name ]

where guard is one of:

.. code-block:: text

when default 'string'
when ( typemod_expression )
and not null

and option is one of:

.. code-block:: text

{ drop | keep } default
{ drop | keep } typemod
{ drop | keep | set } not null
{ drop | keep } extra

Parameters
""""""""""

type source_type
Match all columns whose MS SQL data type is source_type
(case-insensitive). This is the most common form.

column table_name.column_name
Match a single column by its qualified name. This form accepts the
same option list as the type form but no guard.

to target_type
The PostgreSQL type to emit in CREATE TABLE. When omitted, the
type from the first matching built-in rule is used and only the
options take effect.

Guards
""""""

Guards narrow the set of columns a rule applies to. Multiple guards on
the same rule are combined with AND semantics.

when default '\ string\ '
Match only columns whose default value equals string.

when ( typemod_expression )
Match only columns whose type modifier satisfies typemod_expression,
a Common Lisp s-expression where precision and scale are bound
to the modifier components. For example, nvarchar(40) has
precision = 40 and scale = 0; nvarchar(max) has
precision = -1.

Supported operators: ``=``, ``<``, ``>``, ``<=``, ``>=``; combine
with ``and`` and ``or``.

and not null
Match only columns declared NOT NULL in the source.

Options
"""""""

drop typemod / keep typemod
Controls whether the length modifier is carried into the PostgreSQL
type definition.

*  **Default for all built-in MSSQL rules**: ``drop typemod`` — the
   modifier is discarded.  ``nvarchar(40)`` with ``type nvarchar to
   varchar`` (or the built-in rule) produces a plain ``varchar``.
*  ``keep typemod`` preserves it.  ``nvarchar(40)`` with
   ``type nvarchar to varchar keep typemod`` produces ``varchar(40)``.

drop default / keep default
Controls whether the source column's DEFAULT expression is copied
to the CREATE TABLE statement. The default behaviour is drop default because MS SQL default expressions are often not valid
PostgreSQL syntax.

drop not null / keep not null / set not null
Controls the NOT NULL constraint on the target column.
set not null adds the constraint even when the source column is
nullable.

Examples
""""""""

Preserve character-type length modifiers instead of mapping everything to
text::

CAST type nvarchar to varchar keep typemod,
type nchar to char keep typemod,
type varchar to varchar keep typemod,
type char to char keep typemod

With these rules, nvarchar(40) becomes varchar(40),
nchar(10) becomes char(10), and so on. Without them the
built-in defaults would produce plain text for all four types.

Handle nvarchar(max) (the driver reports precision = -1)
separately, keeping it as text, while using a fixed-length type for
all other nvarchar columns::

CAST type nvarchar when (= precision -1) to text drop typemod,
type nvarchar to varchar keep typemod

Override a single column while keeping the general rule::

CAST type nvarchar to varchar keep typemod,
column dbo.product.code to char(8) drop typemod

MS SQL Views Support Copy Chapter

MS SQL views support allows pgloader to migrate view as if they were base
tables. This feature then allows for on-the-fly transformation from MS SQL
to PostgreSQL, as the view definition is used rather than the base data.

MATERIALIZE VIEWS
^^^^^^^^^^^^^^^^^

This clause allows you to implement custom data processing at the data
source by providing a view definition against which pgloader will query
the data. It's not possible to just allow for plain SQL because we want to
know a lot about the exact data types of each column involved in the query
output.

This clause expect a comma separated list of view definitions, each one
being either the name of an existing view in your database or the following
expression::

name AS $$ sql query $$

The name and the sql query will be used in a CREATE VIEW statement at
the beginning of the data loading, and the resulting view will then be
dropped at the end of the data loading.

MATERIALIZE ALL VIEWS
^^^^^^^^^^^^^^^^^^^^^

Same behaviour as MATERIALIZE VIEWS using the dynamic list of views as
returned by MS SQL rather than asking the user to specify the list.

MS SQL Partial Migration Copy Chapter

INCLUDING ONLY TABLE NAMES LIKE
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Introduce a comma separated list of table name patterns used to limit the
tables to migrate to a sublist. More than one such clause may be used, they
will be accumulated together.

The patterns use SQL Server's native LIKE syntax, meaning % matches
any sequence of characters and _ matches any single character. Regular
expressions are not supported for MS SQL sources because SQL Server has no
native regex operator; the filter is applied directly in the WHERE clause
of the introspection query sent to the source database.

Example::

including only table names like 'GlobalAccount' in schema 'dbo'

EXCLUDING TABLE NAMES LIKE
^^^^^^^^^^^^^^^^^^^^^^^^^^

Introduce a comma separated list of table name patterns used to exclude
table names from the migration. This filter only applies to the result of
the INCLUDING filter.

::

excluding table names like 'LocalAccount' in schema 'dbo'

MS SQL Schema Transformations Copy Chapter

ALTER SCHEMA '...' RENAME TO '...'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Allows to rename a schema on the flight, so that for instance the tables
found in the schema 'dbo' in your source database will get migrated into the
schema 'public' in the target database with this command::

alter schema 'dbo' rename to 'public'

ALTER TABLE NAMES MATCHING ... IN SCHEMA '...'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Introduce a comma separated list of table names or regular expressions
that you want to target in the pgloader ALTER TABLE command. Available
actions are SET SCHEMA, RENAME TO, and SET::

ALTER TABLE NAMES MATCHING ~/_list$/, 'sales_by_store', ~/sales_by/
  IN SCHEMA 'dbo'
 SET SCHEMA 'mv'

ALTER TABLE NAMES MATCHING 'film' IN SCHEMA 'dbo' RENAME TO 'films'

ALTER TABLE NAMES MATCHING ~/./ IN SCHEMA 'dbo' SET (fillfactor='40')

ALTER TABLE NAMES MATCHING ~/./ IN SCHEMA 'dbo' SET TABLESPACE 'tlbspc'

You can use as many such rules as you need. The list of tables to be
migrated is searched in pgloader memory against the ALTER TABLE matching
rules, and for each command pgloader stops at the first matching criteria
(regexp or string).

No ALTER TABLE command is sent to PostgreSQL, the modification happens at
the level of the pgloader in-memory representation of your source database
schema. In case of a name change, the mapping is kept and reused in the
foreign key and index support.

The SET () action takes effect as a WITH clause for the CREATE TABLE
command that pgloader will run when it has to create a table.

The SET TABLESPACE action takes effect as a TABLESPACE clause for the
CREATE TABLE command that pgloader will run when it has to create a table.

The matching is done in pgloader itself, with a Common Lisp regular
expression lib, so doesn't depend on the LIKE implementation of MS SQL,
nor on the lack of support for regular expressions in the engine.

Azure SQL / Azure Active Directory Authentication Copy Chapter

pgloader v4 uses the Microsoft JDBC driver (mssql-jdbc) and bundles both
MSAL4J and the Azure Identity library (azure-identity), so all
non-browser-dependent Entra ID authentication modes work out of the box.
Always use a jdbc:sqlserver:// URL for Azure SQL so that encrypt=true
and authentication= reach the driver exactly as written.

Quick reference:

+--------------------------------------------+------------------------------------------------+
| Mode | Typical use case |
+============================================+================================================+
| ActiveDirectoryServicePrincipal | CI/CD — app registration (client ID + secret) |
+--------------------------------------------+------------------------------------------------+
| ActiveDirectoryServicePrincipalCertif. | CI/CD — app registration with certificate |
+--------------------------------------------+------------------------------------------------+
| ActiveDirectoryManagedIdentity | Azure VM / App Service / AKS — no credentials |
+--------------------------------------------+------------------------------------------------+
| ActiveDirectoryDefault | Developer laptop: az login fallback chain |
+--------------------------------------------+------------------------------------------------+
| ActiveDirectoryIntegrated | Windows / Kerberos domain-joined machine |
+--------------------------------------------+------------------------------------------------+
| ActiveDirectoryPassword | Deprecated — blocked by MFA enforcement |
+--------------------------------------------+------------------------------------------------+

ActiveDirectoryServicePrincipal
Authenticate as an Azure AD application (client ID + client secret) —
the recommended mode for CI/CD pipelines and automated migrations::

FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\

authentication=ActiveDirectoryServicePrincipal;
AADSecurePrincipalId=;AADSecurePrincipalSecret=;
encrypt=true"

ActiveDirectoryManagedIdentity
Authenticate using the managed identity of the Azure VM, App Service,
AKS pod, or container that pgloader runs in — no credentials in the URL::

FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\

authentication=ActiveDirectoryManagedIdentity;encrypt=true"

ActiveDirectoryDefault
The recommended mode for developer workstations. Uses the Azure
Identity DefaultAzureCredential chain: environment variables →
workload identity → managed identity → Azure CLI → Azure PowerShell.
After a one-time az login, no credentials need to appear in the URL::

FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\

authentication=ActiveDirectoryDefault;encrypt=true;trustServerCertificate=false"

This also works headlessly in WSL2: ``DefaultAzureCredential`` falls back
to ``AzureCliCredential``, which shells out to ``az account
get-access-token`` using your existing ``az login`` session — no browser
popup is needed.

ActiveDirectoryIntegrated
Authenticate via Kerberos on a domain-joined machine. Requires a valid
Kerberos ticket (kinit on Linux/macOS, automatic on Windows)::

FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\

authentication=ActiveDirectoryIntegrated;encrypt=true"

ActiveDirectoryPassword
.. note::

**Deprecated by Microsoft.**  This mode is incompatible with mandatory
   Entra ID multifactor authentication (MFA), which Microsoft now enforces
   by default across most tenants.  Attempting it in an MFA-required
   tenant returns ``AADSTS50076``.  Use ``ActiveDirectoryDefault`` or
   ``ActiveDirectoryServicePrincipal`` instead.

::

    FROM "jdbc:sqlserver://myserver.database.windows.net:1433;databaseName=mydb;\

authentication=ActiveDirectoryPassword;user=[email protected];
password=secret;encrypt=true;trustServerCertificate=false"

.. note::

ActiveDirectoryInteractive makes MSAL4J open a system browser via
xdg-open for an OAuth2 interactive login. It works on native Windows,
native macOS, and Linux desktops with a display server. It fails in
WSL2 (linux_xdg_open_failed — WSL2 has no JVM-accessible browser
integration), headless Linux, Docker/Podman containers, and CI pipelines.
Use ActiveDirectoryDefault with an existing az login session instead.

The native mssql:// scheme defaults to encrypt=false to match
on-premises SQL Server behaviour. For Azure SQL use the
jdbc:sqlserver:// form shown above so that encrypt=true is set
explicitly.

MS SQL Driver setup and encoding Copy Chapter

pgloader is using the FreeTDS driver, and internally expects the data to
be sent in utf-8. To achieve that, you can configure the FreeTDS driver with
those defaults, in the file ~/.freetds.conf::

[global]
    tds version = 7.4
    client charset = UTF-8

Default MS SQL Casting Rules Copy Chapter

When migrating from MS SQL the following Casting Rules are provided:

Numbers::

type tinyint to smallint

type float to float using float-to-string
type real to real using float-to-string
type double to double precision using float-to-string
type numeric to numeric using float-to-string
type decimal to numeric using float-to-string
type money to numeric using float-to-string
type smallmoney to numeric using float-to-string

Texts::

type char to text drop typemod
type nchar to text drop typemod
type varchar to text drop typemod
type nvarchar to text drop typemod
type xml to text drop typemod

Binary::

type binary to bytea using byte-vector-to-bytea
type varbinary to bytea using byte-vector-to-bytea

Date::

type datetime to timestamptz
type datetime2 to timestamptz

Others::

type bit to boolean
type hierarchyid to bytea
type geography to bytea
type uniqueidentifier to uuid using sql-server-uniqueidentifier-to-uuid