Postgresql queries

PostgreSQL.md

📗 Relational database

PostgreSQL is a free and open source database management system, that uses and extends the SQL language with others feature to store and scale data. It can help developers to build applications and protect data integrity no matter how big or small is the dataset.

Data Types

• Primitives: Integer, Numeric, String, Boolean
• Structured: Date/Time, Array, Range, UUID
• Document: JSON/JSONB, XML, Key-value (Hstore)
• Geometry: Point, Line, Circle, Polygon
• Customizations: Composite, Custom Types

Data Integrity

• UNIQUE, NOT NULL
• Primary Keys
• Foreign Keys
• Exclusion Constraints
• Explicit Locks, Advisory Locks

Security

• Authentication: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, Certificate, and more
• Robust access-control system
• Column and row-level security
• Multi-factor authentication with certificates and an additional method

💻 Documentation

🔻 CREATE TABLE will create a new, initially empty table in the current database.

CREATE TABLE public.customer (
	id serial NOT NULL,
	name varchar(255) NOT NULL,
	phone varchar(11) NOT NULL,
	email varchar(255) NOT NULL UNIQUE,
	password varchar(36) NOT NULL,
	state varchar(2) NOT NULL,
	city varchar(20) NOT NULL,
	address varchar(100) NOT NULL,
	created_date TIMESTAMP NOT NULL DEFAULT 'now()',
	updated_date TIMESTAMP,
	deleted_date TIMESTAMP,
	CONSTRAINT customer_pk PRIMARY KEY (id)
) WITH (
  OIDS=FALSE
);

🔻 INSERT inserts new rows into a table. One can insert one or more rows specified by value expressions.

INSERT INTO public.customer
	 (name, phone, email, password, state, city, address)
VALUES
	 ('Luana Lessa','88999999999','luana@gmail.com','1234','CE','Itaiçaba','Rua de alguma coisa 1233')
	,('Vanessa Maria','88999999999','maria@gmail.com','1234','CE','Itaiçaba','Rua blabla 1233')

🔻 DROP TABLE removes tables from the database

DROP TABLE customer CASCADE

CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.

🔻 TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster.

TRUNCATE customer 

🔻 SELECT and the FROM clause create a new table and fill it with data computed by a query.

SELECT name, phone, email FROM customer
	WHERE customer.name = 'Luana Lessa'

🔻 The optional WHERE clause specify a pipeline of successive transformations performed on the table derived in the FROM clause.

SELECT * FROM services
	WHERE service_group_id = 5;

SELECT * FROM customer
	WHERE state IN ('CE');
	
SELECT * FROM customer
	WHERE state NOT IN ('CE');

SELECT * FROM profissional
	WHERE name LIKE 'V%' and email LIKE 'sv%';
	
SELECT * FROM agenda
	WHERE hour BETWEEN '11:30:00' AND '12:30:00';

🔻 The optional JOIN condition determines which rows from the two source tables are considered to “match”.

SELECT A.date, A.hour, P.name  
	FROM agenda AS A
	INNER JOIN profissional AS P
	ON A.profissional_id = P.id
	WHERE P.name LIKE ('Vanessa%');

SELECT A.date, A.hour, AP.customer_id, AP.profissional_id, AP.service_id  
	FROM appointment AS AP
	LEFT JOIN agenda AS A
	ON AP.agenda_id = A.id;


SELECT A.date, A.hour, AP.customer_id, AP.profissional_id, AP.service_id  
	FROM appointment AS AP
	RIGHT JOIN agenda AS A
	ON AP.agenda_id = A.id;
	
SELECT A.date, A.hour, AP.customer_id, AP.profissional_id, AP.service_id  
	FROM appointment AS AP
	FULL OUTER JOIN agenda AS A
	ON AP.agenda_id = A.id;

🔻 The optional JOIN rows of a table

UPDATE services SET procedure = 'Limpeza de Pele simples' WHERE id = 15

🔻CREATE INDEX is used to speed up the performance of queries.

CREATE INDEX idx_customer
ON customer (name);

CREATE INDEX idx_profissional 
ON profissional (name, email);

🔻CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

CREATE VIEW profissional_appointment AS
	SELECT 
		  (SELECT profissional.name AS profissional
		 	FROM profissional
		  	WHERE profissional.id = appointment.profissional_id)
		 ,( SELECT customer.name AS customer 
 		   	FROM customer
		 	WHERE appointment.customer_id = customer.id )
 		, ( SELECT services.procedure AS service
  		   	FROM services
  		   	WHERE appointment.service_id = services.id )
  		, ( SELECT agenda.date
  		   	FROM agenda
  		   	WHERE appointment.agenda_id = agenda.id )
  		, ( SELECT agenda.hour
  		   	FROM agenda
  		   	WHERE appointment.agenda_id = agenda.id )
		, ( SELECT agenda.status
  		   	FROM agenda
  		   	WHERE appointment.agenda_id = agenda.id )
 	FROM appointment
 	WHERE appointment.profissional_id = 5 ;


SELECT * FROM profissional_appointment 

🔻GROUP BY is used to arrange identical data into groups with the help of some functions.

SELECT * FROM services 
	WHERE profissional_id = 5
	GROUP BY id
	ORDER BY procedure