postgresql
INSÉRER

INSERT de base

Disons que nous avons un tableau simple appelé personne:

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

L'insertion la plus élémentaire consiste à insérer toutes les valeurs dans la table:

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

Si vous souhaitez insérer uniquement des colonnes spécifiques, vous devez indiquer explicitement quelles colonnes:

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

Notez que si des contraintes existent sur la table, telles que NOT NULL, vous devrez inclure ces colonnes dans les deux cas.

Insertion de plusieurs lignes

Vous pouvez insérer plusieurs lignes dans la base de données en même temps:

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

Insérer de select

Vous pouvez insérer des données dans une table à la suite d'une instruction select:

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

Notez que la projection de la sélection doit correspondre aux colonnes requises pour l'insertion. Dans ce cas, la table tmp_person a les mêmes colonnes que person .

Insérer des données en utilisant COPY

COPY est le mécanisme d'insertion en bloc de PostgreSQL. C'est un moyen pratique de transférer des données entre des fichiers et des tables, mais il est également beaucoup plus rapide que INSERT lorsque vous ajoutez plus de quelques milliers de lignes à la fois.

Commençons par créer un fichier de données exemple.

cat > samplet_data.csv

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

Et nous avons besoin d'un tableau à deux colonnes dans lequel ces données peuvent être importées.

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

Maintenant l'opération de copie proprement dite, cela créera six enregistrements dans la table.

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

Au lieu d’utiliser un fichier sur le disque, vous pouvez insérer des données depuis 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

Vous pouvez également copier des données d'une table pour les classer comme suit:

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

Pour plus de détails sur COPY, vous pouvez vérifier ici

INSERT données et valeurs RETURING

Si vous insérez des données dans une table avec une colonne d'incrémentation automatique et si vous souhaitez obtenir la valeur de la colonne d'incrémentation automatique.

Disons que vous avez une table appelée 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 vous voulez insérer des données dans my_table et obtenir l'identifiant de cette ligne:

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

La requête ci-dessus renvoie l'identifiant de la ligne où le nouvel enregistrement a été inséré.

SELECT les données dans le fichier.

Vous pouvez copier le tableau et le coller dans un fichier.

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 ... SUR LES CONFLITS, MISE À JOUR ...

depuis la version 9.5, postgres offre la fonctionnalité UPSERT avec l'instruction INSERT .

Disons que vous avez une table appelée my_table, créée dans plusieurs exemples précédents. Nous insérons une ligne, renvoyant la valeur PK de la ligne insérée:

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

INSERT 0 1

Maintenant, si nous essayons d'insérer une ligne avec une clé unique existante, cela générera une exception:

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 fonctionnalité Upsert offre la possibilité de l'insérer quand même, résolvant le conflit:

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