SQL Data Definition Language for Database in PostgreSQL

As was described into my previous post SQL Commands Types cheat sheet Data Definition Language, or just DDL, is a set of commands for creating, deleting, renaming databases and tables. In this tutorial I will describe how to use DDL with databases(schemes). PostgreSQL will be used for demonstration.

How to setup PostgreSQL (and PgAdmin3) on Ubuntu please refer to this tutorial: How to install PostgreSQL on Ubuntu 16.04

CREATE database

CREATE DATABASE blog;

PostgreSQL will create a database named blog with default parameters. But we can create database with specifying one of the following parameter (or all of them):

OWNER - the user's role which will own the new database
TEMPLATE - template from which database should be created (template1 is used by default)
ENCODING - Character set encoding to use in the new database. 
TABLESPACE - tablespace's name to be associated with the new database. 
CONNECTION LIMIT - amount of concurrent connection allowed to the new database. -1 set by default (no limits)

To create database named blog with UTF8 encodding, owned by user blog_admin and with 5 allowed connections:

CREATE DATABASE blog OWNER = blog_admin ENCODING = UTF8 CONNECTION LIMIT = 5;
  • SQL standard doesn't have CREATE DATABASE statement.
  • CREATE DATABASE can not be executed inside a transaction block.

Modify the database structure

ALTER command is used for changing one of the database's attribute or for renaming database.

ALTER DATABASE blog CONNECTION LIMIT -1;

will set the unlimited connections to databaseblog.

ALTER DATABAE blog RENAME TO my_new_blog;

will rename the existing database blog to my_new_blog

ALTER command can be also used with the following commands for changing one of the database's attributes:

CONNECTION LIMIT - changes the connection limit to the specified value
RENAME TO - renames the database to the specified name
OWNER TO - sets the database's owner to the specified one.
SET TABLESPACE - changes the tablespace to the specified value
SET - adds/changes a specified runtime parameter (e.g. search path, time zone, index scan, etc)
RESET - removes the specified runtime parameter (e.g. search path, time zone, index scan, etc)

Remove a database

DROP command is used for removing/deleting the database.

DROP DATABASE blog;

will remove the database named blog. In case when specified database doesn't exist the following error will occur:

ERROR:  database "specified_database_name" does not exist

IF EXISTS parameter will prevent us from such errors.

DROP DATABASE IF EXISTS blog;

Specified database will be removed only if it exists, otherwise nothing will happen.

Alexey Bogdanov

Read more posts by this author.

Subscribe to Development notes

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!