Postgres Copy Parse Error At End Of Line
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack syntax error at or near copy Overflow the company Business Learn more about hiring developers or posting ads with us Stack \copy: parse error at "header;" Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community psql \copy example of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Postgres: \copy syntax error in .sql file up vote 2 down vote favorite I'm trying to write psql copy a script that copies data from a crosstab query to a .csv file in Postgres 8.4. I am able to run the command in the psql command line but when I put the command in a file and run it using the -f option, I get a syntax error. Here's an example of what I'm looking at (from this great answer): CREATE TEMP TABLE t ( section text ,status text ,ct
Postgres Export Csv
integer ); INSERT INTO t VALUES ('A', 'Active', 1), ('A', 'Inactive', 2) ,('B', 'Active', 4), ('B', 'Inactive', 5) , ('C', 'Inactive', 7); \copy ( SELECT * FROM crosstab( 'SELECT section, status, ct FROM t ORDER BY 1,2' ,$$VALUES ('Active'::text), ('Inactive')$$) AS ct ("Section" text, "Active" int, "Inactive" int) ) TO 'test.csv' HEADER CSV I then run this and get the following syntax error: $ psql [system specific] -f copy_test.sql CREATE TABLE INSERT 0 5 psql:copy_test.sql:12: \copy: parse error at end of line psql:copy_test.sql:19: ERROR: syntax error at or near ")" LINE 7: ) TO 'test.csv' HEADER CSV ^ A similar exercise doing just a simple query without crosstab works without incident. What is causing the syntax error and how can I copy this table to a csv file using script file? postgresql csv crosstab share|improve this question asked Apr 14 '15 at 16:32 David Kelley 544623 You're able to run that \copy as-is from the psql command line? –Politank-Z Apr 14 '15 at 16:42 Correct. Straight copy-paste does it. –David Kelley Apr 14 '15 at 16:47 1 For giggles, try taking all of the newlines out of the \copy command in your file and see what happens. –Politank-Z Apr 14 '15 at 16:50 Ok, so
8.0 / 8.1 / 8.2 / 8.3 / 8.4 / 9.0 PostgreSQL 9.2.18 Documentation Prev Up Next COPY NameCOPY--copy data between a
Postgresql Copy Command
file and a table Synopsis COPY table_name [ ( column_name [, ...] ) error: must be superuser to copy to or from a file ] FROM { 'filename' | STDIN } [ [ WITH ] ( option [, ...] ) ] COPY { postgres copy syntax error at or near table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | STDOUT } [ [ WITH ] ( option [, ...] ) ] where option http://stackoverflow.com/questions/29632700/postgres-copy-syntax-error-in-sql-file can be one of: FORMAT format_name OIDS [ boolean ] DELIMITER 'delimiter_character' NULL 'null_string' HEADER [ boolean ] QUOTE 'quote_character' ESCAPE 'escape_character' FORCE_QUOTE { ( column_name [, ...] ) | * } FORCE_NOT_NULL ( column_name [, ...] ) ENCODING 'encoding_name' Description COPY moves data between PostgreSQL tables and standard file-system files. COPY TO copies the contents of a table to a file, while https://www.postgresql.org/docs/9.2/static/sql-copy.html COPY FROM copies data from a file to a table (appending the data to whatever is in the table already). COPY TO can also copy the results of a SELECT query. If a list of columns is specified, COPY will only copy the data in the specified columns to or from the file. If there are any columns in the table that are not in the column list, COPY FROM will insert the default values for those columns. COPY with a file name instructs the PostgreSQL server to directly read from or write to a file. The file must be accessible to the server and the name must be specified from the viewpoint of the server. When STDIN or STDOUT is specified, data is transmitted via the connection between the client and the server. Parameters table_name The name (optionally schema-qualified) of an existing table. column_name An optional list of columns to be copied. If no column list is specified, all columns of the table will be copied. query A SELECT or VALUES command whose results are to be copied. Note that parentheses are required around the query. filename
pgsql-announce pgsql-bugs pgsql-docs pgsql-general pgsql-interfaces pgsql-jobs pgsql-novice pgsql-performance pgsql-php pgsql-sql pgsql-students Developer lists Regional lists Associations User groups Project lists Inactive lists IRC Local User Groups Featured https://www.postgresql.org/message-id/20101113120135.6b7fdc9e@dick.coachhouse Users International Sites Propaganda Resources Weekly News Re: psql -f COPY from STDIN From: Tarlika Elisabeth Schmitz