PostgreSQL DDL Operations
Jump to navigation
Jump to search
Internal
Database Cluster
initdb
initdb will create a new database cluster.
Database
List Databases
psql -l [-h ... -U ... -d ...]
or
> \l
or
SELECT datname FROM pg_database;
Create a Database
createdb <dbname>
createdb -h localhost -U admin playground
or
CREATE DATABASE <dbname>;
Also see:
.
Connect to a Database
psql <database-name>
Drop a Database
DROP DATABASE <dbname>;
Users
The following user operations are supported.
List Users
psql \du
or
SELECT usename FROM pg_user;
Create User
Command line:
createuser <user_name>
or SQL client:
CREATE USER <user_name>;
Change Password
In psql, for the current user:
\password <user_name>
or
ALTER USER user_name WITH PASSWORD 'new_password';
Granting Privileges on a Database
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <user>;
Tablespace
List Tablespaces
psql \db
Tables
List Tables
psql \dt
Create a Table
CREATE TABLE poc_library_component ( "id" integer NOT NULL, "name" text, "desc" text, CONSTRAINT poc_library_component_pk PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); ALTER TABLE poc_library_component OWNER TO is3_as;
Note: apparently, PostgreSQL does not like uppercases in column names.