postgreSQL

Connaitre la version

---

Server version

pg_config --version

Client version

psql --version

En SQL

SELECT version();

Configuration

---

Exporter le contenu de la base (le -W force une vérification du mot de passe)

pg_dump -U utilisateur -W nomDeLaBase > nomDuFichier.sql

Charger le contenu de la base

psql -d nomDeLaBase -f nomDuFichier.sql

Cloner une base de donnée

CREATE DATABASE bdd2 TEMPLATE bdd1;

Cloner une table

CREATE TABLE table2 (LIKE table1 INCLUDING ALL);

INSERT INTO table2 SELECT * FROM table1;

Reset l'ID courant

ALTER SEQUENCE "data_id_seq" RESTART WITH 1;

Regénérer les ID

UPDATE table SET id=nextval('data_id_seq');

Changer l'encodage

SET client_encoding TO 'latin1';

Ajouter une colonne

ALTER TABLE table ADD COLUMN rs CHARACTER VARYING;

Renommer une colonne

ALTER TABLE table RENAME COLUMN rs TO rs2;

Transtypage

ALTER TABLE table ALTER COLUMN colonne TYPE smallint USING (colonne::integer);

ALTER TABLE table ALTER COLUMN colonne TYPE date USING (colonne::text::date);

Indexer une colonne

CREATE INDEX colonne_index ON table (colonne NULLS FIRST);

CREATE INDEX colonne_index ON table USING btree (colonne DESC NULLS LAST);

Ajouter une FOREIGN KEY

ALTER TABLE employee
ADD FOREIGN KEY (dno) 
REFERENCES department(dnumber);

Ajouter une CONSTRAINT

ALTER TABLE nom_table
ADD CONSTRAINT nom_table_nom_colonne_fkey
FOREIGN KEY (nom_colonne)
REFERENCES nom_table_reference(id);

Update une CONSTRAINT

ALTER TABLE nom_table
DROP CONSTRAINT nom_table_nom_colonne_fkey,
ADD CONSTRAINT nom_table_nom_colonne_fkey
FOREIGN KEY (nom_colonne)
REFERENCES nom_table_reference(id)
ON DELETE CASCADE;

COPY

Import

COPY table(colonne1, colonne2, colonne3...)
FROM 'F:\Downloads\shoot.csv' WITH DELIMITER ';' CSV HEADER QUOTE '"';

Export

COPY (
	[SELECT request]
) TO 'F:\Downloads\shoot.csv' WITH (DELIMITER ';', FORMAT CSV, HEADER TRUE, FORCE_QUOTE *, ENCODING 'ISO88591');

concat, concat_ws

SELECT concat(lower(prenom), '.', lower(nom), '@', domain, '.', groupe_domaine)
FROM table WHERE prenom IS NOT NULL AND nom IS NOT NULL

UPDATE b2b_mivane SET emailn=concat_ws(
     ';'
     , email_nominatif
     , CASE WHEN pl_email1 IN (email_nominatif) THEN NULL ELSE pl_email1 END
     , CASE WHEN pl_email2 IN (email_nominatif, pl_email1) THEN NULL ELSE pl_email2 END);

GROUP BY

SELECT colonne, COUNT(colonne)
FROM table
GROUP BY colonne
ORDER BY count(colonne) DESC

GROUP BY HAVING

SELECT COUNT(1) FROM table
WHERE colonne IN(
	SELECT colonne
	FROM table
	GROUP BY colonne
	HAVING COUNT(colonne)=1
)

INSERT INTO SELECT

INSERT INTO ref_b2b_mivane_telephones(id_ref_b2b_mivane, telephone, source)

	SELECT id, telephone, 'telephone'
	FROM ref_b2b_mivane
	WHERE telephone IS NOT NULL;

Imbrication de requêtes

SELECT SUM(CASE WHEN seqnum_1 = 1 THEN 1 ELSE 0 END) as field1, 
       SUM(CASE WHEN seqnum_2 = 1 THEN 1 ELSE 0 END) as field2, 
       SUM(CASE WHEN seqnum_3 = 1 THEN 1 ELSE 0 END) as field3 
FROM (SELECT field1, field2, field3,
             ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY field1) as seqnum_1,
             ROW_NUMBER() OVER (PARTITION BY field2 ORDER BY field2) as seqnum_2,
             ROW_NUMBER() OVER (PARTITION BY field3 ORDER BY field3) as seqnum_3
      FROM (SELECT field1, field2, field3
            FROM table1 JOIN
                 table2
                 ON table1.id = table2.idt1 
            ORDER BY table1.id ASC
            LIMIT 10000
           ) t
     ) rq;

Expression régulière

UPDATE b2b_mivane SET ca_txt=NULL WHERE ca_txt IS NOT NULL AND ca_txt ~ '^[0-9]{1,}$';

SELECT * FROM books WHERE title ~ '^\d+ ?';

Conversion de type

UPDATE b2b_mivane SET ca_int=ca_txt::bigint WHERE ca_txt IS NOT NULL AND ca_txt ~ '^[0-9]{1,}$';

SELECT * FROM books WHERE CAST(price AS TEXT) LIKE '123%';

replace, regexp_replace

UPDATE b2b_mivane SET telephone = replace(telephone, ' ', '') WHERE telephone IS NOT NULL;

UPDATE b2b_mivane SET telephone = regexp_replace(telephone, '\D+', '') WHERE telephone IS NOT NULL;

Case converting

UPDATE b2b_mivane
	SET 	colonne1 = initcap(rs),
		colonne2 = upper(bp),
		colonne3 = lower(email_generique)

Générer un nombre aléatoire

SELECT TRUNC(RANDOM() * 100)

Conditional clause in COUNT

SELECT
	COUNT(last_date_o)*100/COUNT(regionn) AS ratio,
	SUM(CASE WHEN gender='mr' THEN 1 ELSE 0 END) AS mrs,
	SUM(CASE WHEN (gender='mme' OR gender='mlle') THEN 1 ELSE 0 END) AS mmes
FROM data

SELECT
	COUNT(last_date_o)*100/COUNT(regionn) AS ratio,
	COUNT(gender='mr' or null), 
	COUNT(gender='mme' or null)
FROM data

Doublons

Compter et afficher les doublons

SELECT COUNT(*) AS nbr_doublon, email
FROM data
GROUP BY email
HAVING COUNT(*) > 1;

Dédoublonnage

DELETE FROM emails USING emails tmp WHERE emails.email = tmp.email AND emails.id < tmp.id;

DELETE FROM table
USING table tmp
WHERE
(
	table.email = tmp.email AND
	table.dirfonct = tmp.dirfonct AND
	table.sigle = tmp.sigle AND
	table.siret = tmp.siret AND
	table.dirname1 = tmp.dirname1 AND
	table.dirname2 = tmp.dirname2
)
AND table.id < tmp.id;

INTERSECT

SELECT COUNT(*)
FROM (
	SELECT siren FROM full_email_generique
		INTERSECT
	SELECT siren FROM b2b_mivane_all
) intersection;

Croisement de table

EXISTS

INSERT INTO data(email) SELECT email FROM tmp_data2 WHERE NOT EXISTS (SELECT email FROM data WHERE data.email = tmp_data2.email);

IN

INSERT INTO data(email) SELECT email FROM laurence WHERE laurence.email NOT IN (SELECT email FROM data);

EXCEPT

COPY (SELECT email FROM dany EXCEPT (SELECT email FROM data_backup)) TO '/tmp/exportcompare.csv' WITH CSV;

SELECT *
FROM table
WHERE update_date >= '2013-05-03'::date
AND update_date < ('2013-05-03'::date + '1 day'::interval);

SET datestyle = 'ISO, DMY';

SELECT substr(emailn, 1, 1)||'[...]@'||substr(split_part(emailn, '@', 2), 1, 1)||'[...].'||split_part(split_part(emailn, '@', 2), '.', (length(split_part(emailn, '@', 2)) - length(replace(split_part(emailn, '@', 2),'.',''))) + 1) AS emailn
FROM tab2_contacts WHERE emailn IS NOT NULL LIMIT 100

SELECT substring(
	split_part(emailn, '@', 2) from 0 for length(split_part(emailn, '@', 2))- length(
		split_part(
			split_part(emailn, '@', 2), '.', (
				length(
					split_part(emailn, '@', 2)
				) - length(
					replace(split_part(emailn, '@', 2),'.','')
				)
			) + 1
		)
	)
)
FROM tab2_contacts WHERE emailn IS NOT NULL LIMIT 100

SELECT split_part(
	split_part(emailn, '@', 2), '.', (
		length(
			split_part(emailn, '@', 2)
		) - length(
			replace(split_part(emailn, '@', 2),'.','')
		)
	) + 1
)
FROM tab2_contacts WHERE emailn IS NOT NULL LIMIT 100

DELETE FROM emails
WHERE (
	domain = 'neuf' 
	OR domain = 'free' 
	OR domain = 'yahoo' 
	OR domain = 'laposte' 
	OR domain = 'numericable' 
	OR domain = 'voila' 
	OR domain = 'sfr' 
	OR domain = 'aliceadsl' 
	OR domain = 'noos' 
	OR domain = 'bbox' 
	OR domain = 'club-internet' 
	OR domain = 'aol' 
	OR domain = 'gmail' 
	OR domain = 'wanadoo' 
	OR domain = 'orange' 
	OR domain = 'outlook' 
	OR domain = 'hotmail' 
	OR domain = 'bing' 
	OR domain = 'msn' 
	OR domain = '9online'
	OR domain = 'gmx'
	OR domain = 'libertysurf'
	OR domain = 'live'
	OR domain = 'caramail'
	OR domain = 'cegetel'
	OR domain = 'freesurf'
	OR domain = 'tiscali'
)

DELETE FROM tmp WHERE email !~ '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$';

DELETE FROM tmp WHERE length(split_part(email, '@', 1)) <= 3;

DELETE FROM tmp WHERE email ~ '[0-9]{4,}';