Easily Import Data into your Database

Community Content and Articles
1

Originally published at: Easily Import Data into your Database | Liquibase.com

We recently saw a tweet looking for an easy way to import data into a database. Many Liquibase users may not know that Liquibase can easily help with this!

Any AWS postgres users know how to import a CSV into postgres? I get the "must be a superuser" error b/c AWS doesn't let you be a superuser.

Tried to log in using psql and do it that way, but I'm messing something up.

Note: New to postgres, new to db hosting.

— Tim Courtney 🇺🇸 (@timcourtney) May 3, 2021

Liquibase does far more than CSV data loading. You can manage any database schema change just like app code change in source control. But databases don’t just have code! They have data, too. In some instances, you may want to load data into your database. For example, your application may hold insurance policies that are described in the database. Using the Liquibase loadData functionality can make sure those changes are applied correctly.

You can easily do this with the following XML. (JSON and YAML, are supported, too.)

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

Maybe you have data in the table that simply needs to be replaced with the CSV file data? To do that, use the delete functionality.

Remember to add runAlways=”true” to both of these changesets. That way, Liquibase will make sure the data is always up to date. Here’s a full example:

<changeSet  author="liquibase-docs"  id="delete-example" runAlways=”true”>  
    <delete  catalogName="cat"  
           schemaName="public"  
            tableName="person"/>  
</changeSet>

<changeSet author=“liquibase-docs” id=“loadData-example” runAlways=”true”>

<loadData catalogName=“cat”

commentLineStartsWith=“//”

encoding=“UTF-8”

file=“example/users.csv”

quotchar=“'”

relativeToChangelogFile=“true”

schemaName=“public”

separator=“;”

tableName=“person”

usePreparedStatements=“true”>

<column header=“header1”

name=“id”

type=“NUMERIC”/>

<column index=“3”

name=“name”

type=“BOOLEAN”/>

</loadData>

</changeSet>

Load your data with Liquibase

Need help or have questions about loading data into your database with Liquibase? We’re happy to help. You can always try out our expert support through a Liquibase Pro trial or head over to get help on our community forum.