SQL

I. Comment supprimer les doublons d’une table

La table prise pour exemple sera la table elu composées des champs suivants :

  • elu_id (primary key)
  • elu_nom
  • elu_prenom
  • elu_date_naissance
  • elu_lieu_naissance
  • elu_genre

Nous pouvons identifier les doublons comme suit :

SELECT elu_nom, elu_prenom, elu_date_naissance, elu_lieu_naissance, elu_genre, COUNT(*) FROM elu 
GROUP BY elu_nom, elu_prenom, elu_date_naissance, elu_lieu_naissance, elu_genre 
HAVING COUNT(*) > 1;
Note

3 méthodes possibles pour supprimer les doublons

  1. suppression depuis un INNER JOIN (USING avec postgresql)

DELETE FROM elu e1
USING elu e2
WHERE e1.elu_nom = e2.elu_nom
  AND e1.elu_prenom = e2.elu_prenom
  AND e1.elu_date_naissance = e2.elu_date_naissance
  AND e1.elu_lieu_naissance = e2.elu_lieu_naissance
  AND e1.elu_genre = e2.elu_genre 
  AND e1.elu_id < e2.elu_id;
Important

Cette méthode ne marche pas s’il n’existe pas d’index (comme elu_id ici)

  1. La deuxième méthode consiste à
- recréer une table identique
CREATE TABLE IF NOT EXISTS new_elu (
  elu_id SERIAL PRIMARY KEY,
  elu_nom VARCHAR(50) NOT NULL,
  elu_prenom VARCHAR(50) NOT NULL,
  elu_date_naissance DATE NOT NULL,
  elu_lieu_naissance VARCHAR(50),
  elu_genre genre NOT NULL
  );
- copier une version unique de chaque enregistrement

  a. première méthode
INSERT INTO new_elu (elu_nom, elu_prenom, elu_date_naissance, elu_lieu_naissance, elu_genre)
SELECT elu_nom, elu_prenom, elu_date_naissance, elu_lieu_naissance, elu_genre 
FROM elu 
GROUP BY elu_nom, elu_prenom, elu_date_naissance, elu_lieu_naissance, elu_genre;
  b. deuxième méthode avec DISTINCT
INSERT INTO new_elu (elu_nom, elu_prenom, elu_date_naissance, elu_lieu_naissance, elu_genre)
SELECT DISTINCT elu_nom, elu_prenom, elu_date_naissance, elu_lieu_naissance, elu_genre
FROM elu;
- supprimer la table initiale
DROP table elu;
  • renommer la copie
ALTER table new_elu
RENAME TO elu;
  1. la methode consiste à utiliser la fonction ROW_NUMBER()
DELETE FROM elu
WHERE elu_id IN (
  SELECT elu_id
  FROM (
    SELECT elu_id,
    ROW_NUMBER() OVER(PARTITION BY elu_nom, elu_prenom, elu_date_naissance, elu_lieu_naissance, elu_genre) AS rownum
    FROM elu
  ) AS sub
  WHERE rownum > 1
);

II. PostgreSQL interesting commands

  • REASSIGN OWNED BY <old_role> to <new_role>;

  • REVOKE ALL PRIVILEGES ON DATABASE <database> FROM <role>;

III. Tests de connexion à une base de données

library(RPostgres)
library(tibble)

DB_HOSTNAME <- Sys.getenv("DB_HOSTNAME")
DB_NAME <- Sys.getenv("DB_NAME")
DB_PORT <- Sys.getenv("DB_PORT")
DB_USER <- Sys.getenv("DB_USER")
DB_PASSWORD <- Sys.getenv("DB_PASSWORD")

conn <- RPostgres::dbConnect(
  drv = RPostgres::Postgres(), 
  dbname = DB_NAME,
  host = DB_HOSTNAME,
  port = DB_PORT,
  user = DB_USER,
  password = DB_PASSWORD)

RPostgres::dbSendQuery(
  conn,
  "ALTER DATABASE alcor SET SEARCH_PATH TO rne
  ;"
)
<PqResult>
  SQL  ALTER DATABASE alcor SET SEARCH_PATH TO rne
  ;
  ROWS Fetched: 0 [complete]
       Changed: 0
RPostgres::dbListTables(conn)
Warning: Closing open result set, cancelling previous query
[1] "csp" "elu"
result <- RPostgres::dbGetQuery(
  conn, 
  "SELECT elu_genre, elu_nom, elu_prenom, UPPER(elu_lieu_naissance) 
  FROM rne.elu 
  WHERE elu_date_naissance = '1967-04-19' 
  ORDER BY elu_genre, elu_nom, elu_prenom;")

print(result)
   elu_genre       elu_nom     elu_prenom              upper
1          F       BEUGNET        Ginette              ARRAS
2          F         BOYER       Patricia               AGEN
3          F       BRUNEAU      Catherine              PARIS
4          F       CARISEY        Valérie      SAINT-VALLIER
5          F     DELAURIER  Sylvie Nicole           TONNEINS
6          F       GARNIER        Corinne    LE PUY EN VELAY
7          F     LABRUGERE        Martine             CANNES
8          F      LACRESSE     Christelle              NANCY
9          F        LAVEST       Isabelle           AURILLAC
10         F      LEPOUTRE     Anne-Marie              REIMS
11         F      LIGAUDAN Laure Isabelle      MAISON-ALFORT
12         F       MAILLOT         Karine             DRANCY
13         F         PAOLI         Muriel               ALES
14         F       PFISTER        Monique          INGWILLER
15         F    QUATREVAUX         Muriel             RENNES
16         F        SCHULD        Adeline          PARIS 17E
17         F         TALVA          Nelly           FOUGÈRES
18         M      BARDELLE     Christophe               CAEN
19         M     BERENGUER          Hervé            BÉZIERS
20         M      BERNADET         Pascal                DAX
21         M       BERRARD           Eric    LONS LE SAUNIER
22         M        BOIRET          Alain         CHÂTEAUDUN
23         M         BORDE       Philippe       BAR SUR AUBE
24         M    BUCHHOLZER         Fabien             BITCHE
25         M       CARTIER       Frédéric             LANGON
26         M     CERQUEIRA      Christian              MACON
27         M DEVALQUENAIRE           Eric             AMIENS
28         M        FUCHET       François         LE CREUSOT
29         M       GRASSET        Laurent              NIMES
30         M          GROS          Serge       LA BOISSIERE
31         M     JEANDARME        Francis   NOGENT SUR SEINE
32         M       JEANNEY        Patrick               AGEN
33         M      LE QUÉRÉ        Antoine            PONTIVY
34         M          LEVY          Hervé           BESANCON
35         M        MANUEL        Thierry               <NA>
36         M        MAYEUX        Laurent       BETHUNE (62)
37         M         PINEL       Emmanuel LES GRANDES VENTES
38         M     PRODHOMME         Daniel  NOYAL SUR VILAINE
39         M        TRAORE            Yan            ABIDJAN
40         M      VALADIER           Eddy  SAINT GILLES (30)