devoured elysium devoured elysium - 11 days ago 7
SQL Question

Is it possible to create a single sql script to both create a database and to populate it with tables?

When using the

psql
command-line tool to create a database, what I usually do (because I've seen it previously work and I don't really know any other way of doing things) is to:


  1. Start off by creating a new user
    U
    (
    CREATE USER U WITH PASSWORD 'U'
    );

  2. Then create a database named
    U
    (
    CREATE DATABASE U
    );

  3. Quit
    psql
    and start it again with
    psql -U U

  4. Start creating tables (
    CREATE TABLE T()
    ).



The problem this imposes is that it doesn't work that well with SQL scripts. I'd prefer to just have one single .sql file where the full db creation could take place. Is this even possible?

Thanks

Answer

With a bash script like this:

#!/bin/bash
psqluser="U"   # username
psqlpass="U"   # password
psqldb="U"   # db

sudo printf "CREATE USER $psqluser WITH PASSWORD '$psqlpass';\nCREATE DATABASE $psqldb WITH OWNER $psqluser;\nGRANT ALL PRIVILEGES ON database $psqldb TO $psqluser;" > createdb.sql

sudo -u postgres psql -f createdb.sql

echo "Populating inserting.sql"
sudo -u postgres psql -d $psqldb -f inserting.sql

where inserting.sql is something like:

CREATE TABLE T (
    id serial,
    description text
);


ALTER TABLE T OWNER TO U;


INSERT INTO T(description) VALUES ('description 1');
INSERT INTO T(description) VALUES ('description 2');


ALTER TABLE ONLY T ADD CONSTRAINT id_pkey PRIMARY KEY (id);