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 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
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 DATABASEcan 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 database
ALTER DATABAE blog RENAME TO my_new_blog;
will rename the existing database
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.