Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 125 ยท Getting Started

Creating, Selecting and Dropping Databases

Learn how to create, select, list, rename and drop databases in PostgreSQL, MySQL and SQL Server, how schemas differ, and how SQLite uses ATTACH and DETACH.

Tables don't float around on their own. They live inside a database, and before you create your first table on a real server, someone has to create the database that holds it. On a database server, that's one statement: CREATE DATABASE shop;. Removing it again is just as short, which is exactly why you need to know what it does.

This lesson covers the life cycle of a whole database: creating one, choosing which database your session works in, listing what exists, renaming and dropping, and how databases relate to schemas, a word that means different things in different products. These statements differ more between dialects than almost anything else in SQL, so the server examples are read-only and labeled. SQLite works differently again (a database is a file), and its version of the same ideas, ATTACH and DETACH, runs right here in your browser.

Servers, databases, schemas and tables

Client/server databases organize objects in layers. One running server (PostgreSQL calls it a cluster, SQL Server an instance) holds several databases. Inside a database, objects may be grouped further into schemas, and tables live in those:

TEXT
server (instance / cluster)
โ””โ”€โ”€ database: shop
    โ”œโ”€โ”€ schema: public (PostgreSQL) / dbo (SQL Server)
    โ”‚   โ”œโ”€โ”€ table: customers
    โ”‚   โ””โ”€โ”€ table: orders
    โ””โ”€โ”€ schema: reporting
        โ””โ”€โ”€ view: monthly_sales

Not every product uses every layer, and the names don't line up:

Product What a "database" is What a "schema" is Full table name
PostgreSQL A separate, isolated container on the server A namespace inside a database (public by default) shop.public.orders (but queries can't cross databases)
SQL Server A container with its own data and log files A namespace inside a database (dbo by default) shop.dbo.orders (cross-database queries allowed)
MySQL / MariaDB A namespace on the server Exactly the same thing: CREATE SCHEMA = CREATE DATABASE shop.orders
Oracle Usually one database (or a pluggable database) per application Each user owns a schema with the same name shop_owner.orders
SQLite One file Each attached file gets a schema name (main, temp, ...) main.orders

The practical rule: in PostgreSQL and SQL Server, use one database per application and schemas to group objects inside it. In MySQL, the two words mean the same thing. The Databases and Tables lesson introduced the idea of a schema as the database's blueprint; here, "schema" is the namespace sense of the word.

CREATE DATABASE

The basic statement is the same in PostgreSQL, MySQL and SQL Server:

SQL
CREATE DATABASE shop;

You need permission to do this. On a fresh install, the administrator account (postgres, root, sa) has it; application accounts usually shouldn't (see Users and Permissions). The real differences are in the options, which mostly control text encoding, sorting rules and where the files go (read-only):

SQL
-- PostgreSQL: UTF-8 text, a sort order (collation) and an owner role
CREATE DATABASE shop
  WITH OWNER = shop_owner
       ENCODING = 'UTF8'
       LC_COLLATE = 'en_US.UTF-8'
       LC_CTYPE = 'en_US.UTF-8'
       TEMPLATE = template0;

-- MySQL / MariaDB: always ask for utf8mb4 (full Unicode, including emoji)
CREATE DATABASE IF NOT EXISTS shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- SQL Server: optional file placement and a default collation
CREATE DATABASE shop
  COLLATE Latin1_General_100_CI_AS_SC_UTF8;

A few things to notice:

  • Encoding matters and is hard to change later. In MySQL, the old utf8 character set stores at most 3 bytes per character and can't hold emoji; utf8mb4 is the one you want (it's the default since MySQL 8.0). PostgreSQL's UTF8 is complete.
  • Collation decides comparisons and sorting. ai_ci in MySQL means accent-insensitive and case-insensitive, so 'resume' = 'Rรฉsumรฉ' is true. The MySQL lesson covers this in depth.
  • IF NOT EXISTS is supported by MySQL and MariaDB. PostgreSQL has no IF NOT EXISTS for databases (the statement simply fails if the name is taken), and SQL Server uses a check such as IF DB_ID('shop') IS NULL CREATE DATABASE shop;.
  • PostgreSQL copies a template. Every new database is a copy of template1 (or the TEMPLATE you name). Anything you add to template1, such as an extension, appears in every database created afterward.
  • CREATE DATABASE can't run inside a transaction in PostgreSQL or SQL Server, so you can't roll it back. Plan it like any other DDL.

Managed cloud services usually create the first database for you, and the command-line tools have shortcuts: createdb shop (PostgreSQL) or mysqladmin create shop (MySQL).

Selecting a database to work in

A connection always works in one current database. Unqualified table names, like orders, are looked up there. How you pick it depends on the product:

SQL
-- MySQL / MariaDB and SQL Server: switch inside the session
USE shop;

-- MySQL: which database is current?
SELECT DATABASE();

-- SQL Server
SELECT DB_NAME();
TEXT
-- PostgreSQL: the database is part of the connection, so you reconnect
$ psql -d shop            -- connect straight to shop
postgres=# \c shop        -- psql: open a new connection to shop
shop=# SELECT current_database();

PostgreSQL has no USE. A connection is tied to its database for its whole life, and a query can't read tables in another database directly (extensions such as postgres_fdw exist for that). This is one reason PostgreSQL users group objects with schemas instead: you can query across schemas freely.

In application code, you normally don't call USE at all. The database name is part of the connection string, for example postgresql://[email protected]:5432/shop (see Connecting to Databases from Code).

Listing databases

Every server can tell you which databases exist (read-only):

SQL
-- MySQL / MariaDB
SHOW DATABASES;

-- PostgreSQL (psql also has the shortcut \l)
SELECT datname FROM pg_database WHERE NOT datistemplate;

-- SQL Server
SELECT name, create_date FROM sys.databases;

Expect a few you didn't create: postgres, template0 and template1 in PostgreSQL; mysql, information_schema, performance_schema and sys in MySQL; master, model, msdb and tempdb in SQL Server. Those are system databases. Never drop or modify them.

SQLite's equivalent is PRAGMA database_list, which lists the files the current connection has open:

SQL Listing the open databases

Press Run (or Ctrl + Enter) to see the result here.

The first result shows one row: main, the database you opened. After ATTACH, a second row appears for archive with its file name. (The runner's file names, like /dbfile_..., are temporary files that disappear after each run.)

SQLite: one file per database, ATTACH and DETACH

SQLite has no server and no CREATE DATABASE statement. A database is a single file, and you "create" it by opening a file name that doesn't exist yet, for example sqlite3 shop.db in the command-line shell or sqlite3.connect('shop.db') in Python. To work with several databases in one connection, you attach them:

SQL
ATTACH DATABASE 'file-name.db' AS schema_name;   -- the word DATABASE is optional
DETACH DATABASE schema_name;

Attaching a file that doesn't exist creates it, empty. ATTACH ':memory:' AS scratch attaches a temporary in-memory database instead. After attaching, you refer to tables as schema_name.table, and you can join tables from different files in one query.

A classic use is archiving: moving old rows out of the live database into a separate file. Here the cancelled orders of the shop go into archive.db:

SQL Archiving rows into an attached database ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The CREATE TABLE ... AS SELECT copies 17 cancelled orders into the new file, the DELETE removes the same 17 from main, and the counts show 103 live and 17 archived orders. The join in the middle reads customers from one file and archived orders from another, as if they were one database. In real code, wrap the copy and the delete in one transaction, which SQLite can do across attached files.

How SQLite finds an unqualified name

When you write a table name without a schema, SQLite searches temp first, then main, then attached databases in the order you attached them. The temp schema always exists and holds anything created with CREATE TEMP TABLE. That means a temporary table can hide a permanent one with the same name:

SQL temp, main and attached schemas

Press Run (or Ctrl + Enter) to see the result here.

The second INSERT went into the temporary table, so the first SELECT returns temp copy, while main.notes still holds main copy. The empty settings result shows that an unqualified name falls through to attached databases. When more than one database is open, qualifying names (main.notes) is the safe habit.

Detaching

DETACH closes an attached file. It doesn't delete anything: the file stays on disk with all its tables, ready to attach again. Two things can't be detached, and this example fails on purpose to show the first:

SQL What DETACH refuses to do

Press Run (or Ctrl + Enter) to see the result here.

The first DETACH succeeds, PRAGMA database_list is back to just main, and the last line stops with Error: cannot detach database main. The same is true for temp. Attaching a second database under a name that's already in use fails too, with Error: database scratch is already in use. SQLite also limits how many databases one connection can attach (10 by default, and in this browser runner).

To truly drop an SQLite database, you close every connection to it and delete the file (plus any -wal and -shm files next to it) with your operating system or program.

Schemas inside a database

In PostgreSQL and SQL Server, schemas let you organize one database into areas, and control permissions per area. These statements are read-only here, since SQLite schemas come only from attached files:

SQL
-- PostgreSQL
CREATE SCHEMA reporting;
CREATE TABLE reporting.daily_sales (day date PRIMARY KEY, total numeric(12, 2));

-- Which schemas are searched for unqualified names, and in what order?
SHOW search_path;                       -- "$user", public
SET search_path = reporting, public;

DROP SCHEMA reporting;                  -- fails if it still contains objects
DROP SCHEMA reporting CASCADE;          -- drops everything inside it too

-- SQL Server
CREATE SCHEMA reporting;
-- (a batch separator, GO, is needed after CREATE SCHEMA in scripts)
CREATE TABLE reporting.daily_sales (day date PRIMARY KEY, total decimal(12, 2));

PostgreSQL's search_path plays the same role as SQLite's temp-then-main lookup order: it decides which schema an unqualified name comes from. A common pattern is one schema per application module (billing, inventory) or per tenant, and a separate schema for reporting views that analysts may read.

Renaming and changing a database

ALTER DATABASE changes a database's properties. Renaming is the most common reason to use it, and each product does it differently (read-only):

SQL
-- PostgreSQL: nobody may be connected to it (including you)
ALTER DATABASE shop RENAME TO shop_legacy;
ALTER DATABASE shop SET timezone = 'UTC';    -- a default for new sessions

-- SQL Server
ALTER DATABASE shop MODIFY NAME = shop_legacy;

-- MySQL / MariaDB: change defaults for new tables; there is no rename
ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

MySQL has no statement to rename a database. The usual workaround is to create the new database and move every table with RENAME TABLE shop.orders TO shop_legacy.orders;, or to dump and restore. Also note that MySQL's ALTER DATABASE ... CHARACTER SET changes only the default for tables created later; existing tables keep their character set until you convert them.

DROP DATABASE

DROP DATABASE deletes a database and everything in it: every table, every row, every view and procedure. There's no undo; the only way back is a backup.

SQL
-- All three products
DROP DATABASE shop;
DROP DATABASE IF EXISTS shop_test;

-- PostgreSQL: fails while anyone is connected; version 13+ can end those sessions
DROP DATABASE shop_test WITH (FORCE);

-- SQL Server: disconnect other users first, then drop
ALTER DATABASE shop_test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE shop_test;

In PostgreSQL, you can't drop the database you're connected to, so you connect to another one (often postgres) first. MySQL drops the database immediately, even if other sessions are using it. DROP SCHEMA in MySQL is the same statement as DROP DATABASE, which surprises people coming from PostgreSQL, where dropping a schema only removes one namespace.

Check where you are before you drop. Run SELECT current_database(); (PostgreSQL) or SELECT DATABASE(); (MySQL) and read the server name in your client first. Many teams color production connections red in their database client and don't give application or developer accounts permission to drop databases at all. Make sure a recent, tested backup exists before dropping anything that matters.

Naming databases

Database names follow the same rules as other identifiers (see SQL Syntax), with a few extra points:

  • Use lowercase snake_case, such as shop or school_admin. PostgreSQL folds unquoted names to lowercase, and in MySQL each database is a directory on disk, so its names are case-sensitive on Linux but not on Windows. Lowercase avoids both problems.
  • Avoid spaces, hyphens and reserved words. my-shop or order would need quoting every time you use them.
  • Add the environment as a suffix when several copies share a server: shop_dev, shop_test, shop_staging. It makes the dangerous one easy to spot.
  • Respect the length limits: 63 bytes in PostgreSQL, 64 characters in MySQL, 128 in SQL Server.
  • SQLite file names are file system paths, so the extension is just a convention: .db, .sqlite and .sqlite3 are all common.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You connect to a PostgreSQL server and want to work in the database named shop. What do you do?

    PostgreSQL has no USE statement. A connection belongs to one database for its whole life, so you connect to the database you want.
  2. 2. In MySQL, what does CREATE SCHEMA reports; create?

    In MySQL and MariaDB, SCHEMA is a synonym for DATABASE. In PostgreSQL and SQL Server, the same statement would create a namespace inside the current database.
  3. 3. An SQLite connection has a permanent table notes and also ran CREATE TEMP TABLE notes (...). Which table does SELECT * FROM notes; read?

    Unqualified names are looked up in temp, then main, then attached databases in attach order. Write main.notes to reach the permanent table.
  4. 4. What happens to the file when you run DETACH DATABASE archive; in SQLite?

    DETACH only closes the attached database. To drop an SQLite database, you delete the file after closing every connection to it.
  5. 5. Which statement about DROP DATABASE is true?

    DROP DATABASE is permanent. PostgreSQL and SQL Server don't even allow it inside a transaction, so plan it and check your backups first.

Summary

  • A server holds many databases; PostgreSQL and SQL Server split a database further into schemas (namespaces), while in MySQL "schema" and "database" are synonyms.
  • CREATE DATABASE name works in PostgreSQL, MySQL and SQL Server; the options set the encoding (use UTF-8 / utf8mb4), collation and owner. Only MySQL supports IF NOT EXISTS for it.
  • USE name switches databases in MySQL and SQL Server. In PostgreSQL, the database is part of the connection: reconnect with \c or a different connection string.
  • List databases with SHOW DATABASES, pg_database or sys.databases; leave the system databases alone.
  • ALTER DATABASE renames (PostgreSQL, SQL Server) or changes defaults; MySQL can't rename a database.
  • DROP DATABASE permanently deletes everything inside. Check which server and database you're on, and have a tested backup.
  • In SQLite, a database is a file. ATTACH 'file.db' AS name opens another one, name.table reaches its tables, and DETACH closes it without deleting it. Unqualified names search temp, then main, then attached databases.

Next: SQL Careers and Where SQL Is Used, a short map of the jobs and industries where SQL pays off, before you dive into the Querying Data chapter.