Introduzione a COPY
In PostgreSQL, il comando SQL COPY è usato per fare duplicati di tabelle, record e altri oggetti, ma è anche utile per trasferire dati da un formato ad un altro. Per esempio, il comando COPY può essere usato per inserire dati CSV in una tabella come record PostgreSQL. Questo articolo fornirà diversi esempi di PostgreSQL COPY che illustrano come usare questo comando come parte della vostra amministrazione del database.
Dichiarazione COPY di PostgreSQL
La dichiarazione SQL COPY è simile ai comandi pg_dump e pg_restore che sono usati per il backup e il ripristino di tabelle e database. Si può usare il comando COPY per spostare file all’interno di una tabella PostgreSQL o per mettere i dati di una tabella PostgreSQL in un file.
Prequisiti
Prima di iniziare a guardare i nostri esempi di PostgreSQL COPY, abbiamo bisogno di rivedere alcuni dei prerequisiti per questo tutorial:
- Primo, dovrai avere PostgreSQL installato e funzionante sulla tua macchina. Se stai usando Linux, prova il comando
service postgresql statusin un terminale per vedere se PostgreSQL è installato e funzionante. Puoi poi usare CTRL + C per uscire dal processo. Su macOS o Linux, è anche possibile eseguire il comandogrepattraversopsper cercare eventuali processi PostgreSQL in esecuzione:
|
1
|
ps aux | grep postgres
|
Si può anche usare psql -V per confermare che l’interfaccia a riga di comando psql è installata.è installata e funzionante; Questo comando restituisce la versione dell’utilità e le informazioni sull’installazione.
Si dovrebbe vedere un output simile al seguente:
|
1
|
psql (PostgreSQL) 11.5
|
Connettersi all’interfaccia a riga di comando psql per PostgreSQL
Una volta che tutti i prerequisiti sono a posto, si può provare a connettersi all’interfaccia psql. Usa il sudo su - postgres in un terminale seguito da psql. Il comando psql --help può essere utilizzato per ottenere maggiori informazioni sulla connessione a un database PostgreSQL con un utente.
Creare un database PostgreSQL
Ecco il comando che useremo nell’interfaccia psql per creare un database PostgreSQL:
|
1
|
CREATE DATABASE somedb;
|
Ora possiamo connetterci al nostro database usando il comando \c così possiamo iniziare ad inserire le query.
Creare una tabella PostgreSQL
In seguito, creeremo una tabella da usare nei nostri esempi:
|
1
2 3 4 5 |
CREATE TABLE csv_tbl(
id SERIAL PRIMARY KEY, nome TEXT, cognome TEXT ); |
Esempi di copia PostgreSQL
A questo punto, siamo pronti per tuffarci in alcuni esempi di come possiamo usare il comando SQL COPY per duplicare o trasferire dati.
Copiare dati da file di testo in Postgres
Un formato di file TEXT è composto da righe composte da colonne separate da un carattere delimitatore; i dati vengono quindi scritti o letti come un file di testo con una riga di tabella per riga.
|
1
2 |
COPY TABLE_NAME (col, col2)
FROM / TO ‘filename’ DELIMITER ‘ ‘; |
Possiamo usare il comando COPY di PostgreSQL per esportare un file CSV nella nostra tabella demo con un formato TEXT:
|
1
2 |
COPY demo(nome, cognome) TO
‘demo.txt’ DELIMITER ‘ ‘; |
Copiare dati binari in Postgres
Un BINARY formato di file è identificato come un file ‘non testo’ poiché tutti i dati sono memorizzati o letti in formato binario. Questo può essere più veloce del formato testo e csv.
Proviamoci a usare il comando COPY per esportare un singolo record di una tabella in un file binario. Ecco la sintassi di base:
|
1
2 3 4 5 6 |
COPY (
SELECT col1, col2, col3 FROM TABLE_NAME WHERE {condition} ) TO STDOUT WITH BINARY; |
Per il nostro esempio, il comando sarà come questo:
|
1
2 3 4 5 |
COPY (
SELECT firstname, cognome FROM demo WHERE id=50 ) TO STDOUT WITH BINARY; PGCOPY |
Inserire dati CSV in PostgreSQL usando COPY
Ora che abbiamo visto un paio di esempi di esportazione dati, proviamo a usare COPY per inserire dati CSV in una tabella PostgreSQL. Useremo le seguenti due righe di dati CSV di prova per illustrare come si può usare COPY per inserire dati in Postgres da un file CSV:
|
1
2 3 |
id, str_col, int_col, bool_col
1, “sono da un file CSV”, 112233, true 2, “ciao, Postgres”, 8765, false |
terminologia CSV
Quando si parla di file CSV, ci sono un paio di termini chiave da capire:
-
DELIMITER– Un delimitatore è un carattere che separa ogni riga del file in colonne; in un file CSV, il delimitatore è una virgola. I delimitatori non possono essere usati nel formato binario. -
HEADER– Quando viene creato un file CSV, la riga di intestazione è la prima riga del file che contiene i nomi delle colonne.
Utilizzare le parole chiave COPY e FROM per inserire dati Postgres da un file
La seguente istruzione SQL inserirà righe di dati CSV in una tabella chiamata some_table:
|
1
2 3 |
COPY some_table
FROM ‘/Users/macuser/postgres-data.csv’ DELIMITER ‘,’ CSV HEADER; |
NOTA: Assicuratevi di passare il percorso assoluto del file come una stringa nella vostra FROM clausola per evitare errori.
Utilizzare COPY per tagliare gli spazi bianchi dai dati delle tabelle PostgreSQL
Un piccolo problema nell’utilizzo di COPY per inserire dati CSV è che risulterà in spazi bianchi iniziali e finali nella nostra colonna stringa. Fortunatamente, c’è una semplice soluzione a questo problema. Proviamo di nuovo a inserire i nostri dati CSV, e questa volta useremo una tabella temporanea per memorizzare i dati. Useremo poi la funzione LTRIM di SQL per togliere gli spazi bianchi dalla nostra colonna stringa prima di inserirla nella nostra tabella PostgreSQL permanente.
NOTA: Assicuratevi di troncare la tabella di prova digitando TRUNCATE some_table; in psql prima di reinserire i dati CSV per evitare qualsiasi errore di chiave duplicata.
Creare una tabella temporanea per memorizzare i dati CSV
La seguente istruzione SQL creerà una tabella temporanea per i nostri dati CSV:
|
1
2 |
CREATE TEMP TABLE temp_tbl AS
SELECT * FROM some_table LIMIT 0; |
Questo comando SQL restituirà SELECT 0 se ha successo.
Ora, proviamo di nuovo il comando COPY, ma questa volta copiamo le righe del CSV nella tabella temporanea:
|
1
2 3 |
COPY temp_tbl
FROM ‘/Users/macuser/postgres-data.csv’ DELIMITER ‘,’ CSV HEADER; |
Lo statement SQL mostrato sopra dovrebbe restituire COPY seguito dal numero di righe CSV copiate.
Utilizzare LTRIM() per togliere gli spazi bianchi dai dati della tabella PostgreSQL
Il nostro prossimo esempio inserirà i dati della tabella temporanea nella tabella permanente. Quando eseguiamo questa dichiarazione INSERT, dobbiamo specificare esplicitamente ogni intestazione di colonna e assicurarci di passare la colonna stringa alla funzione LTRIM() per eliminare gli spazi bianchi:
|
1
2 3 4 |
INSERT INTO some_table
(id, str_col, int_col, bool_col) SELECT id, LTRIM(str_col), int_col, bool_col FROM temp_tbl; |
Dopo aver eseguito questo INSERT comando, i record dovrebbero essere copiati dalla tabella temporanea nella nostra some_table tabella PostgreSQL. Ora useremo l’istruzione SELECT * FROM some_table per verificare che i dati CSV siano stati inseriti senza gli spazi bianchi iniziali e finali.
Il comando mostrato sotto è necessario solo se avete bisogno di liberare memoria o spazio per un breve periodo; altrimenti, la tabella temporanea sarà eliminata automaticamente quando terminate la sessione psql:
|
1
|
DROP TABLE temp_tbl;
|
Conclusione
Non c’è dubbio che il comando SQL COPY può essere usato per una varietà di compiti di amministrazione del database. Non solo si può usare il comando per duplicare tabelle e record, ma si può anche usare COPY per trasferire dati da un formato all’altro. In questo articolo, vi abbiamo mostrato esempi di PostgreSQL COPY che illustrano come importare ed esportare dati tra un file CSV e una tabella PostgreSQL. Con questi esempi per iniziare, sarete in grado di sfruttare la potenza del comando COPY nella vostra implementazione PostgreSQL.
0 commenti