postgres-# ( terminal commands) - list all databases : \l - list of all tables : \d - list only the tables : \dt - list all fields in a table : \d person (name of the table) - import from external file : \i c:/postgrescourse/person.sql Ex. \i /home/forge/forge33-5-16-23.sql - connect to database psql and import .sql file > psql -h localhost -U postgres -d forgeprod -f C:\forgeprod.sql https://kb.objectrocket.com/postgresql/how-to-run-an-sql-file-in-postgres-846 - access to database to query > psql -U postgres -d forgeprod - datatypes info: bigserial : increment int in sequence date : year,month,day - create database : create database test; - drop database : drop database test; - drop table : drop table person; - connect to a database ( two forms ) : \c test (or) -h localhost -p 5432 -U postgres test - create table sintax : create table table_name ( column name + data type + constraints if any ) - create table : create table person ( id int, first_name varchar(50), last_name varchar(50), gender varchar(6), date_of_birth date ); - create table w/constraints ( need to satisfied rules): create table person ( id bigserial not null primary key, first_name varchar(50) not null, last_name varchar(50) not null, gender varchar(6) not null, date_of_birth date not null ); - create table : insert into person ( first_name, last_name, gender, date_of_birth) values ('Anne','Smith','Female',date '1988-01-09'); - Using OFFSET and LIMIT : select * from person OFFSET 5 LIMIT 5; - Using OFFSET and FETCH : select * from person OFFSET 5 FETCH 5 ROW ONLY; - Using BETWEEN : select * from person where date_of_birth BETWEEN DATE '2000-01-01' and '2015-01-01'; - Diferences between LIKE and ILIKE : ILIKE is case insensitive. - Using Group By : select country_of_birth, count(*) from person group by country_of_birth; ( will count how many people are from that country ) - Using Having with Group By : select country_of_birth, count(*) from person having count(*) > 5 group by country_of_birth; ( must have above 5 to show )
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter