postgresql
ВСТАВИТЬ

Основной INSERT

Допустим, у нас есть простая таблица, называемая человеком:

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

Самая основная вставка включает в себя вставку всех значений в таблицу:

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

Если вы хотите вставить только определенные столбцы, вам нужно явно указать, какие столбцы:

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

Обратите внимание: если в таблице существуют какие-либо ограничения, например NOT NULL, вам необходимо будет включить эти столбцы в любом случае.

Вставка нескольких строк

Вы можете вставлять сразу несколько строк в базу данных:

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

Вставить из выбранного

Вы можете вставлять данные в таблицу в результате оператора select:

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

Обратите внимание, что проекция выбора должна соответствовать столбцам, необходимым для вставки. В этом случае таблица tmp_person имеет те же столбцы, что и person .

Вставить данные с помощью COPY

COPY - механизм вставки вставки PostgreSQL. Это удобный способ передачи данных между файлами и таблицами, но он также намного быстрее, чем INSERT при добавлении более нескольких тысяч строк за раз.

Начнем с создания образца файла данных.

cat > samplet_data.csv

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

И нам нужна таблица с двумя столбцами, в которую эти данные могут быть импортированы.

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

Теперь фактическая операция копирования, это создаст шесть записей в таблице.

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

Вместо использования файла на диске можно вставлять данные из 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

Также вы можете скопировать данные из таблицы в файл, как показано ниже:

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

Подробнее о КОПИИ вы можете узнать здесь.

Данные INSERT и значения RETURING

Если вы вставляете данные в таблицу с столбцом автоматического увеличения, и если вы хотите получить значение столбца автоматического увеличения.

Скажем, у вас есть таблица под названием 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)
);

Если вы хотите вставить данные в my_table и получить идентификатор этой строки:

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

Над запросом будет возвращен идентификатор строки, в которую была вставлена ​​новая запись.

ВЫБОР данных в файл.

Вы можете копировать таблицу и вставлять ее в файл.

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 - INSERT ... ON CONFLICT DO UPDATE ...

поскольку версия 9.5 postgres предлагает функциональность UPSERT с INSERT .

Скажем, у вас есть таблица my_table, созданная в нескольких предыдущих примерах. Мы вставляем строку, возвращающую значение PK вставленной строки:

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

INSERT 0 1

Теперь, если мы попытаемся вставить строку с существующим уникальным ключом, это вызовет исключение:

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.

Функция Upsert предлагает возможность вставить ее в любом случае, разрешая конфликт:

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