PostgreSQL - Guía rápida

PostgreSQL es un potente sistema de base de datos relacional de objetos de código abierto. Tiene más de 15 años de fase de desarrollo activo y una arquitectura probada que le ha ganado una sólida reputación por su confiabilidad, integridad de datos y corrección.

Este tutorial le dará un comienzo rápido con PostgreSQL y lo hará sentir cómodo con la programación de PostgreSQL.

¿Qué es PostgreSQL?

PostgreSQL (pronunciado como post-gress-Q-L) es un sistema de gestión de bases de datos relacionales (DBMS) de código abierto desarrollado por un equipo mundial de voluntarios. PostgreSQL no está controlado por ninguna corporación u otra entidad privada y el código fuente está disponible de forma gratuita.

Una breve historia de PostgreSQL

PostgreSQL, originalmente llamado Postgres, fue creado en UCB por un profesor de ciencias de la computación llamado Michael Stonebraker. Stonebraker inició Postgres en 1986 como un proyecto de seguimiento de su predecesor, Ingres, ahora propiedad de Computer Associates.

1977-1985 - Se desarrolló un proyecto denominado INGRES.
- Prueba de concepto para bases de datos relacionales
- Estableció la empresa Ingres en 1980
- Comprado por Computer Associates en 1994
1986-1994 - POSTGRES
- Desarrollo de los conceptos en INGRES con foco en la orientación a objetos y el lenguaje de consulta - Quel
- El código base de INGRES no se utilizó como base para POSTGRES
- Comercializado como Illustra (comprado por Informix, comprado por IBM)
1994-1995 - Postgres95
- El soporte para SQL se agregó en 1994
- Publicado como Postgres95 en 1995
- Relanzado como PostgreSQL 6.0 en 1996
- Establecimiento del equipo de desarrollo global de PostgreSQL

Características clave de PostgreSQL

PostgreSQL se ejecuta en todos los principales sistemas operativos, incluidos Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64) y Windows. Admite texto, imágenes, sonidos y video, e incluye interfaces de programación para C / C ++, Java, Perl, Python, Ruby, Tcl y Open Database Connectivity (ODBC).

PostgreSQL es compatible con una gran parte del estándar SQL y ofrece muchas características modernas, incluidas las siguientes:

Consultas SQL complejas
Sub-selecciones de SQL
Llaves extranjeras
Trigger
Views
Transactions
Control de concurrencia de múltiples versiones (MVCC)
Replicación de transmisión (a partir de la versión 9.0)
Hot Standby (a partir de 9.0)

Puede consultar la documentación oficial de PostgreSQL para comprender las características mencionadas anteriormente. PostgreSQL puede ser extendido por el usuario de muchas formas. Por ejemplo, agregando nuevo -

Tipos de datos
Functions
Operators
Funciones agregadas
Métodos de índice

Soporte de idiomas procedimentales

PostgreSQL admite cuatro lenguajes de procedimiento estándar, lo que permite a los usuarios escribir su propio código en cualquiera de los lenguajes y puede ser ejecutado por el servidor de base de datos PostgreSQL. Estos lenguajes de procedimiento son: PL / pgSQL, PL / Tcl, PL / Perl y PL / Python. Además, también se admiten otros lenguajes de procedimiento no estándar como PL / PHP, PL / V8, PL / Ruby, PL / Java, etc.

Para comenzar a comprender los conceptos básicos de PostgreSQL, primero instalemos PostgreSQL. Este capítulo explica cómo instalar PostgreSQL en plataformas Linux, Windows y Mac OS.

Instalación de PostgreSQL en Linux / Unix

Siga los pasos dados para instalar PostgreSQL en su máquina Linux. Asegúrese de haber iniciado sesión comoroot antes de continuar con la instalación.

Elija el número de versión de PostgreSQL que desee y, lo más exactamente posible, la plataforma que desea de EnterpriseDB
yo descargué postgresql-9.2.4-1-linux-x64.runpara mi máquina CentOS-6 de 64 bits. Ahora, ejecutémoslo de la siguiente manera:

[root@host]# chmod +x postgresql-9.2.4-1-linux-x64.run
[root@host]# ./postgresql-9.2.4-1-linux-x64.run
------------------------------------------------------------------------
Welcome to the PostgreSQL Setup Wizard.

------------------------------------------------------------------------
Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.2]:

Una vez que inicie el instalador, le hará algunas preguntas básicas como la ubicación de la instalación, la contraseña del usuario que usará la base de datos, el número de puerto, etc. Por lo tanto, mantenga todos sus valores predeterminados excepto la contraseña, que puede proporcionar la contraseña según su elección. Instalará PostgreSQL en su máquina Linux y mostrará el siguiente mensaje:

Please wait while Setup installs PostgreSQL on your computer.

 Installing
 0% ______________ 50% ______________ 100%
 #########################################

-----------------------------------------------------------------------
Setup has finished installing PostgreSQL on your computer.

Siga los siguientes pasos posteriores a la instalación para crear su base de datos:

[root@host]# su - postgres
Password:
bash-4.1$ createdb testdb bash-4.1$ psql testdb
psql (8.4.13, server 9.2.4)

test=#

Puede iniciar / reiniciar el servidor de Postgres en caso de que no se esté ejecutando usando el siguiente comando:

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Si su instalación fue correcta, tendrá el indicador de PotsgreSQL test=# como se muestra arriba.

Instalación de PostgreSQL en Windows

Siga los pasos dados para instalar PostgreSQL en su máquina Windows. Asegúrese de haber desactivado el antivirus de terceros durante la instalación.

Elija el número de versión de PostgreSQL que desee y, lo más exactamente posible, la plataforma que desea de EnterpriseDB
Descargué postgresql-9.2.4-1-windows.exe para mi PC con Windows que se ejecuta en modo de 32 bits, así que ejecutemos postgresql-9.2.4-1-windows.execomo administrador para instalar PostgreSQL. Seleccione la ubicación donde desea instalarlo. De forma predeterminada, se instala dentro de la carpeta Archivos de programa.

El siguiente paso del proceso de instalación sería seleccionar el directorio donde se almacenarían sus datos. De forma predeterminada, se almacena en el directorio "datos".

A continuación, la configuración solicita una contraseña, para que pueda usar su contraseña favorita.

El siguiente paso; mantener el puerto por defecto.

En el siguiente paso, cuando se me preguntó por "Configuración regional", seleccioné "Inglés, Estados Unidos".
Lleva un tiempo instalar PostgreSQL en su sistema. Una vez finalizado el proceso de instalación, aparecerá la siguiente pantalla. Desmarque la casilla de verificación y haga clic en el botón Finalizar.

Una vez completado el proceso de instalación, puede acceder al shell pgAdmin III, StackBuilder y PostgreSQL desde su menú de programa en PostgreSQL 9.2.

Instalación de PostgreSQL en Mac

Siga los pasos dados para instalar PostgreSQL en su máquina Mac. Asegúrese de haber iniciado sesión comoadministrator antes de continuar con la instalación.

Elija el número de versión más reciente de PostgreSQL para Mac OS disponible en EnterpriseDB
yo descargué postgresql-9.2.4-1-osx.dmgpara mi Mac OS con OS X versión 10.8.3. Ahora, abramos la imagen dmg en el buscador y simplemente haga doble clic en ella, lo que le dará el instalador de PostgreSQL en la siguiente ventana:

A continuación, haga clic en el postgres-9.2.4-1-osxicono, que dará un mensaje de advertencia. Acepte la advertencia y continúe con la instalación. Le pedirá la contraseña de administrador como se ve en la siguiente ventana:

Ingrese la contraseña, continúe con la instalación y luego de este paso, reinicie su máquina Mac. Si no ve la siguiente ventana, vuelva a iniciar la instalación.

Una vez que inicie el instalador, le hará algunas preguntas básicas como la ubicación de la instalación, la contraseña del usuario que usará la base de datos, el número de puerto, etc. Por lo tanto, mantenga todos ellos en sus valores predeterminados excepto la contraseña, que puede proporcionar según su elección. Instalará PostgreSQL en su máquina Mac en la carpeta Aplicación que puede verificar:

Ahora, puede iniciar cualquiera de los programas para empezar. Comencemos con SQL Shell. Cuando inicie SQL Shell, simplemente use todos los valores predeterminados que muestra excepto, ingrese su contraseña, que había seleccionado en el momento de la instalación. Si todo va bien, entonces estará dentro de la base de datos de Postgres y unapostgress# se mostrará el mensaje como se muestra a continuación:

¡¡¡Felicidades!!! Ahora tiene su entorno listo para comenzar con la programación de bases de datos PostgreSQL.

Este capítulo proporciona una lista de los comandos SQL de PostgreSQL, seguida de las reglas de sintaxis precisas para cada uno de estos comandos. Este conjunto de comandos se toma de la herramienta de línea de comandos psql. Ahora que tiene Postgres instalado, abra psql como:

Program Files → PostgreSQL 9.2 → SQL Shell(psql).

Con psql, puede generar una lista completa de comandos mediante el comando \ help. Para la sintaxis de un comando específico, use el siguiente comando:

postgres-# \help <command_name>

La declaración SQL

Una declaración SQL se compone de tokens donde cada token puede representar una palabra clave, identificador, identificador entre comillas, constante o símbolo de carácter especial. La tabla que se proporciona a continuación utiliza una instrucción SELECT simple para ilustrar una instrucción SQL básica, pero completa, y sus componentes.

	SELECCIONE	id, nombre	DESDE	estados
Tipo de token	Palabra clave	Identificadores	Palabra clave	Identificador
Descripción	Mando	Columnas de identificación y nombre	Cláusula	Nombre de la tabla

Comandos SQL de PostgreSQL

ABORTAR

Abortar la transacción actual.

ABORT [ WORK | TRANSACTION ]

ALTERAR AGREGAR

Cambie la definición de una función agregada.

ALTER AGGREGATE name ( type ) RENAME TO new_name
ALTER AGGREGATE name ( type ) OWNER TO new_owner

ALTERAR LA CONVERSIÓN

Cambia la definición de conversión.

ALTER CONVERSION name RENAME TO new_name
ALTER CONVERSION name OWNER TO new_owner

ALTERAR BASE DE DATOS

Cambiar un parámetro específico de la base de datos.

ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
ALTER DATABASE name RENAME TO new_name
ALTER DATABASE name OWNER TO new_owner

ALTER DOMAIN

Cambie la definición de un parámetro específico de dominio.

ALTER DOMAIN name { SET DEFAULT expression | DROP DEFAULT }
ALTER DOMAIN name { SET | DROP } NOT NULL
ALTER DOMAIN name ADD domain_constraint
ALTER DOMAIN name DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
ALTER DOMAIN name OWNER TO new_owner

FUNCIÓN ALTER

Cambia la definición de una función.

ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name
ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO new_owner

ALTERAR GRUPO

Cambiar un grupo de usuarios.

ALTER GROUP groupname ADD USER username [, ... ]
ALTER GROUP groupname DROP USER username [, ... ]
ALTER GROUP groupname RENAME TO new_name

ALTER INDICE

Cambia la definición de un índice.

ALTER INDEX name OWNER TO new_owner
ALTER INDEX name SET TABLESPACE indexspace_name
ALTER INDEX name RENAME TO new_name

ALTERAR IDIOMA

Cambie la definición de un lenguaje procedimental.

ALTER LANGUAGE name RENAME TO new_name

OPERADOR ALTER

Cambie la definición de un operador.

ALTER OPERATOR name ( { lefttype | NONE }, { righttype | NONE } )
OWNER TO new_owner

CLASE DE OPERADOR ALTER

Cambie la definición de una clase de operador.

ALTER OPERATOR CLASS name USING index_method RENAME TO new_name
ALTER OPERATOR CLASS name USING index_method OWNER TO new_owner

ALTER SCHEMA

Cambia la definición de un esquema.

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner

ALTER SEQUENCE

Cambia la definición de un generador de secuencias.

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

ALTERAR TABLA

Cambia la definición de una tabla.

ALTER TABLE [ ONLY ] name [ * ]
action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
RENAME TO new_name

Donde la acción es una de las siguientes líneas:

ADD [ COLUMN ] column_type [ column_constraint [ ... ] ]
DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD table_constraint
DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
OWNER TO new_owner
SET TABLESPACE tablespace_name

ALTER TABLESPACE

Cambie la definición de un espacio de tabla.

ALTER TABLESPACE name RENAME TO new_name
ALTER TABLESPACE name OWNER TO new_owner

ALTER TRIGGER

Cambia la definición de un disparador.

ALTER TRIGGER name ON table RENAME TO new_name

ALTER TIPO

Cambie la definición de un tipo.

ALTER TYPE name OWNER TO new_owner

ALTER USUARIO

Cambiar una cuenta de usuario de la base de datos.

ALTER USER name [ [ WITH ] option [ ... ] ]
ALTER USER name RENAME TO new_name
ALTER USER name SET parameter { TO | = } { value | DEFAULT }
ALTER USER name RESET parameter

Donde la opción puede ser -

[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| VALID UNTIL 'abstime'

ANALIZAR

Recopile estadísticas sobre una base de datos.

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

EMPEZAR

Inicie un bloque de transacciones.

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

Donde transaction_mode es uno de:

ISOLATION LEVEL { 
   SERIALIZABLE | REPEATABLE READ | READ COMMITTED
   | READ UNCOMMITTED
}
READ WRITE | READ ONLY

CONTROL

Forzar un punto de control del registro de transacciones.

CHECKPOINT

CERCA

Cierre un cursor.

CLOSE name

RACIMO

Agrupe una tabla según un índice.

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER

COMENTARIO

Definir o cambiar el comentario de un objeto.

COMMENT ON {
   TABLE object_name |
   COLUMN table_name.column_name |
   AGGREGATE agg_name (agg_type) |
   CAST (source_type AS target_type) |
   CONSTRAINT constraint_name ON table_name |
   CONVERSION object_name |
   DATABASE object_name |
   DOMAIN object_name |
   FUNCTION func_name (arg1_type, arg2_type, ...) |
   INDEX object_name |
   LARGE OBJECT large_object_oid |
   OPERATOR op (left_operand_type, right_operand_type) |
   OPERATOR CLASS object_name USING index_method |
   [ PROCEDURAL ] LANGUAGE object_name |
   RULE rule_name ON table_name |
   SCHEMA object_name |
   SEQUENCE object_name |
   TRIGGER trigger_name ON table_name |
   TYPE object_name |
   VIEW object_name
} 
IS 'text'

COMETER

Confirma la transacción actual.

COMMIT [ WORK | TRANSACTION ]

COPIAR

Copie datos entre un archivo y una tabla.

COPY table_name [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
COPY table_name [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

CREAR AGREGADO

Defina una nueva función agregada.

CREATE AGGREGATE name (
   BASETYPE = input_data_type,
   SFUNC = sfunc,
   STYPE = state_data_type
   [, FINALFUNC = ffunc ]
   [, INITCOND = initial_condition ]
)

CREAR REPARTO

Defina un nuevo elenco.

CREATE CAST (source_type AS target_type)
WITH FUNCTION func_name (arg_types)
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]

CREAR DISPARADOR DE RESTRICCIONES

Defina un nuevo activador de restricción.

CREATE CONSTRAINT TRIGGER name
AFTER events ON
table_name constraint attributes
FOR EACH ROW EXECUTE PROCEDURE func_name ( args )

CREAR CONVERSIÓN

Defina una nueva conversión.

CREATE [DEFAULT] CONVERSION name
FOR source_encoding TO dest_encoding FROM func_name

CREAR BASE DE DATOS

Crea una nueva base de datos.

CREATE DATABASE name
[ [ WITH ] [ OWNER [=] db_owner ]
   [ TEMPLATE [=] template ]
   [ ENCODING [=] encoding ]
   [ TABLESPACE [=] tablespace ] 
]

CREAR DOMINIO

Defina un nuevo dominio.

CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]

Donde la restricción es -

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }

CREAR FUNCIÓN

Defina una nueva función.

CREATE [ OR REPLACE ] FUNCTION name ( [ [ arg_name ] arg_type [, ...] ] )
RETURNS ret_type
{ LANGUAGE lang_name
   | IMMUTABLE | STABLE | VOLATILE
   | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
   | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
   | AS 'definition'
   | AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]

CREA UN GRUPO

Defina un nuevo grupo de usuarios.

CREATE GROUP name [ [ WITH ] option [ ... ] ]
Where option can be:
SYSID gid
| USER username [, ...]

CREAR ÍNDICE

Defina un nuevo índice.

CREATE [ UNIQUE ] INDEX name ON table [ USING method ]
( { column | ( expression ) } [ opclass ] [, ...] )
[ TABLESPACE tablespace ]
[ WHERE predicate ]

CREAR IDIOMA

Definir un nuevo lenguaje procedimental.

CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR val_function ]

CREAR OPERADOR

Defina un nuevo operador.

CREATE OPERATOR name (
   PROCEDURE = func_name
   [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
   [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
   [, RESTRICT = res_proc ] [, JOIN = join_proc ]
   [, HASHES ] [, MERGES ]
   [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
   [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
)

CREAR CLASE DE OPERADOR

Defina una nueva clase de operador.

CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type
USING index_method AS
{ OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ]
   | FUNCTION support_number func_name ( argument_type [, ...] )
   | STORAGE storage_type
} [, ... ]

CREAR REGLA

Defina una nueva regla de reescritura.

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

CREAR ESQUEMA

Defina un nuevo esquema.

CREATE SCHEMA schema_name
[ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username
[ schema_element [ ... ] ]

CREAR SECUENCIA

Defina un nuevo generador de secuencias.

CREATE [ TEMPORARY | TEMP ] SEQUENCE name
[ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ]
[ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

CREAR MESA

Defina una nueva tabla.

CREATE [ [ GLOBAL | LOCAL ] { 
   TEMPORARY | TEMP } ] TABLE table_name ( { 
      column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
      | table_constraint
      | LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] 
   } [, ... ]
)
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

Donde column_constraint es -

[ CONSTRAINT constraint_name ] { 
   NOT NULL |
   NULL |
   UNIQUE [ USING INDEX TABLESPACE tablespace ] |
   PRIMARY KEY [ USING INDEX TABLESPACE tablespace ] |
   CHECK (expression) |
   REFERENCES ref_table [ ( ref_column ) ]
   [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
   [ ON DELETE action ] [ ON UPDATE action ] 
}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Y table_constraint es -

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
PRIMARY KEY ( column_name [, ... ] ) [ USING INDEX TABLESPACE tablespace ] |
CHECK ( expression ) |
FOREIGN KEY ( column_name [, ... ] )
REFERENCES ref_table [ ( ref_column [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

CREAR TABLA COMO

Defina una nueva tabla a partir de los resultados de una consulta.

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name
[ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ]
AS query

CREAR TABLESPACE

Defina un nuevo espacio de tabla.

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'

CREAR DISPARADOR

Defina un nuevo disparador.

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE func_name ( arguments )

CREAR TIPO

Defina un nuevo tipo de datos.

CREATE TYPE name AS
( attribute_name data_type [, ... ] )
CREATE TYPE name (
INPUT = input_function,
OUTPUT = output_function
[, RECEIVE = receive_function ]
[, SEND = send_function ]
[, ANALYZE = analyze_function ]
[, INTERNALLENGTH = { internal_length | VARIABLE } ]
[, PASSEDBYVALUE ]
[, ALIGNMENT = alignment ]
[, STORAGE = storage ]
[, DEFAULT = default ]
[, ELEMENT = element ]
[, DELIMITER = delimiter ]
)

CREAR USUARIO

Defina una nueva cuenta de usuario de base de datos.

CREATE USER name [ [ WITH ] option [ ... ] ]

Donde la opción puede ser -

SYSID uid
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| IN GROUP group_name [, ...]
| VALID UNTIL 'abs_time'

CREAR VISTA

Defina una nueva vista.

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query

DESACTIVAR

Desasignar una declaración preparada.

DEALLOCATE [ PREPARE ] plan_name

DECLARAR

Defina un cursor.

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]

ELIMINAR

Elimina filas de una tabla.

DELETE FROM [ ONLY ] table [ WHERE condition ]

AGREGADO DE GOTA

Eliminar una función agregada.

DROP AGGREGATE name ( type ) [ CASCADE | RESTRICT ]

Lanzamiento de gota

Retire un yeso.

DROP CAST (source_type AS target_type) [ CASCADE | RESTRICT ]

CONVERSIÓN DE GOTA

Eliminar una conversión.

DROP CONVERSION name [ CASCADE | RESTRICT ]

DROP DATABASE

Eliminar una base de datos.

DROP DATABASE name

DROP DOMAIN

Eliminar un dominio.

DROP DOMAIN name [, ...] [ CASCADE | RESTRICT ]

FUNCIÓN DROP

Eliminar una función.

DROP FUNCTION name ( [ type [, ...] ] ) [ CASCADE | RESTRICT ]

GRUPO DE GOTA

Eliminar un grupo de usuarios.

DROP GROUP name

ÍNDICE DE GOTA

Eliminar un índice.

DROP INDEX name [, ...] [ CASCADE | RESTRICT ]

LENGUAJE DROP

Eliminar un lenguaje de procedimiento.

DROP [ PROCEDURAL ] LANGUAGE name [ CASCADE | RESTRICT ]

OPERADOR DE GOTA

Eliminar un operador.

DROP OPERATOR name ( { left_type | NONE }, { right_type | NONE } )
[ CASCADE | RESTRICT ]

CLASE DE OPERADOR DE GOTAS

Eliminar una clase de operador.

DROP OPERATOR CLASS name USING index_method [ CASCADE | RESTRICT ]

REGLA DE GOTA

Elimina una regla de reescritura.

DROP RULE name ON relation [ CASCADE | RESTRICT ]

ESQUEMA DE GOTA

Eliminar un esquema.

DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]

SECUENCIA DE GOTA

Eliminar una secuencia.

DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]

MESA PLEGABLE

Retire una mesa.

DROP TABLE name [, ...] [ CASCADE | RESTRICT ]

ESPACIO DE MESA DE GOTA

Eliminar un espacio de tabla.

DROP TABLESPACE tablespace_name

GATILLO DE GOTA

Retire un gatillo.

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]

TIPO DE GOTA

Eliminar un tipo de datos.

DROP TYPE name [, ...] [ CASCADE | RESTRICT ]

DROP USER

Eliminar una cuenta de usuario de la base de datos.

DROP USER name

VISTA GOTA

Eliminar una vista.

DROP VIEW name [, ...] [ CASCADE | RESTRICT ]

FIN

Confirma la transacción actual.

END [ WORK | TRANSACTION ]

EJECUTAR

Ejecute una declaración preparada.

EXECUTE plan_name [ (parameter [, ...] ) ]

EXPLIQUE

Muestre el plan de ejecución de una declaración.

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

IR A BUSCAR

Recupere filas de una consulta usando un cursor.

FETCH [ direction { FROM | IN } ] cursor_name

Donde la dirección puede estar vacía o una de las siguientes:

NEXT
PRIOR
FIRST
LAST
ABSOLUTE count
RELATIVE count
count
ALL
FORWARD
FORWARD count
FORWARD ALL
BACKWARD
BACKWARD count
BACKWARD ALL

CONCEDER

Defina privilegios de acceso.

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

INSERTAR

Crea nuevas filas en una tabla.

INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }

ESCUCHA

Escuche una notificación.

LISTEN name

CARGA

Cargue o vuelva a cargar un archivo de biblioteca compartida.

LOAD 'filename'

BLOQUEAR

Bloquea una mesa.

LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]

Donde lock_mode es uno de -

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE
| SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE

MOVERSE

Coloque un cursor.

MOVE [ direction { FROM | IN } ] cursor_name

NOTIFICAR

Genera una notificación.

NOTIFY name

PREPARAR

Prepare una declaración para su ejecución.

PREPARE plan_name [ (data_type [, ...] ) ] AS statement

REINDEX

Reconstruir índices.

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]

LIBERAR SAVEPOINT

Destruye un punto de guardado previamente definido.

RELEASE [ SAVEPOINT ] savepoint_name

REINICIAR

Restaura el valor de un parámetro de tiempo de ejecución al valor predeterminado.

RESET name
RESET ALL

REVOCAR

Quite los privilegios de acceso.

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON FUNCTION func_name ([type, ...]) [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

RETROCEDER

Abortar la transacción actual.

ROLLBACK [ WORK | TRANSACTION ]

ROLLBACK TO SAVEPOINT

Regrese a un punto de guardado.

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name

PUNTO DE GUARDADO

Defina un nuevo punto de guardado dentro de la transacción actual.

SAVEPOINT savepoint_name

SELECCIONE

Recupere filas de una tabla o vista.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

from_item

[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
function_name ( [ argument [, ...] ] )
[ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item
[ ON join_condition | USING ( join_column [, ...] ) ]

SELECCIONAR EN

Defina una nueva tabla a partir de los resultados de una consulta.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]

CONJUNTO

Cambiar un parámetro de tiempo de ejecución.

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }

ESTABLECER RESTRICCIONES

Establecer modos de comprobación de restricciones para la transacción actual.

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

CONFIGURAR AUTORIZACIÓN DE SESIÓN

Establezca el identificador de usuario de la sesión y el identificador de usuario actual de la sesión actual.

SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
RESET SESSION AUTHORIZATION

CONFIGURAR TRANSACCIÓN

Establece las características de la transacción actual.

SET TRANSACTION transaction_mode [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]

Donde transaction_mode es uno de:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

SHOW

Muestra el valor de un parámetro de tiempo de ejecución.

SHOW name
SHOW ALL

INICIAR TRANSACCIÓN

Inicie un bloque de transacciones.

START TRANSACTION [ transaction_mode [, ...] ]

Donde transaction_mode es uno de:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED
| READ UNCOMMITTED }
READ WRITE | READ ONLY

TRUNCAR

Vacíe una mesa.

TRUNCATE [ TABLE ] name

NO ESCUCHAR

Deja de escuchar una notificación.

UNLISTEN { name | * }

ACTUALIZAR

Actualiza filas de una tabla.

UPDATE [ ONLY ] table SET column = { expression | DEFAULT } [, ...]
[ FROM from_list ]
[ WHERE condition ]

VACÍO

Recolectar basura y, opcionalmente, analizar una base de datos.

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

En este capítulo, analizaremos los tipos de datos utilizados en PostgreSQL. Al crear la tabla, para cada columna, especifica un tipo de datos, es decir, qué tipo de datos desea almacenar en los campos de la tabla.

Esto permite varios beneficios:

Consistency - Las operaciones contra columnas del mismo tipo de datos dan resultados consistentes y suelen ser las más rápidas.
Validation - El uso adecuado de los tipos de datos implica la validación del formato de los datos y el rechazo de los datos fuera del alcance del tipo de datos.
Compactness - Como una columna puede almacenar un solo tipo de valor, se almacena de forma compacta.
Performance- El uso adecuado de los tipos de datos proporciona el almacenamiento de datos más eficiente. Los valores almacenados se pueden procesar rápidamente, lo que mejora el rendimiento.

PostgreSQL admite un amplio conjunto de tipos de datos. Además, los usuarios pueden crear su propio tipo de datos personalizados usando el comando CREATE TYPE SQL. Hay diferentes categorías de tipos de datos en PostgreSQL. Se comentan a continuación.

Tipos numéricos

Los tipos numéricos constan de enteros de dos, cuatro y ocho bytes, números de coma flotante de cuatro y ocho bytes y decimales de precisión seleccionable. La siguiente tabla enumera los tipos disponibles.

Nombre	Tamaño de almacenamiento	Descripción	Rango
pequeño	2 bytes	entero de rango pequeño	-32768 al +32767
entero	4 bytes	elección típica para entero	-2147483648 al +2147483647
Empezando	8 bytes	entero de rango grande	-9223372036854775808 al 9223372036854775807
decimal	variable	precisión especificada por el usuario, exacta	hasta 131072 dígitos antes del punto decimal; hasta 16383 dígitos después del punto decimal
numérico	variable	precisión especificada por el usuario, exacta	hasta 131072 dígitos antes del punto decimal; hasta 16383 dígitos después del punto decimal
real	4 bytes	precisión variable, inexacta	Precisión de 6 dígitos decimales
Precisión doble	8 bytes	precisión variable, inexacta	Precisión de 15 dígitos decimales
pequeña serie	2 bytes	pequeño entero autoincrementante	1 hasta 32767
de serie	4 bytes	entero autoincrementante	1 al 2147483647
bigserial	8 bytes	entero grande autoincrementante	1 al 9223372036854775807

Tipos monetarios

El tipo de dinero almacena una cantidad de moneda con una precisión fraccionaria fija. Los valores de los tipos de datos numérico, int y bigint se pueden convertir en dinero . No se recomienda el uso de números de coma flotante para manejar dinero debido a la posibilidad de errores de redondeo.

Nombre	Tamaño de almacenamiento	Descripción	Rango
dinero	8 bytes	cantidad de moneda	-92233720368547758.08 al +92233720368547758.07

Tipos de caracteres

La tabla que se proporciona a continuación enumera los tipos de caracteres de uso general disponibles en PostgreSQL.

S. No.	Nombre y descripción
1	character varying(n), varchar(n) longitud variable con límite
2	character(n), char(n) de longitud fija, en blanco acolchado
3	text longitud ilimitada variable

Tipos de datos binarios

El tipo de datos bytea permite el almacenamiento de cadenas binarias como se muestra en la siguiente tabla.

Nombre	Tamaño de almacenamiento	Descripción
bytea	1 o 4 bytes más la cadena binaria real	cadena binaria de longitud variable

Tipos de fecha / hora

PostgreSQL admite un conjunto completo de tipos de fecha y hora de SQL, como se muestra en la tabla siguiente. Las fechas se cuentan según el calendario gregoriano. Aquí, todos los tipos tienen resolución de1 microsecond / 14 digits excepto date tipo, cuya resolución es day.

Nombre	Tamaño de almacenamiento	Descripción	Bajo valor	Alto valor
marca de tiempo [(p)] [sin zona horaria]	8 bytes	tanto la fecha como la hora (sin zona horaria)	4713 a. C.	294276 AD
TIMESTAMPTZ	8 bytes	tanto la fecha como la hora, con la zona horaria	4713 a. C.	294276 AD
fecha	4 bytes	fecha (sin hora del día)	4713 a. C.	5874897 AD
hora [(p)] [sin zona horaria]	8 bytes	hora del día (sin fecha)	00:00:00	24:00:00
hora [(p)] con zona horaria	12 bytes	solo horas del día, con zona horaria	00: 00: 00 + 1459	24: 00: 00-1459
intervalo [campos] [(p)]	12 bytes	intervalo de tiempo	-178000000 años	178000000 años

Tipo booleano

PostgreSQL proporciona el tipo de SQL estándar Boolean. El tipo de datos booleano puede tener los estados verdadero , falso y un tercer estado, desconocido , que está representado por el valor nulo de SQL.

Nombre	Tamaño de almacenamiento	Descripción
booleano	1 byte	estado de verdadero o falso

Tipo enumerado

Los tipos enumerados (enum) son tipos de datos que comprenden un conjunto de valores estático y ordenado. Son equivalentes a los tipos de enumeración admitidos en varios lenguajes de programación.

A diferencia de otros tipos, los tipos enumerados deben crearse mediante el comando CREATE TYPE. Este tipo se utiliza para almacenar un conjunto de valores estático y ordenado. Por ejemplo, direcciones de la brújula, es decir, NORTE, SUR, ESTE y OESTE o días de la semana como se muestra a continuación:

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

Enumerado, una vez creado, se puede utilizar como cualquier otro tipo.

Tipo geométrico

Los tipos de datos geométricos representan objetos espaciales bidimensionales. El tipo más fundamental, el punto, forma la base de todos los demás tipos.

Nombre	Tamaño de almacenamiento	Representación	Descripción
punto	16 bytes	Punto en un avión	(x, y)
línea	32 bytes	Línea infinita (no implementada completamente)	((x1, y1), (x2, y2))
lseg	32 bytes	Segmento de línea finita	((x1, y1), (x2, y2))
caja	32 bytes	Caja rectangular	((x1, y1), (x2, y2))
camino	16 + 16n bytes	Camino cerrado (similar al polígono)	((x1, y1), ...)
camino	16 + 16n bytes	Camino abierto	[(x1, y1), ...]
polígono	40 + 16n	Polígono (similar al camino cerrado)	((x1, y1), ...)
circulo	24 bytes	Circulo	<(x, y), r> (punto central y radio)

Tipo de dirección de red

PostgreSQL ofrece tipos de datos para almacenar direcciones IPv4, IPv6 y MAC. Es mejor usar estos tipos en lugar de tipos de texto sin formato para almacenar direcciones de red, porque estos tipos ofrecen verificación de errores de entrada y operadores y funciones especializados.

Nombre	Tamaño de almacenamiento	Descripción
cidr	7 o 19 bytes	Redes IPv4 e IPv6
inet	7 o 19 bytes	Redes y hosts IPv4 e IPv6
macaddr	6 bytes	Direcciones MAC

Tipo de cadena de bits

Los tipos de cadenas de bits se utilizan para almacenar máscaras de bits. Son 0 o 1. Hay dos tipos de bits SQL:bit(n) y bit varying(n), donde n es un número entero positivo.

Tipo de búsqueda de texto

Este tipo admite la búsqueda de texto completo, que es la actividad de buscar a través de una colección de documentos en lenguaje natural para ubicar los que mejor coinciden con una consulta. Hay dos tipos de datos para esto:

S. No.	Nombre y descripción
1	tsvector Esta es una lista ordenada de palabras distintas que se han normalizado para fusionar diferentes variantes de la misma palabra, llamadas "lexemas".
2	tsquery Esto almacena los lexemas que se van a buscar y los combina respetando los operadores booleanos & (Y), \| (O y ! (NO). Se pueden utilizar paréntesis para imponer la agrupación de los operadores.

Tipo de UUID

Un UUID (Universal Unique Identifiers) se escribe como una secuencia de dígitos hexadecimales en minúsculas, en varios grupos separados por guiones, específicamente un grupo de ocho dígitos, seguido de tres grupos de cuatro dígitos, seguido de un grupo de 12 dígitos, para un total de 32 dígitos que representan los 128 bits.

Un ejemplo de UUID es: 550e8400-e29b-41d4-a716-446655440000

Tipo XML

El tipo de datos XML se puede utilizar para almacenar datos XML. Para almacenar datos XML, primero debe crear valores XML utilizando la función xmlparse de la siguiente manera:

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Tipo JSON

El tipo de datos json se puede utilizar para almacenar datos JSON (notación de objetos JavaScript). Dichos datos también se pueden almacenar como texto , pero el tipo de datos json tiene la ventaja de verificar que cada valor almacenado sea un valor JSON válido. También hay funciones de soporte relacionadas disponibles, que se pueden usar directamente para manejar el tipo de datos JSON de la siguiente manera.

Ejemplo	Resultado de ejemplo
array_to_json ('{{1,5}, {99,100}}' :: int [])	[[1,5], [99,100]]
fila_a_json (fila (1, 'foo'))	{"f1": 1, "f2": "foo"}

Tipo de matriz

PostgreSQL brinda la oportunidad de definir una columna de una tabla como una matriz multidimensional de longitud variable. Se pueden crear matrices de cualquier tipo de base, tipo de enumeración o tipo compuesto integrado o definido por el usuario.

Declaración de matrices

El tipo de matriz se puede declarar como

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

o utilizando la palabra clave "ARRAY" como

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

Insertar valores

Los valores de matriz se pueden insertar como una constante literal, encerrando los valores del elemento entre llaves y separándolos por comas. A continuación se muestra un ejemplo:

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

Acceso a matrices

A continuación, se muestra un ejemplo para acceder a las matrices. El comando dado a continuación seleccionará a las personas cuyos ahorros son más en el segundo trimestre que en el cuarto trimestre.

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

Modificar matrices

Un ejemplo de modificación de matrices se muestra a continuación.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

o usando la sintaxis de expresión ARRAY -

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

Buscando matrices

Un ejemplo de búsqueda de matrices se muestra a continuación.

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

Si se conoce el tamaño de la matriz, se puede utilizar el método de búsqueda indicado anteriormente. De lo contrario, el siguiente ejemplo muestra cómo buscar cuando no se conoce el tamaño.

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

Tipos compuestos

Este tipo representa una lista de nombres de campo y sus tipos de datos, es decir, estructura de una fila o registro de una tabla.

Declaración de tipos compuestos

El siguiente ejemplo muestra cómo declarar un tipo compuesto

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
);

Este tipo de datos se puede utilizar en las tablas de creación como se muestra a continuación:

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

Entrada de valor compuesto

Los valores compuestos se pueden insertar como una constante literal, encerrando los valores de campo entre paréntesis y separándolos por comas. A continuación se muestra un ejemplo:

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Esto es válido para el elemento de inventario definido anteriormente. La palabra clave ROW es realmente opcional siempre que tenga más de un campo en la expresión.

Acceso a tipos compuestos

Para acceder a un campo de una columna compuesta, utilice un punto seguido del nombre del campo, de forma muy similar a seleccionar un campo de un nombre de tabla. Por ejemplo, para seleccionar algunos subcampos de nuestra tabla de ejemplo on_hand, la consulta sería como se muestra a continuación:

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

Incluso puede usar el nombre de la tabla también (por ejemplo, en una consulta de múltiples tablas), así:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Tipos de rango

Los tipos de rango representan tipos de datos que utilizan un rango de datos. El tipo de rango puede ser rangos discretos (por ejemplo, todos los valores enteros de 1 a 10) o rangos continuos (por ejemplo, cualquier momento entre las 10:00 am y las 11:00 am).

Los tipos de rango integrados disponibles incluyen los siguientes rangos:

int4range - Rango de entero
int8range - Rango de bigint
numrange - Rango de numérico
tsrange - Rango de marca de tiempo sin zona horaria
tstzrange - Rango de marca de tiempo con zona horaria
daterange - Rango de fecha

Se pueden crear tipos de rango personalizados para que estén disponibles nuevos tipos de rangos, como rangos de direcciones IP usando el tipo inet como base, o rangos flotantes usando el tipo de datos flotante como base.

Los tipos de rango admiten límites de rango inclusivos y exclusivos utilizando los caracteres [] y (), respectivamente. Por ejemplo, '[4,9)' representa todos los números enteros comenzando desde e incluyendo 4 hasta pero sin incluir 9.

Tipos de identificadores de objetos

PostgreSQL utiliza internamente identificadores de objetos (OID) como claves principales para varias tablas del sistema. Si se especifica WITH OIDS o la variable de configuración default_with_oids está habilitada, solo entonces, en tales casos, los OID se agregan a las tablas creadas por el usuario. La siguiente tabla enumera varios tipos de alias. Los tipos de alias de OID no tienen operaciones propias, excepto las rutinas especializadas de entrada y salida.

Nombre	Referencias	Descripción	Ejemplo de valor
oid	ninguna	identificador de objeto numérico	564182
regproc	pg_proc	nombre de la función	suma
reglamento	pg_proc	función con tipos de argumentos	suma (int4)
regoper	pg_operator	nombre del operador	+
regoperador	pg_operator	operador con tipos de argumentos	* (entero, entero) o - (NINGUNO, entero)
regclass	pg_class	nombre de la relación	pg_type
regtipo	pg_type	nombre del tipo de datos	entero
regconfig	pg_ts_config	configuración de búsqueda de texto	Inglés
regdiccionario	pg_ts_dict	diccionario de búsqueda de texto	simple

Pseudo tipos

El sistema de tipos de PostgreSQL contiene una serie de entradas de propósito especial que se denominan colectivamente pseudo-tipos. Un pseudo-tipo no se puede usar como un tipo de datos de columna, pero se puede usar para declarar el argumento de una función o el tipo de resultado.

La tabla que se proporciona a continuación enumera los pseudo-tipos existentes.

S. No.	Nombre y descripción
1	any Indica que una función acepta cualquier tipo de datos de entrada.
2	anyelement Indica que una función acepta cualquier tipo de datos.
3	anyarray Indica que una función acepta cualquier tipo de datos de matriz.
4	anynonarray Indica que una función acepta cualquier tipo de datos que no sea de matriz.
5	anyenum Indica que una función acepta cualquier tipo de datos de enumeración.
6	anyrange Indica que una función acepta cualquier tipo de datos de rango.
7	cstring Indica que una función acepta o devuelve una cadena C terminada en nulo.
8	internal Indica que una función acepta o devuelve un tipo de datos interno del servidor.
9	language_handler Se declara que un controlador de llamadas de lenguaje procedimental devuelve language_handler.
10	fdw_handler Se declara que un controlador de contenedor de datos externos devuelve fdw_handler.
11	record Identifica una función que devuelve un tipo de fila no especificado.
12	trigger Se declara que una función de disparo devuelve un disparador.
13	void Indica que una función no devuelve ningún valor.

Este capítulo trata sobre cómo crear una nueva base de datos en su PostgreSQL. PostgreSQL proporciona dos formas de crear una nueva base de datos:

Usando CREATE DATABASE, un comando SQL.
Usando createdb un ejecutable de línea de comandos.

Usando CREATE DATABASE

Este comando creará una base de datos desde el indicador de shell de PostgreSQL, pero debe tener el privilegio adecuado para crear una base de datos. De forma predeterminada, la nueva base de datos se creará clonando la plantilla1 de la base de datos del sistema estándar .

Sintaxis

La sintaxis básica de la instrucción CREATE DATABASE es la siguiente:

CREATE DATABASE dbname;

donde dbname es el nombre de una base de datos para crear.

Ejemplo

El siguiente es un ejemplo simple, que creará testdb en su esquema de PostgreSQL

postgres=# CREATE DATABASE testdb;
postgres-#

Usando el comando createdb

El ejecutable de línea de comandos de PostgreSQL createdb es un envoltorio del comando SQL CREATE DATABASE . La única diferencia entre este comando y el comando SQL CREATE DATABASE es que el primero se puede ejecutar directamente desde la línea de comandos y permite agregar un comentario a la base de datos, todo en un solo comando.

Sintaxis

La sintaxis de createdb es la que se muestra a continuación:

createdb [option...] [dbname [description]]

Parámetros

La tabla que se proporciona a continuación enumera los parámetros con sus descripciones.

S. No.	Descripción de parámetros
1	dbname El nombre de una base de datos para crear.
2	description Especifica un comentario que se asociará con la base de datos recién creada.
3	options argumentos de línea de comandos, que createdb acepta.

Opciones

La siguiente tabla enumera los argumentos de la línea de comandos que createdb acepta:

S. No.	Opción y descripción
1	-D tablespace Especifica el espacio de tabla predeterminado para la base de datos.
2	-e Haga eco de los comandos que createdb genera y envía al servidor.
3	-E encoding Especifica el esquema de codificación de caracteres que se utilizará en esta base de datos.
4	-l locale Especifica la configuración regional que se utilizará en esta base de datos.
5	-T template Especifica la base de datos de plantilla a partir de la cual construir esta base de datos.
6	--help Muestre ayuda sobre los argumentos de la línea de comando createdb y salga.
7	-h host Especifica el nombre de host de la máquina en la que se ejecuta el servidor.
8	-p port Especifica el puerto TCP o la extensión del archivo de socket de dominio Unix local en el que el servidor está escuchando conexiones.
9	-U username Nombre de usuario para conectarse como.
10	-w Nunca emita una solicitud de contraseña.
11	-W Obligar a createdb a solicitar una contraseña antes de conectarse a una base de datos.

Abra el símbolo del sistema y vaya al directorio donde está instalado PostgreSQL. Vaya al directorio bin y ejecute el siguiente comando para crear una base de datos.

createdb -h localhost -p 5432 -U postgres testdb
password ******

El comando anterior le pedirá la contraseña del usuario administrador de PostgreSQL, que es postgres, por defecto. Por lo tanto, proporcione una contraseña y proceda a crear su nueva base de datos

Una vez que se crea una base de datos usando cualquiera de los métodos mencionados anteriormente, puede verificarla en la lista de bases de datos usando \l, es decir, la barra invertida el comando de la siguiente manera:

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

postgres-#

Este capítulo explica varios métodos para acceder a la base de datos. Suponga que ya hemos creado una base de datos en nuestro capítulo anterior. Puede seleccionar la base de datos utilizando cualquiera de los siguientes métodos:

Solicitud de SQL de la base de datos
Símbolo del sistema del sistema operativo

Solicitud de SQL de la base de datos

Suponga que ya ha iniciado su cliente PostgreSQL y ha llegado al siguiente indicador de SQL:

postgres=#

Puede verificar la lista de bases de datos disponibles usando \l, es decir, la barra invertida el comando de la siguiente manera:

postgres-# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 testdb    | postgres | UTF8     | C       | C     | 
(4 rows)

postgres-#

Ahora, escriba el siguiente comando para conectar / seleccionar la base de datos deseada; aquí, nos conectaremos a la base de datos testdb .

postgres=# \c testdb;
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

Símbolo del sistema del sistema operativo

Puede seleccionar su base de datos desde el símbolo del sistema en el momento en que inicie sesión en su base de datos. A continuación se muestra un ejemplo simple:

psql -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****
psql (9.2.4)
Type "help" for help.
You are now connected to database "testdb" as user "postgres".
testdb=#

Ahora ha iniciado sesión en PostgreSQL testdb y está listo para ejecutar sus comandos dentro de testdb. Para salir de la base de datos, puede usar el comando \ q.

En este capítulo, discutiremos cómo eliminar la base de datos en PostgreSQL. Hay dos opciones para eliminar una base de datos:

Usando DROP DATABASE, un comando SQL.
Usando dropdb un ejecutable de línea de comandos.

Tenga cuidado antes de usar esta operación porque eliminar una base de datos existente resultaría en la pérdida de toda la información almacenada en la base de datos.

Usando DROP DATABASE

Este comando elimina una base de datos. Elimina las entradas del catálogo de la base de datos y elimina el directorio que contiene los datos. Solo puede ser ejecutado por el propietario de la base de datos. Este comando no se puede ejecutar mientras usted o cualquier otra persona esté conectado a la base de datos de destino (conéctese a postgres o cualquier otra base de datos para emitir este comando).

Sintaxis

La sintaxis de DROP DATABASE se proporciona a continuación:

DROP DATABASE [ IF EXISTS ] name

Parámetros

La tabla enumera los parámetros con sus descripciones.

S. No.	Descripción de parámetros
1	IF EXISTS No arroje un error si la base de datos no existe. En este caso, se emite un aviso.
2	name El nombre de la base de datos que se eliminará.

No podemos eliminar una base de datos que tenga conexiones abiertas, incluida nuestra propia conexión desde psql o pgAdmin III . Debemos cambiar a otra base de datos o plantilla1 si queremos eliminar la base de datos a la que estamos conectados actualmente. Por lo tanto, podría ser más conveniente usar el programa dropdb en su lugar, que es un envoltorio de este comando.

Ejemplo

El siguiente es un ejemplo simple, que eliminará testdb desde su esquema de PostgreSQL -

postgres=# DROP DATABASE testdb;
postgres-#

Usando el comando dropdb

Ejecutable de línea de comando PostgresSQL dropdbes un contenedor de línea de comandos alrededor del comando SQL DROP DATABASE . No existe una diferencia efectiva entre eliminar bases de datos a través de esta utilidad y a través de otros métodos para acceder al servidor. dropdb destruye una base de datos PostgreSQL existente. El usuario que ejecuta este comando debe ser un superusuario de la base de datos o el propietario de la base de datos.

Sintaxis

La sintaxis de dropdb es la que se muestra a continuación:

dropdb  [option...] dbname

Parámetros

La siguiente tabla enumera los parámetros con sus descripciones

S. No.	Descripción de parámetros
1	dbname El nombre de una base de datos que se eliminará.
2	option argumentos de la línea de comandos, que dropdb acepta.

Opciones

La siguiente tabla enumera los argumentos de la línea de comandos que dropdb acepta:

S. No.	Opción y descripción
1	-e Muestra los comandos que se envían al servidor.
2	-i Emite un mensaje de verificación antes de hacer algo destructivo.
3	-V Imprima la versión dropdb y salga.
4	--if-exists No arroje un error si la base de datos no existe. En este caso, se emite un aviso.
5	--help Muestre ayuda sobre los argumentos de la línea de comandos de dropdb y salga.
6	-h host Especifica el nombre de host de la máquina en la que se ejecuta el servidor.
7	-p port Especifica el puerto TCP o la extensión del archivo de socket del dominio UNIX local en el que el servidor está escuchando conexiones.
8	-U username Nombre de usuario para conectarse como.
9	-w Nunca emita una solicitud de contraseña.
10	-W Forzar dropdb para solicitar una contraseña antes de conectarse a una base de datos.
11	--maintenance-db=dbname Especifica el nombre de la base de datos a la que conectarse para descartar la base de datos de destino.

Ejemplo

El siguiente ejemplo demuestra cómo eliminar una base de datos del símbolo del sistema del sistema operativo:

dropdb -h localhost -p 5432 -U postgress testdb
Password for user postgress: ****

El comando anterior elimina la base de datos testdb. Aquí, he usado elpostgres (que se encuentra en pg_roles de template1) nombre de usuario para eliminar la base de datos.

La declaración CREATE TABLE de PostgreSQL se usa para crear una nueva tabla en cualquiera de las bases de datos.

Sintaxis

La sintaxis básica de la instrucción CREATE TABLE es la siguiente:

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

CREAR TABLA es una palabra clave que le dice al sistema de base de datos que cree una tabla nueva. El nombre o identificador exclusivo de la tabla sigue a la instrucción CREATE TABLE. Inicialmente, la tabla vacía en la base de datos actual es propiedad del usuario que emite el comando.

Luego, entre paréntesis, viene la lista, que define cada columna de la tabla y qué tipo de tipo de datos es. La sintaxis se aclarará con un ejemplo a continuación.

Ejemplos

El siguiente es un ejemplo, que crea una tabla EMPRESA con ID como clave principal y NOT NULL son las restricciones que muestran que estos campos no pueden ser NULL al crear registros en esta tabla:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Creemos una tabla más, que usaremos en nuestros ejercicios en los capítulos siguientes:

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Puede verificar si su tabla se ha creado correctamente utilizando \d comando, que se utilizará para enumerar todas las tablas en una base de datos adjunta.

testdb-# \d

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

Utilizar \d tablename para describir cada tabla como se muestra a continuación -

testdb-# \d company

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

Table "public.company"
  Column   |     Type      | Modifiers
-----------+---------------+-----------
 id        | integer       | not null
 name      | text          | not null
 age       | integer       | not null
 address   | character(50) |
 salary    | real          |
 join_date | date          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)

La declaración DROP TABLE de PostgreSQL se usa para eliminar una definición de tabla y todos los datos, índices, reglas, disparadores y restricciones asociados para esa tabla.

Debe tener cuidado al usar este comando porque una vez que se elimina una tabla, toda la información disponible en la tabla también se perderá para siempre.

Sintaxis

La sintaxis básica de la instrucción DROP TABLE es la siguiente:

DROP TABLE table_name;

Ejemplo

Habíamos creado las tablas DEPARTAMENTO y EMPRESA en el capítulo anterior. Primero, verifique estas tablas (use\d para enumerar las tablas) -

testdb-# \d

Esto produciría el siguiente resultado:

List of relations
 Schema |    Name    | Type  |  Owner
--------+------------+-------+----------
 public | company    | table | postgres
 public | department | table | postgres
(2 rows)

Esto significa que las tablas DEPARTMENT y COMPANY están presentes. Así que dejémoslos caer de la siguiente manera:

testdb=# drop table department, company;

Esto produciría el siguiente resultado:

DROP TABLE
testdb=# \d
relations found.
testdb=#

El mensaje devuelto DROP TABLE indica que el comando drop se ejecutó correctamente.

UN schemaes una colección de tablas con nombre. Un esquema también puede contener vistas, índices, secuencias, tipos de datos, operadores y funciones. Los esquemas son análogos a los directorios en el nivel del sistema operativo, excepto que los esquemas no se pueden anidar. La declaración de PostgreSQL CREATE SCHEMA crea un esquema.

Sintaxis

La sintaxis básica de CREATE SCHEMA es la siguiente:

CREATE SCHEMA name;

Donde nombre es el nombre del esquema.

Sintaxis para crear una tabla en esquema

La sintaxis básica para crear una tabla en el esquema es la siguiente:

CREATE TABLE myschema.mytable (
...
);

Ejemplo

Veamos un ejemplo para crear un esquema. Conéctese a la base de datos testdb y cree un esquema myschema de la siguiente manera:

testdb=# create schema myschema;
CREATE SCHEMA

El mensaje "CREAR ESQUEMA" significa que el esquema se creó correctamente.

Ahora, creemos una tabla en el esquema anterior de la siguiente manera:

testdb=# create table myschema.company(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25),
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Esto creará una tabla vacía. Puede verificar la tabla creada con el comando que se proporciona a continuación:

testdb=# select * from myschema.company;

Esto produciría el siguiente resultado:

id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

Sintaxis para eliminar el esquema

Para eliminar un esquema si está vacío (todos los objetos en él se han eliminado), use el comando -

DROP SCHEMA myschema;

Para eliminar un esquema que incluya todos los objetos contenidos, use el comando -

DROP SCHEMA myschema CASCADE;

Ventajas de usar un esquema

Permite que muchos usuarios utilicen una base de datos sin interferir entre sí.
Organiza los objetos de la base de datos en grupos lógicos para hacerlos más manejables.
Las aplicaciones de terceros se pueden colocar en esquemas separados para que no choquen con los nombres de otros objetos.

El PostgreSQL INSERT INTOinstrucción permite insertar nuevas filas en una tabla. Se puede insertar una sola fila a la vez o varias filas como resultado de una consulta.

Sintaxis

La sintaxis básica de la instrucción INSERT INTO es la siguiente:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);

Aquí, column1, column2, ... columnN son los nombres de las columnas de la tabla en la que desea insertar datos.
Los nombres de las columnas de destino se pueden enumerar en cualquier orden. Los valores proporcionados por la cláusula o consulta VALUES están asociados con la lista de columnas explícita o implícita de izquierda a derecha.

Es posible que no necesite especificar el nombre de la (s) columna (s) en la consulta SQL si está agregando valores para todas las columnas de la tabla. Sin embargo, asegúrese de que el orden de los valores esté en el mismo orden que las columnas de la tabla. La sintaxis SQL INSERT INTO sería la siguiente:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Salida

La siguiente tabla resume los mensajes de salida y su significado:

S. No.	Mensaje de salida y descripción
1	INSERT oid 1 Mensaje devuelto si solo se insertó una fila. oid es el OID numérico de la fila insertada.
2	INSERT 0 # Mensaje devuelto si se insertaron más de una fila. # es el número de filas insertadas.

Ejemplos

Creemos la tabla EMPRESA en testdb como sigue -

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   JOIN_DATE	  DATE
);

El siguiente ejemplo inserta una fila en la tabla EMPRESA:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');

El siguiente ejemplo es insertar una fila; aquí se omite la columna de salario y, por lo tanto, tendrá el valor predeterminado -

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,JOIN_DATE) VALUES (2, 'Allen', 25, 'Texas', '2007-12-13');

El siguiente ejemplo utiliza la cláusula DEFAULT para la columna JOIN_DATE en lugar de especificar un valor:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Teddy', 23, 'Norway', 20000.00, DEFAULT );

El siguiente ejemplo inserta varias filas utilizando la sintaxis de VALORES de varias filas:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00, '2007-12-13' ), (5, 'David', 27, 'Texas', 85000.00, '2007-12-13');

Todas las declaraciones anteriores crearían los siguientes registros en la tabla EMPRESA. El siguiente capítulo le enseñará cómo mostrar todos estos registros desde una tabla.

ID        NAME        AGE        ADDRESS     SALARY	  JOIN_DATE
----      ----------  -----      ----------  -------      --------
1         Paul        32         California  20000.0      2001-07-13
2         Allen       25         Texas                    2007-12-13
3         Teddy       23         Norway      20000.0
4         Mark        25         Rich-Mond   65000.0      2007-12-13
5         David       27         Texas       85000.0      2007-12-13

PostgreSQL SELECTLa declaración se utiliza para obtener los datos de una tabla de base de datos, que devuelve datos en forma de tabla de resultados. Estas tablas de resultados se denominan conjuntos de resultados.

Sintaxis

La sintaxis básica de la instrucción SELECT es la siguiente:

SELECT column1, column2, columnN FROM table_name;

Aquí, column1, column2 ... son los campos de una tabla, cuyos valores desea obtener. Si desea obtener todos los campos disponibles en el campo, puede usar la siguiente sintaxis:

SELECT * FROM table_name;

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

El siguiente es un ejemplo, que obtendría los campos ID, Nombre y Salario de los clientes disponibles en la tabla CLIENTES:

testdb=# SELECT ID, NAME, SALARY FROM COMPANY ;

Esto produciría el siguiente resultado:

id | name  | salary
 ----+-------+--------
   1 | Paul  |  20000
   2 | Allen |  15000
   3 | Teddy |  20000
   4 | Mark  |  65000
   5 | David |  85000
   6 | Kim   |  45000
   7 | James |  10000
(7 rows)

Si desea obtener todos los campos de la tabla CLIENTES, utilice la siguiente consulta:

testdb=# SELECT * FROM COMPANY;

Esto produciría el siguiente resultado:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

¿Qué es un operador en PostgreSQL?

Un operador es una palabra reservada o un carácter que se utiliza principalmente en la cláusula WHERE de una declaración de PostgreSQL para realizar operaciones, como comparaciones y operaciones aritméticas.

Los operadores se utilizan para especificar condiciones en una declaración de PostgreSQL y para servir como conjunciones para múltiples condiciones en una declaración.

Operadores aritméticos
Operadores de comparación
Operadores logicos
Operadores bit a bit

Operadores aritméticos de PostgreSQL

Asumir variable a contiene 2 y variable b sostiene 3, luego -

Ejemplo

Operador	Descripción	Ejemplo
+	Adición: agrega valores a ambos lados del operador	a + b dará 5
-	Resta: resta el operando de la mano derecha del operando de la mano izquierda	a - b dará -1
*	Multiplicación: multiplica los valores a ambos lados del operador	a * b dará 6
/	División: divide el operando de la izquierda por el operando de la derecha	b / a dará 1
%	Módulo: divide el operando de la izquierda por el operando de la derecha y devuelve el resto	b% a dará 1
^	Exponenciación: da el valor del exponente del operando de la derecha.	a ^ b dará 8
\| /	raíz cuadrada	\| / 25.0 dará 5
\|\| /	raíz cúbica	\|\| / 27.0 dará 3
!	factorial	5! dará 120
!!	factorial (operador de prefijo)	!! 5 darán 120

Operadores de comparación de PostgreSQL

Suponga que la variable a tiene 10 y la variable b tiene 20, entonces -

Mostrar ejemplos

Operador	Descripción	Ejemplo
=	Comprueba si los valores de dos operandos son iguales o no, si es así, la condición se convierte en verdadera.	(a = b) no es cierto.
! =	Comprueba si los valores de dos operandos son iguales o no, si los valores no son iguales, la condición se convierte en verdadera.	(a! = b) es cierto.
<>	Comprueba si los valores de dos operandos son iguales o no, si los valores no son iguales, la condición se convierte en verdadera.	(a <> b) es cierto.
>	Comprueba si el valor del operando izquierdo es mayor que el valor del operando derecho, si es así, la condición se convierte en verdadera.	(a> b) no es cierto.
<	Comprueba si el valor del operando izquierdo es menor que el valor del operando derecho, si es así, la condición se convierte en verdadera.	(a <b) es cierto.
> =	Comprueba si el valor del operando izquierdo es mayor o igual que el valor del operando derecho, si es así, la condición se convierte en verdadera.	(a> = b) no es cierto.
<=	Comprueba si el valor del operando izquierdo es menor o igual que el valor del operando derecho, si es así, la condición se convierte en verdadera.	(a <= b) es cierto.

Operadores lógicos de PostgreSQL

Aquí hay una lista de todos los operadores lógicos disponibles en PostgresSQL.

Mostrar ejemplos

S. No.	Operador y descripción
1	AND El operador AND permite la existencia de múltiples condiciones en la cláusula WHERE de una declaración de PostgresSQL.
2	NOT El operador NOT invierte el significado del operador lógico con el que se utiliza. P.ej. NO EXISTE, NO ENTRE, NO EN, etc.This is negate operator.
3	OR El operador OR se utiliza para combinar varias condiciones en la cláusula WHERE de una declaración de PostgresSQL.

Operadores de cadena de bits de PostgreSQL

El operador bit a bit trabaja en bits y realiza operaciones bit a bit. La tabla de verdad para & y | es como sigue -

pags	q	p & q	p \| q
0	0	0	0
0	1	0	1
1	1	1	1
1	0	0	1

Suponga si A = 60; y B = 13; ahora en formato binario serán los siguientes:

A = 0011 1100

B = 0000 1101

-----------------

A&B = 0000 1100

A | B = 0011 1101

~ A = 1100 0011

Mostrar ejemplos

Los operadores Bitwise admitidos por PostgreSQL se enumeran en la siguiente tabla:

Operador	Descripción	Ejemplo
Y	El operador AND binario copia un bit al resultado si existe en ambos operandos.	(A & B) dará 12 que es 0000 1100
\|	El operador OR binario copia un bit si existe en cualquiera de los operandos.	(A \| B) dará 61 que es 0011 1101
~	El operador de complemento binario es unario y tiene el efecto de "voltear" bits.	(~ A) dará -61 que es 1100 0011 en forma de complemento a 2 debido a un número binario con signo.
<<	Operador binario de cambio a la izquierda. El valor de los operandos de la izquierda se mueve a la izquierda el número de bits especificado por el operando de la derecha.	Un << 2 dará 240 que es 1111 0000
>>	Operador de cambio a la derecha binario. El valor de los operandos de la izquierda se mueve hacia la derecha el número de bits especificado por el operando de la derecha.	A >> 2 dará 15 que es 0000 1111
#	XOR bit a bit.	A # B dará 49 que es 0100 1001

Una expresión es una combinación de uno o más valores, operadores y funciones de PostgresSQL que evalúan a un valor.

Las EXPRESIONES de PostgreSQL son como fórmulas y están escritas en lenguaje de consulta. También puede utilizar para consultar la base de datos para un conjunto específico de datos.

Sintaxis

Considere la sintaxis básica de la instrucción SELECT de la siguiente manera:

SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION | EXPRESSION];

Hay diferentes tipos de expresiones de PostgreSQL, que se mencionan a continuación:

PostgreSQL - Expresiones booleanas

Las expresiones booleanas de PostgreSQL obtienen los datos sobre la base de un valor único coincidente. A continuación se muestra la sintaxis:

SELECT column1, column2, columnN
FROM table_name
WHERE SINGLE VALUE MATCHTING EXPRESSION;

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Aquí está el ejemplo simple que muestra el uso de expresiones booleanas de PostgreSQL:

testdb=# SELECT * FROM COMPANY WHERE SALARY = 10000;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address  | salary
----+-------+-----+----------+--------
  7 | James |  24 | Houston  |  10000
(1 row)

PostgreSQL - Expresión numérica

Estas expresiones se utilizan para realizar cualquier operación matemática en cualquier consulta. A continuación se muestra la sintaxis:

SELECT numerical_expression as  OPERATION_NAME
[FROM table_name WHERE CONDITION] ;

En este caso, numerical_expression se usa para expresiones matemáticas o cualquier fórmula. A continuación se muestra un ejemplo simple que muestra el uso de expresiones numéricas SQL:

testdb=# SELECT (15 + 6) AS ADDITION ;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

addition
----------
       21
(1 row)

Hay varias funciones integradas como avg (), sum (), count () para realizar lo que se conoce como cálculos de datos agregados contra una tabla o una columna de tabla específica.

testdb=# SELECT COUNT(*) AS "RECORDS" FROM COMPANY;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

RECORDS
---------
       7
(1 row)

PostgreSQL - Expresiones de fecha

Las expresiones de fecha devuelven los valores de fecha y hora del sistema actual y estas expresiones se utilizan en diversas manipulaciones de datos.

testdb=#  SELECT CURRENT_TIMESTAMP;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

now
-------------------------------
 2013-05-06 14:38:28.078+05:30
(1 row)

La cláusula WHERE de PostgreSQL se utiliza para especificar una condición mientras se obtienen los datos de una sola tabla o se unen con varias tablas.

Si se cumple la condición dada, solo entonces devuelve un valor específico de la tabla. Puede filtrar las filas que no desea incluir en el conjunto de resultados utilizando la cláusula WHERE.

La cláusula WHERE no solo se usa en la instrucción SELECT, sino que también se usa en la instrucción UPDATE, DELETE, etc., que examinaremos en capítulos posteriores.

Sintaxis

La sintaxis básica de la instrucción SELECT con la cláusula WHERE es la siguiente:

SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]

Puede especificar una condición de búsqueda utilizando operadores lógicos o de comparación. like>, <, =, LIKE, NOT, etc. Los siguientes ejemplos aclararían este concepto.

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Aquí hay ejemplos simples que muestran el uso de operadores lógicos de PostgreSQL. La siguiente instrucción SELECT mostrará una lista de todos los registros donde la EDAD es mayor o igual a 25AND el salario es mayor o igual a 65000,00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age |  address   | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

La siguiente instrucción SELECT enumera todos los registros donde EDAD es mayor o igual a 25 OR el salario es mayor o igual a 65000,00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(4 rows)

La siguiente instrucción SELECT enumera todos los registros donde AGE no es NULL, lo que significa todos los registros, porque ninguno de los registros tiene AGE igual a NULL -

testdb=#  SELECT * FROM COMPANY WHERE AGE IS NOT NULL;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   1 | Paul  |  32 | California |  20000
   2 | Allen |  25 | Texas      |  15000
   3 | Teddy |  23 | Norway     |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   5 | David |  27 | Texas      |  85000
   6 | Kim   |  22 | South-Hall |  45000
   7 | James |  24 | Houston    |  10000
(7 rows)

La siguiente instrucción SELECT enumera todos los registros donde NAME comienza con 'Pa', sin importar lo que venga después de 'Pa'.

testdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name | age |address    | salary
----+------+-----+-----------+--------
  1 | Paul |  32 | California|  20000

La siguiente instrucción SELECT enumera todos los registros donde el valor AGE es 25 o 27 -

testdb=# SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

La siguiente instrucción SELECT enumera todos los registros donde el valor AGE no es ni 25 ni 27 -

testdb=# SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(4 rows)

La siguiente instrucción SELECT enumera todos los registros donde el valor de EDAD está ENTRE 25 Y 27 -

testdb=# SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(3 rows)

La siguiente instrucción SELECT hace uso de la subconsulta SQL donde la subconsulta encuentra todos los registros con el campo AGE que tiene SALARIO> 65000 y luego se usa la cláusula WHERE junto con el operador EXISTS para enumerar todos los registros donde existe AGE de la consulta externa en el resultado devuelto por subconsulta -

testdb=# SELECT AGE FROM COMPANY
        WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

age
-----
  32
  25
  23
  25
  27
  22
  24
(7 rows)

La siguiente instrucción SELECT hace uso de la subconsulta SQL donde la subconsulta encuentra todos los registros con el campo AGE que tiene SALARIO> 65000 y la cláusula WHERE posterior se está usando junto con el operador> para enumerar todos los registros donde la consulta AGE desde el resultado devuelto por la subconsulta -

testdb=# SELECT * FROM COMPANY
        WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name | age | address    | salary
----+------+-----+------------+--------
  1 | Paul |  32 | California |  20000

El PostgreSQL AND y ORLos operadores se utilizan para combinar varias condiciones para reducir los datos seleccionados en una declaración de PostgreSQL. Estos dos operadores se denominan operadores conjuntivos.

Estos operadores proporcionan un medio para realizar múltiples comparaciones con diferentes operadores en la misma declaración de PostgreSQL.

El operador AND

los ANDEl operador permite la existencia de múltiples condiciones en la cláusula WHERE de una declaración de PostgreSQL. Al usar el operador AND, la condición completa se asumirá como verdadera cuando todas las condiciones sean verdaderas. Por ejemplo, [condición1] Y [condición2] serán verdaderas solo cuando tanto condición1 como condición2 sean verdaderas.

Sintaxis

La sintaxis básica del operador AND con la cláusula WHERE es la siguiente:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

Puede combinar N número de condiciones utilizando el operador AND. Para que la instrucción PostgreSQL lleve a cabo una acción, ya sea una transacción o una consulta, todas las condiciones separadas por AND deben ser VERDADERAS.

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

La siguiente instrucción SELECT enumera todos los registros donde EDAD es mayor o igual a 25 AND el salario es mayor o igual a 65000,00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(2 rows)

El operador OR

El operador OR también se utiliza para combinar varias condiciones en la cláusula WHERE de una declaración de PostgreSQL. Al usar el operador OR, la condición completa se asumirá como verdadera cuando al menos alguna de las condiciones sea verdadera. Por ejemplo, [condición1] O [condición2] será verdadera si condición1 o condición2 es verdadera.

Sintaxis

La sintaxis básica del operador OR con la cláusula WHERE es la siguiente:

SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

Puede combinar N número de condiciones utilizando el operador OR. Para que la instrucción PostgreSQL lleve a cabo una acción, ya sea una transacción o una consulta, solo UNA de las condiciones separadas por OR debe ser VERDADERA.

Ejemplo

Considere la tabla EMPRESA , que tiene los siguientes registros:

# select * from COMPANY;
  id | name  | age | address   | salary
 ----+-------+-----+-----------+--------
   1 | Paul  |  32 | California|  20000
   2 | Allen |  25 | Texas     |  15000
   3 | Teddy |  23 | Norway    |  20000
   4 | Mark  |  25 | Rich-Mond |  65000
   5 | David |  27 | Texas     |  85000
   6 | Kim   |  22 | South-Hall|  45000
   7 | James |  24 | Houston   |  10000
(7 rows)

La siguiente instrucción SELECT enumera todos los registros donde EDAD es mayor o igual a 25 OR el salario es mayor o igual a 65000,00 -

testdb=# SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(4 rows)

El PostgreSQL UPDATELa consulta se utiliza para modificar los registros existentes en una tabla. Puede usar la cláusula WHERE con la consulta UPDATE para actualizar las filas seleccionadas. De lo contrario, se actualizarían todas las filas.

Sintaxis

La sintaxis básica de la consulta UPDATE con la cláusula WHERE es la siguiente:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

Puede combinar N número de condiciones utilizando operadores AND u OR.

Ejemplo

Considere la tabla EMPRESA , que tiene registros de la siguiente manera:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

El siguiente es un ejemplo, que actualizaría ADDRESS para un cliente, cuyo ID es 6 -

testdb=# UPDATE COMPANY SET SALARY = 15000 WHERE ID = 3;

Ahora, la tabla EMPRESA tendría los siguientes registros:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  3 | Teddy |  23 | Norway     |  15000
(7 rows)

Si desea modificar todos los valores de la columna DIRECCIÓN y SALARIO en la tabla COMPAÑÍA, no necesita usar la cláusula DONDE y la consulta ACTUALIZAR sería la siguiente:

testdb=# UPDATE COMPANY SET ADDRESS = 'Texas', SALARY=20000;

Ahora, la tabla EMPRESA tendrá los siguientes registros:

id | name  | age | address | salary
----+-------+-----+---------+--------
  1 | Paul  |  32 | Texas   |  20000
  2 | Allen |  25 | Texas   |  20000
  4 | Mark  |  25 | Texas   |  20000
  5 | David |  27 | Texas   |  20000
  6 | Kim   |  22 | Texas   |  20000
  7 | James |  24 | Texas   |  20000
  3 | Teddy |  23 | Texas   |  20000
(7 rows)

El PostgreSQL DELETELa consulta se utiliza para eliminar los registros existentes de una tabla. Puede usar la cláusula WHERE con la consulta DELETE para eliminar las filas seleccionadas. De lo contrario, se eliminarían todos los registros.

Sintaxis

La sintaxis básica de la consulta DELETE con la cláusula WHERE es la siguiente:

DELETE FROM table_name
WHERE [condition];

Puede combinar N número de condiciones utilizando operadores AND u OR.

Ejemplo

Considere la tabla EMPRESA , que tiene registros de la siguiente manera:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

El siguiente es un ejemplo, que BORRARÍA un cliente cuyo ID es 7 -

testdb=# DELETE FROM COMPANY WHERE ID = 2;

Ahora, la tabla EMPRESA tendrá los siguientes registros:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
(6 rows)

Si desea ELIMINAR todos los registros de la tabla EMPRESA, no necesita usar la cláusula WHERE con las consultas DELETE, que serían las siguientes:

testdb=# DELETE FROM COMPANY;

Ahora, la tabla EMPRESA no tiene ningún registro porque la instrucción DELETE ha eliminado todos los registros.

El PostgreSQL LIKEEl operador se utiliza para hacer coincidir los valores de texto con un patrón utilizando comodines. Si la expresión de búsqueda puede coincidir con la expresión del patrón, el operador LIKE devolverá verdadero, que es1.

Hay dos comodines que se utilizan junto con el operador LIKE:

El signo de porcentaje (%)
El subrayado (_)

El signo de porcentaje representa cero, uno o varios números o caracteres. El guión bajo representa un solo número o carácter. Estos símbolos se pueden utilizar en combinaciones.

Si alguno de estos dos signos no se usa junto con la cláusula LIKE, LIKE actúa como el operador igual.

Sintaxis

La sintaxis básica de% y _ es la siguiente:

SELECT FROM table_name
WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name
WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name
WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX'

or

SELECT FROM table_name
WHERE column LIKE '_XXXX_'

Puede combinar N número de condiciones utilizando operadores AND u OR. Aquí XXXX podría ser cualquier valor numérico o de cadena.

Ejemplo

Aquí hay una serie de ejemplos que muestran WHERE parte con una cláusula LIKE diferente con operadores '%' y '_':

S. No.	Declaración y descripción
1	WHERE SALARY::text LIKE '200%' Encuentra cualquier valor que comience con 200
2	WHERE SALARY::text LIKE '%200%' Encuentra cualquier valor que tenga 200 en cualquier posición
3	WHERE SALARY::text LIKE '_00%' Encuentra cualquier valor que tenga 00 en la segunda y tercera posiciones
4	WHERE SALARY::text LIKE '2_%_%' Encuentra cualquier valor que comience con 2 y tenga al menos 3 caracteres de longitud
5	WHERE SALARY::text LIKE '%2' Encuentra cualquier valor que termine en 2
6	WHERE SALARY::text LIKE '_2%3' Encuentra cualquier valor que tenga 2 en la segunda posición y termine con un 3
7	WHERE SALARY::text LIKE '2___3' Encuentra cualquier valor en un número de cinco dígitos que comience con 2 y termine con 3

Postgres LIKE es solo comparar cadenas. Por lo tanto, necesitamos convertir explícitamente la columna de números enteros en una cadena como en los ejemplos anteriores.

Tomemos un ejemplo real, consideremos la tabla EMPRESA , que tiene los siguientes registros:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

El siguiente es un ejemplo, que mostraría todos los registros de la tabla EMPRESA donde EDAD comienza con 2 -

testdb=# SELECT * FROM COMPANY WHERE AGE::text LIKE '2%';

Esto produciría el siguiente resultado:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  8 | Paul  |  24 | Houston     |  20000
(7 rows)

El siguiente es un ejemplo, que mostraría todos los registros de la tabla EMPRESA donde DIRECCIÓN tendrá un guión (-) dentro del texto -

testdb=# SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

Esto produciría el siguiente resultado:

id | name | age |                      address              | salary
----+------+-----+-------------------------------------------+--------
  4 | Mark |  25 | Rich-Mond                                 |  65000
  6 | Kim  |  22 | South-Hall                                |  45000
(2 rows)

El PostgreSQL LIMIT La cláusula se usa para limitar la cantidad de datos devueltos por la instrucción SELECT.

Sintaxis

La sintaxis básica de la instrucción SELECT con la cláusula LIMIT es la siguiente:

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]

La siguiente es la sintaxis de la cláusula LIMIT cuando se usa junto con la cláusula OFFSET:

SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

LIMIT y OFFSET le permiten recuperar solo una parte de las filas que genera el resto de la consulta.

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

El siguiente es un ejemplo, que limita la fila en la tabla de acuerdo con el número de filas que desea obtener de la tabla:

testdb=# SELECT * FROM COMPANY LIMIT 4;

Esto produciría el siguiente resultado:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
(4 rows)

Sin embargo, en determinadas situaciones, es posible que deba recoger un conjunto de registros de un desplazamiento en particular. Aquí hay un ejemplo, que recoge tres registros a partir de la tercera posición:

testdb=# SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

Esto produciría el siguiente resultado:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
(3 rows)

El PostgreSQL ORDER BY La cláusula se utiliza para ordenar los datos en orden ascendente o descendente, según una o más columnas.

Sintaxis

La sintaxis básica de la cláusula ORDER BY es la siguiente:

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

Puede utilizar más de una columna en la cláusula ORDER BY. Asegúrese de que cualquier columna que esté usando para ordenar, esa columna debe estar disponible en la lista de columnas.

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

El siguiente es un ejemplo, que ordenaría el resultado en orden ascendente por SALARIO:

testdb=# SELECT * FROM COMPANY ORDER BY AGE ASC;

Esto produciría el siguiente resultado:

id | name  | age | address    | salary
 ----+-------+-----+------------+--------
   6 | Kim   |  22 | South-Hall |  45000
   3 | Teddy |  23 | Norway     |  20000
   7 | James |  24 | Houston    |  10000
   8 | Paul  |  24 | Houston    |  20000
   4 | Mark  |  25 | Rich-Mond  |  65000
   2 | Allen |  25 | Texas      |  15000
   5 | David |  27 | Texas      |  85000
   1 | Paul  |  32 | California |  20000
   9 | James |  44 | Norway     |   5000
  10 | James |  45 | Texas      |   5000
(10 rows)

El siguiente es un ejemplo, que ordenaría el resultado en orden ascendente por NOMBRE y SALARIO:

testdb=# SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

Esto produciría el siguiente resultado:

id | name  | age | address      | salary
----+-------+-----+--------------+--------
  2 | Allen |  25 | Texas        |  15000
  5 | David |  27 | Texas        |  85000
 10 | James |  45 | Texas        |   5000
  9 | James |  44 | Norway       |   5000
  7 | James |  24 | Houston      |  10000
  6 | Kim   |  22 | South-Hall   |  45000
  4 | Mark  |  25 | Rich-Mond    |  65000
  1 | Paul  |  32 | California   |  20000
  8 | Paul  |  24 | Houston      |  20000
  3 | Teddy |  23 | Norway       |  20000
(10 rows)

El siguiente es un ejemplo, que ordenaría el resultado en orden descendente por NOMBRE:

testdb=# SELECT * FROM COMPANY ORDER BY NAME DESC;

Esto produciría el siguiente resultado:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  3 | Teddy |  23 | Norway     |  20000
  1 | Paul  |  32 | California |  20000
  8 | Paul  |  24 | Houston    |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  9 | James |  44 | Norway     |   5000
 10 | James |  45 | Texas      |   5000
  5 | David |  27 | Texas      |  85000
  2 | Allen |  25 | Texas      |  15000
(10 rows)

El PostgreSQL GROUP BYLa cláusula se usa en colaboración con la instrucción SELECT para agrupar aquellas filas en una tabla que tienen datos idénticos. Esto se hace para eliminar la redundancia en la salida y / o calcular los agregados que se aplican a estos grupos.

La cláusula GROUP BY sigue a la cláusula WHERE en una instrucción SELECT y precede a la cláusula ORDER BY.

Sintaxis

La sintaxis básica de la cláusula GROUP BY se proporciona a continuación. La cláusula GROUP BY debe seguir las condiciones de la cláusula WHERE y debe preceder a la cláusula ORDER BY si se utiliza una.

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

Puede utilizar más de una columna en la cláusula GROUP BY. Asegúrese de que cualquier columna que esté usando para agrupar, esa columna debe estar disponible en la lista de columnas.

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Si desea saber el monto total del salario de cada cliente, la consulta GROUP BY sería la siguiente:

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

Esto produciría el siguiente resultado:

name  |  sum
 -------+-------
  Teddy | 20000
  Paul  | 20000
  Mark  | 65000
  David | 85000
  Allen | 15000
  Kim   | 45000
  James | 10000
(7 rows)

Ahora, creemos tres registros más en la tabla EMPRESA utilizando las siguientes instrucciones INSERT:

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

Ahora, nuestra tabla tiene los siguientes registros con nombres duplicados:

id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

Nuevamente, usemos la misma declaración para agrupar todos los registros usando la columna NOMBRE de la siguiente manera:

testdb=# SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

Esto produciría el siguiente resultado:

name  |  sum
-------+-------
 Allen | 15000
 David | 85000
 James | 20000
 Kim   | 45000
 Mark  | 65000
 Paul  | 40000
 Teddy | 20000
(7 rows)

Usemos la cláusula ORDER BY junto con la cláusula GROUP BY de la siguiente manera:

testdb=#  SELECT NAME, SUM(SALARY)
         FROM COMPANY GROUP BY NAME ORDER BY NAME DESC;

Esto produciría el siguiente resultado:

name  |  sum
-------+-------
 Teddy | 20000
 Paul  | 40000
 Mark  | 65000
 Kim   | 45000
 James | 20000
 David | 85000
 Allen | 15000
(7 rows)

En PostgreSQL, la consulta WITH proporciona una forma de escribir declaraciones auxiliares para usar en una consulta más grande. Ayuda a dividir consultas complicadas y grandes en formas más simples, que son fácilmente legibles. Estas declaraciones a menudo denominadas Expresiones de tabla comunes o CTE, se pueden considerar como la definición de tablas temporales que existen solo para una consulta.

La consulta WITH, que es una consulta CTE, es particularmente útil cuando la subconsulta se ejecuta varias veces. Es igualmente útil en lugar de tablas temporales. Calcula la agregación una vez y nos permite hacer referencia a ella por su nombre (puede ser varias veces) en las consultas.

La cláusula WITH debe definirse antes de que se utilice en la consulta.

Sintaxis

La sintaxis básica de la consulta WITH es la siguiente:

WITH
   name_for_summary_data AS (
      SELECT Statement)
   SELECT columns
   FROM name_for_summary_data
   WHERE conditions <=> (
      SELECT column
      FROM name_for_summary_data)
   [ORDER BY columns]

Donde name_for_summary_data es el nombre dado a la cláusula WITH. El name_for_summary_data puede ser el mismo que el nombre de una tabla existente y tendrá prioridad.

Puede utilizar declaraciones de modificación de datos (INSERT, UPDATE o DELETE) en WITH. Esto le permite realizar varias operaciones diferentes en la misma consulta.

Recursivo CON

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ahora, escribamos una consulta usando la cláusula WITH para seleccionar los registros de la tabla anterior, de la siguiente manera:

With CTE AS
(Select
 ID
, NAME
, AGE
, ADDRESS
, SALARY
FROM COMPANY )
Select * From CTE;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ahora, escribamos una consulta usando la palabra clave RECURSIVE junto con la cláusula WITH, para encontrar la suma de los salarios menores a 20000, de la siguiente manera:

WITH RECURSIVE t(n) AS (
   VALUES (0)
   UNION ALL
   SELECT SALARY FROM COMPANY WHERE SALARY < 20000
)
SELECT sum(n) FROM t;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

sum
-------
 25000
(1 row)

Escribamos una consulta utilizando declaraciones de modificación de datos junto con la cláusula WITH, como se muestra a continuación.

Primero, cree una tabla EMPRESA1 similar a la tabla EMPRESA. La consulta del ejemplo mueve filas de forma efectiva de EMPRESA a EMPRESA1. El DELETE en WITH borra las filas especificadas de EMPRESA, devolviendo su contenido mediante su cláusula RETURNING; y luego la consulta principal lee ese resultado y lo inserta en COMPANY1 TABLE -

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

WITH moved_rows AS (
   DELETE FROM COMPANY
   WHERE
      SALARY >= 30000
   RETURNING *
)
INSERT INTO COMPANY1 (SELECT * FROM moved_rows);

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

INSERT 0 3

Ahora, los registros en las tablas EMPRESA y EMPRESA1 son los siguientes:

testdb=# SELECT * FROM COMPANY;
 id | name  | age |  address   | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  7 | James |  24 | Houston    |  10000
(4 rows)


testdb=# SELECT * FROM COMPANY1;
 id | name  | age | address | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 | South-Hall  |  45000
(3 rows)

La cláusula HAVING nos permite seleccionar filas particulares donde el resultado de la función cumple alguna condición.

La cláusula WHERE coloca condiciones en las columnas seleccionadas, mientras que la cláusula HAVING coloca condiciones en los grupos creados por la cláusula GROUP BY.

Sintaxis

La siguiente es la posición de la cláusula HAVING en una consulta SELECT:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

La cláusula HAVING debe seguir a la cláusula GROUP BY en una consulta y también debe preceder a la cláusula ORDER BY si se utiliza. La siguiente es la sintaxis de la instrucción SELECT, incluida la cláusula HAVING:

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

El siguiente es un ejemplo, que mostraría un registro para el que el número de nombres es menor que 2 -

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;

Esto produciría el siguiente resultado:

name
 -------
  Teddy
  Paul
  Mark
  David
  Allen
  Kim
  James
(7 rows)

Ahora, creemos tres registros más en la tabla EMPRESA utilizando las siguientes instrucciones INSERT:

INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);

Ahora, nuestra tabla tiene los siguientes registros con nombres duplicados:

id | name  | age | address      | salary
 ----+-------+-----+--------------+--------
   1 | Paul  |  32 | California   |  20000
   2 | Allen |  25 | Texas        |  15000
   3 | Teddy |  23 | Norway       |  20000
   4 | Mark  |  25 | Rich-Mond    |  65000
   5 | David |  27 | Texas        |  85000
   6 | Kim   |  22 | South-Hall   |  45000
   7 | James |  24 | Houston      |  10000
   8 | Paul  |  24 | Houston      |  20000
   9 | James |  44 | Norway       |   5000
  10 | James |  45 | Texas        |   5000
(10 rows)

El siguiente es el ejemplo, que mostraría un registro para el que el número de nombres es mayor que 1:

testdb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;

Esto produciría el siguiente resultado:

name
-------
 Paul
 James
(2 rows)

El PostgreSQL DISTINCT La palabra clave se usa junto con la instrucción SELECT para eliminar todos los registros duplicados y obtener solo registros únicos.

Puede haber una situación en la que tenga varios registros duplicados en una tabla. Al buscar dichos registros, tiene más sentido buscar solo registros únicos en lugar de buscar registros duplicados.

Sintaxis

La sintaxis básica de la palabra clave DISTINCT para eliminar registros duplicados es la siguiente:

SELECT DISTINCT column1, column2,.....columnN
FROM table_name
WHERE [condition]

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Agreguemos dos registros más a esta tabla de la siguiente manera:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (8, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (9, 'Allen', 25, 'Texas', 15000.00 );

Ahora, los registros en la tabla EMPRESA serían:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
  8 | Paul  |  32 | California |  20000
  9 | Allen |  25 | Texas      |  15000
(9 rows)

Primero, veamos cómo la siguiente consulta SELECT devuelve registros de salario duplicados:

testdb=# SELECT name FROM COMPANY;

Esto produciría el siguiente resultado:

name
-------
 Paul
 Allen
 Teddy
 Mark
 David
 Kim
 James
 Paul
 Allen
(9 rows)

Ahora, usemos DISTINCT palabra clave con la consulta SELECT anterior y vea el resultado:

testdb=# SELECT DISTINCT name FROM COMPANY;

Esto produciría el siguiente resultado donde no tenemos ninguna entrada duplicada:

name
-------
 Teddy
 Paul
 Mark
 David
 Allen
 Kim
 James
(7 rows)

Las restricciones son las reglas que se aplican a las columnas de datos de la tabla. Estos se utilizan para evitar que se ingresen datos no válidos en la base de datos. Esto asegura la precisión y confiabilidad de los datos en la base de datos.

Las restricciones pueden ser de nivel de columna o de tabla. Las restricciones a nivel de columna se aplican solo a una columna, mientras que las restricciones a nivel de tabla se aplican a toda la tabla. Definir un tipo de datos para una columna es una restricción en sí misma. Por ejemplo, una columna de tipo FECHA limita la columna a fechas válidas.

Las siguientes son restricciones de uso común disponibles en PostgreSQL.

NOT NULL Constraint - Asegura que una columna no pueda tener un valor NULL.
UNIQUE Constraint - Garantiza que todos los valores de una columna sean diferentes.
PRIMARY Key - Identifica de forma única cada fila / registro en una tabla de base de datos.
FOREIGN Key - Restringe los datos basados en columnas en otras tablas.
CHECK Constraint - La restricción CHECK asegura que todos los valores de una columna satisfagan determinadas condiciones.
EXCLUSION Constraint - La restricción EXCLUDE asegura que si se comparan dos filas en las columnas o expresiones especificadas utilizando los operadores especificados, no todas estas comparaciones devolverán VERDADERO.

Restricción NOT NULL

De forma predeterminada, una columna puede contener valores NULL. Si no desea que una columna tenga un valor NULL, entonces debe definir dicha restricción en esta columna especificando que NULL ahora no está permitido para esa columna. Una restricción NOT NULL siempre se escribe como una restricción de columna.

Un NULL no es lo mismo que sin datos; más bien, representa datos desconocidos.

Ejemplo

Por ejemplo, la siguiente declaración de PostgreSQL crea una nueva tabla llamada COMPAÑÍA1 y agrega cinco columnas, tres de las cuales, ID y NOMBRE y EDAD, especifican no aceptar valores NULL -

CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Restricción ÚNICA

La restricción UNIQUE evita que dos registros tengan valores idénticos en una columna en particular. En la tabla EMPRESA, por ejemplo, es posible que desee evitar que dos o más personas tengan la misma edad.

Ejemplo

Por ejemplo, la siguiente declaración de PostgreSQL crea una nueva tabla llamada COMPANY3 y agrega cinco columnas. Aquí, la columna EDAD se establece en ÚNICA, por lo que no puede tener dos registros con la misma edad:

CREATE TABLE COMPANY3(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

Restricción de CLAVE PRIMARIA

La restricción PRIMARY KEY identifica de forma única cada registro en una tabla de base de datos. Puede haber más columnas ÚNICAS, pero solo una clave principal en una tabla. Las claves primarias son importantes al diseñar las tablas de la base de datos. Las claves primarias son identificadores únicos.

Los usamos para referirnos a las filas de la tabla. Las claves primarias se convierten en claves externas en otras tablas, al crear relaciones entre tablas. Debido a una 'supervisión de codificación de larga data', las claves primarias pueden ser NULL en SQLite. Este no es el caso de otras bases de datos

Una clave principal es un campo en una tabla, que identifica de forma única cada fila / registro en una tabla de base de datos. Las claves primarias deben contener valores únicos. Una columna de clave principal no puede tener valores NULL.

Una tabla solo puede tener una clave principal, que puede constar de uno o varios campos. Cuando se utilizan varios campos como clave principal, se denominancomposite key.

Si una tabla tiene una clave primaria definida en cualquier campo, entonces no puede tener dos registros que tengan el mismo valor de ese campo.

Ejemplo

Ya ha visto varios ejemplos arriba donde hemos creado la tabla COMAPNY4 con ID como clave principal -

CREATE TABLE COMPANY4(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Restricción de CLAVE EXTRANJERA

Una restricción de clave externa especifica que los valores de una columna (o un grupo de columnas) deben coincidir con los valores que aparecen en alguna fila de otra tabla. Decimos que esto mantiene la integridad referencial entre dos tablas relacionadas. Se llaman claves externas porque las restricciones son externas; es decir, fuera de la mesa. Las claves externas a veces se denominan clave de referencia.

Ejemplo

Por ejemplo, la siguiente declaración de PostgreSQL crea una nueva tabla llamada COMPANY5 y agrega cinco columnas.

CREATE TABLE COMPANY6(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Por ejemplo, la siguiente declaración de PostgreSQL crea una nueva tabla llamada DEPARTMENT1, que agrega tres columnas. La columna EMP_ID es la clave externa y hace referencia al campo de ID de la tabla COMPANY6.

CREATE TABLE DEPARTMENT1(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      references COMPANY6(ID)
);

VERIFICAR Restricción

La restricción CHECK habilita una condición para verificar el valor que se ingresa en un registro. Si la condición se evalúa como falsa, el registro viola la restricción y no se ingresa en la tabla.

Ejemplo

Por ejemplo, la siguiente declaración de PostgreSQL crea una nueva tabla llamada COMPANY5 y agrega cinco columnas. Aquí, agregamos un CHEQUE con la columna SALARIO, para que no pueda tener ningún SALARIO como Cero.

CREATE TABLE COMPANY5(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL    CHECK(SALARY > 0)
);

Restricción de EXCLUSIÓN

Las restricciones de exclusión aseguran que si se comparan dos filas en las columnas o expresiones especificadas mediante los operadores especificados, al menos una de estas comparaciones de operadores devolverá falso o nulo.

Ejemplo

Por ejemplo, la siguiente declaración de PostgreSQL crea una nueva tabla llamada COMPANY7 y agrega cinco columnas. Aquí, agregamos una restricción EXCLUDE:

CREATE TABLE COMPANY7(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   ADDRESS        CHAR(50),
   SALARY         REAL,
   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH <>)
);

Aquí, USAR gist es el tipo de índice que se debe construir y usar para la aplicación.

Necesita ejecutar el comando CREAR EXTENSIÓN btree_gist , una vez por base de datos. Esto instalará la extensión btree_gist, que define las restricciones de exclusión en tipos de datos escalares simples.

Como hemos hecho cumplir, la edad tiene que ser la misma, veamos esto insertando registros en la tabla:

INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
INSERT INTO COMPANY7 VALUES(3, 'Paul', 42, 'California', 20000.00 );

Para las dos primeras instrucciones INSERT, los registros se agregan a la tabla COMPANY7. Para la tercera instrucción INSERT, se muestra el siguiente error:

ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL:  Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).

Eliminación de restricciones

Para eliminar una restricción, necesita saber su nombre. Si se conoce el nombre, es fácil eliminarlo. De lo contrario, debe averiguar el nombre generado por el sistema. El comando psql \ d nombre de la tabla puede ser útil aquí. La sintaxis general es:

ALTER TABLE table_name DROP CONSTRAINT some_name;

El PostgreSQL JoinsLa cláusula se utiliza para combinar registros de dos o más tablas en una base de datos. Un JOIN es un medio para combinar campos de dos tablas usando valores comunes a cada uno.

Los tipos de unión en PostgreSQL son:

LA CRUZ SE UNE
El INNER JOIN
LA UNIÓN EXTERIOR IZQUIERDA
LA UNIÓN EXTERIOR CORRECTA
LA COMPLETA UNIÓN EXTERIOR

Antes de continuar, consideremos dos tablas, EMPRESA y DEPARTAMENTO. Ya hemos visto instrucciones INSERT para completar la tabla EMPRESA. Así que asumamos la lista de registros disponibles en la tabla EMPRESA:

id | name  | age | address   | salary | join_date
----+-------+-----+-----------+--------+-----------
  1 | Paul  |  32 | California|  20000 | 2001-07-13
  3 | Teddy |  23 | Norway    |  20000 |
  4 | Mark  |  25 | Rich-Mond |  65000 | 2007-12-13
  5 | David |  27 | Texas     |  85000 | 2007-12-13
  2 | Allen |  25 | Texas     |        | 2007-12-13
  8 | Paul  |  24 | Houston   |  20000 | 2005-07-13
  9 | James |  44 | Norway    |   5000 | 2005-07-13
 10 | James |  45 | Texas     |   5000 | 2005-07-13

Otra tabla es DEPARTAMENTO, tiene la siguiente definición:

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

Aquí está la lista de instrucciones INSERT para completar la tabla DEPARTMENT:

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

Finalmente, tenemos la siguiente lista de registros disponibles en la tabla DEPARTAMENTO:

id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |  1
  2 | Engineering |  2
  3 | Finance     |  7

LA CRUZ SE UNE

A CROSS JOIN hace coincidir cada fila de la primera tabla con cada fila de la segunda tabla. Si las tablas de entrada tienen columnas xey, respectivamente, la tabla resultante tendrá columnas x + y. Debido a que las CROSS JOIN tienen el potencial de generar tablas extremadamente grandes, se debe tener cuidado de usarlas solo cuando sea apropiado.

La siguiente es la sintaxis de CROSS JOIN:

SELECT ... FROM table1 CROSS JOIN table2 ...

Según las tablas anteriores, podemos escribir un CROSS JOIN de la siguiente manera:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

La consulta dada anteriormente producirá el siguiente resultado:

emp_id| name  |  dept
------|-------|--------------
    1 | Paul  | IT Billing
    1 | Teddy | IT Billing
    1 | Mark  | IT Billing
    1 | David | IT Billing
    1 | Allen | IT Billing
    1 | Paul  | IT Billing
    1 | James | IT Billing
    1 | James | IT Billing
    2 | Paul  | Engineering
    2 | Teddy | Engineering
    2 | Mark  | Engineering
    2 | David | Engineering
    2 | Allen | Engineering
    2 | Paul  | Engineering
    2 | James | Engineering
    2 | James | Engineering
    7 | Paul  | Finance
    7 | Teddy | Finance
    7 | Mark  | Finance
    7 | David | Finance
    7 | Allen | Finance
    7 | Paul  | Finance
    7 | James | Finance
    7 | James | Finance

El INNER JOIN

Una INNER JOIN crea una nueva tabla de resultados combinando valores de columna de dos tablas (table1 y table2) según el predicado de unión. La consulta compara cada fila de table1 con cada fila de table2 para encontrar todos los pares de filas que satisfacen el predicado de unión. Cuando se satisface el predicado de unión, los valores de columna para cada par coincidente de filas de table1 y table2 se combinan en una fila de resultados.

Una INNER JOIN es el tipo más común de unión y es el tipo de unión predeterminado. Puede utilizar la palabra clave INNER opcionalmente.

La siguiente es la sintaxis de INNER JOIN:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;

Según las tablas anteriores, podemos escribir una INNER JOIN de la siguiente manera:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
        ON COMPANY.ID = DEPARTMENT.EMP_ID;

La consulta dada anteriormente producirá el siguiente resultado:

emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering

LA UNIÓN EXTERIOR IZQUIERDA

OUTER JOIN es una extensión de INNER JOIN. El estándar SQL define tres tipos de OUTER JOINs: LEFT, RIGHT y FULL y PostgreSQL admite todos estos.

En el caso de LEFT OUTER JOIN, primero se realiza una combinación interna. Luego, para cada fila de la tabla T1 que no satisfaga la condición de unión con ninguna fila de la tabla T2, se agrega una fila unida con valores nulos en las columnas de T2. Por lo tanto, la tabla unida siempre tiene al menos una fila para cada fila en T1.

La siguiente es la sintaxis de LEFT OUTER JOIN -

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

Según las tablas anteriores, podemos escribir una combinación interna de la siguiente manera:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

La consulta dada anteriormente producirá el siguiente resultado:

emp_id | name  | dept
--------+-------+------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

LA UNIÓN EXTERIOR CORRECTA

Primero, se realiza una combinación interna. Luego, para cada fila de la tabla T2 que no satisfaga la condición de unión con ninguna fila de la tabla T1, se agrega una fila unida con valores nulos en las columnas de T1. Esta es la inversa de una combinación de izquierda; la tabla de resultados siempre tendrá una fila para cada fila en T2.

La siguiente es la sintaxis de RIGHT OUTER JOIN -

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...

Según las tablas anteriores, podemos escribir una combinación interna de la siguiente manera:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

La consulta dada anteriormente producirá el siguiente resultado:

emp_id | name  | dept
--------+-------+--------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance

LA COMPLETA UNIÓN EXTERIOR

Primero, se realiza una combinación interna. Luego, para cada fila de la tabla T1 que no satisfaga la condición de unión con ninguna fila de la tabla T2, se agrega una fila unida con valores nulos en las columnas de T2. Además, para cada fila de T2 que no satisfaga la condición de unión con ninguna fila en T1, se agrega una fila unida con valores nulos en las columnas de T1.

La siguiente es la sintaxis de FULL OUTER JOIN -

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...

Según las tablas anteriores, podemos escribir una combinación interna de la siguiente manera:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID;

La consulta dada anteriormente producirá el siguiente resultado:

emp_id | name  | dept
--------+-------+---------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 |       | Finance
        | James |
        | David |
        | Paul  |
        | Mark  |
        | Teddy |
        | James |

El PostgreSQL UNION La cláusula / operador se utiliza para combinar los resultados de dos o más sentencias SELECT sin devolver filas duplicadas.

Para usar UNION, cada SELECT debe tener el mismo número de columnas seleccionadas, el mismo número de expresiones de columna, el mismo tipo de datos y tenerlas en el mismo orden, pero no es necesario que tengan la misma longitud.

Sintaxis

La sintaxis básica de UNION es como sigue -

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Aquí, la condición dada podría ser cualquier expresión dada según su requisito.

Ejemplo

Considere las siguientes dos tablas, (a) La tabla EMPRESA es la siguiente:

testdb=# SELECT * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

(b) Otra tabla es DEPARTAMENTO como sigue:

testdb=# SELECT * from DEPARTMENT;
 id | dept        | emp_id
----+-------------+--------
  1 | IT Billing  |      1
  2 | Engineering |      2
  3 | Finance     |      7
  4 | Engineering |      3
  5 | Finance     |      4
  6 | Engineering |      5
  7 | Finance     |      6
(7 rows)

Ahora unamos estas dos tablas usando la instrucción SELECT junto con la cláusula UNION de la siguiente manera:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Esto produciría el siguiente resultado:

emp_id | name  |  dept
--------+-------+--------------
      5 | David | Engineering
      6 | Kim   | Finance
      2 | Allen | Engineering
      3 | Teddy | Engineering
      4 | Mark  | Finance
      1 | Paul  | IT Billing
      7 | James | Finance
(7 rows)

La cláusula UNION ALL

El operador UNION ALL se utiliza para combinar los resultados de dos sentencias SELECT, incluidas las filas duplicadas. Las mismas reglas que se aplican a UNION se aplican también al operador UNION ALL.

Sintaxis

La sintaxis básica de UNION ALL es como sigue -

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

UNION ALL

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Aquí, la condición dada podría ser cualquier expresión dada según su requisito.

Ejemplo

Ahora, unamos las dos tablas mencionadas anteriormente en nuestra declaración SELECT de la siguiente manera:

testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
   ON COMPANY.ID = DEPARTMENT.EMP_ID
   UNION ALL
      SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
         ON COMPANY.ID = DEPARTMENT.EMP_ID;

Esto produciría el siguiente resultado:

emp_id | name  | dept
--------+-------+--------------
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
      1 | Paul  | IT Billing
      2 | Allen | Engineering
      7 | James | Finance
      3 | Teddy | Engineering
      4 | Mark  | Finance
      5 | David | Engineering
      6 | Kim   | Finance
(14 rows)

El PostgreSQL NULLes el término utilizado para representar un valor perdido. Un valor NULO en una tabla es un valor en un campo que parece estar en blanco.

Un campo con un valor NULL es un campo sin valor. Es muy importante comprender que un valor NULO es diferente de un valor cero o un campo que contiene espacios.

Sintaxis

La sintaxis básica del uso NULL mientras crea una tabla es la siguiente:

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Aquí, NOT NULLsignifica que la columna siempre debe aceptar un valor explícito del tipo de datos dado. Hay dos columnas en las que no usamos NOT NULL. Por lo tanto, esto significa que estas columnas podrían ser NULL.

Un campo con un valor NULO es uno que se ha dejado en blanco durante la creación del registro.

Ejemplo

El valor NULO puede causar problemas a la hora de seleccionar datos, ya que al comparar un valor desconocido con cualquier otro valor, el resultado siempre es desconocido y no se incluye en los resultados finales. Considere la siguiente tabla, EMPRESA que tiene los siguientes registros:

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

Usemos la instrucción UPDATE para establecer algunos valores anulables como NULL de la siguiente manera:

testdb=# UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);

Ahora, la tabla EMPRESA debe tener los siguientes registros:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  1 | Paul  |  32 | California  |  20000
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
  6 | Kim   |  22 |             |
  7 | James |  24 |             |
(7 rows)

A continuación, veamos el uso de IS NOT NULL operador para enumerar todos los registros donde SALARIO no es NULO -

testdb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY
   FROM COMPANY
   WHERE SALARY IS NOT NULL;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
(5 rows)

El siguiente es el uso de IS NULL operador que enumerará todos los registros donde SALARIO es NULO -

testdb=#  SELECT  ID, NAME, AGE, ADDRESS, SALARY
        FROM COMPANY
        WHERE SALARY IS NULL;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age | address | salary
----+-------+-----+---------+--------
  6 | Kim   |  22 |         |
  7 | James |  24 |         |
(2 rows)

Puede cambiar el nombre de una tabla o columna temporalmente dando otro nombre, que se conoce como ALIAS. El uso de alias de tabla significa cambiar el nombre de una tabla en una declaración de PostgreSQL en particular. El cambio de nombre es un cambio temporal y el nombre real de la tabla no cambia en la base de datos.

Los alias de columna se utilizan para cambiar el nombre de las columnas de una tabla con el propósito de una consulta PostgreSQL en particular.

Sintaxis

La sintaxis básica de table alias es el siguiente:

SELECT column1, column2....
FROM table_name AS alias_name
WHERE [condition];

La sintaxis básica de column alias es el siguiente:

SELECT column_name AS alias_name
FROM table_name
WHERE [condition];

Ejemplo

Considere las siguientes dos tablas, (a) La tabla EMPRESA es la siguiente:

testdb=# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

(b) Otra tabla es DEPARTAMENTO como sigue:

id | dept         | emp_id
----+--------------+--------
  1 | IT Billing   |      1
  2 | Engineering  |      2
  3 | Finance      |      7
  4 | Engineering  |      3
  5 | Finance      |      4
  6 | Engineering  |      5
  7 | Finance      |      6
(7 rows)

Ahora, a continuación se muestra el uso de TABLE ALIAS donde usamos C y D como alias para las tablas COMPANY y DEPARTMENT, respectivamente -

testdb=# SELECT C.ID, C.NAME, C.AGE, D.DEPT
   FROM COMPANY AS C, DEPARTMENT AS D
   WHERE  C.ID = D.EMP_ID;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

id | name  | age |  dept
----+-------+-----+------------
  1 | Paul  |  32 | IT Billing
  2 | Allen |  25 | Engineering
  7 | James |  24 | Finance
  3 | Teddy |  23 | Engineering
  4 | Mark  |  25 | Finance
  5 | David |  27 | Engineering
  6 | Kim   |  22 | Finance
(7 rows)

Veamos un ejemplo del uso de COLUMN ALIAS donde COMPANY_ID es un alias de la columna de ID y COMPANY_NAME es un alias de la columna de nombre -

testdb=# SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
   FROM COMPANY AS C, DEPARTMENT AS D
   WHERE  C.ID = D.EMP_ID;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

company_id | company_name | age | dept
------------+--------------+-----+------------
      1     | Paul         |  32 | IT Billing
      2     | Allen        |  25 | Engineering
      7     | James        |  24 | Finance
      3     | Teddy        |  23 | Engineering
      4     | Mark         |  25 | Finance
      5     | David        |  27 | Engineering
      6     | Kim          |  22 | Finance
(7 rows)

PostgreSQL Triggers son funciones de devolución de llamada de la base de datos, que se ejecutan / invocan automáticamente cuando se produce un evento de base de datos específico.

Los siguientes son puntos importantes sobre los disparadores de PostgreSQL:

El disparador de PostgreSQL se puede especificar para disparar
- Antes de que se intente la operación en una fila (antes de que se verifiquen las restricciones y se intente INSERT, UPDATE o DELETE)
- Después de que la operación se haya completado (después de que se verifiquen las restricciones y se hayan completado INSERT, UPDATE o DELETE)
- En lugar de la operación (en el caso de inserciones, actualizaciones o eliminaciones en una vista)
Un disparador que está marcado PARA CADA FILA se llama una vez por cada fila que modifica la operación. Por el contrario, un disparador que está marcado PARA CADA DECLARACIÓN solo se ejecuta una vez para una operación determinada, independientemente de cuántas filas modifique.
Tanto la cláusula WHEN como las acciones desencadenantes pueden acceder a elementos de la fila que se inserta, borra o actualiza utilizando referencias del formulario NEW.column-name y OLD.column-name, donde nombre-columna es el nombre de una columna de la tabla con la que está asociado el desencadenador.
Si se proporciona una cláusula WHEN, las declaraciones de PostgreSQL especificadas solo se ejecutan para las filas para las que la cláusula WHEN es verdadera. Si no se proporciona una cláusula WHEN, las declaraciones de PostgreSQL se ejecutan para todas las filas.
Si se definen varios disparadores del mismo tipo para el mismo evento, se dispararán en orden alfabético por nombre.
La palabra clave BEFORE, AFTER o INSTEAD OF determina cuándo se ejecutarán las acciones de activación en relación con la inserción, modificación o eliminación de la fila asociada.
Los activadores se eliminan automáticamente cuando se elimina la tabla a la que están asociados.
La tabla que se va a modificar debe existir en la misma base de datos que la tabla o vista a la que se adjunta el disparador y se debe usar solo tablenameno database.tablename.
Una opción CONSTRAINT cuando se especifica crea un activador de restricción . Esto es lo mismo que un gatillo normal, excepto que la sincronización del disparo del gatillo se puede ajustar usando SET CONSTRAINTS. Se espera que los activadores de restricciones generen una excepción cuando se violen las restricciones que implementan.

Sintaxis

La sintaxis básica para crear un trigger es como sigue -

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name
ON table_name
[
 -- Trigger logic goes here....
];

Aquí, event_namepodría ser INSERT, DELETE, UPDATE y TRUNCATE operación de base de datos en la tabla mencionadatable_name. Opcionalmente, puede especificar PARA CADA FILA después del nombre de la tabla.

La siguiente es la sintaxis de la creación de un disparador en una operación UPDATE en una o más columnas especificadas de una tabla de la siguiente manera:

CREATE  TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column_name
ON table_name
[
 -- Trigger logic goes here....
];

Ejemplo

Consideremos un caso en el que queremos mantener una prueba de auditoría para cada registro que se inserta en la tabla EMPRESA, que crearemos nuevamente de la siguiente manera (elimine la tabla EMPRESA si ya la tiene).

testdb=# CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Para mantener la prueba de auditoría, crearemos una nueva tabla llamada AUDIT donde se insertarán mensajes de registro cada vez que haya una entrada en la tabla EMPRESA para un nuevo registro:

testdb=# CREATE TABLE AUDIT(
   EMP_ID INT NOT NULL,
   ENTRY_DATE TEXT NOT NULL
);

Aquí, ID es el ID del registro de AUDIT, y EMP_ID es el ID, que vendrá de la tabla COMPANY, y DATE mantendrá la marca de tiempo cuando el registro se creará en la tabla COMPANY. Así que ahora, creemos un disparador en la tabla EMPRESA de la siguiente manera:

testdb=# CREATE TRIGGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();

Donde auditlogfunc () es un PostgreSQL procedure y tiene la siguiente definición:

CREATE OR REPLACE FUNCTION auditlogfunc() RETURNS TRIGGER AS $example_table$
   BEGIN
      INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, current_timestamp);
      RETURN NEW;
   END;
$example_table$ LANGUAGE plpgsql;

Ahora, comenzaremos el trabajo real. Comencemos insertando un registro en la tabla EMPRESA que debería resultar en la creación de un registro de auditoría en la tabla AUDIT. Así que creemos un registro en la tabla EMPRESA de la siguiente manera:

testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

Esto creará un registro en la tabla EMPRESA, que es el siguiente:

id | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000

Al mismo tiempo, se creará un registro en la tabla AUDIT. Este registro es el resultado de un disparador, que hemos creado en la operación INSERTAR en la tabla EMPRESA. Del mismo modo, puede crear sus activadores en las operaciones ACTUALIZAR y ELIMINAR según sus requisitos.

emp_id |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)

Listado de gatillos

Puede enumerar todos los disparadores en la base de datos actual desde pg_trigger tabla de la siguiente manera:

testdb=# SELECT * FROM pg_trigger;

La declaración de PostgreSQL dada anteriormente enumerará todos los disparadores.

Si desea enumerar los disparadores en una tabla en particular, use la cláusula AND con el nombre de la tabla de la siguiente manera:

testdb=# SELECT tgname FROM pg_trigger, pg_class WHERE tgrelid=pg_class.oid AND relname='company';

La declaración de PostgreSQL dada anteriormente también enumerará solo una entrada de la siguiente manera:

tgname
-----------------
 example_trigger
(1 row)

Soltando GATILLOS

El siguiente es el comando DROP, que se puede usar para eliminar un disparador existente:

testdb=# DROP TRIGGER trigger_name;

Los índices son tablas de búsqueda especiales que el motor de búsqueda de la base de datos puede utilizar para acelerar la recuperación de datos. En pocas palabras, un índice es un puntero a datos en una tabla. Un índice en una base de datos es muy similar a un índice al final de un libro.

Por ejemplo, si desea hacer referencia a todas las páginas de un libro que trata un tema determinado, primero debe consultar el índice, que enumera todos los temas en orden alfabético y luego hacer referencia a uno o más números de página específicos.

Un índice ayuda a acelerar las consultas SELECT y las cláusulas WHERE; sin embargo, ralentiza la entrada de datos, con declaraciones UPDATE e INSERT. Los índices se pueden crear o eliminar sin ningún efecto sobre los datos.

La creación de un índice implica la instrucción CREATE INDEX, que le permite nombrar el índice, especificar la tabla y qué columna o columnas indexar, e indicar si el índice está en orden ascendente o descendente.

Los índices también pueden ser únicos, similar a la restricción ÚNICA, en el sentido de que el índice evita entradas duplicadas en la columna o combinación de columnas en las que hay un índice.

El comando CREATE INDEX

La sintaxis básica de CREATE INDEX es como sigue -

CREATE INDEX index_name ON table_name;

Tipos de índice

PostgreSQL proporciona varios tipos de índices: árbol B, Hash, GiST, SP-GiST y GIN. Cada tipo de índice utiliza un algoritmo diferente que se adapta mejor a diferentes tipos de consultas. De forma predeterminada, el comando CREATE INDEX crea índices de árbol B, que se ajustan a las situaciones más comunes.

Índices de una sola columna

Un índice de una sola columna es aquel que se crea basándose en una sola columna de la tabla. La sintaxis básica es la siguiente:

CREATE INDEX index_name
ON table_name (column_name);

Índices multicolumna

Un índice de varias columnas se define en más de una columna de una tabla. La sintaxis básica es la siguiente:

CREATE INDEX index_name
ON table_name (column1_name, column2_name);

Ya sea para crear un índice de una sola columna o un índice de varias columnas, tenga en cuenta las columnas que puede usar con mucha frecuencia en la cláusula WHERE de una consulta como condiciones de filtro.

Si solo se usa una columna, la opción debe ser un índice de una sola columna. Si hay dos o más columnas que se utilizan con frecuencia en la cláusula WHERE como filtros, el índice de varias columnas sería la mejor opción.

Índices únicos

Los índices únicos se utilizan no solo para el rendimiento, sino también para la integridad de los datos. Un índice único no permite que se inserten valores duplicados en la tabla. La sintaxis básica es la siguiente:

CREATE UNIQUE INDEX index_name
on table_name (column_name);

Índices parciales

Un índice parcial es un índice construido sobre un subconjunto de una tabla; el subconjunto está definido por una expresión condicional (denominada predicado del índice parcial). El índice contiene entradas solo para aquellas filas de la tabla que satisfacen el predicado. La sintaxis básica es la siguiente:

CREATE INDEX index_name
on table_name (conditional_expression);

Índices implícitos

Los índices implícitos son índices que el servidor de bases de datos crea automáticamente cuando se crea un objeto. Los índices se crean automáticamente para restricciones de clave primaria y restricciones únicas.

Ejemplo

El siguiente es un ejemplo en el que crearemos un índice en la tabla EMPRESA para la columna de salario:

# CREATE INDEX salary_index ON COMPANY (salary);

Ahora, enumeremos todos los índices disponibles en la tabla EMPRESA usando \d company mando.

# \d company

Esto producirá el siguiente resultado, donde company_pkey es un índice implícito, que se creó cuando se creó la tabla.

Table "public.company"
 Column  |     Type      | Modifiers
---------+---------------+-----------
 id      | integer       | not null
 name    | text          | not null
 age     | integer       | not null
 address | character(50) |
 salary  | real          |
Indexes:
    "company_pkey" PRIMARY KEY, btree (id)
    "salary_index" btree (salary)

Puede enumerar toda la base de datos de índices utilizando el \di comando -

El comando DROP INDEX

Se puede eliminar un índice usando PostgreSQL DROPmando. Se debe tener cuidado al eliminar un índice porque el rendimiento se puede ralentizar o mejorar.

La sintaxis básica es la siguiente:

DROP INDEX index_name;

Puede usar la siguiente declaración para eliminar el índice creado anteriormente:

# DROP INDEX salary_index;

¿Cuándo se deben evitar los índices?

Aunque los índices están destinados a mejorar el rendimiento de una base de datos, hay ocasiones en las que deben evitarse. Las siguientes pautas indican cuándo se debe reconsiderar el uso de un índice:

Los índices no deben usarse en tablas pequeñas.
Tablas que tienen operaciones de inserción o actualización por lotes grandes y frecuentes.
Los índices no deben usarse en columnas que contienen un número elevado de valores NULL.
Las columnas que se manipulan con frecuencia no deben indexarse.

El PostgreSQL ALTER TABLE El comando se usa para agregar, eliminar o modificar columnas en una tabla existente.

También usaría el comando ALTER TABLE para agregar y eliminar varias restricciones en una tabla existente.

Sintaxis

La sintaxis básica de ALTER TABLE para agregar una nueva columna en una tabla existente es la siguiente:

ALTER TABLE table_name ADD column_name datatype;

La sintaxis básica de ALTER TABLE para DROP COLUMN en una tabla existente es la siguiente:

ALTER TABLE table_name DROP COLUMN column_name;

La sintaxis básica de ALTER TABLE para cambiar el DATA TYPE de una columna en una tabla es la siguiente:

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

La sintaxis básica de ALTER TABLE para agregar un NOT NULL La restricción a una columna en una tabla es la siguiente:

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

La sintaxis básica de ALTER TABLE para ADD UNIQUE CONSTRAINT a una mesa es la siguiente:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint UNIQUE(column1, column2...);

La sintaxis básica de ALTER TABLE para ADD CHECK CONSTRAINT a una mesa es la siguiente:

ALTER TABLE table_name
ADD CONSTRAINT MyUniqueConstraint CHECK (CONDITION);

La sintaxis básica de ALTER TABLE para ADD PRIMARY KEY La restricción a una tabla es la siguiente:

ALTER TABLE table_name
ADD CONSTRAINT MyPrimaryKey PRIMARY KEY (column1, column2...);

La sintaxis básica de ALTER TABLE para DROP CONSTRAINT de una tabla es la siguiente:

ALTER TABLE table_name
DROP CONSTRAINT MyUniqueConstraint;

Si está utilizando MySQL, el código es el siguiente:

ALTER TABLE table_name
DROP INDEX MyUniqueConstraint;

La sintaxis básica de ALTER TABLE para DROP PRIMARY KEY La restricción de una tabla es la siguiente:

ALTER TABLE table_name
DROP CONSTRAINT MyPrimaryKey;

Si está utilizando MySQL, el código es el siguiente:

ALTER TABLE table_name
DROP PRIMARY KEY;

Ejemplo

Considere que nuestra tabla EMPRESA tiene los siguientes registros:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

El siguiente es el ejemplo para AGREGAR una nueva columna en una tabla existente:

testdb=# ALTER TABLE COMPANY ADD GENDER char(1);

Ahora, se cambia la tabla EMPRESA y lo siguiente sería el resultado de la instrucción SELECT:

id | name  | age | address     | salary | gender
----+-------+-----+-------------+--------+--------
  1 | Paul  |  32 | California  |  20000 |
  2 | Allen |  25 | Texas       |  15000 |
  3 | Teddy |  23 | Norway      |  20000 |
  4 | Mark  |  25 | Rich-Mond   |  65000 |
  5 | David |  27 | Texas       |  85000 |
  6 | Kim   |  22 | South-Hall  |  45000 |
  7 | James |  24 | Houston     |  10000 |
(7 rows)

El siguiente es el ejemplo para DROP gender column de la tabla existente:

testdb=# ALTER TABLE COMPANY DROP GENDER;

Ahora, se cambia la tabla EMPRESA y lo siguiente sería el resultado de la instrucción SELECT:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

El PostgreSQL TRUNCATE TABLEEl comando se usa para eliminar datos completos de una tabla existente. También puede usar el comando DROP TABLE para eliminar la tabla completa, pero eliminaría la estructura completa de la tabla de la base de datos y necesitaría volver a crear esta tabla una vez más si desea almacenar algunos datos.

Tiene el mismo efecto que DELETE en cada tabla, pero como en realidad no escanea las tablas, es más rápido. Además, recupera espacio en disco inmediatamente, en lugar de requerir una operación de VACÍO posterior. Esto es más útil en tablas grandes.

Sintaxis

La sintaxis básica de TRUNCATE TABLE es como sigue -

TRUNCATE TABLE  table_name;

Ejemplo

Considere que la tabla EMPRESA tiene los siguientes registros:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(7 rows)

El siguiente es el ejemplo para truncar:

testdb=# TRUNCATE TABLE COMPANY;

Ahora, la tabla EMPRESA está truncada y la siguiente sería la salida de la instrucción SELECT:

testdb=# SELECT * FROM CUSTOMERS;
 id | name | age | address | salary
----+------+-----+---------+--------
(0 rows)

Las vistas son pseudo-tablas. Es decir, no son tablas reales; sin embargo aparecen como tablas ordinarias para SELECT. Una vista puede representar un subconjunto de una tabla real, seleccionando ciertas columnas o ciertas filas de una tabla ordinaria. Una vista puede incluso representar tablas unidas. Debido a que a las vistas se les asignan permisos separados, puede usarlos para restringir el acceso a la tabla para que los usuarios vean solo filas o columnas específicas de una tabla.

Una vista puede contener todas las filas de una tabla o filas seleccionadas de una o más tablas. Se puede crear una vista a partir de una o varias tablas, lo que depende de la consulta PostgreSQL escrita para crear una vista.

Las vistas, que son una especie de tablas virtuales, permiten a los usuarios hacer lo siguiente:

Estructurar los datos de una manera que los usuarios o clases de usuarios encuentren natural o intuitiva.
Restrinja el acceso a los datos de modo que un usuario solo pueda ver datos limitados en lugar de una tabla completa.
Resuma datos de varias tablas, que se pueden utilizar para generar informes.

Dado que las vistas no son tablas ordinarias, es posible que no pueda ejecutar una instrucción DELETE, INSERT o UPDATE en una vista. Sin embargo, puede crear una REGLA para corregir este problema de utilizar DELETE, INSERT o UPDATE en una vista.

Creando Vistas

Las vistas de PostgreSQL se crean utilizando el CREATE VIEWdeclaración. Las vistas de PostgreSQL se pueden crear a partir de una sola tabla, varias tablas u otra vista.

La sintaxis básica de CREATE VIEW es la siguiente:

CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE [condition];

Puede incluir varias tablas en su declaración SELECT de una manera muy similar a como las usa en una consulta SELECT de PostgreSQL normal. Si la palabra clave opcional TEMP o TEMPORARY está presente, la vista se creará en el espacio temporal. Las vistas temporales se eliminan automáticamente al final de la sesión actual.

Ejemplo

Considere, la tabla EMPRESA tiene los siguientes registros:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000

Ahora, el siguiente es un ejemplo para crear una vista desde la tabla EMPRESA. Esta vista se usaría para tener solo unas pocas columnas de la tabla EMPRESA:

testdb=# CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY;

Ahora, puede consultar COMPANY_VIEW de forma similar a como consulta una tabla real. A continuación se muestra el ejemplo:

testdb=# SELECT * FROM COMPANY_VIEW;

Esto produciría el siguiente resultado:

id | name  | age
----+-------+-----
  1 | Paul  |  32
  2 | Allen |  25
  3 | Teddy |  23
  4 | Mark  |  25
  5 | David |  27
  6 | Kim   |  22
  7 | James |  24
(7 rows)

Descartar vistas

Para eliminar una vista, simplemente use la instrucción DROP VIEW con el view_name. La sintaxis básica de DROP VIEW es la siguiente:

testdb=# DROP VIEW view_name;

El siguiente comando eliminará la vista COMPANY_VIEW, que creamos en la última sección:

testdb=# DROP VIEW COMPANY_VIEW;

Una transacción es una unidad de trabajo que se realiza en una base de datos. Las transacciones son unidades o secuencias de trabajo realizadas en un orden lógico, ya sea de forma manual por un usuario o automáticamente por algún tipo de programa de base de datos.

Una transacción es la propagación de uno o más cambios en la base de datos. Por ejemplo, si está creando un registro, actualizando un registro o eliminando un registro de la tabla, entonces está realizando una transacción en la tabla. Es importante controlar las transacciones para garantizar la integridad de los datos y manejar los errores de la base de datos.

Prácticamente, agrupará muchas consultas de PostgreSQL en un grupo y las ejecutará todas juntas como parte de una transacción.

Propiedades de las transacciones

Las transacciones tienen las siguientes cuatro propiedades estándar, a las que generalmente se hace referencia con el acrónimo ACID:

Atomicity- Asegura que todas las operaciones dentro de la unidad de trabajo se completen con éxito; de lo contrario, la transacción se aborta en el punto de falla y las operaciones anteriores se revierten a su estado anterior.
Consistency - Garantiza que la base de datos cambie correctamente de estado tras una transacción confirmada con éxito.
Isolation - Permite que las transacciones funcionen de forma independiente y transparente entre sí.
Durability - Asegura que el resultado o efecto de una transacción comprometida persista en caso de falla del sistema.

Control de transacciones

Los siguientes comandos se utilizan para controlar las transacciones:

BEGIN TRANSACTION - Para iniciar una transacción.
COMMIT - Para guardar los cambios, alternativamente puede usar END TRANSACTION mando.
ROLLBACK - Revertir los cambios.

Los comandos de control transaccional solo se utilizan con los comandos DML INSERT, UPDATE y DELETE únicamente. No se pueden usar al crear tablas o descartarlas porque estas operaciones se confirman automáticamente en la base de datos.

El comando BEGIN TRANSACTION

Las transacciones se pueden iniciar usando BEGIN TRANSACTION o simplemente el comando BEGIN. Estas transacciones generalmente persisten hasta que se encuentra el siguiente comando COMMIT o ROLLBACK. Pero una transacción también se ROLLBACK si la base de datos está cerrada o si ocurre un error.

La siguiente es la sintaxis simple para iniciar una transacción:

BEGIN;

or

BEGIN TRANSACTION;

El comando COMMIT

El comando COMMIT es el comando transaccional que se utiliza para guardar los cambios invocados por una transacción en la base de datos.

El comando COMMIT guarda todas las transacciones en la base de datos desde el último comando COMMIT o ROLLBACK.

La sintaxis del comando COMMIT es la siguiente:

COMMIT;

or

END TRANSACTION;

El comando ROLLBACK

El comando ROLLBACK es el comando transaccional que se utiliza para deshacer transacciones que aún no se han guardado en la base de datos.

El comando ROLLBACK solo se puede utilizar para deshacer transacciones desde que se emitió el último comando COMMIT o ROLLBACK.

La sintaxis del comando ROLLBACK es la siguiente:

ROLLBACK;

Ejemplo

Considere que la tabla EMPRESA tiene los siguientes registros:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

Ahora, iniciemos una transacción y eliminemos registros de la tabla que tengan edad = 25 y finalmente usamos el comando ROLLBACK para deshacer todos los cambios.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK;

Si comprueba que la tabla EMPRESA todavía tiene los siguientes registros:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

Ahora, comencemos otra transacción y eliminemos registros de la tabla que tengan edad = 25 y finalmente usamos el comando COMMIT para confirmar todos los cambios.

testdb=# BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;

Si comprueba la tabla EMPRESA, todavía tiene los siguientes registros:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)

Cerraduras o exclusivo cerraduras o bloqueos de escritura impiden toda posibilidad de modificar una fila o una tabla entera. Las filas modificadas por UPDATE y DELETE se bloquean exclusivamente de forma automática durante la duración de la transacción. Esto evita que otros usuarios cambien la fila hasta que la transacción se confirme o se deshaga.

El único momento en que los usuarios deben esperar a otros usuarios es cuando intentan modificar la misma fila. Si modifican diferentes filas, no es necesario esperar. Las consultas SELECT nunca tienen que esperar.

La base de datos realiza el bloqueo automáticamente. En algunos casos, sin embargo, el bloqueo debe controlarse manualmente. El bloqueo manual se puede realizar mediante el comando LOCK. Permite especificar el alcance y el tipo de bloqueo de una transacción.

Sintaxis del comando LOCK

La sintaxis básica del comando LOCK es la siguiente:

LOCK [ TABLE ]
name
 IN
lock_mode

name- El nombre (opcionalmente calificado por esquema) de una tabla existente para bloquear. Si se especifica SOLO antes del nombre de la tabla, solo esa tabla está bloqueada. Si no se especifica ONLY, la tabla y todas sus tablas descendientes (si las hay) se bloquean.
lock_mode- El modo de bloqueo especifica con qué bloqueos entra en conflicto este bloqueo. Si no se especifica ningún modo de bloqueo, se utiliza ACCESS EXCLUSIVE, el modo más restrictivo. Los valores posibles son: ACCESO COMPARTIDO, FILA COMPARTIDA, FILA EXCLUSIVA, COMPARTIR ACTUALIZACIÓN EXCLUSIVA, COMPARTIR, COMPARTIR FILA EXCLUSIVA, EXCLUSIVA, ACCESO EXCLUSIVO.

Una vez obtenido, el bloqueo se mantiene durante el resto de la transacción actual. No hay ningún comando UNLOCK TABLE; los bloqueos siempre se liberan al final de la transacción.

DeadLocks

Los interbloqueos pueden ocurrir cuando dos transacciones están esperando que la otra finalice sus operaciones. Si bien PostgreSQL puede detectarlos y finalizarlos con un ROLLBACK, los puntos muertos aún pueden ser inconvenientes. Para evitar que sus aplicaciones se encuentren con este problema, asegúrese de diseñarlas de tal manera que bloqueen los objetos en el mismo orden.

Cerraduras de aviso

PostgreSQL proporciona medios para crear bloqueos que tienen significados definidos por la aplicación. Estos se denominan bloqueos de aviso . Como el sistema no hace cumplir su uso, corresponde a la aplicación utilizarlos correctamente. Los bloqueos de aviso pueden resultar útiles para las estrategias de bloqueo que no encajan con el modelo MVCC.

Por ejemplo, un uso común de los bloqueos de aviso es emular las estrategias de bloqueo pesimistas típicas de los sistemas de gestión de datos denominados "archivos planos". Si bien una bandera almacenada en una tabla podría usarse para el mismo propósito, los bloqueos de aviso son más rápidos, evitan la hinchazón de la tabla y el servidor los limpia automáticamente al final de la sesión.

Ejemplo

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

El siguiente ejemplo bloquea la tabla EMPRESA dentro de la base de datos testdb en modo ACCESO EXCLUSIVO. La instrucción LOCK funciona solo en un modo de transacción:

testdb=#BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

LOCK TABLE

El mensaje anterior indica que la tabla está bloqueada hasta que finalice la transacción y para finalizar la transacción tendrá que revertir o confirmar la transacción.

Una subconsulta o consulta interna o consulta anidada es una consulta dentro de otra consulta de PostgreSQL e incrustada dentro de la cláusula WHERE.

Una subconsulta se utiliza para devolver datos que se utilizarán en la consulta principal como condición para restringir aún más los datos que se recuperarán.

Las subconsultas se pueden usar con las sentencias SELECT, INSERT, UPDATE y DELETE junto con los operadores como =, <,>,> =, <=, IN, etc.

Hay algunas reglas que las subconsultas deben seguir:

Las subconsultas deben ir entre paréntesis.
Una subconsulta solo puede tener una columna en la cláusula SELECT, a menos que haya varias columnas en la consulta principal para que la subconsulta compare sus columnas seleccionadas.
Un ORDER BY no se puede usar en una subconsulta, aunque la consulta principal puede usar un ORDER BY. GROUP BY se puede usar para realizar la misma función que ORDER BY en una subconsulta.
Las subconsultas que devuelven más de una fila solo se pueden usar con operadores de valor múltiple, como el operador IN, EXISTS, NOT IN, ANY / SOME, ALL.
El operador BETWEEN no se puede utilizar con una subconsulta; sin embargo, BETWEEN se puede utilizar dentro de la subconsulta.

Subconsultas con la instrucción SELECT

Las subconsultas se utilizan con mayor frecuencia con la instrucción SELECT. La sintaxis básica es la siguiente:

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

Ejemplo

Considere la tabla EMPRESA que tiene los siguientes registros:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

Ahora, verifiquemos la siguiente subconsulta con la instrucción SELECT:

testdb=# SELECT *
   FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY
      WHERE SALARY > 45000) ;

Esto produciría el siguiente resultado:

id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(2 rows)

Subconsultas con la instrucción INSERT

Las subconsultas también se pueden usar con instrucciones INSERT. La instrucción INSERT utiliza los datos devueltos por la subconsulta para insertarlos en otra tabla. Los datos seleccionados en la subconsulta se pueden modificar con cualquiera de las funciones de carácter, fecha o número.

La sintaxis básica es la siguiente:

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ] ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Ejemplo

Considere una tabla COMPANY_BKP, con una estructura similar a la tabla COMPANY y se puede crear usando la misma CREATE TABLE usando COMPANY_BKP como el nombre de la tabla. Ahora, para copiar la tabla COMPAÑÍA completa en COMPANY_BKP, la siguiente es la sintaxis:

testdb=# INSERT INTO COMPANY_BKP
   SELECT * FROM COMPANY
   WHERE ID IN (SELECT ID
      FROM COMPANY) ;

Subconsultas con la instrucción UPDATE

La subconsulta se puede utilizar junto con la instrucción UPDATE. Se pueden actualizar una o varias columnas en una tabla cuando se usa una subconsulta con la instrucción UPDATE.

La sintaxis básica es la siguiente:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Ejemplo

Suponiendo que tenemos la tabla COMPANY_BKP disponible, que es una copia de seguridad de la tabla COMPANY.

El siguiente ejemplo actualiza SALARIO 0,50 veces en la tabla EMPRESA para todos los clientes, cuya EDAD sea mayor o igual a 27 -

testdb=# UPDATE COMPANY
   SET SALARY = SALARY * 0.50
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE >= 27 );

Esto afectaría a dos filas y finalmente la tabla EMPRESA tendría los siguientes registros:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  1 | Paul  |  32 | California  |  10000
  5 | David |  27 | Texas       |  42500
(7 rows)

Subconsultas con la instrucción DELETE

La subconsulta se puede usar junto con la instrucción DELETE como con cualquier otra instrucción mencionada anteriormente.

La sintaxis básica es la siguiente:

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Ejemplo

Suponiendo que tenemos la tabla COMPANY_BKP disponible, que es una copia de seguridad de la tabla COMPANY.

El siguiente ejemplo elimina registros de la tabla EMPRESA para todos los clientes, cuya EDAD es mayor o igual a 27 -

testdb=# DELETE FROM COMPANY
   WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
      WHERE AGE > 27 );

Esto afectaría a dos filas y finalmente la tabla EMPRESA tendría los siguientes registros:

id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  5 | David |  27 | Texas       |  42500
(6 rows)

PostgreSQL tiene los tipos de datos smallserial , serial y bigserial ; estos no son tipos verdaderos, sino simplemente una conveniencia de notación para crear columnas de identificador único. Son similares a la propiedad AUTO_INCREMENT admitida por algunas otras bases de datos.

Si desea que una columna de serie tenga una restricción única o sea una clave principal, ahora debe especificarla, como cualquier otro tipo de datos.

El nombre de tipo serial crea columnas enteras . El nombre de tipo bigserial crea una columna bigint . bigserial debe usarse si prevé el uso de más de 2 ³¹ identificadores durante la vida útil de la tabla. El nombre de tipo smallserial crea una columna smallint .

Sintaxis

El uso básico de SERIAL el tipo de datos es el siguiente:

CREATE TABLE tablename (
   colname SERIAL
);

Ejemplo

Considere que la tabla EMPRESA se creará de la siguiente manera:

testdb=# CREATE TABLE COMPANY(
   ID  SERIAL PRIMARY KEY,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Ahora, inserte los siguientes registros en la tabla EMPRESA:

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ('Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'David', 27, 'Texas', 85000.00 );


INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)
VALUES ( 'James', 24, 'Houston', 10000.00 );

Esto insertará siete tuplas en la tabla EMPRESA y EMPRESA tendrá los siguientes registros:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  2 | Allen |  25 | Texas      |  15000
  3 | Teddy |  23 | Norway     |  20000
  4 | Mark  |  25 | Rich-Mond  |  65000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000

Siempre que se crea un objeto en una base de datos, se le asigna un propietario. El propietario suele ser quien ejecutó la declaración de creación. Para la mayoría de los tipos de objetos, el estado inicial es que solo el propietario (o un superusuario) puede modificar o eliminar el objeto. Para permitir que otros roles o usuarios lo usen, se deben otorgar privilegios o permisos.

Los diferentes tipos de privilegios en PostgreSQL son:

SELECT,
INSERT,
UPDATE,
DELETE,
TRUNCATE,
REFERENCES,
TRIGGER,
CREATE,
CONNECT,
TEMPORARY,
EJECUTAR, y
USAGE

Dependiendo del tipo de objeto (tabla, función, etc.), se aplican privilegios al objeto. Para asignar privilegios a los usuarios, se utiliza el comando GRANT.

Sintaxis de GRANT

La sintaxis básica del comando GRANT es la siguiente:

GRANT privilege [, ...]
ON object [, ...]
TO { PUBLIC | GROUP group | username }

privilege - los valores pueden ser: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
object- El nombre de un objeto al que conceder acceso. Los posibles objetos son: tabla, vista, secuencia
PUBLIC - Un formulario corto que representa a todos los usuarios.
GRUPO group - Un grupo al que conceder privilegios.
username- El nombre de un usuario al que conceder privilegios. PUBLIC es una forma abreviada que representa a todos los usuarios.

Los privilegios se pueden revocar mediante el comando REVOKE.

Sintaxis de REVOKE

La sintaxis básica para el comando REVOKE es la siguiente:

REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP groupname | username }

privilege - los valores pueden ser: SELECT, INSERT, UPDATE, DELETE, RULE, ALL.
object- El nombre de un objeto al que conceder acceso. Los posibles objetos son: tabla, vista, secuencia
PUBLIC - Un formulario corto que representa a todos los usuarios.
GRUPO group - Un grupo al que conceder privilegios.
username- El nombre de un usuario al que conceder privilegios. PUBLIC es una forma abreviada que representa a todos los usuarios.

Ejemplo

Para comprender los privilegios, primero creemos un USUARIO de la siguiente manera:

testdb=# CREATE USER manisha WITH PASSWORD 'password';
CREATE ROLE

El mensaje CREAR ROL indica que se ha creado el USUARIO "manisha".

Considere la tabla EMPRESA que tiene registros de la siguiente manera:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

A continuación, concedamos todos los privilegios en una mesa EMPRESA al usuario "manisha" de la siguiente manera:

testdb=# GRANT ALL ON COMPANY TO manisha;
GRANT

El mensaje GRANT indica que todos los privilegios están asignados al USUARIO.

A continuación, revoquemos los privilegios del USUARIO "manisha" de la siguiente manera:

testdb=# REVOKE ALL ON COMPANY FROM manisha;
REVOKE

El mensaje REVOKE indica que se revocan todos los privilegios del USUARIO.

Incluso puede eliminar al usuario de la siguiente manera:

testdb=# DROP USER manisha;
DROP ROLE

El mensaje DROP ROLE indica que el USUARIO 'Manisha' se ha eliminado de la base de datos.

Hablamos sobre los tipos de datos de fecha / hora en el capítulo Tipos de datos . Ahora, veamos los operadores y funciones de fecha / hora.

La siguiente tabla enumera los comportamientos de los operadores aritméticos básicos:

Operador	Ejemplo	Resultado
+	fecha '2001-09-28' + entero '7'	fecha '2001-10-05'
+	fecha '2001-09-28' + intervalo '1 hora'	marca de tiempo '2001-09-28 01:00:00'
+	fecha '2001-09-28' + hora '03: 00 '	marca de tiempo '2001-09-28 03:00:00'
+	intervalo '1 día' + intervalo '1 hora'	intervalo '1 día 01:00:00'
+	marca de tiempo '2001-09-28 01:00' + intervalo '23 horas '	marca de tiempo '2001-09-29 00:00:00'
+	tiempo '01: 00 '+ intervalo' 3 horas '	tiempo '04: 00: 00 '
-	- intervalo '23 horas '	intervalo '-23: 00: 00'
-	fecha '2001-10-01' - fecha '2001-09-28'	entero '3' (días)
-	fecha '2001-10-01' - entero '7'	fecha '2001-09-24'
-	fecha '2001-09-28' - intervalo '1 hora'	marca de tiempo '2001-09-27 23:00:00'
-	hora '05: 00 '- hora '03: 00'	intervalo '02: 00: 00 '
-	tiempo '05: 00 '- intervalo' 2 horas '	tiempo '03: 00: 00 '
-	marca de tiempo '2001-09-28 23:00' - intervalo '23 horas '	marca de tiempo '2001-09-28 00:00:00'
-	intervalo '1 día' - intervalo '1 hora'	intervalo '1 día -01: 00: 00'
-	marca de tiempo '2001-09-29 03:00' - marca de tiempo '2001-09-27 12:00'	intervalo '1 día 15:00:00'
*	900 * intervalo '1 segundo'	intervalo '00: 15: 00 '
*	21 * intervalo '1 día'	intervalo '21 días '
*	doble precisión '3,5' * intervalo '1 hora'	intervalo '03: 30: 00 '
/	intervalo '1 hora' / precisión doble '1,5'	intervalo '00: 40: 00 '

La siguiente es la lista de todas las funciones importantes relacionadas con la fecha y la hora disponibles.

S. No.	Función descriptiva
1	AÑOS() Restar argumentos
2	FECHA / HORA ACTUAL () Fecha y hora actual
3	DATE_PART () Obtener subcampo (equivalente a extraer)
4	EXTRAER() Obtener subcampo
5	ISFINITE () Prueba de fecha, hora e intervalo finitos (no +/- infinito)
6	JUSTIFICAR Ajustar intervalo

AGE (marca de tiempo, marca de tiempo), AGE (marca de tiempo)

S. No.	Función descriptiva
1	AGE(timestamp, timestamp) Cuando se invoca con la forma TIMESTAMP del segundo argumento, AGE () resta argumentos, produciendo un resultado "simbólico" que usa años y meses y es de tipo INTERVAL.
2	AGE(timestamp) Cuando se invoca solo con TIMESTAMP como argumento, AGE () se resta de current_date (a la medianoche).

Ejemplo de la función EDAD (marca de tiempo, marca de tiempo) es -

testdb=# SELECT AGE(timestamp '2001-04-10', timestamp '1957-06-13');

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

age
-------------------------
 43 years 9 mons 27 days

Ejemplo de la función EDAD (marca de tiempo) es -

testdb=# select age(timestamp '1957-06-13');

La declaración de PostgreSQL dada anteriormente producirá el siguiente resultado:

age
--------------------------
 55 years 10 mons 22 days

FECHA / HORA ACTUAL ()

PostgreSQL proporciona una serie de funciones que devuelven valores relacionados con la fecha y hora actuales. A continuación se muestran algunas funciones:

S. No.	Función descriptiva
1	CURRENT_DATE Entrega la fecha actual.
2	CURRENT_TIME Entrega valores con zona horaria.
3	CURRENT_TIMESTAMP Entrega valores con zona horaria.
4	CURRENT_TIME(precision) Opcionalmente, toma un parámetro de precisión, lo que hace que el resultado se redondee a esa cantidad de dígitos fraccionarios en el campo de segundos.
5	CURRENT_TIMESTAMP(precision) Opcionalmente, toma un parámetro de precisión, lo que hace que el resultado se redondee a esa cantidad de dígitos fraccionarios en el campo de segundos.
6	LOCALTIME Entrega valores sin zona horaria.
7	LOCALTIMESTAMP Entrega valores sin zona horaria.
8	LOCALTIME(precision) Opcionalmente, toma un parámetro de precisión, lo que hace que el resultado se redondee a esa cantidad de dígitos fraccionarios en el campo de segundos.
9	LOCALTIMESTAMP(precision) Opcionalmente, toma un parámetro de precisión, lo que hace que el resultado se redondee a esa cantidad de dígitos fraccionarios en el campo de segundos.

Ejemplos que utilizan las funciones de la tabla anterior:

testdb=# SELECT CURRENT_TIME;
       timetz
--------------------
 08:01:34.656+05:30
(1 row)


testdb=# SELECT CURRENT_DATE;
    date
------------
 2013-05-05
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP;
              now
-------------------------------
 2013-05-05 08:01:45.375+05:30
(1 row)


testdb=# SELECT CURRENT_TIMESTAMP(2);
         timestamptz
------------------------------
 2013-05-05 08:01:50.89+05:30
(1 row)


testdb=# SELECT LOCALTIMESTAMP;
       timestamp
------------------------
 2013-05-05 08:01:55.75
(1 row)

PostgreSQL también proporciona funciones que devuelven la hora de inicio de la declaración actual, así como la hora actual actual en el instante en que se llama a la función. Estas funciones son:

S. No.	Función descriptiva
1	transaction_timestamp() Es equivalente a CURRENT_TIMESTAMP, pero su nombre refleja claramente lo que devuelve.
2	statement_timestamp() Devuelve la hora de inicio de la declaración actual.
3	clock_timestamp() Devuelve la hora actual real y, por lo tanto, su valor cambia incluso dentro de un solo comando SQL.
4	timeofday() Devuelve la hora actual real, pero como una cadena de texto formateada en lugar de una marca de tiempo con el valor de la zona horaria.
5	now() Es un tradicional PostgreSQL equivalente a transaction_timestamp ().

DATE_PART (texto, marca de tiempo), DATE_PART (texto, intervalo), DATE_TRUNC (texto, marca de tiempo)

S. No.	Función descriptiva
1	DATE_PART('field', source) Estas funciones obtienen los subcampos. El parámetro de campo debe ser un valor de cadena, no un nombre. Los nombres de campo válidos son: siglo, día, década, dow, doy, época, hora, isodow, isoyear, microsegundos, milenio, milisegundos, minuto, mes, cuarto, segundo, zona horaria, timezone_hour, timezone_minute, semana, año.
2	DATE_TRUNC('field', source) Esta función es conceptualmente similar a la función trunc para números. fuente es una expresión de valor de tipo marca de tiempo o intervalo. campo selecciona con qué precisión truncar el valor de entrada. El valor de retorno es de tipo marca de tiempo o intervalo . Los valores válidos para el campo son: microsegundos, milisegundos, segundo, minuto, hora, día, semana, mes, trimestre, año, década, siglo, milenio

Los siguientes son ejemplos de funciones DATE_PART ( 'campo' , fuente):

testdb=# SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)


testdb=# SELECT date_part('hour', INTERVAL '4 hours 3 minutes');
 date_part
-----------
         4
(1 row)

Los siguientes son ejemplos de funciones DATE_TRUNC ( 'campo' , fuente):

testdb=# SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-02-16 20:00:00
(1 row)


testdb=# SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
     date_trunc
---------------------
 2001-01-01 00:00:00
(1 row)

EXTRACT (campo de la marca de tiempo), EXTRACT (campo del intervalo)

los EXTRACT(field FROM source)La función recupera subcampos como el año o la hora de los valores de fecha / hora. La fuente debe ser una expresión de valor de tipo marca de tiempo, hora o intervalo . El campo es un identificador o cadena que selecciona qué campo extraer del valor de origen. La función EXTRACCIÓN devuelve valores de tipo precisión doble .

Los siguientes son nombres de campo válidos (similares a los nombres de campo de la función DATE_PART): siglo, día, década, dow, doy, época, hora, isodow, isoyear, microsegundos, milenio, milisegundos, minuto, mes, trimestre, segundo, zona horaria, zona horaria_hora , timezone_minute, semana, año.

Los siguientes son ejemplos de funciones EXTRACT ( 'campo' , fuente):

testdb=# SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
 date_part
-----------
        20
(1 row)


testdb=# SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)

ISFINITE (fecha), ISFINITE (marca de tiempo), ISFINITE (intervalo)

S. No.	Función descriptiva
1	ISFINITE(date) Pruebas de fecha finita.
2	ISFINITE(timestamp) Pruebas de sello de tiempo finito.
3	ISFINITE(interval) Pruebas de intervalo finito.

Los siguientes son ejemplos de las funciones ISFINITE ():

testdb=# SELECT isfinite(date '2001-02-16');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(timestamp '2001-02-16 21:28:30');
 isfinite
----------
 t
(1 row)


testdb=# SELECT isfinite(interval '4 hours');
 isfinite
----------
 t
(1 row)

JUSTIFY_DAYS (intervalo), JUSTIFY_HOURS (intervalo), JUSTIFY_INTERVAL (intervalo)

S. No.	Función descriptiva
1	JUSTIFY_DAYS(interval) Ajusta el intervalo para que los períodos de 30 días se representen como meses. Devuelve elinterval tipo
2	JUSTIFY_HOURS(interval) Ajusta el intervalo para que los períodos de tiempo de 24 horas se representen como días. Devuelve elinterval tipo
3	JUSTIFY_INTERVAL(interval) Ajusta el intervalo usando JUSTIFY_DAYS y JUSTIFY_HOURS, con ajustes de señal adicionales. Devuelve elinterval tipo

Los siguientes son ejemplos de las funciones ISFINITE ():

testdb=# SELECT justify_days(interval '35 days');
 justify_days
--------------
 1 mon 5 days
(1 row)


testdb=# SELECT justify_hours(interval '27 hours');
 justify_hours
----------------
 1 day 03:00:00
(1 row)


testdb=# SELECT justify_interval(interval '1 mon -1 hour');
 justify_interval
------------------
 29 days 23:00:00
(1 row)

PostgreSQL functions, también conocidos como Procedimientos almacenados, le permiten realizar operaciones que normalmente requerirían varias consultas y viajes de ida y vuelta en una sola función dentro de la base de datos. Las funciones permiten la reutilización de la base de datos, ya que otras aplicaciones pueden interactuar directamente con sus procedimientos almacenados en lugar de un código de nivel medio o duplicado.

Las funciones se pueden crear en un lenguaje de su elección como SQL, PL / pgSQL, C, Python, etc.

Sintaxis

La sintaxis básica para crear una función es la siguiente:

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
RETURNS return_datatype AS $variable_name$
   DECLARE
      declaration;
      [...]
   BEGIN
      < function_body >
      [...]
      RETURN { variable_name | value }
   END; LANGUAGE plpgsql;

Dónde,

function-name especifica el nombre de la función.
La opción [O REEMPLAZAR] permite modificar una función existente.
La función debe contener un return declaración.
RETURNcláusula especifica el tipo de datos que va a devolver de la función. losreturn_datatype puede ser un tipo base, compuesto o de dominio, o puede hacer referencia al tipo de columna de una tabla.
function-body contiene la parte ejecutable.
La palabra clave AS se utiliza para crear una función independiente.
plpgsqles el nombre del lenguaje en el que se implementa la función. Aquí, usamos esta opción para PostgreSQL, puede ser SQL, C, interno o el nombre de un lenguaje procedimental definido por el usuario. Para compatibilidad con versiones anteriores, el nombre puede incluirse entre comillas simples.

Ejemplo

El siguiente ejemplo ilustra la creación y la llamada de una función independiente. Esta función devuelve el número total de registros en la tabla EMPRESA. Usaremos la tabla EMPRESA , que tiene los siguientes registros:

testdb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

La función totalRecords () es la siguiente:

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
	total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

Cuando se ejecuta la consulta anterior, el resultado sería:

testdb# CREATE FUNCTION

Ahora, ejecutemos una llamada a esta función y verifiquemos los registros en la tabla EMPRESA

testdb=# select totalRecords();

Cuando se ejecuta la consulta anterior, el resultado sería:

totalrecords
--------------
      7
(1 row)

Las funciones integradas de PostgreSQL, también llamadas funciones agregadas, se utilizan para realizar el procesamiento de cadenas o datos numéricos.

La siguiente es la lista de todas las funciones integradas de PostgreSQL de propósito general:

Función COUNT de PostgreSQL: la función agregada COUNT de PostgreSQL se utiliza para contar el número de filas en una tabla de base de datos.
Función PostgreSQL MAX: la función agregada PostgreSQL MAX nos permite seleccionar el valor más alto (máximo) para una determinada columna.
Función PostgreSQL MIN: la función agregada PostgreSQL MIN nos permite seleccionar el valor más bajo (mínimo) para una determinada columna.
Función de AVG de PostgreSQL: la función agregada de AVG de PostgreSQL selecciona el valor promedio para determinada columna de la tabla.
Función SUMA de PostgreSQL: la función agregada SUMA de PostgreSQL permite seleccionar el total de una columna numérica.
Funciones de ARRAY de PostgreSQL: la función agregada ARRAY de PostgreSQL coloca los valores de entrada, incluidos los nulos, concatenados en una matriz.
Funciones numéricas de PostgreSQL : lista completa de funciones de PostgreSQL necesarias para manipular números en SQL.
Funciones de cadena de PostgreSQL : lista completa de funciones de PostgreSQL necesarias para manipular cadenas en PostgreSQL.

Este tutorial va a utilizar libpqxxlibrary, que es la API cliente oficial de C ++ para PostgreSQL. El código fuente de libpqxx está disponible bajo la licencia BSD, por lo que puede descargarlo, pasarlo a otros, cambiarlo, venderlo, incluirlo en su propio código y compartir sus cambios con quien desee.

Instalación

La última versión de libpqxx está disponible para descargar desde el enlace Descargar Libpqxx . Así que descargue la última versión y siga los siguientes pasos:

wget http://pqxx.org/download/software/libpqxx/libpqxx-4.0.tar.gz
tar xvfz libpqxx-4.0.tar.gz
cd libpqxx-4.0
./configure
make
make install

Antes de comenzar a usar la interfaz C / C ++ PostgreSQL, busque el pg_hba.conf archivo en su directorio de instalación de PostgreSQL y agregue la siguiente línea -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Puede iniciar / reiniciar el servidor de Postgres en caso de que no se esté ejecutando usando el siguiente comando:

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

API de interfaz C / C ++

Las siguientes son rutinas de interfaz importantes que pueden ser suficientes para trabajar con la base de datos PostgreSQL desde su programa C / C ++. Si está buscando una aplicación más sofisticada, puede consultar la documentación oficial de libpqxx o puede utilizar las API disponibles comercialmente.

S. No.	API y descripción
1	pqxx::connection C( const std::string & dbstring ) Este es un typedef que se utilizará para conectarse a la base de datos. Aquí, dbstring proporciona los parámetros necesarios para conectarse a la base de datos, por ejemplodbname = testdb user = postgres password=pass123 hostaddr=127.0.0.1 port=5432. Si la conexión se configura correctamente, crea C con un objeto de conexión que proporciona varias funciones públicas útiles.
2	C.is_open() El método is_open () es un método público de objeto de conexión y devuelve un valor booleano. Si la conexión está activa, este método devuelve verdadero; de lo contrario, devuelve falso.
3	C.disconnect() Este método se utiliza para desconectar una conexión de base de datos abierta.
4	pqxx::work W( C ) Este es un typedef que se usará para crear un objeto transaccional usando la conexión C, que finalmente se usará para ejecutar sentencias SQL en modo transaccional. Si el objeto de transacción se crea correctamente, se asigna a la variable W, que se utilizará para acceder a los métodos públicos relacionados con el objeto de transacción.
5	W.exec(const std::string & sql) Este método público del objeto transaccional se utilizará para ejecutar la instrucción SQL.
6	W.commit() Este método público del objeto transaccional se utilizará para confirmar la transacción.
7	W.abort() Este método público del objeto transaccional se utilizará para revertir la transacción.
8	pqxx::nontransaction N( C ) Este es un typedef que se usará para crear un objeto no transaccional usando la conexión C, que finalmente se usará para ejecutar sentencias SQL en modo no transaccional. Si el objeto de transacción se crea correctamente, se asigna a la variable N, que se utilizará para acceder a los métodos públicos relacionados con el objeto no transaccional.
9	N.exec(const std::string & sql) Este método público del objeto no transaccional se utilizará para ejecutar la instrucción SQL y devuelve un objeto de resultado que en realidad es un interador que contiene todos los registros devueltos.

Conectando a la base de datos

El siguiente segmento de código C muestra cómo conectarse a una base de datos existente que se ejecuta en una máquina local en el puerto 5432. Aquí, utilicé barra invertida \ para la continuación de la línea.

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }
}

Ahora, compilemos y ejecutemos el programa anterior para conectarnos a nuestra base de datos testdb, que ya está disponible en su esquema y se puede acceder usando postgres de usuario y contraseña pass123 .

Puede utilizar el ID de usuario y la contraseña según la configuración de su base de datos. ¡Recuerde mantener -lpqxx y -lpq en el orden indicado! De lo contrario, el enlazador se quejará amargamente de las funciones que faltan con nombres que comienzan con "PQ".

$g++ test.cpp -lpqxx -lpq $./a.out
Opened database successfully: testdb

Crear una tabla

El siguiente segmento de código C se utilizará para crear una tabla en la base de datos creada anteriormente:

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "CREATE TABLE COMPANY("  \
      "ID INT PRIMARY KEY     NOT NULL," \
      "NAME           TEXT    NOT NULL," \
      "AGE            INT     NOT NULL," \
      "ADDRESS        CHAR(50)," \
      "SALARY         REAL );";

      /* Create a transactional object. */
      work W(C);
      
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Table created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Cuando el programa anterior se compila y ejecuta, creará la tabla COMPAÑÍA en su base de datos testdb y mostrará las siguientes declaraciones:

Opened database successfully: testdb
Table created successfully

INSERTAR Operación

El siguiente segmento de código C muestra cómo podemos crear registros en nuestra tabla EMPRESA creada en el ejemplo anterior:

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (1, 'Paul', 32, 'California', 20000.00 ); " \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "  \
         "VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); "     \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );" \
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)" \
         "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";

      /* Create a transactional object. */
      work W(C);
      
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records created successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Cuando el programa anterior se compila y ejecuta, creará los registros dados en la tabla EMPRESA y mostrará las siguientes dos líneas:

Opened database successfully: testdb
Records created successfully

SELECCIONAR Operación

El siguiente segmento de código C muestra cómo podemos buscar y mostrar registros de nuestra tabla EMPRESA creada en el ejemplo anterior:

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }

      /* Create SQL statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);
      
      /* Execute SQL query */
      result R( N.exec( sql ));
      
      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Cuando el programa anterior se compila y ejecuta, producirá el siguiente resultado:

Opened database successfully: testdb
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 20000
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
Operation done successfully

Operación ACTUALIZAR

El siguiente segmento de código C muestra cómo podemos usar la instrucción UPDATE para actualizar cualquier registro y luego buscar y mostrar registros actualizados de nuestra tabla EMPRESA:

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      
      /* Create a transactional object. */
      work W(C);
      /* Create  SQL UPDATE statement */
      sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records updated successfully" << endl;
      
      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);
      
      /* Execute SQL query */
      result R( N.exec( sql ));
      
      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Cuando el programa anterior se compila y ejecuta, producirá el siguiente resultado:

Opened database successfully: testdb
Records updated successfully
ID = 2
Name = Allen
Age = 25
Address = Texas
Salary = 15000
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

Operación DELETE

El siguiente segmento de código C muestra cómo podemos usar la instrucción DELETE para eliminar cualquier registro y luego buscar y mostrar los registros restantes de nuestra tabla EMPRESA:

#include <iostream>
#include <pqxx/pqxx> 

using namespace std;
using namespace pqxx;

int main(int argc, char* argv[]) {
   char * sql;
   
   try {
      connection C("dbname = testdb user = postgres password = cohondob \
      hostaddr = 127.0.0.1 port = 5432");
      if (C.is_open()) {
         cout << "Opened database successfully: " << C.dbname() << endl;
      } else {
         cout << "Can't open database" << endl;
         return 1;
      }
      
      /* Create a transactional object. */
      work W(C);
      /* Create  SQL DELETE statement */
      sql = "DELETE from COMPANY where ID = 2";
      /* Execute SQL query */
      W.exec( sql );
      W.commit();
      cout << "Records deleted successfully" << endl;
      
      /* Create SQL SELECT statement */
      sql = "SELECT * from COMPANY";

      /* Create a non-transactional object. */
      nontransaction N(C);
      
      /* Execute SQL query */
      result R( N.exec( sql ));
      
      /* List down all the records */
      for (result::const_iterator c = R.begin(); c != R.end(); ++c) {
         cout << "ID = " << c[0].as<int>() << endl;
         cout << "Name = " << c[1].as<string>() << endl;
         cout << "Age = " << c[2].as<int>() << endl;
         cout << "Address = " << c[3].as<string>() << endl;
         cout << "Salary = " << c[4].as<float>() << endl;
      }
      cout << "Operation done successfully" << endl;
      C.disconnect ();
   } catch (const std::exception &e) {
      cerr << e.what() << std::endl;
      return 1;
   }

   return 0;
}

Cuando el programa anterior se compila y ejecuta, producirá el siguiente resultado:

Opened database successfully: testdb
Records deleted successfully
ID = 3
Name = Teddy
Age = 23
Address = Norway
Salary = 20000
ID = 4
Name = Mark
Age = 25
Address = Rich-Mond
Salary = 65000
ID = 1
Name = Paul
Age = 32
Address = California
Salary = 25000
Operation done successfully

Instalación

Antes de comenzar a usar PostgreSQL en nuestros programas Java, debemos asegurarnos de tener PostgreSQL JDBC y Java configurados en la máquina. Puede consultar el tutorial de Java para la instalación de Java en su máquina. Ahora veamos cómo configurar el controlador JDBC de PostgreSQL.

Descargue la última versión de postgresql- (VERSION) .jdbc.jar del repositorio postgresql-jdbc .
Agregue el archivo jar descargado postgresql- (VERSION) .jdbc.jar en su ruta de clases, o puede usarlo junto con la opción -classpath como se explica a continuación en los ejemplos.

La siguiente sección asume que tiene poco conocimiento sobre los conceptos de Java JDBC. Si no lo ha hecho, le recomendamos que dedique media hora a JDBC Tutorial para familiarizarse con los conceptos que se explican a continuación.

Conectando a la base de datos

El siguiente código Java muestra cómo conectarse a una base de datos existente. Si la base de datos no existe, se creará y finalmente se devolverá un objeto de base de datos.

import java.sql.Connection;
import java.sql.DriverManager;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "postgres", "123");
      } catch (Exception e) {
         e.printStackTrace();
         System.err.println(e.getClass().getName()+": "+e.getMessage());
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

Antes de compilar y ejecutar el programa anterior, busque pg_hba.conf archivo en su directorio de instalación de PostgreSQL y agregue la siguiente línea -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Puede iniciar / reiniciar el servidor de Postgres en caso de que no se esté ejecutando usando el siguiente comando:

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Ahora, compilemos y ejecutemos el programa anterior para conectarnos con testdb. Aquí, estamos usandopostgres como ID de usuario y 123como contraseña para acceder a la base de datos. Puede cambiar esto según la configuración y configuración de su base de datos. También asumimos la versión actual del controlador JDBCpostgresql-9.2-1002.jdbc3.jar está disponible en la ruta actual.

C:\JavaPostgresIntegration>javac PostgreSQLJDBC.java
C:\JavaPostgresIntegration>java -cp c:\tools\postgresql-9.2-1002.jdbc3.jar;C:\JavaPostgresIntegration PostgreSQLJDBC
Open database successfully

Crear una tabla

El siguiente programa de Java se utilizará para crear una tabla en la base de datos previamente abierta. Asegúrese de no tener esta tabla en su base de datos de destino.

import java.sql.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
            "(ID INT PRIMARY KEY     NOT NULL," +
            " NAME           TEXT    NOT NULL, " +
            " AGE            INT     NOT NULL, " +
            " ADDRESS        CHAR(50), " +
            " SALARY         REAL)";
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

Cuando se compila y ejecuta un programa, creará la tabla EMPRESA en testdb base de datos y mostrará las siguientes dos líneas:

Opened database successfully
Table created successfully

INSERTAR Operación

El siguiente programa Java muestra cómo podemos crear registros en nuestra tabla EMPRESA creada en el ejemplo anterior:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class PostgreSQLJDBC {
   public static void main(String args[]) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (1, 'Paul', 32, 'California', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (2, 'Allen', 25, 'Texas', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
            + "VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch (Exception e) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

Cuando se compile y ejecute el programa anterior, creará registros dados en la tabla EMPRESA y mostrará las siguientes dos líneas:

Opened database successfully
Records created successfully

SELECCIONAR Operación

El siguiente programa Java muestra cómo podemos buscar y mostrar registros de nuestra tabla EMPRESA creada en el ejemplo anterior:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Cuando el programa se compila y ejecuta, producirá el siguiente resultado:

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

Operación ACTUALIZAR

El siguiente código Java muestra cómo podemos usar la instrucción UPDATE para actualizar cualquier registro y luego buscar y mostrar registros actualizados de nuestra tabla EMPRESA:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Cuando el programa se compila y ejecuta, producirá el siguiente resultado:

Opened database successfully
ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

Operation done successfully

Operación DELETE

El siguiente código Java muestra cómo podemos usar la declaración DELETE para eliminar cualquier registro y luego buscar y mostrar los registros restantes de nuestra tabla EMPRESA:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class PostgreSQLJDBC6 {
   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;
      try {
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/testdb",
            "manisha", "123");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID = 2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );
         while ( rs.next() ) {
            int id = rs.getInt("id");
            String  name = rs.getString("name");
            int age  = rs.getInt("age");
            String  address = rs.getString("address");
            float salary = rs.getFloat("salary");
            System.out.println( "ID = " + id );
            System.out.println( "NAME = " + name );
            System.out.println( "AGE = " + age );
            System.out.println( "ADDRESS = " + address );
            System.out.println( "SALARY = " + salary );
            System.out.println();
         }
         rs.close();
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

Cuando el programa se compila y ejecuta, producirá el siguiente resultado:

Opened database successfully
ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0
Operation done successfully

Instalación

La extensión PostgreSQL está habilitada de forma predeterminada en las últimas versiones de PHP 5.3.x. Es posible deshabilitarlo usando--without-pgsqlen tiempo de compilación. Aún así, puede usar el comando yum para instalar PHP -Interfaz PostgreSQL -

yum install php-pgsql

Antes de comenzar a utilizar la interfaz PHP PostgreSQL, busque el pg_hba.conf archivo en su directorio de instalación de PostgreSQL y agregue la siguiente línea -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Puede iniciar / reiniciar el servidor de Postgres, en caso de que no se esté ejecutando, usando el siguiente comando:

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

Los usuarios de Windows deben habilitar php_pgsql.dll para poder usar esta extensión. Esta DLL se incluye con las distribuciones de Windows en las últimas versiones de PHP 5.3.x

Para obtener instrucciones de instalación detalladas, consulte nuestro tutorial de PHP y su sitio web oficial.

API de interfaz PHP

Las siguientes son rutinas PHP importantes, que pueden ser suficientes para trabajar con la base de datos PostgreSQL desde su programa PHP. Si está buscando una aplicación más sofisticada, puede consultar la documentación oficial de PHP.

S. No.	API y descripción
1	resource pg_connect ( string $connection_string [, int $connect_type ] ) Esto abre una conexión a una base de datos PostgreSQL especificada por connection_string. Si PGSQL_CONNECT_FORCE_NEW se pasa como connect_type, entonces se crea una nueva conexión en el caso de una segunda llamada a pg_connect (), incluso si la connection_string es idéntica a una conexión existente.
2	bool pg_connection_reset ( resource $connection ) Esta rutina restablece la conexión. Es útil para la recuperación de errores. Devuelve VERDADERO en caso de éxito o FALSO en caso de error.
3	int pg_connection_status ( resource $connection ) Esta rutina devuelve el estado de la conexión especificada. Devuelve PGSQL_CONNECTION_OK o PGSQL_CONNECTION_BAD.
4	string pg_dbname ([ resource $connection ] ) Esta rutina devuelve el nombre de la base de datos que el recurso de conexión de PostgreSQL dado.
5	resource pg_prepare ([ resource $connection ], string $stmtname, string $query ) Esto envía una solicitud para crear una declaración preparada con los parámetros dados y espera su finalización.
6	resource pg_execute ([ resource $connection ], string $stmtname, array $params ) Esta rutina envía una solicitud para ejecutar una declaración preparada con parámetros dados y espera el resultado.
7	resource pg_query ([ resource $connection ], string $query ) Esta rutina ejecuta la consulta en la conexión de base de datos especificada.
8	array pg_fetch_row ( resource $result [, int $row ] ) Esta rutina obtiene una fila de datos del resultado asociado con el recurso de resultado especificado.
9	array pg_fetch_all ( resource $result ) Esta rutina devuelve una matriz que contiene todas las filas (registros) en el recurso de resultado.
10	int pg_affected_rows ( resource $result ) Esta rutina devuelve el número de filas afectadas por las consultas INSERT, UPDATE y DELETE.
11	int pg_num_rows ( resource $result ) Esta rutina devuelve el número de filas en un recurso de resultado de PostgreSQL, por ejemplo, el número de filas devueltas por la instrucción SELECT.
12	bool pg_close ([ resource $connection ] ) Esta rutina cierra la conexión no persistente a una base de datos PostgreSQL asociada con el recurso de conexión dado.
13	string pg_last_error ([ resource $connection ] ) Esta rutina devuelve el último mensaje de error para una conexión determinada.
14	string pg_escape_literal ([ resource $connection ], string $data ) Esta rutina escapa de un literal para su inserción en un campo de texto.
15	string pg_escape_string ([ resource $connection ], string $data ) Esta rutina escapa a una cadena para consultar la base de datos.

Conectando a la base de datos

El siguiente código PHP muestra cómo conectarse a una base de datos existente en una máquina local y finalmente se devolverá un objeto de conexión a la base de datos.

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
?>

Ahora, ejecutemos el programa anterior para abrir nuestra base de datos testdb: si la base de datos se abre correctamente, aparecerá el siguiente mensaje:

Opened database successfully

Crear una tabla

El siguiente programa PHP se utilizará para crear una tabla en una base de datos creada previamente:

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   
   $sql =<<<EOF CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
   } else {
      echo "Table created successfully\n";
   }
   pg_close($db);
?>

Cuando se ejecuta el programa anterior, creará la tabla EMPRESA en su testdb y mostrará los siguientes mensajes:

Opened database successfully
Table created successfully

INSERTAR Operación

El siguiente programa PHP muestra cómo podemos crear registros en nuestra tabla EMPRESA creada en el ejemplo anterior:

<?php
   $host        = "host=127.0.0.1";
   $port = "port=5432"; $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) { echo "Error : Unable to open database\n"; } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, 'Paul', 32, 'California', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;

   $ret = pg_query($db, $sql); if(!$ret) {
      echo pg_last_error($db); } else { echo "Records created successfully\n"; } pg_close($db);
?>

Cuando se ejecuta el programa dado anteriormente, creará los registros dados en la tabla EMPRESA y mostrará las siguientes dos líneas:

Opened database successfully
Records created successfully

SELECCIONAR Operación

El siguiente programa PHP muestra cómo podemos buscar y mostrar registros de nuestra tabla EMPRESA creada en el ejemplo anterior:

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }

   $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
      exit;
   } 
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n"; echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n"; echo "SALARY = ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

Cuando se ejecuta el programa dado anteriormente, producirá el siguiente resultado. Tenga en cuenta que los campos se devuelven en la secuencia en que se usaron al crear la tabla.

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Operación ACTUALIZAR

El siguiente código PHP muestra cómo podemos usar la instrucción UPDATE para actualizar cualquier registro y luego buscar y mostrar registros actualizados de nuestra tabla EMPRESA:

<?php
   $host        = "host=127.0.0.1";
   $port = "port=5432"; $dbname      = "dbname = testdb";
   $credentials = "user = postgres password=pass123"; $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db) { echo "Error : Unable to open database\n"; } else { echo "Opened database successfully\n"; } $sql =<<<EOF
      UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
   $ret = pg_query($db, $sql); if(!$ret) {
      echo pg_last_error($db); exit; } else { echo "Record updated successfully\n"; } $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = pg_query($db, $sql); if(!$ret) {
      echo pg_last_error($db); exit; } while($row = pg_fetch_row($ret)) { echo "ID = ". $row[0] . "\n";
      echo "NAME = ". $row[1] ."\n"; echo "ADDRESS = ". $row[2] ."\n";
      echo "SALARY =  ".$row[4] ."\n\n"; } echo "Operation done successfully\n"; pg_close($db);
?>

Cuando se ejecuta el programa anterior, producirá el siguiente resultado:

Opened database successfully
Record updated successfully
ID = 2
NAME = Allen
ADDRESS = 25
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

Operación DELETE

El siguiente código PHP muestra cómo podemos usar la declaración DELETE para eliminar cualquier registro y luego buscar y mostrar los registros restantes de nuestra tabla EMPRESA:

<?php
   $host = "host = 127.0.0.1"; $port        = "port = 5432";
   $dbname = "dbname = testdb"; $credentials = "user = postgres password=pass123";

   $db = pg_connect( "$host $port $dbname $credentials" ); if(!$db) {
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF DELETE from COMPANY where ID=2; EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
      exit;
   } else {
      echo "Record deleted successfully\n";
   }
   
   $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = pg_query($db, $sql);
   if(!$ret) { echo pg_last_error($db);
      exit;
   } 
   while($row = pg_fetch_row($ret)) {
      echo "ID = ". $row[0] . "\n"; echo "NAME = ". $row[1] ."\n";
      echo "ADDRESS = ". $row[2] ."\n"; echo "SALARY = ".$row[4] ."\n\n";
   }
   echo "Operation done successfully\n";
   pg_close($db);
?>

Cuando se ejecuta el programa anterior, producirá el siguiente resultado:

Opened database successfully
Record deleted successfully
ID = 3
NAME = Teddy
ADDRESS = 23
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = 25
SALARY =  65000

ID = 1
NAME = Paul
ADDRESS = 32
SALARY =  25000

Operation done successfully

Instalación

PostgreSQL se puede integrar con Perl usando el módulo Perl DBI, que es un módulo de acceso a la base de datos para el lenguaje de programación Perl. Define un conjunto de métodos, variables y convenciones que proporcionan una interfaz de base de datos estándar.

Aquí hay pasos simples para instalar el módulo DBI en su máquina Linux / Unix:

$ wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
$ tar xvfz DBI-1.625.tar.gz $ cd DBI-1.625
$ perl Makefile.PL $ make
$ make install

Si necesita instalar el controlador SQLite para DBI, puede instalarlo de la siguiente manera:

$ wget http://search.cpan.org/CPAN/authors/id/T/TU/TURNSTEP/DBD-Pg-2.19.3.tar.gz
$ tar xvfz DBD-Pg-2.19.3.tar.gz $ cd DBD-Pg-2.19.3
$ perl Makefile.PL $ make
$ make install

Antes de comenzar a utilizar la interfaz Perl PostgreSQL, busque el pg_hba.conf archivo en su directorio de instalación de PostgreSQL y agregue la siguiente línea -

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

Puede iniciar / reiniciar el servidor de Postgres, en caso de que no se esté ejecutando, usando el siguiente comando:

[root@host]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]

API de interfaz DBI

A continuación se muestran las rutinas DBI importantes, que pueden ser suficientes para su requisito de trabajar con la base de datos SQLite desde su programa Perl. Si está buscando una aplicación más sofisticada, puede consultar la documentación oficial de Perl DBI.

S. No.	API y descripción
1	DBI→connect($data_source, "userid", "password", \%attr) Establece una conexión de base de datos, o sesión, al $ data_source solicitado. Devuelve un objeto de identificador de base de datos si la conexión se realiza correctamente. La fuente de datos tiene la forma siguiente: DBI:Pg:dbname=$database;host=127.0.0.1;port=5432 Pg es el nombre del controlador de PostgreSQL y testdb es el nombre de la base de datos.
2	$dbh→do($sql) Esta rutina prepara y ejecuta una sola instrucción SQL. Devuelve el número de filas afectadas o indefinidas en caso de error. Un valor de retorno de -1 significa que el número de filas no se conoce, no es aplicable o no está disponible. Aquí $ dbh es un identificador devuelto por DBI → llamada connect ().
3	$dbh→prepare($sql) Esta rutina prepara una declaración para su posterior ejecución por parte del motor de base de datos y devuelve una referencia a un objeto identificador de declaración.
4	$sth→execute() Esta rutina realiza cualquier procesamiento necesario para ejecutar la instrucción preparada. Se devuelve un indef si se produce un error. Una ejecución exitosa siempre devuelve verdadero independientemente del número de filas afectadas. aquí$sth is a statement handle returned by $dbh → prepare ($ sql) llamada.
5	$sth→fetchrow_array() Esta rutina obtiene la siguiente fila de datos y la devuelve como una lista que contiene los valores del campo. Los campos nulos se devuelven como valores indefinidos en la lista.
6	$DBI::err Esto es equivalente a $ h → err, donde $h is any of the handle types like $dbh, $sth, or $drh. Esto devuelve el código de error del motor de base de datos nativo del último método de controlador llamado.
7	$DBI::errstr Esto es equivalente a $ h → errstr, donde $h is any of the handle types like $dbh, $sth, or $drh. Esto devuelve el mensaje de error del motor de base de datos nativo del último método DBI llamado.
8	$dbh->disconnect() Esta rutina cierra una conexión de base de datos previamente abierta por una llamada a DBI → connect ().

Conectando a la base de datos

El siguiente código de Perl muestra cómo conectarse a una base de datos existente. Si la base de datos no existe, se creará y finalmente se devolverá un objeto de base de datos.

#!/usr/bin/perl

use DBI;
use strict;

my $driver  = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) 
   or die $DBI::errstr;

print "Opened database successfully\n";

Ahora, ejecutemos el programa anterior para abrir nuestra base de datos testdb; si la base de datos se abre correctamente, aparecerá el siguiente mensaje:

Open database successfully

Crear una tabla

El siguiente programa Perl se utilizará para crear una tabla en la base de datos creada anteriormente:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname=$database;host=127.0.0.1;port=5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL););
my $rv = $dbh->do($stmt); if($rv < 0) {
   print $DBI::errstr; } else { print "Table created successfully\n"; } $dbh->disconnect();

Cuando se ejecuta el programa anterior, creará la tabla EMPRESA en su testdb y mostrará los siguientes mensajes:

Opened database successfully
Table created successfully

INSERTAR Operación

El siguiente programa Perl muestra cómo podemos crear registros en nuestra tabla EMPRESA creada en el ejemplo anterior:

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "Pg"; my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432"; my $userid = "postgres";
my $password = "pass123"; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 )); my $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (2, 'Allen', 25, 'Texas', 15000.00 ));
$rv = $dbh->do($stmt) or die $DBI::errstr;

$stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )); $rv = $dbh->do($stmt) or die $DBI::errstr; $stmt = qq(INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ););
$rv = $dbh->do($stmt) or die $DBI::errstr;

print "Records created successfully\n";
$dbh->disconnect();

Cuando se ejecuta el programa dado anteriormente, creará registros dados en la tabla EMPRESA y mostrará las siguientes dos líneas:

Opened database successfully
Records created successfully

SELECCIONAR Operación

El siguiente programa Perl muestra cómo podemos buscar y mostrar registros de nuestra tabla EMPRESA creada en el ejemplo anterior:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt ); my $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) { print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

Cuando se ejecuta el programa anterior, producirá el siguiente resultado:

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Operación ACTUALIZAR

El siguiente código de Perl muestra cómo podemos usar la instrucción UPDATE para actualizar cualquier registro y luego buscar y mostrar registros actualizados de nuestra tabla EMPRESA:

#!/usr/bin/perl

use DBI;
use strict;

my $driver = "Pg"; my $database = "testdb";
my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432"; my $userid = "postgres";
my $password = "pass123"; my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 }) or die $DBI::errstr;
print "Opened database successfully\n";

my $stmt = qq(UPDATE COMPANY set SALARY = 25000.00 where ID=1;); my $rv = $dbh->do($stmt) or die $DBI::errstr; if( $rv < 0 ) {
   print $DBI::errstr; }else{ print "Total number of rows updated : $rv\n";
}
$stmt = qq(SELECT id, name, address, salary from COMPANY;); my $sth = $dbh->prepare( $stmt );
$rv = $sth->execute() or die $DBI::errstr; if($rv < 0) {
   print $DBI::errstr; } while(my @row = $sth->fetchrow_array()) {
      print "ID = ". $row[0] . "\n"; print "NAME = ". $row[1] ."\n";
      print "ADDRESS = ". $row[2] ."\n"; print "SALARY = ". $row[3] ."\n\n";
}
print "Operation done successfully\n";
$dbh->disconnect();

Cuando se ejecuta el programa anterior, producirá el siguiente resultado:

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Operación DELETE

El siguiente código de Perl muestra cómo podemos usar la declaración DELETE para eliminar cualquier registro y luego buscar y mostrar los registros restantes de nuestra tabla EMPRESA:

#!/usr/bin/perl

use DBI;
use strict;

my $driver   = "Pg"; 
my $database = "testdb"; my $dsn = "DBI:$driver:dbname = $database;host = 127.0.0.1;port = 5432";
my $userid = "postgres"; my $password = "pass123";
my $dbh = DBI->connect($dsn, $userid, $password, { RaiseError => 1 })
   or die $DBI::errstr; print "Opened database successfully\n"; my $stmt = qq(DELETE from COMPANY where ID=2;);
my $rv = $dbh->do($stmt) or die $DBI::errstr;
if( $rv < 0 ) { print $DBI::errstr;
} else{
   print "Total number of rows deleted : $rv\n"; } $stmt = qq(SELECT id, name, address, salary  from COMPANY;);
my $sth = $dbh->prepare( $stmt ); $rv = $sth->execute() or die $DBI::errstr;
if($rv < 0) { print $DBI::errstr;
}
while(my @row = $sth->fetchrow_array()) { print "ID = ". $row[0] . "\n";
      print "NAME = ". $row[1] ."\n"; print "ADDRESS = ". $row[2] ."\n";
      print "SALARY =  ". $row[3] ."\n\n"; } print "Operation done successfully\n"; $dbh->disconnect();

Cuando se ejecuta el programa anterior, producirá el siguiente resultado:

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

Instalación

PostgreSQL se puede integrar con Python usando el módulo psycopg2. sycopg2 es un adaptador de base de datos PostgreSQL para el lenguaje de programación Python. psycopg2 fue escrito con el objetivo de ser muy pequeño y rápido, y estable como una roca. No es necesario instalar este módulo por separado porque se envía, por defecto, junto con la versión 2.5.x de Python en adelante.

Si no lo tiene instalado en su máquina, puede usar el comando yum para instalarlo de la siguiente manera:

$yum install python-psycopg2

Para usar el módulo psycopg2, primero debe crear un objeto Connection que represente la base de datos y luego, opcionalmente, puede crear un objeto cursor que lo ayudará a ejecutar todas las declaraciones SQL.

API del módulo Python psycopg2

Las siguientes son rutinas importantes del módulo psycopg2, que pueden ser suficientes para trabajar con la base de datos PostgreSQL desde su programa Python. Si está buscando una aplicación más sofisticada, puede consultar la documentación oficial del módulo Python psycopg2.

S. No.	API y descripción
1	psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432") Esta API abre una conexión a la base de datos PostgreSQL. Si la base de datos se abre correctamente, devuelve un objeto de conexión.
2	connection.cursor() Esta rutina crea una cursor que se utilizará en toda la programación de su base de datos con Python.
3	cursor.execute(sql [, optional parameters]) Esta rutina ejecuta una instrucción SQL. La instrucción SQL puede parametrizarse (es decir, marcadores de posición en lugar de literales SQL). El módulo psycopg2 admite marcadores de posición con el signo% s Por ejemplo: cursor.execute ("insertar en personas valores (% s,% s)", (quién, edad))
4	cursor.executemany(sql, seq_of_parameters) Esta rutina ejecuta un comando SQL contra todas las secuencias de parámetros o asignaciones que se encuentran en la secuencia sql.
5	cursor.callproc(procname[, parameters]) Esta rutina ejecuta un procedimiento de base de datos almacenada con el nombre dado. La secuencia de parámetros debe contener una entrada para cada argumento que espera el procedimiento.
6	cursor.rowcount Este atributo de solo lectura que devuelve el número total de filas de la base de datos que han sido modificadas, insertadas o eliminadas por la última ejecución * ().
7	connection.commit() Este método confirma la transacción actual. Si no llama a este método, cualquier cosa que haya hecho desde la última llamada a commit () no será visible desde otras conexiones de base de datos.
8	connection.rollback() Este método revierte cualquier cambio en la base de datos desde la última llamada a commit ().
9	connection.close() Este método cierra la conexión a la base de datos. Tenga en cuenta que esto no llama automáticamente a commit (). Si cierra la conexión de su base de datos sin llamar a commit () primero, ¡sus cambios se perderán!
10	cursor.fetchone() Este método obtiene la siguiente fila de un conjunto de resultados de consulta y devuelve una sola secuencia, o None cuando no hay más datos disponibles.
11	cursor.fetchmany([size=cursor.arraysize]) Esta rutina recupera el siguiente conjunto de filas del resultado de una consulta y devuelve una lista. Se devuelve una lista vacía cuando no hay más filas disponibles. El método intenta obtener tantas filas como indique el parámetro de tamaño.
12	cursor.fetchall() Esta rutina recupera todas las filas (restantes) del resultado de una consulta y devuelve una lista. Se devuelve una lista vacía cuando no hay filas disponibles.

Conectando a la base de datos

El siguiente código de Python muestra cómo conectarse a una base de datos existente. Si la base de datos no existe, se creará y finalmente se devolverá un objeto de base de datos.

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")

print "Opened database successfully"

Aquí, también puede proporcionar la base de datos testdb como nombre y si la base de datos se abre con éxito, aparecerá el siguiente mensaje:

Open database successfully

Crear una tabla

El siguiente programa de Python se utilizará para crear una tabla en la base de datos creada anteriormente:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);''')
print "Table created successfully"

conn.commit()
conn.close()

Cuando se ejecuta el programa anterior, creará la tabla EMPRESA en su test.db y mostrará los siguientes mensajes:

Opened database successfully
Table created successfully

INSERTAR Operación

El siguiente programa de Python muestra cómo podemos crear registros en nuestra tabla EMPRESA creada en el ejemplo anterior:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

Cuando se ejecuta el programa dado anteriormente, creará registros dados en la tabla EMPRESA y mostrará las siguientes dos líneas:

Opened database successfully
Records created successfully

SELECCIONAR Operación

El siguiente programa de Python muestra cómo podemos buscar y mostrar registros de nuestra tabla EMPRESA creada en el ejemplo anterior:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Cuando se ejecuta el programa anterior, producirá el siguiente resultado:

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

Operación ACTUALIZAR

El siguiente código de Python muestra cómo podemos usar la instrucción UPDATE para actualizar cualquier registro y luego buscar y mostrar registros actualizados de nuestra tabla EMPRESA:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Cuando se ejecuta el programa anterior, producirá el siguiente resultado:

Opened database successfully
Total number of rows updated : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

Operación DELETE

El siguiente código de Python muestra cómo podemos usar la declaración DELETE para eliminar cualquier registro y luego buscar y mostrar los registros restantes de nuestra tabla EMPRESA:

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print "Total number of rows deleted :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

Cuando se ejecuta el programa anterior, producirá el siguiente resultado:

Opened database successfully
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully