Check which version you are running:
$ psql --version
psql (PostgreSQL) 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1)
Login to postgreSQL
sudo su - postgres
postgres@inspiron-3542:~$ psql
postgres=#
postgres=#\connect dbname
To create table and user
CREATE DATABASE mydbname;
CREATE DATABASE myuser;
CREATE USER myuser WITH PASSWORD 'myuser';
ALTER ROLE myuser SET client_encoding TO 'utf8';
ALTER ROLE myuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE myuser SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE mydbname TO myuser;
To exit from command prompt
databasename-# \q
postgres@ubuntu:~$ exit
logout
username@ubuntu:~$
Creating new user.
CREATE USER davide WITH PASSWORD 'jw8s0F4';
further details here
Change user password
ALTER USER admin WITH PASSWORD 'admin';
making a user as superuser
ALTER USER username WITH SUPERUSER;
further details here
check the list of users:
select u.usename,
(select string_agg(d.datname, ',' order by d.datname)
from pg_database d
where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_databases
from pg_user u
order by u.usename
Creating a table
CREATE TABLE equity (
symbol character(20),
timestamp TIMESTAMP,
open numeric,
high numeric,
low numeric,
close numeric,
volume INT,
average numeric,
barCount numeric
--UNIQUE (symbol, timestamp)
);
Alter table and add column
ALTER TABLE public.nseequitydata
ADD COLUMN splitfactor bigint NOT NULL DEFAULT 1;
Important commands
Activity | Command |
Show all users | postgres=# \du |
Show all databases | postgres=# \l |
Connect to database | postgres=# \connect equityindia |
Show tables | equityindia=# \dt |
Show tables details | equityindia=# \d |
Show to remote postgresql database | psql -h host -d database -U user -W |
Show views | equityindia=# \dv |
Show functions | equityindia=# \df |
Command History | equityindia=# \s |
check the size of tables
SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a;