PostgreSQL: columna de identidad

En PostgreSQL , la restricción GENERATED AS IDENTITY se usa para crear una columna de identidad de PostgreSQL. Permite a los usuarios asignar automáticamente un valor único a una columna. La restricción GENERATED AS IDENTITY es la variante conforme al estándar SQL de la columna SERIAL de PostgreSQL.

Syntax:
column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

Analicemos la sintaxis anterior.

• El tipo puede ser SMALLINT , INT o BIGINT .
• GENERATED SIEMPRE indica a PostgreSQL que siempre genere un valor para la columna de identidad. Si intenta insertar (o actualizar ) un valor en la columna GENERATED SIEMPRE COMO IDENTIDAD, PostgreSQL generará un error.
• GENERATED BY DEFAULT también le indica a PostgreSQL que genere un valor para la columna de identidad. Sin embargo, si proporciona un valor para insertar o actualizar, PostgreSQL usará ese valor para insertarlo en la columna de identidad en lugar de usar el valor generado por el sistema.

Ahora veamos algunos ejemplos.

Ejemplo 1:
Primero, cree una tabla nombrada color con color_id como columna de identidad:

CREATE TABLE color (
    color_id INT GENERATED ALWAYS AS IDENTITY,
    color_name VARCHAR NOT NULL
);

En segundo lugar, inserte una nueva fila en la color tabla:

INSERT INTO color (color_name)
VALUES
    ('Red');

Debido a que la columna color_id tiene la restricción GENERATED AS IDENTITY, PostgreSQL genera un valor para ella como se muestra en la consulta a continuación:

SELECT * FROM color;

Esto dará como resultado lo siguiente:

Tercero, inserte una nueva fila proporcionando valores para las columnas color_id y color_name:

INSERT INTO color (color_id, color_name)
VALUES
    (2, 'Green');

PostgreSQL emitió el siguiente error:

[Err] ERROR:  cannot insert into column "color_id"
DETAIL:  Column "color_id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

To fix the error, in this case, you can use the OVERRIDING SYSTEM VALUE clause as follows:

INSERT INTO color (color_id, color_name)
OVERRIDING SYSTEM VALUE 
VALUES
    (2, 'Green');

Ahora, si usamos la siguiente declaración para verificar la entrada:

SELECT * FROM color;

Producción:

Ejemplo 2:
En este ejemplo, usaremos GENERADO POR DEFECTO COMO IDENTIDAD para crear la misma tabla que creamos anteriormente. Para hacerlo, suelte la tabla de colores como se muestra a continuación:

DROP TABLE color;

Ahora vuelva a crear la tabla como se muestra a continuación:

CREATE TABLE color (
    color_id INT GENERATED BY DEFAULT AS IDENTITY,
    color_name VARCHAR NOT NULL
);

Ahora, inserte una fila en la tabla de colores:

INSERT INTO color (color_name)
VALUES
    ('White');

Ahora, inserte otra fila con un valor para la columna color_id:

INSERT INTO color (color_id, color_name)
VALUES
    (2, 'Yellow');

Aquí, a diferencia del ejemplo anterior que usa la restricción GENERATED SIEMPRE COMO IDENTIDAD, esta declaración también funciona.
Para verificar los datos insertados, use la siguiente declaración:

SELECT * FROM color;

Producción: