Skip to main content

PostgreSQL Basic Operations

Open
Open in GitHubOpen in ChatGPTOpen in Claude

Create a new database​

CREATE DATABASE <database_name>;

Rename a database​

ALTER DATABASE <database_name> RENAME TO <new_database_name>;

Listing databases​

A single Postgres server process can manage multiple databases at the same time. You can view all of the defined databases on the server by using the \list meta-command or its shortcut \l.

\l

Switch to a specific database​

To swtich to different database in postgresql, use the \connect meta-command or its shortcut \c.

\c <database>

Listing tables​

To list all tables in your current database, use the \dt meta-command.

\dt

Listing all users​

Use \du to list all users accounts (or roles) in the connected PostgreSQL server.

\du

To get more information in all users, use \du+.

\du+

Listing users using SQL Statement​

SELECT usename AS role_name,
  CASE
     WHEN usesuper AND usecreatedb THEN
	   CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN
	    CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN
	    CAST('create database' AS pg_catalog.text)
     ELSE
	    CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

Creating user​

CREATE USER <username> WITH ENCRYPTED PASSWORD <password>;

Giving all permission to a user on a specific database​

GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <user_name>;

Changing the owner of the PostgreSQL Database​

ALTER DATABASE <database> OWNER TO <username>;

Checking active connections to postgresql server​

select pid as process_id,
       usename as username,
       datname as database_name,
       client_addr as client_address,
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;