PostgreSQL - Operation

Operation commands:

* \q quit
* \l list databases
* \du list users
* \dt list tables
* \d+ tablename - describe table
* \z - To view the grant table, use the following command:

Select and export to CSV:

COPY (SELECT * FROM mdl_user WHERE idnumber = '') TO '/tmp/test.csv' WITH CSV;

Create DB setting its owner:

createdb database -O owner

Create database:

CREATE DATABASE jerry;

Delete database:

dropdb database

Rename sequence:

ALTER SEQUENCE id_seq_player RENAME TO player_id_seq;

Copy table:

select * into newtable from oldtable

Select number of rows for a large table quick:

SELECT reltuples AS approximate_row_count FROM pg_class WHERE relname = 'file';

Select number of rows for a large table slow:

select (*) from file;

Select table:

SELECT * from users where userid = '111';

Select table less or equal:

select * from mdl_user where lastaccess <= '1437840728';

Count table rows:

SELECT COUNT(*) from mdl_user WHERE suspended = '1';

Set unsalted MD5 hash:

UPDATE users SET password = 'xxx' WHERE userid = '1111111';

Update table:

UPDATE mdl_user SET suspended = '0' WHERE idnumber = ('111','222','333');

Update table less or equal:

update mdl_user set password='unsaltedhash' where lastaccess <= '1437840728';

Truncate table:

TRUNCATE ONLY mdl_user;

Sources:

  • https://stackoverflow.com/questions/36361860/change-sequence-name-in-postgresql
  • https://stackoverflow.com/questions/6601978/completely-copying-a-postgres-table-with-sql
  • https://www.heatware.net/databases/postgres-tables-auto-vacuum-analyze/