Search code examples
postgresqllimit

postgresql: create dabase by using template option with no or less data

as you know PostgreSQL does not support limit in create database. This sql creates 'bDB' by using 'aDB' with all data.

create database bDB template aDB

I only want to copy aDB table layout with a few data to bDB. I mean if aDB has 'ta' table with 100 rows(data), bDB has only 10 rows(data) in 'tb'.

Do you know any work arounds?

Solution

  • CREATE DATABASE copies the whole database indiscriminately, and there is no intention to change that. I would suggest the following:

    # create an empty database
createdb --template=template0 bdb
# dump the objects definitions and restore them to the new database
pg_dump --schema-only adb | psql -d bdb

    Copying some of the data will not be simple, because the data will probably depend on each other via foreign key constraints. You'll have to develop code of your own to do that for your database.