PostgreSQL cheatsheet

06 October,2018 by Jack Vamvas

--quit from psql
\q

--version
select version();

--Create PostgreSQL database

create database MYDB owner myowner;

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

drop database MYDB;

--list PostgreSQL databases
\list
\l

--list tables
\dt

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

--create a schema
CREATE SCHEMA myschema;

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

--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 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);

--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 and

--query PostgreSQL port number

select inet_server_port();


Author: Jack Vamvas (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