query

getting started

## switch and conenct
sudo -u postgres psql

## list all database
\l

## show system status
\conninfo

## show current user permissions
\du

## show all tables in db
\dt

## list table schema
\d <table_name>
\d+ <table_name>

## show all tables globally
\dt *.*

## list functions
\df

## connect to the database named postgres
\c postgres

## disconnect
\q
\!

psql commands

psql -d mydb

psql -U john mydb

psql -h localhost -p 5432 mydb

psql -U admin -h 192.168.0.0 -p 2506 -d mydb

## password
psql -W mydb

## execute a sql query or command
psql -c '\c postgres' -c '\dt'

## generate html report
psql -c "\l+" -H postgres > database.html

psql -l

psql mydb -f file.sql

psql -V

postgres working

-- show version
show SERVER_VERSION;

-- show environmental variables
show all

-- list users
select rolename from pg_roles

-- show current user
select current_user

-- show current database
select current_database()

-- create database
create database <mydb> with owner <abc>

-- drop database
drop database if exists <mydb>

-- rename database
alter database <old_name> rename to <new_name>

-- list tables in current db
select table_schema, table_name from information_schema.tables 

-- list tables globally
select * from pg_catalog.pg_tables

-- list table schema
select column_name, data_type, character_maximim_length from information_schema.columns where table_name = <table_name>

-- create table
create table <table_name> (
    <column_name> <column_type>,
    <column_name> <column_type>,
)

-- create table with an autoincrement primary key
create table <table_name> (
    <column_name> SERIAL PRIMARY KEY,
    <column_name> <column_type>
)

-- delete table
drop table if exists <table_name> CASCADE;