Eksportuj określone wiersze z tabeli PostgreSQL jako skrypt INSERT SQL
Mam schemat bazy danych o nazwie: nyummy
i tabelę o nazwie cimory
:
create table nyummy.cimory (
id numeric(10,0) not null,
name character varying(60) not null,
city character varying(50) not null,
CONSTRAINT cimory_pkey PRIMARY KEY (id)
);
Chcę wyeksportować dane tabeli cimory
jako plik skryptu insert SQL. Chcę jednak eksportować tylko te rekordy / dane, w których miasto jest równe 'Tokio' (Załóżmy, że dane miasta są małymi literami).
Nie ma znaczenia, czy rozwiązanie znajduje się w darmowych narzędziach GUI, czy w wierszu poleceń(chociaż rozwiązanie GUI tools jest lepsze). Próbowałem pgAdmin III, ale nie mogę znaleźć opcji, aby zrobić to.
9 answers
Utwórz tabelę z zestawem, który chcesz wyeksportować, a następnie użyj narzędzia linii poleceń pg_dump, aby wyeksportować do pliku:
create table export_table as
select id, name, city
from nyummy.cimory
where city = 'tokio'
$ pg_dump --table=export_table --data-only --column-inserts my_database > data.sql
--column-inserts
będzie zrzucać jako polecenia Wstaw z nazwami kolumn.
--data-only
nie wysyp schematu.
Jak skomentowano poniżej, utworzenie widoku zamiast tabeli uniemożliwi tworzenie tabeli za każdym razem, gdy konieczny jest nowy eksport.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-07-27 19:35:39
For a data-only export use COPY
.
Otrzymujesz plik z jednym wierszem tabeli w wierszu jako zwykły tekst( nie polecenia INSERT
), jest mniejszy i szybszy:
COPY (SELECT * FROM nyummy.cimory WHERE city = 'tokio') TO '/path/to/file.csv';
Import to samo do innej tabeli o tej samej strukturze w dowolnym miejscu z:
COPY other_tbl FROM '/path/to/file.csv';
COPY
zapisuje i odczytuje pliki lokalnie do serwera , W przeciwieństwie do programów klienckich, takich jak pg_dump
lub psql
, które odczytują i zapisują pliki lokalnie do klienta. Jeśli oba działają na tym samym maszyna, to nie ma większego znaczenia, ale ma znaczenie dla połączeń zdalnych.
Istnieje również \copy
polecenie psql, które:
Wykonuje kopię interfejsu (klienta). Jest to operacja, która prowadzi Polecenie SQL
COPY
, ale zamiast odczytu lub zapisu serwera podanego pliku, psql odczytuje lub zapisuje plik i przesyła dane pomiędzy serwerem a lokalnym systemem plików. Oznacza to, że plik dostępność i przywileje należą do użytkownika lokalnego, nie serwera i nie są wymagane uprawnienia superużytkownika SQL.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-08-28 21:43:16
To jest Łatwy i szybki sposób na eksport tabeli do skryptu za pomocą pgAdmin Ręcznie bez dodatkowych instalacji :
- Kliknij prawym przyciskiem myszy na tabeli docelowej i wybierz "Kopia zapasowa".
- Wybierz ścieżkę pliku do przechowywania kopii zapasowej. Jako Format wybierz "zwykły".
- otwórz zakładkę "Opcje zrzutu # 2" u dołu i zaznacz "Użyj wstawek kolumn".
- Kliknij przycisk kopia zapasowa.
- jeśli otworzysz plik wynikowy za pomocą czytnika tekstu (np. notepad++), Pobierz skrypt, aby utworzyć całą tabelę. Stamtąd można po prostu skopiować wygenerowane instrukcje INSERT -.
Ta metoda działa również z techniką tworzenia tabeli eksportowej, jak pokazano w odpowiedzi @Clodoaldo Neto.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-15 20:13:04
SQL Workbench ma taką funkcję.
Po uruchomieniu zapytania kliknij prawym przyciskiem myszy na wynikach zapytania i wybierz "Kopiuj dane jako SQL > SQL Insert"
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-11-27 12:58:12
W moim przypadku użycia udało mi się po prostu podłączyć do grepa.
pg_dump -U user_name --data-only --column-inserts -t nyummy.cimory | grep "tokyo" > tokyo.sql
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-08-25 15:38:41
Możesz zrobić widok tabeli z określonymi rekordami, a następnie zrzut pliku sql
CREATE VIEW foo AS
SELECT id,name,city FROM nyummy.cimory WHERE city = 'tokyo'
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-10-10 09:04:19
Po prostu wymyśliłem szybki zabieg, żeby to zrobić. Działa tylko dla jednego wiersza, więc tworzę tymczasowy widok, który po prostu wybiera wiersz, który chcę, a następnie zastępuje pg_temp.temp_view z aktualną tabelą, do której chcę wstawić.
CREATE OR REPLACE FUNCTION dv_util.gen_insert_statement(IN p_schema text, IN p_table text)
RETURNS text AS
$BODY$
DECLARE
selquery text;
valquery text;
selvalue text;
colvalue text;
colrec record;
BEGIN
selquery := 'INSERT INTO ' || quote_ident(p_schema) || '.' || quote_ident(p_table);
selquery := selquery || '(';
valquery := ' VALUES (';
FOR colrec IN SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = p_table and table_schema = p_schema
ORDER BY ordinal_position
LOOP
selquery := selquery || quote_ident(colrec.column_name) || ',';
selvalue :=
'SELECT CASE WHEN ' || quote_ident(colrec.column_name) || ' IS NULL' ||
' THEN ''NULL''' ||
' ELSE '''' || quote_literal('|| quote_ident(colrec.column_name) || ')::text || ''''' ||
' END' ||
' FROM '||quote_ident(p_schema)||'.'||quote_ident(p_table);
EXECUTE selvalue INTO colvalue;
valquery := valquery || colvalue || ',';
END LOOP;
-- Replace the last , with a )
selquery := substring(selquery,1,length(selquery)-1) || ')';
valquery := substring(valquery,1,length(valquery)-1) || ')';
selquery := selquery || valquery;
RETURN selquery;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
Wywołany TAK:
SELECT distinct dv_util.gen_insert_statement('pg_temp_' || sess_id::text,'my_data')
from pg_stat_activity
where procpid = pg_backend_pid()
Nie testowałem tego przed atakami iniekcyjnymi, proszę dać mi znać, jeśli wywołanie quote_literal nie jest do tego wystarczające.
Działa również tylko dla kolumn, które można po prostu wrzucić do ::text i z powrotem jeszcze raz.
Również to jest dla Greenplum, ale nie mogę wymyślić powodu, dla którego nie działałoby to na Postgres, CMIIW.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-02-17 13:53:06
Próbowałem napisać procedurę robiącą to, na podstawie kodów @ PhilHibbs, w inny sposób. Proszę spojrzeć i przetestować.
CREATE OR REPLACE FUNCTION dump(IN p_schema text, IN p_table text, IN p_where text)
RETURNS setof text AS
$BODY$
DECLARE
dumpquery_0 text;
dumpquery_1 text;
selquery text;
selvalue text;
valrec record;
colrec record;
BEGIN
-- ------ --
-- GLOBAL --
-- build base INSERT
-- build SELECT array[ ... ]
dumpquery_0 := 'INSERT INTO ' || quote_ident(p_schema) || '.' || quote_ident(p_table) || '(';
selquery := 'SELECT array[';
<<label0>>
FOR colrec IN SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = p_table and table_schema = p_schema
ORDER BY ordinal_position
LOOP
dumpquery_0 := dumpquery_0 || quote_ident(colrec.column_name) || ',';
selquery := selquery || 'CAST(' || quote_ident(colrec.column_name) || ' AS TEXT),';
END LOOP label0;
dumpquery_0 := substring(dumpquery_0 ,1,length(dumpquery_0)-1) || ')';
dumpquery_0 := dumpquery_0 || ' VALUES (';
selquery := substring(selquery ,1,length(selquery)-1) || '] AS MYARRAY';
selquery := selquery || ' FROM ' ||quote_ident(p_schema)||'.'||quote_ident(p_table);
selquery := selquery || ' WHERE '||p_where;
-- GLOBAL --
-- ------ --
-- ----------- --
-- SELECT LOOP --
-- execute SELECT built and loop on each row
<<label1>>
FOR valrec IN EXECUTE selquery
LOOP
dumpquery_1 := '';
IF not found THEN
EXIT ;
END IF;
-- ----------- --
-- LOOP ARRAY (EACH FIELDS) --
<<label2>>
FOREACH selvalue in ARRAY valrec.MYARRAY
LOOP
IF selvalue IS NULL
THEN selvalue := 'NULL';
ELSE selvalue := quote_literal(selvalue);
END IF;
dumpquery_1 := dumpquery_1 || selvalue || ',';
END LOOP label2;
dumpquery_1 := substring(dumpquery_1 ,1,length(dumpquery_1)-1) || ');';
-- LOOP ARRAY (EACH FIELD) --
-- ----------- --
-- debug: RETURN NEXT dumpquery_0 || dumpquery_1 || ' --' || selquery;
-- debug: RETURN NEXT selquery;
RETURN NEXT dumpquery_0 || dumpquery_1;
END LOOP label1 ;
-- SELECT LOOP --
-- ----------- --
RETURN ;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
A następnie:
-- for a range
SELECT dump('public', 'my_table','my_id between 123456 and 123459');
-- for the entire table
SELECT dump('public', 'my_table','true');
Testowane na moim postgres 9.1, z tabelą z mieszanym polem datatype (text, double, int,timestamp bez strefy czasowej, itd.).
Dlatego potrzebny jest odlew w typie tekstowym. Mój test działa poprawnie przez około 9m linii, wygląda na to, że nie tuż przed 18 minutami biegu.
Ps: znalazłem odpowiednik dla mysql na WWW.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-05-24 13:15:07
Czy próbowałeś w pgadmin wykonać zapytanie z opcją " EXECUTE QUERY WRITE RESULT TO FILE "
Jego tylko eksport danych, w przeciwnym razie spróbuj jak
pg_dump -t view_name DB_name > db.sql
- opcja t używana dla = = > zrzut tylko tabel (lub widoków lub sekwencji) dopasowujących tabelę, refer
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-10-10 09:22:06