CINXE.COM

<!doctype html> <html lang="en"> <head> <title>Postgres | Ebean ORM</title> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <link rel="shortcut icon" href="/images/favicon.ico"> <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto|Source+Sans+Pro|Ubuntu&display=swap"> <link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.1.0/css/all.css" integrity="sha384-lKuwvrZot6UHsBSfcMvOkWwlCMgc0TaWr+30HWe3a4ltaBwTZhyTEggF5tJv8tbt" crossorigin="anonymous"> <link rel="stylesheet" href="/css/reset3.css"> <link rel="stylesheet" href="/css/site3.css"> <link rel="stylesheet" href="/css/pygments3.css"> </head> <body> <div id="main"> <div id="banner"> <header> <nav id="top"> <h1 id="breadcrumb"> <a class="nav-logo" href="/"><img src="/images/logo-200.png" height="35"></a>  <a href="/docs">Documentation</a><span class="sep"> / </span><a href="/docs/database">Database platforms</a><span class="sep"> / </span><span class="last">Postgres</span> </h1> <ul> <li><a onclick="toggleTheme();" title="switch dark light theme"><i class="fas fa-adjust"></i></a></li> </ul> </nav> </header> </div> <div class="grid grid-docs"> <aside> <nav class="side"> <ul> <li class="nav0 "> <a href="/docs/getting-started">Getting started</a> </li> <li class="nav0 "> <a href="/docs/intro">Introduction</a> </li> <li class="nav0 active"> <a class="active" href="/docs">Documentation</a> <ul> <li class="nav1 "> <a href="/docs/best-practice">Best practice</a> </li> <li class="nav1 "> <a href="/docs/query">Query</a> </li> <li class="nav1 "> <a href="/docs/persist">Persist</a> </li> <li class="nav1 "> <a href="/docs/transactions">Transactions</a> </li> <li class="nav1 "> <a href="/docs/mapping">Mapping</a> </li> <li class="nav1 "> <a href="/docs/ddl-generation">DDL & Migrations</a> </li> <li class="nav1 "> <a href="/docs/logging">Logging</a> </li> <li class="nav1 "> <a href="/docs/testing">Testing</a> </li> <li class="nav1 "> <a href="/docs/read-replicas">Read Replicas</a> </li> <li class="nav1 active"> <a class="active" href="/docs/database">Database platforms</a> <ul class="nav"> <li class="active"> <a class="active" href="/docs/database/postgres">Postgres</a> </li> <li > <a href="/docs/database/h2">H2</a> </li> <li > <a href="/docs/database/mysql">MySql</a> </li> <li > <a href="/docs/database/mariadb">MariaDB</a> </li> <li > <a href="/docs/database/sqlserver">SQL Server</a> </li> <li > <a href="/docs/database/oracle">Oracle</a> </li> <li > <a href="/docs/database/db2">DB2</a> </li> <li > <a href="/docs/database/hana">SAP Hana</a> </li> <li > <a href="/docs/database/sqlite">Sqlite</a> </li> <li > <a href="/docs/database/clickhouse">ClickHouse</a> </li> <li > <a href="/docs/database/cockroach">Cockroach</a> </li> <li > <a href="/docs/database/yugabyte">YugabyteDB</a> </li> <li > <a href="/docs/database/nuodb">NuoDB</a> </li> <li > <a href="/docs/database/elasticsearch">ElasticSearch</a> </li> <li > <a href="/docs/database/redis">Redis</a> </li> </ul> </li> <li class="nav1 "> <a href="/docs/multi-database">Multiple databases</a> </li> <li class="nav1 "> <a href="/docs/kotlin">Kotlin</a> </li> <li><a href="/docs/tuning">Tuning</a></li> <li class="nav1 "> <a href="/docs/features">Features</a> </li> </ul> </li> <li class="nav0 "> <a href="/support">Getting help</a> </li> <li class="nav0 "> <a target="_blank" href="/apidoc/13">API Javadoc</a> </li> <li class="nav0 "> <a href="/videos">Videos</a> </li> <li class="nav0 "> <a href="/docs/upgrading">Upgrading</a> </li> <li class="nav0 "> <a href="/releases">Releases</a> </li> </ul> </nav> </aside> <article> <form action="https://www.google.com/search" method="get" class="inline-form"> <input type="hidden" name="as_sitesearch" value="ebean.io"> <div id="page-search"> <div class="input-group"> <input class="frm" name="q" id="searchinput" type="text" placeholder="Search... (press 's' to focus)" data-placeholder-focus="Search... (use '↑', '↓' and '⏎' to select results)" data-placeholder-blur="Search... (press 's' to focus)" autocomplete="off"> <div class="input-group-btn"> <button class="frm" type="submit"><i class="fas fa-search"></i></button> </div> </div> <div id="page-search-results" style="display: none;"> <ul id="search-results-container" class="search-results"><li class=" active"><a href="/docs" title="Docs"><span style="color:#777;">Docs</span> Documentation </a></li><li class=""><small style="color:#999;">And 101 more...</small></li></ul> </div> </div> </form> <h2 id="postgres">Postgres</h2> <p> To test against Postgres docker test container set the <em>platform</em> to <code>postgres</code> in <code>src/test/resources/application-test.yaml</code> </p> <p> Refer to <a href="/docs/testing">docs / testing</a> if application-test.yaml doesn't exist yet. </p> <div class="syntax yml"><div class="highlight"><pre><span></span><span class="nt">ebean</span><span class="p">:</span> <span class="nt">test</span><span class="p">:</span> <span class="nt">platform</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">postgres</span> <span class="c1"># h2, postgres, ...</span> <span class="nt">ddlMode</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">dropCreate</span> <span class="c1"># none | dropCreate | migrations | create</span> <span class="nt">dbName</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">my_app</span> </pre></div> </div> <p> That is all we need to do. Running tests via IDE, maven or gradle will all automatically setup a docker test container for postgres including creating the database and user etc. </p> <p> The above will use the following defaults: </p> <table class="compact w100"> <tr><th>username:</th><td>{dbName}</td></tr> <tr><th>password:</th><td>test</td></tr> <tr><th>port:</th><td>6432</td></tr> <tr><th>url:</th><td>jdbc:postgresql://localhost:{port}/{dbName}</td></tr> <tr><th>image:</th><td>postgres:{version:12}</td></tr> </table> <h2 id="ebean-postgres">ebean-postgres dependency</h2> <p> We can use the <code>io.ebean:ebean-postgres</code> dependency rather than <code>io.ebean:ebean</code> if we want to only bring in the Postgres specific platform code. Depending on <code>io.ebean:ebean</code> will bring in all platforms. </p> <h2 id="types">Postgres types</h2> <h5>UUID</h5> <p> UUID is mapped to native Postgres UUID type. </p> <h5>INET</h5> <p> <em>java.net.InetAddress</em> and <em>io.ebean.types.Inet</em> are both automatically mapped to native Postgres INET type. When using InetAddress we need to take care that it doesn't perform unwanted DNS lookup validating addresses. <em>io.ebean.types.Inet</em> is a simple value type. </p> <h5>Array types - @DbArray</h5> <p> We use <a href="/docs/mapping/extensions/dbarray">@DbArray</a> to map Lists or Sets of UUID, String, Enums, Number types. These are mapped to Postgres array types like <em>uuid[], varchar[], integer[] ...</em> </p> <p> For more on <a href="arraytype">Postgres Array types</a>. </p> <h5>JSON / JSONB - @DbJson</h5> <p> We can use <a href="/docs/mapping/extensions/dbjson">@DbJson</a> and <a href="/docs/mapping/extensions/dbjson">@DbJsonB</a> to map content to Postgres JSON or JSONB types. </p> <h5>HSTORE - @DbMap</h5> <p> We can use <a href="/docs/mapping/extensions/dbmap">@DbMap</a> to map <code>Map<String,String></code> properties to Postgres HSTORE type. </p> <h2 id="any">Postgres ANY</h2> <p> Postgres has an <code>ANY</code> operator which allows binding an Array of values. This can provide a very big benefit when using <code> = ANY(?)</code> rather than the usual <code> IN (?,?,? ...)</code> when the number of bind values for an IN clause is variable. Effectively no matter the number of bind values with Postgres <code>= ANY</code> we get to use the exact same SQL. This means we need only use a single Ebean query plan, a single JDBC PreparedStatement and the database server also only see the one sql statement and typically means that it only needs to parse that once to determine the query plan ("the database has to do less hard parsing"). </p> <p> For these reasons Ebean will use Postgres <code>= ANY</code> whenever it can rather than use a <code>IN</code> clause. </p> <p> Additionally you often want to use <code>= ANY</code> in SQL statements for <a href="/docs/query/dtoquery#any">DtoQuery</a> and <a href="/docs/query/sqlquery#any">SqlQuery</a>. </p> <h2 id="history">History support</h2> <p> History support for Postgres is provided by generating triggers and history table. </p> <h2 id="partitioning">Table Partitioning</h2> <p> Postgres 10 added support for table partitioning. We use <code>@DbPartition</code> to define the table should have range partitioning based on DAY, WEEK, MONTH or YEAR. </p> <div class="syntax java"><div class="highlight"><pre><span></span><span class="nd">@DbPartition</span><span class="o">(</span><span class="nx">mode</span> <span class="o">=</span> <span class="nx">DAY</span><span class="o">,</span> <span class="nx">property</span> <span class="o">=</span> <span class="s">"eventTime"</span><span class="o">)</span> <span class="nd">@Entity</span> <span class="nd">@Table</span><span class="o">(</span><span class="nx">name</span> <span class="o">=</span> <span class="s">"event"</span><span class="o">)</span> <span class="kd">public</span> <span class="kd">class</span> <span class="nc">DEvent</span> <span class="kd">extends</span> <span class="n">BaseDomain</span> <span class="o">{</span> <span class="o">...</span> </pre></div> </div> <h2 id="extensions">Extensions</h2> <p> We may want to use extensions like <code>hstore</code> and <code>pgcrypto</code>. Note that when using ebean-test with docker it will automatically add those 2 extensions by default. </p> <p> We can specify extensions that should be installed automatically via: </p> <div class="syntax yml"><div class="highlight"><pre><span></span><span class="nt">ebean</span><span class="p">:</span> <span class="nt">test</span><span class="p">:</span> <span class="nt">platform</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">postgres</span> <span class="c1">#, h2, postgres, mysql, oracle, sqlserver</span> <span class="nt">ddlMode</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">dropCreate</span> <span class="c1"># none | dropCreate | migrations | create</span> <span class="nt">dbName</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">myapp</span> <span class="nt">postgres</span><span class="p">:</span> <span class="nt">extensions</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">pgcrypto, hstore</span> </pre></div> </div> <h2 id="schema">Schema</h2> <p> With Postgres it could be considered good practice to create our tables into a named schema (and not the public schema). If we want to do this we have made this easy in Ebean version 11.18.2 where we can specify <code>ebean.dbSchema</code> and this is then used for both DB migrations and create-all.sql. </p> <p> It is advisable that the DB User matches the DB Schema. When this is done then there is no need to use <code>currentSchema</code> or modify the <code>search path</code>. For example, if I want to use a schema called <code>myapp</code> it is advisable to have the DB user/role match and be <code>myapp</code>. </p> <h5>application.yaml</h5> <div class="syntax yml"><div class="highlight"><pre><span></span><span class="nt">ebean</span><span class="p">:</span> <span class="nt">dbSchema</span><span class="p">:</span> <span class="l l-Scalar l-Scalar-Plain">myapp</span> <span class="c1">## use this database schema</span> </pre></div> </div> <h5>DatabaseConfig</h5> <div class="syntax java"><div class="highlight"><pre><span></span><span class="n">databaseConfig</span><span class="o">.</span><span class="na">setDbSchema</span><span class="o">(</span><span class="s">"myapp"</span><span class="o">);</span> </pre></div> </div> <h2 id="starting">Docker container</h2> <p> We can programmatically start a docker container version of Postgres. This can be a useful way to run an application locally. </p> <p> The below uses <code>ebean-test-docker</code> dependency which already comes with <code>ebean-test</code>. If we do not have a dependency on <code>ebean-test</code> then add <code>io.ebean:ebean-test-docker:5.0</code> as a dependency. </p> <div class="syntax java"><div class="highlight"><pre><span></span><span class="kn">package</span> <span class="nn">main</span><span class="o">;</span> <span class="kn">import</span> <span class="nn">io.ebean.docker.commands.PostgresContainer</span><span class="o">;</span> <span class="kd">public</span> <span class="kd">class</span> <span class="nc">StartPostgres</span> <span class="o">{</span> <span class="kd">public</span> <span class="kd">static</span> <span class="kt">void</span> <span class="nf">main</span><span class="o">(</span><span class="n">String</span><span class="o">[]</span> <span class="n">args</span><span class="o">)</span> <span class="o">{</span> <span class="n">PostgresContainer</span> <span class="n">container</span> <span class="o">=</span> <span class="n">PostgresContainer</span><span class="o">.</span><span class="na">newBuilder</span><span class="o">(</span><span class="s">"14"</span><span class="o">)</span> <span class="c1">// .port(5432) // Note: defaults to 6432</span> <span class="o">.</span><span class="na">dbName</span><span class="o">(</span><span class="s">"my_app"</span><span class="o">)</span> <span class="o">.</span><span class="na">user</span><span class="o">(</span><span class="s">"my_app"</span><span class="o">)</span> <span class="o">.</span><span class="na">password</span><span class="o">(</span><span class="s">"silly"</span><span class="o">)</span> <span class="o">.</span><span class="na">containerName</span><span class="o">(</span><span class="s">"pg14x"</span><span class="o">)</span> <span class="o">.</span><span class="na">extensions</span><span class="o">(</span><span class="s">"hstore,pgcrypto"</span><span class="o">)</span> <span class="o">.</span><span class="na">build</span><span class="o">();</span> <span class="n">container</span><span class="o">.</span><span class="na">start</span><span class="o">();</span> <span class="o">}</span> <span class="o">}</span> </pre></div> </div> <p> The above will programmatically start a Postgres 13 docker container on <b>port 6432</b>. It will create a database and user with container name <em>pg13x</em>. It will drop and re-create the container if the container already exists. </p> <h3>Docker trace logging</h3> <p> Set the logging level for <code>io.ebean.docker</code> to <code>trace</code> to help trouble shoot any issues or understand what ebean-test-docker is doing. </p> <h2 id="postgis">PostGIS</h2> <p> To use PostGIS goto the <a href="postgis">PostGIS documentation</a>. </p> <nav class="next"> <p class="edit-page"> <a href="https://github.com/ebean-orm/website-source/blob/master/docs/database/postgres/index.html"><i class="fab fa-github"></i> Edit Page</a> </p> <p class="next"> <a href="/docs/database/mysql" class="btn btn-info">Next: MySql</a> </p> </nav> </article> </div> </div> <script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script> <script src="/js/site3.js"></script> <script src="/js/search3.js"></script> <script async src="https://www.googletagmanager.com/gtag/js?id=UA-75181644-1"></script> <script> window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'UA-75181644-1'); </script> </body> </html>