CREATE DATABASE

ADVERTISEMENT

--CREATE DATABASE

--STEP 1: Connect to PostgreSQL on Amazon RDS using psql from EC2
psql --host=connection endpoint/connection string --port=5432 --dbname=postgres --username=postgres

-- STEP 2: Basic CREATE DATABASE command
CREATE DATABASE sales;

-- This creates a new database named 'sales' with default settings.
-- The default template is 'template1', default encoding is UTF8,
-- and the owner will be the current user (here, 'postgres').

-- STEP 3: CREATE DATABASE with full options
CREATE DATABASE sampledb
WITH
   OWNER = postgres              -- (Optional) Specifies the database owner
   TEMPLATE = template1          -- (Optional) Clone this template DB (default: template1)
   ENCODING = 'UTF8'             -- (Optional) Set character encoding (common: UTF8)
   LC_COLLATE = 'en_US.UTF-8'    -- (Optional) Set string sorting rules (collation)
   LC_CTYPE = 'en_US.UTF-8'      -- (Optional) Set character classification rules
   TABLESPACE = pg_default       -- (Optional) Define where data is stored
   ALLOW_CONNECTIONS = true      -- (Optional) Allow or disallow connections to this DB
   CONNECTION LIMIT = 50         -- (Optional) Max number of concurrent connections
   IS_TEMPLATE = false;          -- (Optional) If true, this DB can be used as template

-- Let's break each parameter :
-- OWNER: Assigns which PostgreSQL role will own this database
-- TEMPLATE: Clones from an existing database structure (usually template1)
-- ENCODING: Defines character set; UTF8 is the default and most used
-- LC_COLLATE: Affects ORDER BY behavior in queries (e.g., sorting language)
-- LC_CTYPE: Affects character classification (like upper/lower case detection)
-- TABLESPACE: Specifies storage location (pg_default or a custom tablespace)
-- ALLOW_CONNECTIONS: If false, no users can connect (useful for maintenance)
-- CONNECTION LIMIT: Use -1 for unlimited connections (default), or specify a limit
-- IS_TEMPLATE: If true, this DB can be cloned via TEMPLATE by other users


-- STEP 4: Create a simplified custom DB using only a few parameters
CREATE DATABASE hr
WITH
   ENCODING = 'UTF8'
   CONNECTION LIMIT = 100;

-- Common use case: Set encoding and restrict user connections

-- STEP 5: List all databases (works on Linux too)
-- Output includes name, owner, encoding, collation, ctype, and access privileges
SELECT datname, datdba::regrole AS owner, encoding, datcollate, datctype
FROM pg_database
WHERE datistemplate = false;

-- STEP 6: List databases using SQL (useful in scripts)
SELECT datname FROM pg_database;

-- STEP 7: Check your current connected user
SELECT current_user;

-- STEP 8: Exit psql terminal
exit

ADVERTISEMENT