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 schemas in a database with psql
\dn
--list schemas in database with postgresql
select nspname from pg_catalog.pg_namespace;
--list tables
\dt
--list all tables,views,sequences
\dtvs the_schema.*
--list all servers on Postgres
\des+
--list all extensions
\dx
--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
--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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |