• Author / Uploaded
• Elena Mejía Lugo

• Categories
• Postgre Sql
• Microsoft Windows
• SQL
• Tabla (base de datos)
• Plataforma cruzada

Citation preview

http://postgresql-dbms.blogspot.mx/p/importante-sobre-el-dbms-postgresql.html

Sobre PostgreSQL Sáb, 02/10/2010 - 22:29 — rafaelma

Introducción Características Historia Ciclo de vida (EOL) y soporte

Introducción PostgreSQL es un sistema de gestión de bases de datos objetorelacional, distribuido bajo licencia BSD y con su código fuente disponible libremente. Es el sistema de gestión de bases de datos de código abierto más potente del mercado y en sus últimas versiones no tiene nada que envidiarle a otras bases de datos comerciales. PostgreSQL utiliza un modelo cliente/servidor y usa multiprocesos en vez de multihilospara garantizar la estabilidad del sistema. Un fallo en uno de los procesos no afectará el resto y el sistema continuará funcionando. A continuación teneis un gráfico que ilustra de manera general los componentes más importantes en un sistema PostgreSQL.



Aplicación cliente: Esta es la aplicación cliente que utiliza PostgreSQL como administrador de bases de datos. La conexión puede ocurrir via TCP/IP ó sockets locales.



Demonio postmaster: Este es el proceso principal de PostgreSQL. Es el encargado de escuchar por un puerto/socket por conexiones entrantes de clientes. Tambien es el encargado de crear los procesos hijos que se encargaran de autentificar estas peticiones, gestionar las consultas y mandar los resultados a las aplicaciones clientes



Ficheros de configuracion: Los 3 ficheros principales de configuración utilizados por PostgreSQL, postgresql.conf, pg_hba.conf y pg_ident.conf



Procesos hijos postgres: Procesos hijos que se encargan de autentificar a los clientes, de gestionar las consultas y mandar los resultados a las aplicaciones clientes



PostgreSQL share buffer cache: Memoria compartida usada por POstgreSQL para almacenar datos en caché.



Write-Ahead Log (WAL): Componente del sistema encargado de asegurar la integridad de los datos (recuperación de tipo REDO)



Kernel disk buffer cache: Caché de disco del sistema operativo



Disco: Disco físico donde se almacenan los datos y toda la información necesaria para que PostgreSQL funcione

Características La última serie de producción es la 9.3. Sus características técnicas la hacen una de las bases de datos más potentes y robustas del mercado. Su desarrollo comenzo hace más de 16 años, y durante este tiempo, estabilidad, potencia, robustez, facilidad de administración e implementación de estándares han sido las características que más se han tenido en cuenta durante su desarrollo. PostgreSQL funciona muy bien con grandes cantidades de datos y una alta concurrencia de usuarios accediendo a la vez a el sistema. A continuación teneis algunas de las características más importantes y soportadas por PostgreSQL:

Generales 

Es una base de datos 100% ACID



Integridad referencial



Tablespaces



Nested transactions (savepoints)



Replicación asincrónica/sincrónica / Streaming replication - Hot Standby



Two-phase commit



PITR - point in time recovery



Copias de seguridad en caliente (Online/hot backups)



Unicode



Juegos de caracteres internacionales



Regionalización por columna



Multi-Version Concurrency Control (MVCC)



Multiples métodos de autentificación



Acceso encriptado via SSL



Actualización in-situ integrada (pg_upgrade)



SE-postgres



Completa documentación



Licencia BSD



Disponible para Linux y UNIX en todas sus variantes (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) y Windows 32/64bit.

Programación / Desarrollo 

Funciones/procedimientos almacenados (stored procedures) en numerosos lenguajes de programacion, entre otros PL/pgSQL (similar al PL/SQL de oracle), PL/Perl, PL/Python y PL/Tcl



Bloques anónimos de código de procedimientos (sentencias DO)



Numerosos tipos de datos y posibilidad de definir nuevos tipos. Además de los tipos estándares en cualquier base de datos, tenemos disponibles, entre otros, tipos geométricos, de direcciones de red, de cadenas binarias, UUID, XML, matrices, etc



Soporta el almacenamiento de objetos binarios grandes (gráficos, videos, sonido, ...)



APIs para programar en C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, PHP, Lisp, Scheme, Qt y muchos otros.

SQL 

SQL92,SQL99,SQL2003,SQL2008



Llaves primarias (primary keys) y foráneas (foreign keys)



Check, Unique y Not null constraints



Restricciones de unicidad postergables (deferrable constraints)



Columnas auto-incrementales



Indices compuestos, únicos, parciales y funcionales en cualquiera de los metodos de almacenamiento disponibles, B-tree, R-tree, hash ó GiST



Sub-selects



Consultas recursivas



Funciones 'Windows'



Joins



Vistas (views)



Disparadores (triggers) comunes, por columna, condicionales.



Reglas (Rules)

 

Herencia de tablas (Inheritance) Eventos LISTEN/NOTIFY

Podeis consultar la lista completa en ingles de características disponibles en todas las versiones en la dirección http://www.postgresql.org/about/featurematrix Algunos de los limites de PostgreSQL son:

Límite

Valor

Máximo tamaño base de dato

Ilimitado (Depende de tu sistema de almacenamiento)

Máximo tamaño de tabla

32 TB

Máximo tamaño de fila

1.6 TB

Máximo tamaño de campo

1 GB

Máximo numero de filas por tabla

Ilimitado

Máximo numero de columnas por tabla

250 - 1600 (dependiendo del tipo)

Máximo numero de indices por tabla

Ilimitado

Historia El proyecto PostgreSQL tal y como lo conocemos hoy en dia empezó en 1996, aunque las bases y el trabajo en la que se asienta tienen sus comienzos en la decada de los 70. A continuación teneis una corta descripción de la historia de PostgreSQL.

Ingres 1977-1985 - "El comienzo"

La década de los 70 fue una década de desarrollos y pruebas de nuevos conceptos en el nuevo mundo de los gestores de bases de datos. IBM habia estado trabajando desde 1973 con los primeros conceptos, ideas y teorias sobre bases de datos relacionales. Su proyecto "System R" fue entre otras cosas la primera implementación del lenguaje SQL (Structured Query Language). Este proyecto, sus decisiones de diseño y muchos de los algoritmos usados, influenciaron muchos de los sistemas de bases de datos relacionales que aparecieron posteriormente. Por aquel entonces un profesor de la Universidad de Berkeley, Michael Stonebraker, leyo unos artículos publicados por IBM sobre "System R" que le hicieron interesarse en el tema. Utilizando el dinero de otro proyecto que ya tenia asignado, Ingres (INteractive Graphics REtrieval System), Stonebraker empezo a desarrollar sus ideas sobre bases de datos relacionales. Durante estos años Ingres mantuvo su código fuente abierto y permanecio en gran medida similar en conceptos a "System R". A principio de los 80, Ingres estuvo compitiendo con Oracle por el liderazgo en el mundo de bases de datos relacionales y su código e implementación evolucionaron y fueron el origen de otras bases de datos relacionales, entre ellas podemos citar a Informix, NonStop SQL y Sybase (Microsoft SQL Server fue una versión licenciada de Sybase hasta su version 6.0). Michael Stonebraker dejo la Universidad de Berkeley en 1982 para comercializar Ingres pero volvio a la misma en 1985 con nuevas ideas.

Postgres 1986-1994 - Despues (post) de ingres Despues de su vuelta a Berkeley en 1985, Michael Stonebraker lideró un nuevo proyecto llamado Postgres (despues de Ingres) patrocinado por la Defense Advanced Research Projects Agency (DARPA), la Army Research Office (ARO), la National Science Foundation (NSF), y ESL, Inc. Con este proyecto y basandose en la experiencia obtenida con Ingres, Stonebraker tenia como meta mejorar lo que habian conseguido y aprendido en el desarrollo de Ingres. Y aunque se baso en muchas ideas de Ingres, no se baso en el código fuente del mismo. Los objetivos iniciales de este proyecto fueron: 

Proporcionar un mejor soporte para objetos complejos



Proporcionar a los usuarios la posibilidad de extender los tipos de datos, operadores y métodos de acceso.



Proporcionar los mecanismos necesarios para crear bases de datos activas (triggers, etc)



Simplificar el código encargado de la recuperación del sistema despues de una caída del mismo

 

Hacer cambios mínimos (preferiblemente ninguno) en el modelo relacional. Mejorar el lenguaje de consulta QUEL heredado de Ingres (POSTQUEL).

Para los interesados en el tema, teneis disponibles una serie de artículos originales y completos en ingles relacionados con el proyecto Postgres: 

"The design of POSTGRES": El diseño de Postgres



"The POSTGRES data model": El módelo de datos de Postgres



"The design of the POSTGRES storage system": El diseño del sistema de almacenamiento de Postgres



"The implementation of POSTGRES": Presentación de la versión 1 de Postgres en la conferencia ACM-SIGMOD de 1988



"A commentary on the POSTGRES rules system": Comentarios sobre el sistema de reglas de Postgres



"On Rules, Procedures, Caching and Views in Database Systems": Sobre reglas, procedimientos, cache y vistas en sistemas de bases de datos

La última versión de Postgres en este projecto fue la versión 4.2.

Postgres95 1994-1995 - Nueva vida en el mundo opensource En 1994, dos estudiantes de Berkeley, Andrew Yu y Jolly Chen, empezaron a trabajar con el código de Postgres (versión 4.2) y llamaron al proyecto Postgres95. Hicieron una limpieza general del código, arreglaron errores en el mismo, e implementaron otras mejoras, entre las que destacan: 

Sustitución de POSTQUEL por un interprete del lenguaje SQL



Reimplementación de las funciones agregadas



psql fue creado para ejecutar consultas SQL



El interface de objetos grandes (large-object) fue revisado

 

Un pequeño tutorial sobre Postgres fue creado Postgres se pudo empezar a compilar con GNU make y GCC sin parchear

La versión 1.0 de Postgre95 vio la luz en 1995, el código era 100% ANSI C, un 25% más corto en relación con la versión 4.2 y un 30-50% más rápido. El código fue publicado en la web y liberado bajo una licencia BSD, y más y más personas empezaron a utilizar y a colaborar en el proyecto.

PostgreSQL 1996-actualidad - Proyecto PostgreSQL En 1996, Andrew Yu y Jolly Chen ya no tenian tanto tiempo para dirigir y desarrollar Postgres95. Algunos de los usuarios habituales de las listas de correo del proyecto decidieron hacerse cargo del mismo y crearon el llamado "PostgreSQL Global Development Team". En un principio este equipo de desarrolladores al cargo de la organización del proyecto estuvo formado por Marc Fournier en Ontario, Canada, Thomas Lockhart en Pasadena, California, Vadim Mikheev en Krasnoyarsk, Rusia y Bruce Momjian in Philadelphia, Pennsylvania. El nombre fue cambiado de Postgres95 a PostgreSQL y lanzaron la versión 6.0 en enero de 1997. Hoy en dia el grupo central (core team) de desarrolladores está formado por 6 personas, existen 38 desarrolladores principales y más 21 desarrolladores habituales. En total alrededor de 65 personas activas, contribuyendo con el desarrollo de PostgreSQL. Podeis encontrar más información sobre este equipo de desarrolladores enhttp://www.postgresql.org/community/contributors/ Existe tambien una gran comunidad de usuarios, programadores y administradores que colaboran actívamente en numerosos aspectos y actividades relacionadas con el proyecto. Informes y soluciones de problemas, tests, comprobación del funcionamiento, aportaciones de nuevas ideas, discusiones sobre características y problemas, documentación y fomento de PostgreSQL son solo algunas de las actividades que la comunidad de usuarios realiza. No tenemos que olvidar tampoco que existen muchas empresas que tambien colaboran con dinero y/ó con tiempo/personas en mejorar PostgreSQL. Muchos desarrolladores y nuevas características están muchas veces patrocinadas por empresas privadas. En los últimos años los trabajos de desarrollo se han concentrado mucho en la velocidad de proceso y en características demandadas en el mundo empresarial. En este gráfico podeis ver cuando las diferentes versiones de PostgreSQL han visto la luz y las principales caracteristicas en las que se ha centrado el desarrollo.

Durante los años de existencia del Proyecto PostgreSQL, el tamaño del mismo, tanto en número de desarrolladores, como en números de linea de código, funciones y complejidad del mismo ha ido aumentando año a año. En el siguiente gráfico teneis

una gráfica con la evolución del número de lineas de código en cada versión de PostgreSQL.

Los datos de este gráfico estan generados con CLOC. Contabilizamos como lineas de código a todas las lineas de código en diferentes lenguaje, más comentarios, menos lineas en blanco. Los ficheros HTML y CSS no se cuentan como código. Usando el modelo de estimación de costes de software "COCOMOII" (Constructive COst MOdel) podemos obtener unos datos meramente orientativos pero que nos pueden ayudar a entender la complejidad del proyecto PostgreSQL y los recursos que se necesitarian para desarrollar un producto similar desde cero. Según COCOMOII, obtendriamos estos números para PostgreSQL 9.0.0:

Descripción

Valor

Números de lineas de código (PG-9.0.0)

969.562

Habilidad de los programadores (alta)

0,6

Complejidad del projecto (alta)

1,24

Precio/hora ($100.000/año - 1.875horas/año)

$53,3

Programadores-año

618,71

Precio por linea de código

$65,30

Precio Total

$63.316.697

Lineas de código por persona/dia

7

Tiempo de desarrollo del proyecto (años)

3.6

Número medio de programadores

171,4

Ref: http://www.cms4site.ru/utility.php?ecur=1.24&eafcur=0.6&utility=cocomoii...

Ciclo de vida (EOL) y soporte El Proyecto PostgreSQL tiene como objetivo mantener y soportar cada versión de PostgreSQL durante 5 años desde el momento de su lanzamiento. A continuación teneis un resumen del ciclo de vida de las diferentes versiones de PostgreSQL:

Versión

Versión menor

Soportada

Lanzamiento

Soporte

9.2

9.2.0

Si

Sep 2012

Sep 2017

9.1

9.1.5

Si

Sep 2011

Sep 2016

9.0

9.0.9

Si

Sep 2010

Sep 2015

8.4

8.4.13

Si

Jul 2009

Jul 2014

8.3

8.3.20

Si

Feb 2008

Feb 2013

8.2

8.2.23

No

Dic 2006

Dic 2011

8.1

8.1.23

No

Nov 2005

Nov 2010

8.0

8.0.26

No

Ene 2005

Oct 2010

7.4

7.4.30

No

Nov 2003

Oct 2010

7.3

7.3.21

No

Nov 2002

Nov 2007

7.2

7.2.8

No

Feb 2002

Feb 2007

7.1

7.1.3

No

Abr 2001

Abr 2006

7.0

7.0.3

No

May 2000

May 2005

6.5

6.5.3

No

Jun 1999

Jun 2004

6.4

6.4.2

No

Oct 1998

Oct 2003

6.3

6.3.2

No

Mar 1998

Mar 2003

Características, limitaciones y ventajas CARACTERÍSTICAS La última serie de producción es la 9.1. Sus características técnicas la hacen una de las bases de datos más potentes y robustas del mercado. Su desarrollo comenzo hace más de 16 años, y durante este tiempo, estabilidad, potencia, robustez, facilidad de administración e implementación de estándares han sido las características que más se han tenido en cuenta durante su desarrollo. PostgreSQL funciona muy bien con grandes cantidades de datos y una alta concurrencia de usuarios accediendo a la vez a el sistema. A continuación teneis algunas de las características más importantes y soportadas por PostgreSQL:

Generales 

Es una base de datos 100% ACID.



Soporta distintos tipos de datos: además del soporte para los tipos base, también soporta datos de tipo

fecha, monetarios, elementos gráficos, datos sobre redes (MAC, IP ...), cadenas de bits, etc. También permite la creación de tipos propios. 

Incluye herencia entre tablas, por lo que a este gestor de bases de datos se le incluye entre los

gestores objeto-relacionales. 

Copias de seguridad en caliente (Online/hot backups)



Unicode



Juegos de caracteres internacionales



Regionalización por columna



Multi-Version Concurrency Control (MVCC)



Multiples métodos de autentificación



Acceso encriptado via SSL



SE-postgres



Completa documentación



Licencia BSD

 Disponible para Linux y UNIX en todas sus variantes (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) y Windows 32/64bit.

Alta concurrencia Mediante un sistema denominado MVCC (Acceso concurrente multiversión, por sus siglas en inglés) PostgreSQL permite que mientras un proceso escribe en una tabla, otros accedan a la misma tabla sin necesidad de bloqueos. Cada usuario obtiene una visión consistente de lo último a lo que se le hizo commit.

LIMITACIONES

• Puntos de recuperación dentro de transacciones. Actualmente, las transacciones abortan completamente si se encuentra un fallo durante su ejecución. • No soporta tablespaces para definir dónde almacenar la base de datos, el esquema, los índices, etc. (versiones antes de la 9.0) • El soporte a orientación a objetos es una simple extensión que ofrece prestaciones como la herencia, no un soporte completo.

Ventajas:



Ampliamente popular - Ideal para tecnologias Web.



Fácil de Administrar.



Su sintaxis SQL es estándar y fácil de aprender.



Footprint bajo de memoria, bastante poderoso con una configuración adecuada.



Multiplataforma.



Capacidades de replicación de datos.



Soporte empresarial disponible.

Estabilidad y confiabilidad legendarias En contraste a muchos sistemas de bases de datos comerciales, es extremadamente común que compañías reporten que PostgreSQL nunca ha presentado caídas en varios años de operación de alta actividad. Ni una sola vez. Simplemente funciona. Extensible El código fuente está disponible para todos sin costo. Si su equipo necesita extender o personalizar PostgreSQL de alguna manera, pueden hacerlo con un mínimo esfuerzo, sin costos adicionales. Esto es complementado por la comunidad de profesionales y entusiastas de PostgreSQL alrededor del mundo que también extienden PostgreSQL todos los días. Multiplataforma PostgreSQL está disponible en casi cualquier Unix (34 plataformas en la última versión estable), y una versión nativa de Windows está actualmente en estado beta de pruebas. Diseñado para ambientes de alto volumen PostgreSQL usa una estrategia de almacenamiento de filas llamada MVCC para conseguir una mucho mejor respuesta en ambientes de grandes volúmenes. Los principales proveedores de sistemas de bases de datos comerciales usan también esta tecnología, por las mismas razones.

DESVENTAJAS

 En comparación con MySQL es más lento en inserciones y actualizaciones, ya que cuenta con cabeceras de intersección que no tiene MySQL.  Soporte en línea: Hay foros oficiales, pero no hay una ayuda obligatoria.  Consume más recursos que MySQL.  La sintaxtis de algunos de sus comandos o sentencias no es nada intuitiva. Enviar por correo electrónico

Instalación de PostgreSQL en windows

DESCARGA E INSTALACIÓN DE POSGRESQL EN WINDOWS 7

Abriremos un navegador web y accederemos a la URL: http://www.postgresql.org/download/windows

Y nos direccionará a otra página donde podremos descargarnos la versión correcta tanto para nuestras necesidades como para el tipo de sistema operativo que tengamos instalado.

Que en nuestro caso es la versión 9.1.3 para Windows de 64 bits. Una vez descargado el archivo de instalación lo ejecutamos y si tenemos activado el control de cuentas de usuario nos mostrará una advertencia con el texto "¿Desea permitir que este programa realice cambios en el equipo?", pulsaremos "Sí" para continuar con la instalación de PostgreSQL:

Se iniciará el asistente para instalar PostgreSQL, pulsaremos "Siguiente":

Indicaremos la carpeta de instalación de PostgreSQL, donde se guardarán los ejecutables, librerías y ficheros de configuración de PostgreSQL:

Indicaremos también la carpeta donde se guardarán los datos por defecto de PostgreSQL:

Introduciremos la contraseña para el súper usuario "postgres" que será con el que iniciemos sesión para administrar la base de datos, si tenemos clave de cuenta en Windows introduciremos nuestra clave.

Introduciremos el puerto de escucha para la conexión con el servidor PostgreSQL, por defecto el 5432:

Seleccionaremos la configuración regional:

Pulsaremos "Siguiente" para iniciar la instalación definitiva.

Se iniciará el asistente para instalar el motor de base de datos PostgreSQL, que creará las carpetas oportunas, copiará los ficheros necesarios y creará el servicio Windows para iniciar de forma automática el motor de base de datos:

Una vez finalizada la instalación el asistente nos dará la posibilidad de ejecutar Stack Builder, aplicación que nos permitirá instalar otros componentes y herramientas para PostgreSQL:

Si hemos marcado la opción de Stack Builder, se iniciará, seleccionaremos "PostgreSQL 9.0 on port 5432". Pulsaremos "Finish" (en nuestro caso cancelaremos Stack Builder pues no instalaremos más componentes).

Creacion de una Base de Datos INTRODUCCION A LA INTERFACE

CREAR UNA BASE DE DATOS EN POSTGRES SQL 1.- Para crear una nueva base de datos, debemos situarnos en el Servidor Postgre SQL, darle clic derecho, seleccionamos conectar, nos pedira nuestra contraseña de de super ususario Postgre SQL.

2.- Una vez realizada la conexión, nos situamos en PostgreSQL, damos clic derecho y seleccionar NEW DATABASE del menu emergente

3.- En la nueva pantalla vamos a ingresar el nombre de la nueva base de datos que en nuestro caso es Pedidos, el dueño va a ser PostgreSQL, damos clic en OK

4.-

Podemos

visualizar

la

nueva

base

de

datos

que

hemos

creado.

5.- Para utilizar la nueva base de datos pedidos, vamos a dar clic derecho sobre la base de datos, seleccionamos CREATE Script

6.- Podremos visualizar una nueva pantalla donde esta nuestro scipt creado por defecto, en esta pantalla podremos trabajar y realizar la programacion adecuada para realizar las modificaciones pertinentes en la base de datos.

CREACION DE LAS TABLAS EN LA BASE DE DATOS. 1.- En la pantalla que tenemos ahora, vamos a escribir el respectivo Script para crear una nueva tabla.

2.-

Una

vez

creada

el

Script

lo

seleccionamos,

lo

mandamos

a

ejecutar.

3.- Para verificar que la tabla se creo, vamos a ir a la primera pagina de PostgresSQL, vamos a la base de datos PEDIDOS, seleccionamos ESQUEMAS, luego PUBLIC, y para finalizar seleccionamos TABLES, donde podemos visualizar que la tabla EMPLEADOS esta creada.

4.- Para insertar datos en la tabla que hemos creado. Vamos a nuestra tabla en este caso EMPLEADOS clic derecho y seleccionamos SCRIPTS, podemos ver que esta selección nos facilita varias opciones para llenar nuestra tabla.

5.- En este caso vamos a seleccionar la opción INSERT, donde se despliega una página donde vamos a ingresar valores.

Script de una BD Una vez terminada la instalación del DBMS lo que resta es ingresar el código SQL, el cual tiene una interfaz muy similar a la del SQL Sever 2008. En este artículo resolvemos los ejercicios planteados en el archivo que se muestran despues de la creacion de la BDD. descargar Script: ScriptBDD.sql Primero vamos a crear el Script para llenar la base de datos: /********** ESCUELA POLITÉNCICA NACIONAL *************/ BASES DE DATOS GRUPO POSTGRESQL *****************************************************/

/*CREACIÓN DE LA BASE DE DATOS 'PEDIDOS' */

drop database if exists PEDIDOS;

create database PEDIDOS;

CREATE TABLE EMPLEADOS( EMPLEADOID int NOT NULL, NOMBRE char(30) NULL, APELLIDO char(30) NULL, FECHA_NAC date NULL, REPORTA_A int NULL, EXTENSION int NULL, CONSTRAINT PK_EMPLEADOS PRIMARY KEY (EMPLEADOID));

CREATE TABLE PROVEEDORES(

PROVEEDORID int NOT NULL, NOMBREPROV char(50) NOT NULL, CONTACTO char(50) NOT NULL, CELUPROV char(12) NULL, FIJOPROV char(12) NULL, CONSTRAINT PK_PROVEEDORES PRIMARY KEY (PROVEEDORID ) );

CREATE TABLE CATEGORIAS( CATEGORIAID int NOT NULL, NOMBRECAT char(50) NOT NULL, CONSTRAINT PK_CATEGORIAS PRIMARY KEY (CATEGORIAID) ) ;

CREATE TABLE CLIENTES( CLIENTEID int NOT NULL, CEDULA_RUC char(10) NOT NULL, NOMBRECIA char(30) NOT NULL, NOMBRECONTACTO char(50) NOT NULL, DIRECCIONCLI char(50) NOT NULL, FAX char(12) NULL, EMAIL char(50) NULL, CELULAR char(12) NULL, FIJO char(12) NULL, CONSTRAINT PK_CLIENTES PRIMARY KEY (CLIENTEID) );

CREATE TABLE ORDENES( ORDENID int NOT NULL, EMPLEADOID int NOT NULL, CLIENTEID int NOT NULL, FECHAORDEN date NOT NULL,

DESCUENTO int NULL, CONSTRAINT PK_ORDENES PRIMARY KEY (ORDENID) );

CREATE TABLE DETALLE_ORDENES( ORDENID int NOT NULL, DETALLEID int NOT NULL, PRODUCTOID int NOT NULL, CANTIDAD int NOT NULL, CONSTRAINT PK_DETALLE_ORDENES PRIMARY KEY (ORDENID,DETALLEID ) );

CREATE TABLE PRODUCTOS( PRODUCTOID int NOT NULL, PROVEEDORID int NOT NULL, CATEGORIAID int NOT NULL, DESCRIPCION char(50) NULL, PRECIOUNIT numeric NOT NULL, EXISTENCIA int NOT NULL, CONSTRAINT PK_PRODUCTOS PRIMARY KEY (PRODUCTOID )) ;

ALTER TABLE ORDENES ADD CONSTRAINT FK_ORDENES_CLIEN_ORD_CLIENTES FOREIGNKEY(CLIENTEID) REFERENCES CLIENTES (CLIENTEID) on delete restrict on update restrict;

ALTER TABLE ORDENES ADD CONSTRAINT FK_ORDENES_EMPLE_ORD_EMPLEADOFOREIGN K EY(EMPLEADOID) REFERENCES EMPLEADOS (EMPLEADOID) on delete restrict on update restrict;

ALTER TABLE DETALLE_ORDENES ADD CONSTRAINTFK_DETALLE__ORDEN_DET_ORDENES F OREIGN KEY(ORDENID) REFERENCES ORDENES (ORDENID) on delete restrict on update restrict;

ALTER TABLE DETALLE_ORDENES ADD CONSTRAINTFK_DETALLE__PROD_DETA_PRODUCTO FOREIGN KEY(PRODUCTOID) REFERENCES PRODUCTOS (PRODUCTOID) on delete restrict on update restrict;

ALTER TABLE PRODUCTOS ADD CONSTRAINTFK_PRODUCTO_CATE_PROD_CATEGORI FOREIG N KEY(CATEGORIAID) REFERENCES CATEGORIAS (CATEGORIAID) on delete restrict on update restrict;

ALTER TABLE PRODUCTOS ADD CONSTRAINTFK_PRODUCTO_PROV_PROD_PROVEEDO FOREIG N KEY(PROVEEDORID) REFERENCES PROVEEDORES (PROVEEDORID) on delete restrict on update restrict;

ALTER TABLE EMPLEADOS ADD CONSTRAINT FK_EMPLEADO_REPORTA FOREIGNKEY(REPOR TA_A) REFERENCES EMPLEADOS (EMPLEADOID) on delete restrict on update restrict;

/*INSERCIÓN DE DATOS EN LA BASE

*/

insert into categorias (categoriaid, nombrecat) values (100,'CARNICOS'); insert into categorias (categoriaid, nombrecat) values (200,'LACTEOS'); insert into categorias (categoriaid, nombrecat) values (300,'LIMPIEZA'); insert into categorias (categoriaid, nombrecat) values (400,'HIGINE PERSONAL'); insert into categorias (categoriaid, nombrecat) values (500,'MEDICINAS');

insert into categorias (categoriaid, nombrecat) values (600,'COSMETICOS') ; insert into categorias (categoriaid, nombrecat) values (700,'REVISTAS');

insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (10, 'DON DIEGO', 'MANUEL ANDRADE', '099234567','2124456'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (20, 'PRONACA', 'JUAN PEREZ', '0923434467','2124456'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (30, 'TONY', 'JORGE BRITO', '099234567','2124456'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (40, 'MIRAFLORES', 'MARIA PAZ', '098124498','2458799'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (50, 'ALMAY', 'PEDRO GONZALEZ', '097654567','2507190'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (60, 'REVLON', 'MONICA SALAS', '099245678','2609876'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (70, 'YANBAL', 'BETY ARIAS', '098124458','2450887'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (120, 'JURIS', 'MANUEL ANDRADE', '099234567','2124456'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (80, 'CLEANER', 'MANUEL ANDRADE', '099234567','2124456'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (90, 'BAYER', 'MANUEL ANDRADE', '099234567','2124456'); insert into proveedores (proveedorid, nombreprov,contacto,celuprov,fijopr ov) values (110, 'PALMOLIVE', 'MANUEL ANDRADE', '099234567','2124456');

INSERT INTO PRODUCTOS VALUES (1,10,100,'SALCHICHAS VIENESAS',2.60,200); INSERT INTO PRODUCTOS VALUES (2,10,100,'SALAMI DE AJO',3.60,300); INSERT INTO PRODUCTOS VALUES (3,10,100,'BOTON PARA ASADO',4.70,400); INSERT INTO PRODUCTOS VALUES (4,20,100,'SALCHICHAS DE POLLO',2.90,200); INSERT INTO PRODUCTOS VALUES (5,20,100,'JAMON DE POLLO',2.80,100); INSERT INTO PRODUCTOS VALUES (6,30,200,'YOGURT NATURAL',4.30,80); INSERT INTO PRODUCTOS VALUES (7,30,200,'LECHE CHOCOLATE',1.60,90); INSERT INTO PRODUCTOS VALUES (8,40,200,'YOGURT DE SABORES',1.60,200); INSERT INTO PRODUCTOS VALUES (9,40,200,'CREMA DE LECHE',3.60,30); INSERT INTO PRODUCTOS VALUES (10,50,600,'BASE DE MAQUILLAJE',14.70,40); INSERT INTO PRODUCTOS VALUES (11,50,600,'RIMMEL',12.90,20); INSERT INTO PRODUCTOS VALUES (13,60,600,'SOMBRA DE OJOS',9.80,100); set datestyle to dmy;

INSERT INTO EMPLEADOS VALUES (1,'JUAN', 'CRUZ', '18/01/67',null,231); INSERT INTO EMPLEADOS VALUES (2,'MARIO', 'SANCHEZ','01/03/79',1,144); INSERT INTO EMPLEADOS VALUES (3,'VERONICA', 'ARIAS','23/06/77',1, 234); INSERT INTO EMPLEADOS VALUES (4,'PABLO', 'CELY', '28/01/77',2,567); INSERT INTO EMPLEADOS VALUES (5,'DIEGO', 'ANDRADE', '15/05/70',2,890); INSERT INTO EMPLEADOS VALUES (6,'JUAN', 'ANDRADE', '17/11/76',3,230); INSERT INTO EMPLEADOS VALUES (7,'MARIA', 'NOBOA', '21/12/79',3,261);

INSERT INTO CLIENTES VALUES (1,'1890786576','SUPERMERCADO ESTRELLA','JUAN ALBAN','AV.AMAZONAS',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (2,'1298765477','EL ROSADO','MARIA CORDERO','AV.AEL INCA',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (3,'1009876567','DISTRIBUIDORA PRENSA','PEDRO PINTO','EL PINAR',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (4,'1876090006','SU TIENDA','PABLO PONCE','AV.AMAZONAS',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (5,'1893456776','SUPERMERCADO DORADO','LORENA PAZ','AV.6 DICIEMBRE',NULL,NULL,NULL,NULL);

INSERT INTO CLIENTES VALUES (6,'1678999891','MI COMISARIATO','ROSARIO UTRERAS','AV.AMAZONAS',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (7,'1244567888','SUPERMERCADO DESCUENTO','LETICIA ORTEGA','AV.LA PRENSA',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (8,'1456799022','EL DESCUENTO','JUAN TORRES','AV.PATRIA',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (9,'1845677777','DE LUISE','JORGE PARRA','AV.AMAZONAS',NULL,NULL,NULL,NULL); INSERT INTO CLIENTES VALUES (10,'183445667','YARBANTRELLA','PABLO POLIT','AV.REPUBLICA',NULL,NULL,NULL,NULL);

INSERT INTO ORDENES VALUES(1,3,4,'17/06/07', 5); INSERT INTO ORDENES VALUES(2,3,4,'02/06/07', 10); INSERT INTO ORDENES VALUES(3,4,5,'05/06/07', 6); INSERT INTO ORDENES VALUES(4,2,6,'06/06/07', 2); INSERT INTO ORDENES VALUES(5,2,7,'09/06/07', NULL); INSERT INTO ORDENES VALUES(6,4,5,'12/06/07', 10); INSERT INTO ORDENES VALUES(7,2,5,'14/06/07', 10); INSERT INTO ORDENES VALUES(8,3,2,'13/06/07', 10); INSERT INTO ORDENES VALUES(9,3,2,'17/06/07', 3); INSERT INTO ORDENES VALUES(10,2,2,'18/06/07', 2);

INSERT INTO detalle_ordenes VALUES(1,1,1,2); INSERT INTO detalle_ordenes VALUES(1,2,4,1); INSERT INTO detalle_ordenes VALUES(1,3,6,1); INSERT INTO detalle_ordenes VALUES(1,4,9,1); INSERT INTO detalle_ordenes VALUES(2,1,10,10); INSERT INTO detalle_ordenes VALUES(2,2,13,20); INSERT INTO detalle_ordenes VALUES(3,1,3,10); INSERT INTO detalle_ordenes VALUES(4,1,9,12); INSERT INTO detalle_ordenes VALUES(5,1,1,14); INSERT INTO detalle_ordenes VALUES(5,2,4,20); INSERT INTO detalle_ordenes VALUES(6,1,3,12); INSERT INTO detalle_ordenes VALUES(7,1,11,10);

INSERT INTO detalle_ordenes VALUES(8,1,2,10); INSERT INTO detalle_ordenes VALUES(8,2,5,14); INSERT INTO detalle_ordenes VALUES(8,3,7,10); INSERT INTO detalle_ordenes VALUES(9,1,11,10); INSERT INTO detalle_ordenes VALUES(10,1,1,5);

Resolución de ejercicios /* EJERCICIOS PROPUESTOS

*********************************************************************** EJERCICIOS ************************************************************************

1. Actualizar el precio unitario de los productos de la categoría CARNICOS, subiéndolos en un 10% (1) */

select * from productos; update productos set preciounit= (preciounit*0.1) + preciounit;

/* 2. Actualizar el teléfono celular del proveedor cuyo contacto es MANUEL ANDRADE, con el valor 099010291 (2) */

select * from proveedores; update proveedores set celuprov= '0990101291' where contacto= 'MANUEL ANDRADE';

/* 3. Borrar el producto YOGURT DE SABORES */

(3)

select * from productos; delete from productos where descripcion ='YOGURT DE SABORES';

/* 4. Realizar las siguientes consultas:

4.1 Mostrar todas las órdenes: el id de la orden, el apellido y nombre del empleado que la atendió (4) el nombre de la compañía cliente y la fecha de orden */

SELECT * FROM ORDENES; select * from empleados; select * from clientes; select ord.ordenid, emp.empleadoid, emp.nombre, emp.apellido,ord.fechaord en, cli.clienteid, cli.nombrecia from ordenes ord, empleados emp, clientes cli where ord.empleadoid=emp.empleadoid andord.clienteid=cli.clienteid ;

/* 4.2 Mostrar la suma total de cada tipo de producto pedidos en todas las órdenes. (5) */

select * from detalle_ordenes; select productoid, sum(cantidad) as "Suma total" from detalle_ordenes group by productoid;

/* 4.3 Mostrar el número de órdenes atendidas por cada empleado, incluidos los que tienen 0 órdenes. (6) */

select * from empleados; select * from ordenes; select nombre as "Nombre" , apellido as "Apellido", count(ord.empleadoid) as "# de ordenes" from empleados emp left join ordenes ord on emp.empleadoid= ord.empleadoid group by nombre, apellido;

/* 4.4 Muestre los proveedores y la suma de dinero vendido en los productos de ese proveedor. (7) */

select * from proveedores; select * from productos; select * from detalle_ordenes;

select prov.nombreprov as "Nombre",sum(d.cantidad *prod.preciounit ) as " Suma dinero" from proveedores prov, productos prod, detalle_ordenes d where d.productoid= prod.productoid and prov.proveedorid=prod.proveedorid group by nombreprov;

/* 5. Realizar el siguiente procedimiento almacenado.

5.1 Escriba un procedimiento almacenado que reciba como parámetro un código de proveedor y (8) devuelve el número de órdenes en las que están incluidos productos de ese proveedor. */

DROP LANGUAGE IF EXISTS plpgsql; CREATE LANGUAGE plpgsql;

select * from productos; select * from ordenes; select * from detalle_ordenes;

create or replace function num_ordene_proveedor(idproo int)returns intege r as $$ declare num int; begin select count(p.productoid) into num from productos pjoin deta lle_ordenes dor on (p.productoid=dor.productoid)join ordenes ordon(ord.ordeni d=dor.ordenid) where proveedorid=idproo group by proveedorid; return num; end; $$ LANGUAGE plpgsql;

select num_ordene_proveedor(10)

/* 5.2 Escriba un procedimiento almacenado que reciba como parámetro un nombre de una categoría y (9) devuelve el código del producto de esa categoría que tiene más unidades vendidas. */

select * from categorias; select * from productos;

select * from detalle_ordenes;

create or replace function mas_vendido (nomCat character(50))returns inte ger as $$ declare num int; begin select p.productoid,sum(cantidad)INTO NUM fromproductos p joi n categorias c on (c.categoriaid=p.categoriaid) join detalle_ordenes dor on(p.productoid=dor.productoid) where c.nombrecat=nomCat group by descripcion, p.productoid order by sum DESC limit 1; return num; end; $$LANGUAGE 'plpgsql';

select mas_vendido('CARNICOS')

Segunda lista de comandos básicos en consola psql – PostgreSQL Posted on 14 octubre, 2012 by Ramón Torres

En este post pretendo dar a conocer algunos comandos básicos de PostgreSQL los cuales nos serán de gran ayuda, con estos comandos podremos hacer lo siguiente: listar bases de datos, selecionar una base de datos, ver la información de una base de datos, entre los más sobresalientes, a continuación la lista 1)El primer comando nos enseñará como iniciar el cliente de psql en nuestra consola: psql -U user -W -h host database

Ya hemos hablado un poco de este comando en otro post, dejo el link por si quieres ver en detalle cada parámetro: Primeros pasos en PosgreSQL 2)Nuestro segundo comando nos ayudara a saber la lista de nuestras bases de datos, el comando es: \l

3)Seleccionar una base de datos o cambiar de base: \c basename

4)Listar tablas de una base de datos: \d

Si la lista es muy larga veremos que podemos movernos hacia abajo y luego para salir solo digitamos la letra “q” 5)Para ver la información de la estructura de una tabla en especifico: \d table

6)Vaciar una tabla en especifico o el famoso TRUNCATE que conocemos:

TRUNCATE TABLE table RESTART IDENTITY

Con este comando borramos el contenido de una tabla y reiniciamos su indice sino agregamos RESTART IDENTITY nuestros indices no seran reiniciados y seguiran según el ultimo registro. 7)Crear una base de datos: CREATE DATABASE basename;

8)Borrar o eliminar una base de datos: DROP DATABASE basename;

9)Borrar o eliminar una tabla en especifico: DROP TABLE tablename;

10)Enviar resultados de una consulta a un archivo delimitado por | COPY (SELECT * FROM tablename) TO '/home/tablename.csv' WITH DELIMITER '|';

Cabe mencionar que el archivo necesito permisos de escritura. 11)Uso de LIMIT y OFFSET SELECT * FROM table LIMIT limit OFFSET offset;

Donde: limit: es nuestro limite de registros a mostrar offset: indica desde donde comenzaran a mostrarce los registros 12)Uso de comillas: SELECT “column” FROM “table” WHERE “column” = 'value';

Generalmente podemos utilizar comillas dobles para nuestras columnas y comillas simples para nuestros valores, esto no es una regla pero a veces es necesario en casos especiales, tales como cuando ocupamos nombres reservados, por ejemplo: SELECT to FROM table; En este caso tenemos un campo llamado “to”, esto nos dará un error de sintaxis, por lo tanto tendremos que usar comillas dobles: SELECT “to” FROM table; 13)Salir del cliente psql: \q

Bueno espero esta lista sea de ayuda para muchos como ha sido de ayuda para mi, a la ves agradezco a mi compañero Hugo Gilmar quien me dió una guía de estos comandos que ahora comparto en este post… This entry was posted in postgres. Bookmark the permalink.