PostgreSQL cheatsheet

06 October,2018 by Rambler

--quit from psql
\q

--version
select version();

--Create PostgreSQL database

create database MYDB owner myowner;

--ALTER Database name 

ALTER DATABASE myolddbname RENAME TO new dbname;

--drop PostgreSQL database.BE CAREFUL USING THIS COMMAND - IT CANNOT BE REVERSED!! TAKE A BACKUP FIRST

drop database MYDB;

--if other user sessions are blocking DROP DATABASE use - How to kill a PostgreSQL user session connection

--list PostgreSQL databases
\list
\l

--list tables
\dt

--list all tables,views,sequences
\dtvs the_schema.*

--list all servers on Postgres

\des+

--list all extensions

\dx

--list schemas in a database with psql

\dn

--create a schema
CREATE SCHEMA myschema;

--list all schemas in a databases
select nspname from pg_catalog.pg_namespace;

--ALTER schema name 

ALTER SCHEMA name RENAME TO new_name

--drop schema

drop schema IF EXISTS my_schema

--you may get this error message - then use the CASCADE option which will drop the dependent objects

---ERROR: cannot drop schema my_schema because other objects depend on it
--DETAIL: table my_schema.mytable depends on schema my_table

drop schema IF EXISTS my_schema CASCADE

-list all tables in a particular schema
\dt public.*

--columns in a table
\d+ my_table_name

--change the schema for a table
ALTER TABLE myTable SET SCHEMA myschema;

--change the schema for a SEQUENCE
ALTER SEQUENCE mysequence SET SCHEMA schema1;

--change - alter sequence name
ALTER TABLE old_seq_name RENAME TO new_seq_name;

 

--add column to existing table
ALTER TABLE distributors ADD COLUMN address varchar(30);

--list SEQUENCES in a database 

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES;

--drop table
DROP TABLE mytable;

--change table name
ALTER TABLE mytab RENAME TO mytab_renamed;

--change table name with schema
SET search_path TO domain;
ALTER TABLE new RENAME TO old;

 

--drop multiple columns on a table
ALTER TABLE table DROP COLUMN col1, DROP COLUMN col2;

 

--execute a sql in Postgres 9
psql -h localhost -d userstoreis -U admin -p 5432 -a -q -f /home/jobs/Desktop/resources/postgresql.sql
psql -U username -d myDataBase -a -f myInsertFile
-h PostgreSQL server IP address
-d database name
-U user name
-p port which PostgreSQL server is listening on
-f path to SQL script

--use database
\c db_name

--create user and assign privileges on a database
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass';
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser;
GRANT SELECT, INSERT, UPDATE,DELETE PRIVILEGES ON DATABASE yourdbname TO youruser;

--list all users
\du;

--change user password

ALTER USER user_name WITH PASSWORD 'new_password';

--backup a Postgres database  to a tar file i.e -F t   (tar)
pg_dump -h localhost -p 1609 -U postgres -W -F  t my_db > mydb_dump_file.tar

 --restore using pg_restore , from a tar file

Option 1 - using the -C switch will create a new db

pg_restore -h localhost -p 2222 -d mydb -C -F t -v /home/postgres/myfile.tar

Option 2 - using the -c switch will clear the source tables 

--restore using Postgres database created from a pg_dump but as a sql file

psql -U username -f mybackupfile.sql

--query PostgreSQL port number

select inet_server_port();

--change owner of database

ALTER DATABASE name OWNER TO new_owner

 


Author: Rambler (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on PostgreSQL cheatsheet


dba-ninja.com