CODEWITHSUNDEEP
sqlwindowspostgresql
devoured elysium
devoured elysium

Reputation: 105227

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

Upvotes: 1

Views: 228

Answers (2)

Luca Marletta
Luca Marletta

Reputation: 457

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);

Upvotes: 1

user330315
user330315

Reputation:

After creating the user and database, you can use \connect command to change the connection to the newly created database. 

create user arthur with password 'secret';
create database arthur_db owner arthur;

\connect dbname=arthur_db user=arthur password=secret

-- create the tables and populate them
create table items
(
  id serial primary key,
  item_name text
);

insert into items (item_name) values ('Towel'), ('Peanuts');

\connect is specific to psql so the above only works with psql, not with other SQL clients.

If you have users that prefer other SQL clients, it might be a good idea to split up the creation of the database and user and the script to setup the database tables.

You can still use a single script that includes those two, so that users using psql can run a single script. Users using a different SQL client can manually run the two scripts and establish the connection themselves:

\i create_db_and_user.sql 
\connect dbname=arthur_db user=arthur password=secret
\i setup_tables.sql

Upvotes: 1

Related Questions