Ejemplo de INSERT de Postgresql

INSERTAR: crea nuevas filas en una tabla

Sinopsis

[WITH[ RECURSIVE ] with_query [,...]]INSERTINTO table_name [AS alias ][( column_name [,...])][ OVERRIDING USER  VALUE]
     query 
    [ON CONFLICT [ conflict_target ] conflict_action ][RETURNING*| output_expression [[AS] output_name ][,...]]where conflict_target can be one of:

    (  index_column_name  [COLLATE collation ][ opclass ][,...])[WHERE index_predicate ]ONCONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DOUPDATESET ( column_name [,...])=[ROW]( DEFAULT  [,...]) [,...][WHERE condition ]

Descripción

INSERT inserta nuevas filas en una tabla. Se pueden insertar una o más filas especificadas por expresiones de valor, o cero o más filas resultantes de una consulta.

Los nombres de las columnas de destino se pueden enumerar en cualquier orden. Si no se proporciona ninguna lista de nombres de columna, el valor predeterminado son todas las columnas de la tabla en su orden declarado; o el primero N nombres de columna, si solo hay N columnas suministradas por el VALUES cláusula o query. Los valores proporcionados por el VALUES cláusula o query están asociados con la lista de columnas explícita o implícita de izquierda a derecha.

Cada columna que no esté presente en la lista de columnas explícitas o implícitas se completará con un valor predeterminado, ya sea su valor predeterminado declarado o nulo si no hay ninguno.

Si la expresión de cualquier columna no es del tipo de datos correcto, se intentará la conversión automática de tipos.

ON CONFLICT se puede utilizar para especificar una acción alternativa para generar una restricción única o un error de violación de restricción de exclusión. (Consulte la cláusula SOBRE CONFLICTO a continuación).

El opcional RETURNING causas de la cláusula INSERT para calcular y devolver valores basados ​​en cada fila realmente insertada (o actualizada, si un ON CONFLICT DO UPDATE se utilizó la cláusula). Esto es principalmente útil para obtener valores que fueron proporcionados por valores predeterminados, como un número de secuencia de serie. Sin embargo, se permite cualquier expresión que utilice las columnas de la tabla. La sintaxis del RETURNING lista es idéntica a la de la lista de salida de SELECT. Solo se devolverán las filas que se insertaron o actualizaron correctamente. Por ejemplo, si una fila se bloqueó pero no se actualizó porque un ON CONFLICT DO UPDATE ... WHERE cláusula condition no quedó satisfecho, la fila no se devolverá.

Debes tener INSERT privilegio sobre una mesa para insertar en ella. Si ON CONFLICT DO UPDATE está presente, UPDATE También se requiere privilegio sobre la mesa.

Si se especifica una lista de columnas, solo necesita INSERT privilegio en las columnas enumeradas. Del mismo modo, cuando ON CONFLICT DO UPDATE se especifica, solo necesitas UPDATE privilegio en la (s) columna (s) que se enumeran para actualizar. Sin embargo, ON CONFLICT DO UPDATE también requiere SELECT privilegio en cualquier columna cuyos valores se leen en el ON CONFLICT DO UPDATE expresiones o condition.

Uso del RETURNING la cláusula requiere SELECT privilegio en todas las columnas mencionadas en RETURNING. Si usa el query cláusula para insertar filas de una consulta, por supuesto, debe tener SELECT privilegio sobre cualquier tabla o columna utilizada en la consulta.

Parámetros

Insertar

Esta sección cubre los parámetros que pueden usarse cuando solo se insertan nuevas filas. Parámetros exclusivamente usado con el ON CONFLICT cláusula se describen por separado.

with_query

los WITH La cláusula le permite especificar una o más subconsultas a las que se puede hacer referencia por nombre en la INSERT consulta. Consulte la Sección 7.8 y SELECT para obtener más detalles.

Es posible que el query (SELECT declaración) para contener también un WITH cláusula. En tal caso, ambos conjuntos de with_query se puede hacer referencia dentro de la query, pero el segundo tiene prioridad ya que está más anidado.

table_name

El nombre (opcionalmente calificado por esquema) de una tabla existente.

alias

Un nombre sustituto para table_name. Cuando se proporciona un alias, oculta completamente el nombre real de la tabla. Esto es particularmente útil cuando ON CONFLICT DO UPDATE apunta a una tabla llamada excluded, ya que de lo contrario se tomará como el nombre de la tabla especial que representa las filas propuestas para la inserción.

column_name

El nombre de una columna en la tabla nombrada por table_name. El nombre de la columna se puede calificar con un nombre de subcampo o un subíndice de matriz, si es necesario. (Insertar solo en algunos campos de una columna compuesta deja los otros campos nulos). Cuando se hace referencia a una columna con ON CONFLICT DO UPDATE, no incluya el nombre de la tabla en la especificación de una columna de destino. Por ejemplo, INSERT INTO table_name ... ON CONFLICT DO UPDATE SET table_name.col = 1 no es válido (esto sigue el comportamiento general para UPDATE).

OVERRIDING SYSTEM VALUE

Si se especifica esta cláusula, los valores proporcionados para las columnas de identidad anularán los valores generados por la secuencia predeterminada.

Para una columna de identidad definida como GENERATED ALWAYS, es un error insertar un valor explícito (que no sea DEFAULT) sin especificar OVERRIDING SYSTEM VALUE o OVERRIDING USER VALUE. (Para una columna de identidad definida como GENERATED BY DEFAULT, OVERRIDING SYSTEM VALUE es el comportamiento normal y especificarlo no hace nada, pero PostgreSQL lo permite como una extensión).

OVERRIDING USER VALUE

Si se especifica esta cláusula, se ignoran los valores proporcionados para las columnas de identidad y se aplican los valores generados por la secuencia predeterminada.

Esta cláusula es útil, por ejemplo, al copiar valores entre tablas. Escribiendo INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1 copiará de tbl1 todas las columnas que no son columnas de identidad en tbl2 mientras que los valores de las columnas de identidad en tbl2 será generado por las secuencias asociadas con tbl2.

DEFAULT VALUES

Todas las columnas se llenarán con sus valores predeterminados, como si DEFAULT se especificaron explícitamente para cada columna. (Un OVERRIDING cláusula no está permitida en este formulario.)

expression

Una expresión o valor para asignar a la columna correspondiente.

DEFAULT

La columna correspondiente se llenará con su valor predeterminado. Una columna de identidad se llenará con un nuevo valor generado por la secuencia asociada. Para una columna generada, se permite especificar esto, pero simplemente especifica el comportamiento normal de calcular la columna a partir de su expresión de generación.

query

Una consulta (SELECT declaración) que proporciona las filas que se van a insertar. Consulte la instrucción SELECT para obtener una descripción de la sintaxis.

output_expression

Una expresión que será calculada y devuelta por el INSERT comando después de insertar o actualizar cada fila. La expresión puede usar cualquier nombre de columna de la tabla nombrada por table_name. Escribir * para devolver todas las columnas de las filas insertadas o actualizadas.

output_name

Un nombre que se utilizará para una columna devuelta.

ON CONFLICT Cláusula

El opcional ON CONFLICT cláusula especifica una acción alternativa para generar una infracción única o un error de infracción de restricción de exclusión. Para cada fila individual propuesta para la inserción, la inserción prosigue o, si un árbitro restricción o índice especificado por conflict_target es violada, la alternativa conflict_action se toma. ON CONFLICT DO NOTHING simplemente evita insertar una fila como acción alternativa. ON CONFLICT DO UPDATE actualiza la fila existente que entra en conflicto con la fila propuesta para la inserción como acción alternativa.

conflict_target puede realizar inferencia de índice único. Al realizar inferencia, consta de uno o más index_column_name columnas y / o index_expression expresiones y un opcional index_predicate. Todos table_name índices únicos que, independientemente del orden, contienen exactamente el conflict_target-Las columnas / expresiones especificadas se infieren (se eligen) como índices de árbitro. Si una index_predicate se especifica, debe, como un requisito adicional para la inferencia, satisfacer los índices de árbitro. Tenga en cuenta que esto significa que un índice único no parcial (un índice único sin un predicado) será inferido (y por lo tanto utilizado por ON CONFLICT) si se dispone de un índice que satisfaga todos los demás criterios. Si un intento de inferencia no tiene éxito, se genera un error.

ON CONFLICT DO UPDATE garantiza un atómico INSERT o UPDATE Salir; siempre que no haya un error independiente, uno de esos dos resultados está garantizado, incluso con una alta concurrencia. Esto también se conoce como UPSERT – “ACTUALIZAR o INSERTAR“.

conflict_target

Especifica qué conflictos ON CONFLICT toma la acción alternativa eligiendo índices de árbitro. O realiza inferencia de índice único, o nombra una restricción explícitamente. Para ON CONFLICT DO NOTHING, es opcional especificar un conflict_target; cuando se omite, se manejan los conflictos con todas las restricciones utilizables (e índices únicos). Para ON CONFLICT DO UPDATE, a conflict_targetdebe ser proporcionado.

conflict_action

conflict_action especifica una alternativa ON CONFLICT acción. Puede ser DO NOTHINGo un DO UPDATE cláusula que especifica los detalles exactos de la UPDATE acción a realizar en caso de conflicto. los SET y WHERE cláusulas en ON CONFLICT DO UPDATE tener acceso a la fila existente usando el nombre de la tabla (o un alias), y a las filas propuestas para la inserción usando el especial excluded mesa. SELECT Se requiere privilegio en cualquier columna de la tabla de destino donde corresponda excluded se leen las columnas.

Tenga en cuenta que los efectos de todos por fila BEFORE INSERT los desencadenantes se reflejan en excluded valores, ya que esos efectos pueden haber contribuido a que la fila se excluya de la inserción.

index_column_name

El nombre de un table_name columna. Se utiliza para inferir índices de árbitro. Sigue CREATE INDEX formato. SELECT privilegio en index_column_name es requerido.

index_expression

Similar a index_column_name, pero se usa para inferir expresiones en table_name columnas que aparecen dentro de las definiciones de índice (no columnas simples). Sigue CREATE INDEX formato. SELECT privilegio en cualquier columna que aparezca dentro index_expression es requerido.

collation

Cuando se especifica, ordena que corresponda index_column_name o index_expression utilice una colación particular para que se hagan coincidir durante la inferencia. Por lo general, esto se omite, ya que las intercalaciones generalmente no afectan si se produce o no una infracción de la restricción. Sigue CREATE INDEX formato.

opclass

Cuando se especifica, ordena que corresponda index_column_name o index_expression use una clase de operador particular para que coincida durante la inferencia. Por lo general, esto se omite, ya que igualdad La semántica a menudo es equivalente en todas las clases de operador de un tipo de todos modos, o porque es suficiente confiar en que los índices únicos definidos tienen la definición pertinente de igualdad. Sigue CREATE INDEX formato.

index_predicate

Se utiliza para permitir la inferencia de índices únicos parciales. Se puede inferir cualquier índice que satisfaga el predicado (que en realidad no tiene por qué ser índices parciales). Sigue CREATE INDEX formato. SELECT privilegio en cualquier columna que aparezca dentro index_predicate es requerido.

constraint_name

Especifica explícitamente un árbitro restricción por nombre, en lugar de inferir una restricción o índice.

condition

Una expresión que devuelve un valor de tipo boolean. Solo filas para las que devuelve esta expresión true se actualizará, aunque todas las filas se bloqueará cuando el ON CONFLICT DO UPDATE se toma acción. Tenga en cuenta que condition se evalúa en último lugar, después de que se haya identificado un conflicto como candidato a actualizar.

Tenga en cuenta que las restricciones de exclusión no se admiten como árbitros con ON CONFLICT DO UPDATE. En todos los casos, solo NOT DEFERRABLE las restricciones y los índices únicos se admiten como árbitros.

INSERT con un ON CONFLICT DO UPDATE la cláusula es una “determinista“ declaración. Esto significa que no se permitirá que el comando afecte a ninguna fila existente más de una vez; se generará un error de violación de cardinalidad cuando surja esta situación. Las filas propuestas para la inserción no deben duplicarse entre sí en términos de atributos restringidos por un índice o restricción de árbitro.

Tenga en cuenta que actualmente no es compatible con ON CONFLICT DO UPDATE cláusula de un INSERT aplicado a una tabla particionada para actualizar la clave de partición de una fila en conflicto de modo que requiera que la fila se mueva a una nueva partición.

Propina

A menudo es preferible usar inferencia de índice único en lugar de nombrar una restricción directamente usando ON CONFLICT ON CONSTRAINTconstraint_name. La inferencia seguirá funcionando correctamente cuando el índice subyacente se sustituya por otro índice más o menos equivalente de forma superpuesta, por ejemplo, al utilizar CREATE UNIQUE INDEX ... CONCURRENTLY antes de dejar caer el índice que se está reemplazando.

Salidas

Una vez completado con éxito, INSERT comando devuelve una etiqueta de comando de la forma

INSERT oid count

los count es el número de filas insertadas o actualizadas. oid es siempre 0 (solía ser el OID asignado a la fila insertada si count era exactamente uno y la tabla de destino se declaró WITH OIDS y 0 en caso contrario, pero creando una tabla WITH OIDS ya no es compatible).

Si el INSERT El comando contiene un RETURNING cláusula, el resultado será similar al de una SELECT declaración que contiene las columnas y valores definidos en el RETURNING lista, calculada sobre las filas insertadas o actualizadas por el comando.

Notas

Si la tabla especificada es una tabla particionada, cada fila se enruta a la partición apropiada y se inserta en ella. Si la tabla especificada es una partición, se producirá un error si una de las filas de entrada viola la restricción de la partición.

Ejemplos de

Insertar una sola fila en la tabla films:

INSERTINTO films VALUES('UA502','Bananas',105,'1971-07-13','Comedy','82 minutes');

En este ejemplo, el len La columna se omite y, por lo tanto, tendrá el valor predeterminado:

INSERTINTO films (code, title, did, date_prod, kind)VALUES('T_601','Yojimbo',106,'1961-06-16','Drama');

Este ejemplo usa el DEFAULT cláusula para las columnas de fecha en lugar de especificar un valor:

INSERTINTO films VALUES('UA502','Bananas',105,DEFAULT,'Comedy','82 minutes');INSERTINTO films (code, title, did, date_prod, kind)VALUES('T_601','Yojimbo',106,DEFAULT,'Drama');

Para insertar una fila que consta en su totalidad de valores predeterminados:

INSERTINTO films DEFAULTVALUES;

Para insertar varias filas usando la fila múltiple VALUES sintaxis:

INSERTINTO films (code, title, did, date_prod, kind)VALUES('B6717','Tampopo',110,'1985-02-10','Comedy'),('HG120','The Dinner Game',140,DEFAULT,'Comedy');

Este ejemplo inserta algunas filas en la tabla films de una mesa tmp_films con el mismo diseño de columna que films:

INSERTINTO films SELECT*FROM tmp_films WHERE date_prod <'2004-05-07';

Este ejemplo se inserta en columnas de matriz:

-- Create an empty 3x3 gameboard for noughts-and-crossesINSERTINTO tictactoe (game, board[1:3][1:3])VALUES(1,'" "," "," "," "," "," "," "," "," "');-- The subscripts in the above example aren't really neededINSERTINTO tictactoe (game, board)VALUES(2,'X," "," "," ",O," "," ",X," "');

Insertar una sola fila en la tabla distributors, devolviendo el número de secuencia generado por el DEFAULT cláusula:

INSERTINTO distributors (did, dname)VALUES(DEFAULT,'XYZ Widgets')RETURNING did;

Aumente el recuento de ventas del vendedor que administra la cuenta de Acme Corporation y registre toda la fila actualizada junto con la hora actual en una tabla de registro:

WITH upd AS(UPDATE employees SET sales_count = sales_count +1WHERE id =(SELECT sales_person FROM accounts WHERE name ='Acme Corporation')RETURNING*)INSERTINTO employees_log SELECT*,current_timestampFROM upd;

Inserte o actualice nuevos distribuidores según corresponda. Supone que se ha definido un índice único que restringe los valores que aparecen en el did columna. Tenga en cuenta que el especial excluded La tabla se utiliza para hacer referencia a los valores propuestos originalmente para la inserción:

INSERTINTO distributors (did, dname)VALUES(5,'Gizmo Transglobal'),(6,'Associated Computing, Inc')ON CONFLICT (did)DOUPDATESET dname = EXCLUDED.dname;

Inserte un distribuidor o no haga nada por las filas propuestas para la inserción cuando exista una fila excluida existente (una fila con una columna o columnas restringidas coincidentes antes de que la inserción de filas active el disparo). El ejemplo asume que se ha definido un índice único que restringe los valores que aparecen en el did columna:

INSERTINTO distributors (did, dname)VALUES(7,'Redline GmbH')ON CONFLICT (did)DO NOTHING;

Inserte o actualice nuevos distribuidores según corresponda. El ejemplo asume que se ha definido un índice único que restringe los valores que aparecen en el did columna. WHERE La cláusula se usa para limitar las filas realmente actualizadas (sin embargo, cualquier fila existente no actualizada seguirá bloqueada):

-- Don't update existing distributors based in a certain ZIP codeINSERTINTO distributors AS d (did, dname)VALUES(8,'Anvil Distribution')ON CONFLICT (did)DOUPDATESET dname = EXCLUDED.dname ||' (formerly '|| d.dname ||')'WHERE d.zipcode <>'21201';-- Name a constraint directly in the statement (uses associated-- index to arbitrate taking the DO NOTHING action)INSERTINTO distributors (did, dname)VALUES(9,'Antwerp Design')ON CONFLICT ONCONSTRAINT distributors_pkey DO NOTHING;

Inserte un nuevo distribuidor si es posible; de lo contrario DO NOTHING. El ejemplo asume que se ha definido un índice único que restringe los valores que aparecen en el did columna en un subconjunto de filas donde el is_active La columna booleana se evalúa como true:

-- This statement could infer a partial unique index on "did"-- with a predicate of "WHERE is_active", but it could also-- just use a regular unique constraint on "did"INSERTINTO distributors (did, dname)VALUES(10,'Conrad International')ON CONFLICT (did)WHERE is_active DO NOTHING;

Compatibilidad

INSERT cumple con el estándar SQL, excepto que el RETURNING la cláusula es una extensión de PostgreSQL, al igual que la capacidad de usar WITH con INSERT, y la capacidad de especificar una acción alternativa con ON CONFLICT. Además, el caso en el que se omite una lista de nombres de columna, pero no todas las columnas se llenan desde el VALUES cláusula o query, no está permitido por el estándar.

El estándar SQL especifica que OVERRIDING SYSTEM VALUE solo se puede especificar si siempre existe una columna de identidad que se genera. PostgreSQL permite la cláusula en cualquier caso y la ignora si no es aplicable.

Posibles limitaciones del query cláusula están documentados en SELECT.