postgresql
INSERTAR

Básico INSERTAR

Digamos que tenemos una tabla simple llamada persona:

CREATE TABLE person (
    person_id BIGINT,
    name VARCHAR(255).
    age INT,
    city VARCHAR(255)
);

La inserción más básica consiste en insertar todos los valores en la tabla:

INSERT INTO person VALUES (1, 'john doe', 25, 'new york');

Si desea insertar solo columnas específicas, debe indicar explícitamente qué columnas:

INSERT INTO person (name, age) VALUES ('john doe', 25);

Tenga en cuenta que si existe alguna restricción en la tabla, como NOT NULL, se le pedirá que incluya esas columnas en cualquier caso.

Insertando múltiples filas

Puede insertar varias filas en la base de datos al mismo tiempo:

INSERT INTO person (name, age) VALUES 
  ('john doe', 25),
  ('jane doe', 20);

Insertar desde seleccionar

Puede insertar datos en una tabla como resultado de una declaración de selección:

INSERT INTO person SELECT * FROM tmp_person WHERE age < 30;

Tenga en cuenta que la proyección de la selección debe coincidir con las columnas necesarias para la inserción. En este caso, la tabla tmp_person tiene las mismas columnas que person .

Insertar datos utilizando COPY

COPY es el mecanismo de inserción masiva de PostgreSQL. Es una forma conveniente de transferir datos entre archivos y tablas, pero también es mucho más rápido que INSERT cuando se agregan más de unos pocos miles de filas a la vez.

Vamos a empezar por crear un archivo de datos de muestra.

cat > samplet_data.csv

1,Yogesh
2,Raunak
3,Varun
4,Kamal
5,Hari
6,Amit

Y necesitamos una tabla de dos columnas en la que se pueden importar estos datos.

CREATE TABLE copy_test(id int, name varchar(8));

Ahora la operación de copia real, esto creará seis registros en la tabla.

COPY copy_test FROM '/path/to/file/sample_data.csv' DELIMITER ',';

En lugar de usar un archivo en el disco, puede insertar datos desde la stdin

COPY copy_test FROM stdin DELIMITER ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 7,Amol
>> 8,Amar
>> \.
Time: 85254.306 ms

SELECT * FROM copy_test ;
 id |  name
----+--------
  1 | Yogesh
  3 | Varun
  5 | Hari
  7 | Amol
  2 | Raunak
  4 | Kamal
  6 | Amit
  8 | Amar

También puede copiar datos de una tabla a un archivo de la siguiente manera:

COPY copy_test TO 'path/to/file/sample_data.csv'  DELIMITER ',';

Para más detalles sobre COPY puede consultar aquí

Insertar datos y valores RETORNOS

Si está insertando datos en una tabla con una columna de incremento automático y si desea obtener el valor de la columna de incremento automático.

Digamos que tienes una tabla llamada my_table :

CREATE TABLE my_table
(
id serial NOT NULL, -- serial data type is auto incrementing four-byte integer
name character varying,
contact_number integer,
CONSTRAINT my_table_pkey PRIMARY KEY (id)
);

Si desea insertar datos en my_table y obtener el ID de esa fila:

INSERT INTO my_table(name, contact_number) VALUES ( 'USER', 8542621) RETURNING id;

La consulta anterior devolverá el ID de la fila donde se insertó el nuevo registro.

SELECCIONE los datos en el archivo.

Puede copiar la tabla y pegarla en un archivo.

postgres=# select * from my_table;
 c1 | c2 | c3 
----+----+----
  1 |  1 |  1
  2 |  2 |  2
  3 |  3 |  3
  4 |  4 |  4
  5 |  5 |   
(5 rows)

postgres=# copy my_table to '/home/postgres/my_table.txt' using delimiters '|' with null as 'null_string' csv header;
COPY 5
postgres=# \! cat my_table.txt
c1|c2|c3
1|1|1
2|2|2
3|3|3
4|4|4
5|5|null_string

UPSERT - INSERTAR ... EN CONFLICTO ACTUALIZAR ...

desde la versión 9.5, postgres ofrece la funcionalidad UPSERT con la declaración INSERT .

Digamos que tienes una tabla llamada my_table, creada en varios ejemplos anteriores. Insertamos una fila, devolviendo el valor PK de la fila insertada:

b=# INSERT INTO my_table (name,contact_number) values ('one',333) RETURNING id;
 id
----
  2
(1 row)

INSERT 0 1

Ahora, si intentamos insertar una fila con una clave única existente, se generará una excepción:

b=# INSERT INTO my_table values (2,'one',333);
ERROR:  duplicate key value violates unique constraint "my_table_pkey"
DETAIL:  Key (id)=(2) already exists.

La funcionalidad Upsert ofrece la capacidad de insertarlo de todos modos, resolviendo el conflicto:

b=# INSERT INTO my_table values (2,'one',333) ON CONFLICT (id) DO UPDATE SET name = my_table.name||' changed to: "two" at '||now() returning *;
 id |                       name                             | contact_number
----+-----------------------------------------------------------------------------------------------------------+----------------
  2 | one changed to: "two" at 2016-11-23 08:32:17.105179+00 |            333
(1 row)

INSERT 0 1