links:
https://www.postgresql.org/docs/9.2/app-psql.html
| Command | Description |
|---|---|
| \c <your_db_name> | Change to database. |
| psql <your_db_name> | Get shell inside of your database. |
| ? | Help |
| \conninfo | Connection Info |
| \dp or \dt | List Tables |
| \du or \z | List Roles |
| \d <your_table_name> | Columns and definitions in table. |
| \db | List Global Table. |
| \pset border 0 | Changes Display Format of Queries. |
| \pset format unaligned | Removes Border Formatting. |
\set PROMPT1 '%n@%m %~%R%# '
\pset border 0
peter@localhost testdb=> \pset format unaligned
Output format is unaligned.
peter@localhost testdb=> \pset fieldsep ","
Field separator is ",".
peter@localhost testdb=> \pset tuples_only
Showing only tuples.
peter@localhost testdb=> SELECT second, first FROM my_table;
one,1
two,2
three,3
four,4
database size in memory:
https://www.a2hosting.ca/kb/developer-corner/postgresql/determining-the-size-of-postgresql-databases-and-tables
select pg_size_pretty(pg_database_size('chanpostgres'));
#or
select pg_size_pretty(pg_total_relation_size('threads'));
https://w3resource.com/PostgreSQL/postgresql-alter-table.php
Drop column
ALTER TABLE orders DROP COLUMN vendor_name;
Drop Table:
https://www.postgresql.org/docs/11/sql-droptable.html
drop table table_name;
List column names
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'some_table';
List distinct values.
select distinct resto from threads;
Rename Table:
ALTER TABLE threads RENAME TO threads1;
Rename column:
ALTER TABLE orders RENAME COLUMN city TO vendor_city;
Add PRIMARY KEY constraint:
ALTER TABLE orders ADD PRIMARY KEY (ord_no);
https://www.w3resource.com/PostgreSQL/postgresql-count-function.php
Count Duplicate Rows
SELECT COUNT( DISTINCT column) FROM table;
Count Rows in Table:
SELECT COUNT(*) FROM table;
Change Primary Key Column:
1 -- Firstly, remove PRIMARY KEY attribute of former PRIMARY KEY
ALTER TABLE <table_name> DROP CONSTRAINT <table_name>_pkey;
2 -- Then change column name of your PRIMARY KEY and PRIMARY KEY candidates properly.
ALTER TABLE <table_name> RENAME COLUMN <primary_key_candidate> TO id;
3 -- Lastly set your new PRIMARY KEY
ALTER TABLE <table_name> ADD PRIMARY KEY (id);
Delete duplicate rows:
https://www.postgresqltutorial.com/how-to-delete-duplicate-rows-in-postgresql/
delete from threads a using threads b where a.id < b.id and a.no = b.no;
Count duplicate values in column. (Same as value_counts() in pandas)
select no, count(no) from threads group by no having count(no) > 1 order by no;