Example with Unix-style file name: COPY (SELECT * FROM tbl) TO ‘/var/lib/postgres/myfile1.csv’ format csv; Read the manual about COPY (link to version 8.2). You have to use an absolute path for the target file. Be sure to double quote file names with spaces. Example for MS Windows: COPY (SELECT * FROM tbl) TO E'”C:\\Documents and … Read more
There are a number of factors at work here: Network latency and round-trip delays Per-statement overheads in PostgreSQL Context switches and scheduler delays COMMIT costs, if for people doing one commit per insert (you aren’t) COPY-specific optimisations for bulk loading Network latency If the server is remote, you might be “paying” a per-statement fixed time … Read more
You can work around this by piping the file through a program. For example I just used this to copy from a 24GB file on Windows 10 and PostgreSQL 11. copy t(c,d) from program ‘cmd /c “type x:\path\to\file.txt”‘ with (format text); This copies the text file file.txt into the table t, columns c and d. … Read more
COPY tbl FROM STDIN; is not supported by pgAdmin. You get a plain syntax error because Postgres gets the data as SQL code. Four possible solutions: 1. Use a multi-row INSERT instead: INSERT INTO beer(name, tags, alcohol, brewery, id, brewery_id, image) VALUES (‘Bons Voeux’, ‘blonde’, 9.5, ‘Brasserie Dupont’, 250, 130, ‘generic.png’) , (‘Boerke Blond’, ‘blonde’, … Read more
You need dynamic SQL: CREATE OR REPLACE FUNCTION loaddata(filepathname text) RETURNS void AS $func$ BEGIN EXECUTE format (‘ COPY climatedata( climatestationid , date … — more columns , tminsflag) FROM %L (FORMAT CSV, HEADER)’ — current syntax — WITH CSV HEADER’ — tolerated legacy syntax , $1); — pass function parameter filepathname to format() END … Read more
Create a table with the set you want to export and then use the command line utility pg_dump to export to a file: create table export_table as select id, name, city from nyummy.cimory where city = ‘tokyo’ $ pg_dump –table=export_table –data-only –column-inserts my_database > data.sql –column-inserts will dump as insert commands with column names. –data-only … Read more
COPY products_273 TO ‘/tmp/products_199.csv’ WITH (FORMAT CSV, HEADER); as described in the manual.
Do you want the resulting file on the server, or on the client? Server side If you want something easy to re-use or automate, you can use Postgresql’s built in COPY command. e.g. Copy (Select * From foo) To ‘/tmp/test.csv’ With CSV DELIMITER ‘,’ HEADER; This approach runs entirely on the remote server – it … Read more
Take a look at this short article. The solution is paraphrased here: Create your table: CREATE TABLE zip_codes (ZIP char(5), LATITUDE double precision, LONGITUDE double precision, CITY varchar, STATE char(2), COUNTY varchar, ZIP_CLASS varchar); Copy data from your CSV file to the table: COPY zip_codes FROM ‘/path/to/csv/ZIP_CODES.txt’ WITH (FORMAT csv);