¡Bienvenido!

Funciones para manipular datos en PostgreSQL

Brian Piccolo

Sr. Director, Digital Strategy

La base de datos Sakila

Altamente normalizada
Tipos de datos representativos
Funciones personalizadas

Temas

Tipos comunes en PostgreSQL
Funciones y operadores de fecha y hora
Análisis y manipulación de texto
Búsqueda de texto completo y extensiones de PostgreSQL

Tipos de datos comunes

Tipos de texto
- CHAR, VARCHAR y TEXT
Tipos numéricos
- INT y DECIMAL
Tipos de fecha y hora
- DATE, TIME, TIMESTAMP, INTERVAL
Arrays

Tipos de texto

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       |
+-----------------------------------------+

Tipos numéricos

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   |
+--------+

Detectar tipos desde tablas existentes

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...} |
+---------------+------------------+------------------------------+

Detectar tipos desde tablas existentes

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             |
+------------------+-------------------+

¡Vamos a practicar!

Funciones para manipular datos en PostgreSQL