PostgreSQL DDL Operations: Difference between revisions
(→initdb) |
|||
Line 37: | Line 37: | ||
{{External|https://www.postgresql.org/docs/9.5/static/app-initdb.html}} | {{External|https://www.postgresql.org/docs/9.5/static/app-initdb.html}} | ||
initdb will create a new database cluster. | <code>initdb</code> will create a new database cluster. | ||
=Database= | =Database= |
Revision as of 20:34, 6 October 2023
Internal
Providing a Password to psql in Command Line
If a database is protected by a password, psql will challenge for password interactively. That can be avoided in scripts as follows:
export PGPASSWORD=password; psql -l -h some.host -U some-user ...
Specifying SQL Commands with psql
psql ... -c "sql-command"
Example:
psql -h something.ak29cdi3ewgm.ca-central-1.rds.amazonaws.com -U infinity -d test_10 -c "GRANT ALL PRIVILEGES ON DATABASE test_10 TO infinity;"
Version
Server version:
pg_config --version
Client version:
psql --version
Also, execute:
SELECT version():
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 (enclose the database name in single quotes):
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<db-name>';
This will report a number of backends and it will take a while until all will be terminated. You may need to run it several times to insure that all backends are gone.
If there is a lot of activity on the database, you may optionally disallow new connections:
UPDATE pg_database SET datallowconn = 'false' WHERE datname = '<db-name>'; ALTER DATABASE <db-name> CONNECTION LIMIT 1;
When all backends are gone, the database can be dropped.
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>;
Alternative from command line:
psql -h something.ak29cdi3ewgm.ca-central-1.rds.amazonaws.com -U infinity -d test_10 -c "GRANT ALL PRIVILEGES ON DATABASE test_10 TO infinity;"
Delete a User
DROP USER <username>
Tablespace
List Tablespaces
psql \db
Tables
List Tables
psql \dt
SELECT * FROM pg_catalog.pg_tables;
Describe Table Structure
\d <table_name>
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.
Records
Delete Records
DELETE FROM <table-name>;