Bienvenue !
Fonctions permettant de manipuler les données dans PostgreSQL
Brian Piccolo
Sr. Director, Digital Strategy
La base de données Sakila
Très normalisé
Sujets
Types de données courants dans PostgreSQL
Types de données courants
Types de données texte
* `DATE`, `TIME`, `TIMESTAMP`, `INTERVAL`
Tableaux
Types de données texte
SELECT title
FROM film
LIMIT 5
+-------------------+
| title |
|-------------------|
| ACADEMY DINOSAUR |
| ACE GOLDFINGER |
| ADAPTATION HOLES |
| AFFAIR PREJUDICE |
| AFRICAN EGG |
+-------------------+
SELECT description
FROM film
LIMIT 2
+-----------------------------------------+
| description |
|-----------------------------------------|
| A Epic Drama of a Feminist And a Mad |
| Scientist who must Battle a Teacher in |
| The Canadian Rockies. |
| A Astounding Epistle of a Database |
| Administrator And a Explorer who |
| must Find a Car in Ancient China |
+-----------------------------------------+
Types de données numériques
SELECT
payment_id
FROM payment
LIMIT 5
+-------------+
| payment_id |
|-------------|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+-------------+
SELECT
amount
FROM payment
LIMIT 5
+--------+
| amount |
|--------|
| 2.99 |
| 0.99 |
| 5.99 |
| 0.99 |
| 9.99 |
+--------+
Détermination des types de données à partir des tables existantes
SELECT
title,
description,
special_features
FROM FILM
LIMIT 5
+---------------+------------------+------------------------------+
| title | description | special_features |
|---------------|------------------|------------------------------|
| ACADEMY D... | A Epic... | {Deleted Scenes,Behi...} |
| ACE GOLD... | A Astound.. | {Trailers,Deleted Scenes} |
| AFFAIR PR... | A Fanciful,.. | {Commentaries,Behind the...} |
+---------------+------------------+------------------------------+
Détermination des types de données à partir des tables existantes
SELECT
column_name,
data_type
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name in ('title','description','special_features')
AND table_name ='film';
+------------------+-------------------+
| column_name | data_type |
|------------------|-------------------|
| title | character varying |
| description | text |
| special_features | ARRAY |
+------------------+-------------------+
Passons à la pratique !
Fonctions permettant de manipuler les données dans PostgreSQL
