Sto generando un elenco di comandi SQL per esportare alcuni dati che alla fine eseguo utilizzando psql -f. Tutte le query ottengono lo stesso sottoinsieme di dati, quindi ho pensato di “prendere in considerazione le qualifiche e inserire un elenco di ID utente idonei in tabelle temporanee in questo modo
create temporary table tmp_export_users as (select id from users where ...) quindi fare riferimento a quello nel mio \ comandi di copia come
\copy (select ... from table where user_id in (select id from tmp_export_users)) TO "filename.csv" WITH CSV HEADER Questi sono tutti nello stesso file, uno per riga, e li eseguono -f ottengo lerrore che i comandi di copia possono ” Non vedo la tabella temporanea, quindi immagino che il comando di copia del client non debba effettivamente utilizzare la stessa sessione postgres di psql.
È corretto? Cè un modo per cambiare questo comportamento?
Risposta
\copy può utilizzare una tabella temporanea.
Per prima cosa ho provato e confermato con la versione 9.0 dalla riga di comando.
Ho quindi creato un file con SQL e il meta comando psql \copy utilizzando più tabelle temporanee. Ha funzionato anche per me.
CREATE TEMP TABLE tmp as SELECT * FROM tbl; \copy (SELECT * FROM tmp JOIN tbl USING (id)) TO "/var/lib/postgres/test1.csv"; Chiama:
psql -p5432 mydb -f test.sql Nota la chiusura punto e virgola, che è facoltativo alla fine di un file (terminato implicitamente), ma richiesto dopo qualsiasi altra istruzione SQL e anche dopo lultima se eseguita in psql in modo interattivo.
Normalmente , i metacomandi psql non possono essere combinati con SQL sulla stessa riga in un file eseguito per psql -f. Cito il manuale su psql :
Lanalisi degli argomenti si interrompe alla fine di la riga o quando viene trovata unaltra barra rovesciata non quotata. Una barra rovesciata non quotata viene considerata come linizio di un nuovo metacomando. La sequenza speciale
\\(due barre rovesciate) segna la fine degli argomenti e continua lanalisi dei comandi SQL, se presenti. In questo modo i comandi SQL e psql possono essere combinati liberamente su una riga. In ogni caso, gli argomenti di un metacomando non possono continuare oltre la fine della riga.
Si applicano regole diverse dopo \copy, però. In sostanza, psql torna automaticamente alla modalità SQL dopo \copy Vedi:
Ma hai scritto di avere tutti i comandi su righe separate. Quindi questa non può essere la spiegazione nel tuo caso.
A parte questo, hai considerato lutilizzo di COPY (il comando SQL ) invece di \copy ( il meta-comando psql )?
Ovviamente, il file di destinazione dovrebbe essere local al server non al client in questo caso. E si applicano privilegi di file diversi. Il manuale :
File denominati in un
COPYvengono letti o scritti direttamente dal server, non dallapplicazione client. Pertanto, devono risiedere o essere accessibili alla macchina del server del database, non al client. Devono essere accessibili e leggibili o scrivibili dallutente PostgreSQL (lID utente con cui viene eseguito il server), non dal client.
Commenti
- copy viene eseguito come utente postgres, \ copy esegue la copia per scrivere su std out e viene reindirizzato al file a cui lo si invia. Puoi anche chiamare psql, usare \ o per inviare loutput a un file, quindi eseguire una copia su stdout per ottenere un effetto simile.
- Per essere sicuro di aver eseguito il test nella mia risposta con un superutente (postgres ) e un utente fittizio. Entrambi lavorano per me. Stessi risultati sulla v8.4.
- Sì, se lutente unix di postgres può accedere a cose come / tmp dipende da cose come se SELinux è installato o meno e se lo lascia fuori dalla sua scatola. \ Copy o copy in stdout sono sicuramente i due modi più affidabili per usare copy.
- Grazie per le risposte a tutti. Sembra che ho trascurato di terminare la riga che ha creato una tabella temporanea con un punto e virgola, quindi ' non è stata creata. Funziona come previsto ora