Skip to content

Postgres

Comandos para moverse

psql 'nombre del gestor' # Conectar al gestor de tablas (base de datos)
\l listado     # ls
\d 'tabla'     # visualiza tabla =dir
\c 'base de datos'   # cd entrar
\i  'url'      # importar
\q             # salir
\df            # ver funciones de usuario
\df+           # ver funciones de usuario mas codigo
\help          # ayuda
\h comando     # ayuda de 1 comando en concreto.
\! clear       #limpia pantalla
\copy 'tabla' to 'nombre archivo(/ruta)' csv header # exportar tabla

Querys

Select Ej

Tiene varias opciones, from, where, and, or y muchas más.

select * from tabla; nos muestra toda la tabla (muy importante ; final).

Muéstrame la columna de la tabla donde columna sea >= 50000

select columna/s from tabla where(dónde) columna >= 50000;

Mostrar filas de una tabla

select count( * ) from tabla ;

Ejemplo and y or:

select columna/s
from tabla
where ( columna condición
and columna condición )
or ( columna condición) ;

like, not like

Lo utilizamos para buscar en una columna partiendo de un carácter o varios.
Ilike es lo mismo que like pero busca el carácter tanto en mayus como minúscula

%     -- comodín vale por todos o 0 caracteres.
_     -- comodín vale por 1 carácter
'm%'  -- primera letra es m
'%m'  -- última letra es m
'%m%' -- contiene m
'_m%' -- segunda letra contiene m y la primera cualquier carácter.

ejemplos like /not like

like any, all

like any o like all sirve para comparar múltiples likes en uno solo, utilizando una array.

  • LIKE ALL : compara como and
  • LIKE ANY : compara como or

Sintaxis:

select * from tabla
where columna like any ( array['% % %' , '%.%'] ) ;

select * from tabla
   where columna like '% % %' or
            columna like '%.%' ;

select * from tabla where columna like all ( array['% % %' , '%.%'] ) ;

select * from tabla 
   where columna like '% % %' and
            columna like '%.%' ;

Similar to

Similar to permite comparar texto similar a like pero nos deja insertar la opción or | dentro de sus opciones

'abc' SIMILAR TO 'abc'      true
'abc' SIMILAR TO 'a'        false
'abc' SIMILAR TO '%(b|d)%'  true
'abc' SIMILAR TO '(b|c)%'   false

Expresiones regulares

Poder utilizar expresiones regulares

Operador Descripcion Ejemplo
~ Matches regular expression, case sensitive 'thomas' ~ '.thomas.'
~* Matches regular expression, case insensitive 'thomas' ~ '.Thomas.'
!~ Does not match regular expression, case sensitive 'thomas' !~ '.Thomas.'
!~* Does not match regular expression, case insensitive 'thomas' !~ '.vadim.'
 select * from tabla where  columna ~ '.$';

AS

As se utiliza para cambiar el nombre a una columna o tabla temporalmente (sobre un select). Si deseas que el nombre tenga espacios en blanco colocarlo entre comillas dobles "uno dos"

select oficina as ofi, 
dir as director,
( ventas *100/objetivo) as porcentaje_cumplido 
from oficinas ;

Limit, Offset

Limit limita el string final a el número que le indiques.

SELECT columnas FROM tabla LIMIT 5;

Offset especifica desde que fila empezara a mostrar el string.
Si hay en una tabla 20 filas y añades un offset 5 mostrara 15 filas empezando por la fila 6.

SELECT columnas FROM tabla OFFSET 5;

Concatenar || substring

Concatenar nos permite juntar 2 columnas o caracteres al resultado.

columna1 || columna2
columna1 || ' carácter o nada' || columna2

select columna1 ||  '-' || columna2 as alias from tabla;

Substring permite extraer caracteres de un resultado de fila.
substring ( columna from 1(comienzo) for 2(largo) )

select nombre as "Nombre",
substring (titulo from 4 for 7)||'  '||
substring (titulo from 1 for 3) as "Titulo",
contrato as "Contrato"
from repventas;

Extract

Permite extraer una sección de la casilla para mostrarla, compararla etc..
Algunos de sus campos: year, month, day, hour, minute, second …

select extract( campo from columna) from tabla;

select  nombre, contrato from repventas where extract( year from contrato) < 1988;

Distinct

Te permite reagrupar los caracteres repetidos en una columna y muestra los iguales

select distinct id_fab from productos;

Is null, Is not null

Para ver los datos nulos o no nulos de una columna.

Select columna from tabla 
   where columna is null;

Round

Redondea y elimina los números decimales.

select round( columna ) from tabla;
select round( columna, 2 ) from tabla; -- dos decimales 125.00

Order by

Ordena columnas según orden ascendente (asc) o descendente (desc)

select *
from tabla 
order by columna1 asc, columna2 asc;

Condición por fecha

select * from tabla
where columna1 >= '2011-1-1' and columna1 <='2011-12-31' ;

Current_date

Muestra los días transcurridos de una fecha.

select current_date - columnafecha from tabla;

Mostrar en años.

select ((current_date - columnafecha) /365) from tabla;

Age

Muestra el tiempo transcurrido en años meses y días desde una fecha.

select age(columnafecha) from tabla;

Coalesce, cast

Coalesce convierte un null en un número. Cast cambia el formato del caracter.

poner null un 0

select coalesce( columna, 0 ) as columna from tabla;

poner null un texto

select coalesce(cast( columna as text), 'texto-a-poner' ) as columna from tabla;

To_char

Extrae el trozo de fecha deseado 'YYYY' años, 'mm' meses, 'dd' días.

 select to_char(columna, 'YYYY') = 1990 from tabla;

( util despues de un where como condición )

Joins

Left outer join

Left outer join junta tablas de izquierda a derecha por un campo, en los campos que no coinciden se quedan en blanco. Podemos ir de izquierda a derecha con LEFT o de derecha a izquierda con RIGHT

SELECT *
FROM tabla1 LEFT OUTER JOIN tabla2 ON ( tabla1.columna = tabla2.columna)
-- A la tabla le puedes poner alias para facilitar escritura 
SELECT *
FROM pedidos ped LEFT OUTER JOIN productos prod ON (ped.fab  = prod.id_fab )
-- Ver los clientes que han hecho pedidos y los que no saldrán espacios en null.
select * 
from clientes cli LEFT OUTER JOIN pedidos ped ON (cli.num_clie = ped.clie)
order by cli.num_clie;

Inner join

Inner join nos junta las tablas y nos muestra las filas que coinciden en la columna escogida.

select *
From tabla1 INNER JOIN tabla2 ON (tabla1.columna = tabla2.columna);
--- 
SELECT *
FROM   oficinas o, repventas r
WHERE  o.dir = r.num_empl;
---
SELECT *
FROM   oficinas o INNER JOIN repventas r ON (o.dir = r.num_empl);

Auto join

Auto join es básicamente un join a una misma tabla lo único diferente a de ser que 2 de las columnas coincidan en los datos.

Se puede hacer tanto en INNER, LEFT o RIGHT

Select *
From tabla t1 LEFT OUTER JOIN tabla t2 on ( t1.columna = t2.columna )

Group by

Group by nos permite agrupar varias filas en un grupo.

Sus atributos son:

  • sum ()
  • count (*)
  • min ()
  • max()
  • avg() → recomendable round ( avg ( columna ) ,2 )

Sintaxis

Select
From
where
Group by
Order by

SELECT clie, COUNT(*),
       SUM(importe),
       ROUND(AVG(importe),2),
       MIN(importe),
       MAX(importe),
       MIN(fecha_pedido),
       MAX(fecha_pedido)
FROM      pedidos
GROUP BY  clie
ORDER BY SUM(importe) DESC;

Having

Having es el filtrado que utiliza group by, se rige a sus normas de estructura, filtra por los atributos de group by.

SELECT   columna, COUNT(*)
FROM        tabla
GROUP BY columna
HAVING   COUNT(*)>4;
( Las columnas han de coincidir, si no dará error.)

Subselects

Subselects permite agregar partes de otra tabla en un mismo select, la respuesta del subselect siempre a deser 1 opción que se comparara con cada opción de la tabla inicial.

select num_empl, nombre,
(select empresa from clientes where clientes.rep_clie=repventas.num_empl order by limite_credito desc limit 1),
(select max(limite_credito) from clientes where clientes.rep_clie=repventas.num_empl) from repventas;

num_empl |    nombre     |      empresa      |   max   
----------+---------------+-------------------+----------
     105 | Bill Adams    | Acme Mfg.         | 50000.00
     109 | Mary Jones    | Holm & Landis     | 55000.00
     102 | Sue Smith     | Fred Lewis Corp.  | 65000.00
     106 | Sam Clark     | Jones Mfg.        | 65000.00
     104 | Bob Smith     | Ian & Schmidt     | 20000.00
     101 | Dan Roberts   | First Corp.       | 65000.00
     110 | Tom Snyder    | Ace International | 35000.00
     108 | Larry Fitch   | Midwest Systems   | 60000.00
     103 | Paul Cruz     | JCP Inc.          | 50000.00
     107 | Nancy Angelli | Peter Brothers    | 40000.00
(10 rows)

All

all es equivalente a un and, tienen que darse todos los casos ciertos de los elementos del subquery.

En el ejemplo muestro las oficinas que todos sus vendedores tienen una superior al 55% del objetivo de la oficina.

select * from oficinas
where ( objetivo * 0.55 ) < all( select cuota from repventas
                                  where oficina_rep = oficina );

oficina | ciudad  | region | dir | objetivo  |  ventas  
---------+---------+--------+-----+-----------+-----------
     22 | Denver  | Oeste  | 108 | 300000.00 | 186042.00
     13 | Atlanta | Este   | 105 | 350000.00 | 367911.00
(2 filas)

Any

Any es equivalente a un or, tiene que ser cierto algún elemento de la subquery.

ejemplo: mostrar oficinas que tienen algún representante con la cuota de mas del 50% que el objetivo de su oficina.

select * from oficinas
where ( objetivo * 0.5 ) < any( select cuota from repventas where oficina_rep = oficina );

oficina |  ciudad  | region | dir | objetivo  |  ventas  
---------+----------+--------+-----+-----------+-----------
     22 | Denver   | Oeste  | 108 | 300000.00 | 186042.00
     11 | New York | Este   | 106 | 575000.00 | 692637.00
     13 | Atlanta  | Este   | 105 | 350000.00 | 367911.00
(3 filas)

otro ejemplo muy útil del any con un array

select num_empl from repventas
       where nombre = any(array['Sue Smith','Mary Jones','Bill Adams']);

 num_empl
----------
      105
      109
      102
(3 filas)

Exists

Exists es muy similar a IN, retorna true o false, si es true muestra la fila si no, no la muestra.

Se tiene que relacionar dentro de la subquery la query original con la subquery, como se ve en el ejemplo.

Ejemplo: todos los clientes que no han hecho ningún pedido.

select num_clie, empresa from clientes
where not exists(select distinct clie from pedidos where num_clie=clie);

num_clie |     empresa    
----------+-----------------
    2123 | Carter & Sons
    2115 | Smithson Corp.
    2121 | QMA Assoc.
    2122 | Three-Way Lines
    2119 | Solomon Inc.
    2105 | AAA Investments
(6 filas)

IN

In nos permite comparar valores de diferentes filas o tablas.

select producto, importe
from pedidos
where clie 
in (select num_clie from clientes where num_clie =2111);

Union

Union como su mismo nombre, une querys en una. Lo único que hay que tener en cuenta es que las 2 querys han de tener el mismo número y tipo de campos, es decir numérica con numérica, texto con texto etc..

En el caso de que un query tenga mas campos que otro, se puede poner uno adicional con NULL.

Ejemplo: en el ejemplo se ve, una query que retorna el director con el número de trabajadores que tiene, y en la otra los trabajadores por director.

select  dir.nombre as director, rep.nombre as esbirro, null as num_mascotas
from repventas rep
   join repventas dir on( rep.director=dir.num_empl)
UNION
select  dir.nombre as director, '-------', count(*) as num_mascotas
from repventas rep
   join repventas dir on( rep.director=dir.num_empl)
group by dir.num_empl
order by 1,3 desc;
 director   |    esbirro    | num_mascotas
-------------+---------------+--------------
Bob Smith   | Dan Roberts   |            
Bob Smith   | Bill Adams    |            
Bob Smith   | Paul Cruz     |            
Bob Smith   | -------       |            3
Dan Roberts | Tom Snyder    |            
Dan Roberts | -------       |            1
Larry Fitch | Nancy Angelli |            
Larry Fitch | Sue Smith     |            
Larry Fitch | -------       |            2
Sam Clark   | Mary Jones    |            
Sam Clark   | Larry Fitch   |            
Sam Clark   | Bob Smith     |            
Sam Clark   | -------       |            3

Case when else end

Case es la manera de añadir condiciones en una query, similar a un if, se pueden añadir tantas condiciones como se quieran, añadiendo when's.

SELECT nombre,
 CASE
  WHEN edad>40 THEN 'Adulto'
  ELSE 'Joven'
END
FROM repventas;

    nombre     |  case  
---------------+--------
 Bill Adams    | Joven
 Mary Jones    | Joven
 Sue Smith     | Adulto
 Sam Clark     | Adulto
 SELECT nombre, edad, ventas
FROM repventas
WHERE
CASE
    WHEN edad < 40 THEN ventas > 200000
    ELSE ventas > 300000
END;
   nombre    | edad |  ventas   
-------------+------+-----------
 Bill Adams  |   37 | 367911.00
 Mary Jones  |   31 | 392725.00
 Sue Smith   |   48 | 474050.00
 Dan Roberts |   45 | 305673.00
 Larry Fitch |   62 | 361865.00
 Paul Cruz   |   29 | 286775.00
(6 filas)

Forzar valor

valor::typo fuerzas a un valor a tener un determinado tipo .

SELECT insertar_usuarios('1'::text);