PostgreSQL DDL Operations: Difference between revisions
Jump to navigation
Jump to search
Line 44: | Line 44: | ||
DROP DATABASE <''dbname''>; | DROP DATABASE <''dbname''>; | ||
This command will not work if there are users connected to the database: | |||
ERROR: database "..." is being accessed by other users | |||
DETAIL: There are 10 other sessions using the database. | |||
The database can though be forcefully dropped as follows: | |||
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<''db-name''>'; | |||
=Users= | =Users= |
Revision as of 19:04, 14 December 2018
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>;
This command will not work if there are users connected to the database:
ERROR: database "..." is being accessed by other users DETAIL: There are 10 other sessions using the database.
The database can though be forcefully dropped as follows:
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<db-name>';
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.