How to change Postgresql password

We can change PostgreSQL password with ‘alter user’ command.

Syntax:

ALTER USER “user_name” WITH PASSWORD ‘new_password’;

Example:

k2info=# alter user k2admin with PASSWORD ‘password@123’;
ALTER ROLE

Create copy of a database in PostgreSQL

Correct way to copy entire database (its structure and data) to a new one in PostgreSQL.

CREATE DATABASE new_db WITH TEMPLATE original_db OWNER dbuser;

Show tables in PostgreSQL

We can get all tables of a database using following methods.

\dt –> To get user tables
\dt * –> To get all tables user tables and system tables
SELECT * FROM pg_catalog.pg_tables; –> To get all tables user tables and system tables

Switch Database in PostgreSQL

We can Switch Database in PostgreSQL using \c or \connect.

1) \c Database_Name
2) \connect Database_Name

Examples:

k2info=# \connect edb
You are now connected to database “edb” as user “enterprisedb”.
edb=# \c k2info
You are now connected to database “k2info” as user “enterprisedb”.
k2info=#

PostgreSQL Describe Table

PostgreSQL Describe Table means to get the all columns names, datatypes, size of the columns and constraints information of a table. We can get the ‘PostgreSQL Describe Table’ using \d+ table_name or describe table_name.

1) \d+ table_name
2) describe table_name

Examples

k2info=# describe emp;
Table “public.emp”
Column | Type | Modifiers
———-+—————————–+———–
empno | numeric(4,0) | not null
ename | character varying(10) |
job | character varying(9) |
mgr | numeric(4,0) |
hiredate | timestamp without time zone |
sal | numeric(7,2) |
comm | numeric(7,2) |
deptno | numeric(2,0) |
Indexes:
“pk_emp” PRIMARY KEY, btree (empno)
Foreign-key constraints:
“fk_deptno” FOREIGN KEY (deptno) REFERENCES dept(deptno)

k2info=# \d+ emp;
Table “public.emp”
Column | Type | Modifiers | Storage | Stats target | Description
———-+—————————–+———–+———-+————–+————-
empno | numeric(4,0) | not null | main | |
ename | character varying(10) | | extended | |
job | character varying(9) | | extended | |
mgr | numeric(4,0) | | main | |
hiredate | timestamp without time zone | | plain | |
sal | numeric(7,2) | | main | |
comm | numeric(7,2) | | main | |
deptno | numeric(2,0) | | main | |
Indexes:
“pk_emp” PRIMARY KEY, btree (empno)
Foreign-key constraints:
“fk_deptno” FOREIGN KEY (deptno) REFERENCES dept(deptno)

Powered by k2schools