loadUpdateData

Loads or updates data from a CSV file into an existing table. Differs from loadData by issuing a SQL batch that checks for the existence of a record. If found, the record is UPDATEd, else the record is INSERTed. Also, generates DELETE statements for a rollback.

A value of NULL in a cell will be converted to a database NULL rather than the string NULL.

Available attributes

Name Description Required for Supports Since
catalogName

Name of the catalog

all 3.0
commentLineStartsWith

Lines starting with this are treated as comment and ignored.

  all  
encoding Encoding of the CSV file (defaults to UTF-8) all
file CSV file to load all all
onlyUpdate If true, records with no matching database record should be ignored all 3.3
primaryKey Comma delimited list of the columns for the primary key all all
quotchar The quote character for string fields containing the separator character. all
relativeToChangelogFile

Specifies whether the file path is relative to the changelog file rather than looked up in the search path. Default: false.

all
schemaName

Name of the schema

all
separator Character separating the fields. all
tableName

Name of the table to insert or update data in

all all
usePreparedStatements Use prepared statements instead of insert statement strings if the database supports it.   all  

Nested tags

Name Description Required for Supports Multiple allowed
column

Column mapping and defaults can be defined. The header or index attributes need to be defined. If the header name in the CSV is different than the column, name needs to be inserted. If no column is defined at all, the type is taken from a database. Otherwise, for non-string columns the type definition might be required.

Note: YAML and JSON changelogs using the column tag must nest it within a columns tag.

all yes

Nested property attributes

Name Description
name Name of the column (Required).
type Data type of the column. Its value has to be one of the accepted values of LOAD_DATA_TYPE. If you want to skip loading a specific column, use the skip data type described earlier. Otherwise, all columns in the CSV file will be used.
header Name of the column in the CSV file from which the value for the column will be taken if it's different from the column name. Ignored if index is also defined.
index Index of the column in the CSV file from which the value for the column will be taken.

Examples

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet  author="liquibase-docs"  id="loadUpdateData-example">
        <loadUpdateData  catalogName="cat"
              commentLineStartsWith="//"
              encoding="UTF-8"
              file="example/users.csv"
              onlyUpdate="true"
              primaryKey="pk_id"
              quotchar="'"
              relativeToChangelogFile="true"
              schemaName="public"
              separator=";"
              tableName="person"
              usePreparedStatements="true">
            <column  header="header1"
                name="id"
                type="NUMERIC"/>
            <column  index="3"
                name="name"
                type="BOOLEAN"/>
        </loadUpdateData>
    </changeSet>

</databaseChangeLog>
databaseChangeLog:
-  changeSet:
    id:  loadUpdateData-example
    author:  liquibase-docs
    changes:
    -  loadUpdateData:
        catalogName:  cat
        columns:
        -  column:
            header:  header1
            name:  id
            type:  NUMERIC
        -  column:
            index:  3
            name:  name
            type:  BOOLEAN
        commentLineStartsWith:  //
        encoding:  UTF-8
        file:  example/users.csv
        onlyUpdate:  true
        primaryKey:  pk_id
        quotchar:  ''''
        relativeToChangelogFile:  true
        schemaName:  public
        separator:  ;
        tableName:  person
        usePreparedStatements:  true
{
    "databaseChangeLog": [
        {
            "changeSet": {
                "id": "loadUpdateData-example",
                "author": "liquibase-docs",
                "changes": [
                    {
                        "loadUpdateData": {
                            "catalogName": "cat",
                            "columns": [
                                {
                                    "column": {
                                        "header": "header1",
                                        "name": "id",
                                        "type": "NUMERIC"
                                    }
                                },
                                {
                                    "column": {
                                        "index": 3,
                                        "name": "name",
                                        "type": "BOOLEAN"
                                    }
                                }
                            ],
                            "commentLineStartsWith": "//",
                            "encoding": "UTF-8",
                            "file": "example/users.csv",
                            "onlyUpdate": true,
                            "primaryKey": "pk_id",
                            "quotchar": "'",
                            "relativeToChangelogFile": true,
                            "schemaName": "public",
                            "separator": ";",
                            "tableName": "person",
                            "usePreparedStatements": true
                        }
                    }
                ]
            }
        }
    ]
}

Database support

Database Notes Auto Rollback
DB2/LUW Supported No
DB2/z Supported No
Derby Supported No
Firebird Supported No
H2 Supported No
HyperSQL Supported No
INGRES Supported No
Informix Supported No
MariaDB Supported No
MySQL Supported No
Oracle Supported No
PostgreSQL Supported No
Snowflake Supported No
SQL Server Supported No
SQLite Supported No
Sybase Supported No
Sybase Anywhere Supported No