Re: [GENERAL] Copy command to handle view for my export requirement
COMMAND: copy (select * from employee) to 'C:/emp.csv' ERROR: could not open file C:/emp.csv for writing: Permission denied ** Error ** ERROR: could not open file C:/emp.csv for writing: Permission denied SQL state: 42501 COMMAND: \copy (select * from employee) to 'C:/emp.csv' ERROR: syntax error at or near \ LINE 1: \copy (select * from employee) to 'C:/emp.csv' ^ ** Error ** ERROR: syntax error at or near \ SQL state: 42601 Please correct me where I am going wrong. FYI, I am running under the administrator accounts of both Windows Login and PostgreSQL. Two things, 1. you need to have a proper permissions where the .csv file creating. 2. In windows you need to use as below postgres=#\copy (select * from employee) to 'C:\\emp.sql' Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Copy command to handle view for my export requirement
On 11/08/2011 7:56 PM, Siva Palanisamy wrote: FYI, I am using PostgreSQL 8.1.4. Argh, ogod why?!?!?! That version is *totally* unsupported on Windows. Not only that, but you're running an ancient point-release - you are missing *19* patch releases worth of bug fixes. The latest point-release is 8.1.23 ! Here is a list of all the fixes you are missing out on: http://www.postgresql.org/docs/8.1/static/release.html I have installed Windows version of Postgres 9.0.4 in my windows machine to test the new copy command as detailed in the below e-mails. When I run the command in SQL Editor, I got permission error. But I am running as an administrator. COMMAND: copy (select * from employee) to 'C:/emp.csv' ERROR: could not open file C:/emp.csv for writing: Permission denied ** Error ** ERROR: could not open file C:/emp.csv for writing: Permission denied SQL state: 42501 The COPY command (as distinct from \copy) runs on the server-side so it has the permissions of the postgres user. You must save the file somewhere the postgres user as write access. Either create a folder and give full control to the user postgres, or write the export within the existing postgresql data directory. COMMAND: \copy (select * from employee) to 'C:/emp.csv' ERROR: syntax error at or near \ LINE 1: \copy (select * from employee) to 'C:/emp.csv' You are not using psql. \copy is a psql command. I don't think it's supported by PgAdmin III, though I could be wrong. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copy command to handle view for my export requirement
You are not using psql. \copy is a psql command. I don't think it's supported by PgAdmin III, though I could be wrong. Right, '\copy' is not supported in PgAdmin III. --Raghav
Re: [GENERAL] COPY from .csv File and Remove Duplicates
On Aug 11, 2011, at 19:13, Rich Shepard rshep...@appl-ecosys.com wrote: A table has a sequence to generate a primary key for inserted records with NULLs in that column. I have a .csv file of approximately 10k rows to copy into this table. My two questions which have not been answered by reference to my postgres reference book or Google searches are: 1) Will the sequence automatically add the nextval() to each new record as the copy command runs? 2) Many of these rows almost certainly are already in the table. I would like to remove duplicates either during the COPY command or immediately after. I'm considering copying the new data into a clone of the table then running a SELECT to add only those rows in the new cloned table to the existing table. Suggestions on getting these data in without duplicating existing rows will be very helpful. If you have duplicates with matching real keys inserting into a staging table and then moving new records to the final table is your best option (in general it is better to do a two-step with a staging table since you can readily use Postgresql to perform any intermediate translations) As for the import itself, I believe if the column with the sequence is present in the csv the sequence will not be used and, if no value is present, a null will be stored for that column - causing any not-null constraint to throw an error. In this case I would just import the data to a staging table without any kind of artificial key, just the true key, and then during the merge with the live table you simply omit the pk field from the insert statement and the sequence will kick in at that point. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY from .csv File and Remove Duplicates
On Thu, 11 Aug 2011, David Johnston wrote: If you have duplicates with matching real keys inserting into a staging table and then moving new records to the final table is your best option (in general it is better to do a two-step with a staging table since you can readily use Postgresql to perform any intermediate translations) As for the import itself, David, I presume what you call a staging table is what I refer to as a copy of the main table, but with no key attribute. Writing the SELECT statement to delete from the staging table those rows that already exist in the main table is where I'm open to suggestions. In this case I would just import the data to a staging table without any kind of artificial key, just the true key, There is no true key, only an artificial key so I can ensure that rows are unique. That's in the main table with the 50K rows. No key column in the .csv file. Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY from .csv File and Remove Duplicates
On 12/08/2011 7:13 AM, Rich Shepard wrote: I have a .csv file of approximately 10k rows to copy into this table. My two questions which have not been answered by reference to my postgres reference book or Google searches are: 1) Will the sequence automatically add the nextval() to each new record as the copy command runs? No, AFAIK COPY input needs to match the table structure and can't have default fields etc. 2) Many of these rows almost certainly are already in the table. I would like to remove duplicates either during the COPY command or immediately after. I'm considering copying the new data into a clone of the table then running a SELECT to add only those rows in the new cloned table to the existing table. Rather than removing them after, I'd recommend COPYing into a temporary staging table, then doing an INSERT INTO realtable SELECT FROM stagingtable LEFT OUTER JOIN realtable ON (conditions) WHERE realtable.primarykey IS NULL; ... where conditions are whatever rules you use to decide that a row in the real table is the same as a row in the staging table. In other words: Only insert a row into the final destination table if it does not already exist in the final destination table. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY from .csv File and Remove Duplicates
There is no true key, only an artificial key so I can ensure that rows are unique. That's in the main table with the 50K rows. No key column in the .csv file. If you have no true key then you have no way to ensure uniqueness. By adding an artificial key two records that are otherwise duplicates would now be considered unique. Since you have not given data/structure for either the CSV or Main Table more specific help is not possible but when using a serialized PK in almost every case the table should also have a candidate key with a UNIQUE index defined. If you cannot explain why yours does not, and why it cannot, I would offer that you need to gain further understanding of your data model. It is generally wise to create a UNIQUE index on a candidate key and risk being wrong. At least you will be given an actual error and, in the worst case, can always drop the UNIQUE index if indeed the duplicate record should be valid; though in that situation you now have more data to input into you model analysis and should be able to correctly modify the table to create a new candidate key. Slight tangent but I have an external accounting source where I know that, with respect to the available data, duplicates can occur (a PK field is not available). Since I have no candidate key I am forced to use an artificial (serial) key and take extra precautions to ensure I do not inadvertently introduce unintentional duplicate data during import. In my case I handle data at the day level. My source gives me every transaction for a given date and I then modify my live table to add only the correct number of records so that, after the merge process, I have an exact duplicate of the data in the source file. Thus, since I trust the source file (and cannot enter data via any other method), I know immediately after processing that any duplicates on a given date are expected duplicates as opposed to, say, me accidentally importing the same file twice and thus having twice as many records. I also understand that if, say for reconciliation purposes, I need to choose one of a duplicate record it does not matter, initially, which one I choose but afterwards, if I only add records, I can ensure that I always pick the same record in the future. However, if I am forced to DELETE a record, from a practical perspective I DELETE BOTH/ALL of the records and then ADD back the correct number of records for that date. Any data that cared about the original records will now need to decide how to handle the fact that their record may no longer be present (instead of deleting only some of the existing records at random without knowing which ones are the correct ones to delete). This is one example I've come across where the data I am working with has absolutely NO inherent PK that I can see but where I can trust that, for a given dataset, I only have valid data. I did have to assign a SERIAL PK to my copy of the data but I also recognized where problems could occur and mitigated them via specific processing routines. One alternative solution would be to simply DELETE everything for a given date and then import every record from the source file into the main table. I rejected that solution because I could not afford to continually delete the existing records as other tables claimed FK relationships to them and continually breaking (ON DELETE SET NULL) them was unacceptable. I still have to do so when I need to delete a record (rare given this is accounting data) but simply adding a new record does not affect existing records. Whether this situation mirrors yours I do not know but I hope this brief description is at least informative and educational for you and others. Feedback/Thoughts are greatly welcomed. I presume what you call a staging table is what I refer to as a copy of the main table, but with no key attribute. Writing the SELECT statement to delete from the staging table those rows that already exist in the main table is where I'm open to suggestions. The big question to ask is how you would be able to identify a record in the CSV file as already being on the main table (either directly or, as my above example, indirectly)? My use of staging table reflects the fact that the structure of the table should roughly match the CSV file and NOT the main table. The SQL you issue to move records from the staging table to the main table will then account for any differences between the two. The general idea is to load up the staging table, optionally update matching records on the main table, insert non-matching records, then truncate/clear the staging table. The general structure for the insert would be: INSERT INTO maintable (cols) SELECT cols FROM staging WHERE staging.idcols NOT IN (SELECT maintable.idcols FROM maintable); There may be more efficient ways to write the query but the idea is the same. David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your
Re: [GENERAL] COPY from .csv File and Remove Duplicates
On 12/08/2011 10:32 AM, David Johnston wrote: The general structure for the insert would be: INSERT INTO maintable (cols) SELECT cols FROM staging WHERE staging.idcols NOT IN (SELECT maintable.idcols FROM maintable); There may be more efficient ways to write the query but the idea is the same. Yeah... I'd favour an EXISTS test or a join. INSERT INTO maintable (cols) SELECT cols FROM staging WHERE NOT EXISTS (SELECT 1 FROM maintable WHERE maintable.idcol = staging.idcol); ... as the NOT IN(...) test can have less than lovely behavior for large key sets. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY TO '|gzip /my/cool/file.gz'
On Wed, Jul 20, 2011 at 8:53 PM, Vibhor Kumar vibhor.ku...@enterprisedb.com wrote: You can use STDOUT to pipe output to a shell command and STDIN to read input from shell command. Something like given below: psql -c COPY mytable to STDOUT|gzip /home/tgl/mytable.dump.gz cat filename|psql -c COPY mytable from STDIN; OR psql -c COPY mytable from STDIN; filename nice one, that works great! (zcat instead of cat, though) -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY TO '|gzip /my/cool/file.gz'
On Jul 20, 2011, at 11:29 PM, david.sahag...@emc.com david.sahag...@emc.com wrote: From May 31, 2006; 12:03pm . . . It struck me that we are missing a feature that's fairly common in Unix programs. Perhaps COPY ought to have the ability to pipe its output to a shell command, or read input from a shell command. Maybe something like: COPY mytable TO '| gzip /home/tgl/mytable.dump.gz'; Is such a feature (ie being able to tell postgres to write a compressed file via COPY TO) being worked on ? You can use STDOUT to pipe output to a shell command and STDIN to read input from shell command. Something like given below: psql -c COPY mytable to STDOUT|gzip /home/tgl/mytable.dump.gz cat filename|psql -c COPY mytable from STDIN; OR psql -c COPY mytable from STDIN; filename Thanks Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company Blog: http://vibhork.blogspot.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY TO '|gzip /my/cool/file.gz'
On 21/07/11 01:59, david.sahag...@emc.com wrote: From May 31, 2006; 12:03pm . . . It struck me that we are missing a feature that's fairly common in Unix programs. Perhaps COPY ought to have the ability to pipe its output to a shell command, or read input from a shell command. Maybe something like: COPY mytable TO '| gzip /home/tgl/mytable.dump.gz'; Is such a feature (ie being able to tell postgres to write a compressed file via COPY TO) being worked on ? Not that I've heard of. In addition to the hint given about using copy to stdout from a psql -c invocation, there is another option. You can create a named pipe (fifo) file node and use COPY TO to write to it. eg: $ mkfifo gzfifo; gzip gzfifo out.gz $ psql -c COPY tablename TO '/server/path/to/gzfifo'; gzip will automatically terminate when the output file is closed. The fifo will not be removed and can be re-used. Supporting COPY to a pipe would be interesting, though the security implications would need plenty of thought. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command documentation
Hi Oisin,
I am right in the condition you described, but nowadays the 8.0
documentation is only available without comments.
I tried the way suggested by Richard Sydney-Smith (*eliminating the spaces
in the path*), but unsuccessfully.
Could you please help me?
thanks, Fabio
*hint from Richard Sydney-Smith*
(
http://postgresql.1045698.n5.nabble.com/Windows-file-path-for-copy-tt1847135.html
):
/Windows XP SP2 with Postgresql 8.0.3
Two commands on fails the other succeeds:
*Fails *:
select import_sharedata('C:\\Documents and
Settings\\Richard\\Desktop\\EzyChart-20050721');
*Succeeds*:
select import_sharedata('C:\\EzyChart-20050721'); /
Oisin Glynn wrote:
I have driven myself to distraction for the last 30 minutes trying to
get COPY to work on Windows XP. The Unix style c:/afolder/afile
instead of c:\afolder\afile was a desperation attempt.
I had tried all sorts of double slashes \\ putting the whole path in
quotes basically all sorts of foolishness. [...]
Oisin
P.S.
I just discovered that the comments from 8.0 had the answer I was
looking for but these comments are not in the 8.1 docs. Should the
comments be rolled forward as new versions are created? Or if valid
comments added to the docs themselves?
http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
http://www.postgresql.org/docs/8.0/interactive/sql-copy.html
Now happily using COPY,
Oisin
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/GENERAL-COPY-command-documentation-tp1858906p4590548.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy record?
On Thu, May 26, 2011 at 3:23 PM, Gauthier, Dave dave.gauth...@intel.com wrote: Hi: From within a perl/DBI script, I want to be able to make a copy of a record in a table, changing only the value of the primary key. I don't know in advance what all the columns are, just the table name. I suppose I could pull the column names for the table from the metadata, query the table/record for the values to copy, build an insert statement from all of that and run it. But is there a simpler, more elegant way to do this? there's a very easy way using the composite type method as long as you know which field(s) are the primary key -- especially if it's say the first column and an integer. postgres=# \d foo Table public.foo Column | Type | Modifiers +-+--- a | integer | b | integer | Indexes: foo_a_b_idx btree (a, b) postgres=# select foo from foo limit 1; foo --- (1,1) (1 row) change 1 - 2 textually, cast the text back to the composite and pass it back in insert into foo select (($$(2,1)$$::foo).*); merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy record?
Well, I found a better way, but still open to suggestions. This is what I have so far... create temporary table foo as select * from maintable where 1-0; -- Credit 4 this goes to a post in the PG archives insert into foo (select * from maintable where primcol=123); update foo, set primcol=456; insert into maintable (select * from foo); From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Gauthier, Dave Sent: Thursday, May 26, 2011 4:24 PM To: pgsql-general@postgresql.org Subject: [GENERAL] copy record? Hi: From within a perl/DBI script, I want to be able to make a copy of a record in a table, changing only the value of the primary key. I don't know in advance what all the columns are, just the table name. I suppose I could pull the column names for the table from the metadata, query the table/record for the values to copy, build an insert statement from all of that and run it. But is there a simpler, more elegant way to do this? Thanks for any help !
Re: [GENERAL] copy record?
Gauthier, Dave wrote: Well, I found a better way, but still open to suggestions. This is what I have so far... create temporary table foo as select * from maintable where 1-0; -- Credit 4 this goes to a post in the PG archives insert into foo (select * from maintable where primcol=123); update foo, set primcol=456; insert into maintable (select * from foo); This alleviates the need to trick the back-end using 'where 1-0' and also does the insert, all in one go. select * into temp table foo from maintable where primcol=123; update foo set primcol = 456; insert into maintable select * from foo; You also may need this is if you intend to use the same sequence of calls on within the same session: drop table foo; HTH Bosco. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy record?
On 2011-05-26, Bosco Rama postg...@boscorama.com wrote: select * into temp table foo from maintable where primcol=123; update foo set primcol = 456; insert into maintable select * from foo; You also may need this is if you intend to use the same sequence of calls on within the same session: drop table foo; Yet another way to do the same thing: begin; create temportary table foo on commit drop as select * from maintable where primcol=123; update foo, set primcol=456; insert into maintable select * from foo; commit; -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY complaining about unquoted carriage return found in data... in a quoted field
On Tue, 10 May 2011 15:59:07 +0200 Ivan Sergio Borgonovo m...@webthatworks.it wrote: Sorry for the noise. The csv was automatically generated. The code was right but during generation there was some problem with the box generating it (php segfaulting) and there were some unclosed quotes in a much earlier line. Postgresql actually behaves as documented and expected... and the documentation is clear. On Tue, 10 May 2011 14:38:23 +0200 Pavel Stehule pavel.steh...@gmail.com wrote: Hello COPY doesn't like '\n' too. Replace '\n' by '\\n' mmm maybe you were mislead by the semi-transliterated hexdump. There is no slash in the record, the actual input was the one reported in hex. The following line was just to help reading the hexdump. 09 22 56 | 69 61 20 46 | 61 65 6E 74 | 69 6E 61 2C 20 35 33 0D | 0A 22 09 22 | 35 30 30 31 | 34 22 09 as a quick reference: 0x09 = \t 0x0a = \n 0x0d = \r 0x20 = space 0x22 = I thought that csv quote as '' where there exactly to avoid escaping of delimiters (other than the quoting character itself) inside fields. From the docs: http://www.postgresql.org/docs/8.3/static/sql-copy.html «If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character.» So up to my understanding once I've quoted a field I've to take care of escaping just the quote and the escape characters and the field may contain delimiter characters (field separator and record separator) without escaping. I was wondering if a) the documentation is wrong b) I didn't write a correct \COPY command string c) there is a bug d) I can't correctly interpret the documentation e) I'm more stupid then usual thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY complaining about unquoted carriage return found in data... in a quoted field
Hello COPY doesn't like '\n' too. Replace '\n' by '\\n' Regards Pavel Stehule 2011/5/10 Ivan Sergio Borgonovo m...@webthatworks.it: I'm on pg 8.3.14 I'm trying to import a csv with \copy anagraficaclienti from 'myfile.csv' delimiter as E' ' -- this is a tab \t null as 'NULL' csv header quote as E'' escape as E'\\' What I get is ERROR: unquoted carriage return found in data HINT: Use quoted CSV field to represent carriage return. CONTEXT: COPY anagraficaclienti, line 48656 The record actually contains \r\n in a field but up to my understanding that field is quoted. I think what seems to be wrong is my understanding of what postgres consider quoted field. the content in hex 09 22 56 | 69 61 20 46 | 61 65 6E 74 | 69 6E 61 2C 20 35 33 0D | 0A 22 09 22 | 35 30 30 31 | 34 22 09 \tVia Faentina, 53\r\n\t What am I missing? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY complaining about unquoted carriage return found in data... in a quoted field
On Tue, 10 May 2011 14:38:23 +0200 Pavel Stehule pavel.steh...@gmail.com wrote: Hello COPY doesn't like '\n' too. Replace '\n' by '\\n' mmm maybe you were mislead by the semi-transliterated hexdump. There is no slash in the record, the actual input was the one reported in hex. The following line was just to help reading the hexdump. 09 22 56 | 69 61 20 46 | 61 65 6E 74 | 69 6E 61 2C 20 35 33 0D | 0A 22 09 22 | 35 30 30 31 | 34 22 09 as a quick reference: 0x09 = \t 0x0a = \n 0x0d = \r 0x20 = space 0x22 = I thought that csv quote as '' where there exactly to avoid escaping of delimiters (other than the quoting character itself) inside fields. From the docs: http://www.postgresql.org/docs/8.3/static/sql-copy.html «If the value contains the delimiter character, the QUOTE character, the NULL string, a carriage return, or line feed character, then the whole value is prefixed and suffixed by the QUOTE character, and any occurrence within the value of a QUOTE character or the ESCAPE character is preceded by the escape character.» So up to my understanding once I've quoted a field I've to take care of escaping just the quote and the escape characters and the field may contain delimiter characters (field separator and record separator) without escaping. I was wondering if a) the documentation is wrong b) I didn't write a correct \COPY command string c) there is a bug d) I can't correctly interpret the documentation e) I'm more stupid then usual thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY statement REAL vs VARCHAR precision issue
On 10/02/2011 17:13, Samuel Gilbert wrote: Hello all, I'm using a COPY statement to load data into a PostGIS. The issue I am facing is that if I define fields with the REAL type, the COPY will only preserve 4 decimals which is not sufficient for my application. If you use NUMERIC, you can define the precision you need, and be sure of accurate calculations: http://www.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from problem
Le 22/12/2010 21:34, Mark Watson a écrit : Hello all, (Postgres 8.4.6 Windows) I am stumped as to why I cannot import this using copy from within pgadmin (the following table is created in an existing database with an encoding of WIN1252 and the Postgres server_encoding is UTF8) : CREATE TABLE test ( col_descr text ) WITH ( OIDS=FALSE ); ALTER TABLE test OWNER TO postgres; set client_encoding = 'WIN1252'; COPY test FROM 'C:\\pgtemp\\test.txt' with delimiter as '|' csv; select * from test; --- col_descr -- -- (empty row) The file test.txt contains 1 line of 2 characters: éÉ (acute accented lowercase and uppercase e, hex(E9C9), valid win1252 characters. Any help would be appreciated. It would be so much easier if you told us your error message. -- Guillaume http://www.postgresql.fr http://dalibo.com Another update : After toying around with this, I think this is a subject for the PgAdmin list. It looks like a display problem in pgAdmin. The characters are being displayed with a client_encoding of UTF8 but not with a client_encoding of WIN1252. My application, which sets client_encoding to WIN1252, receives and displays the characters correctly. By the way, my pgAdmin is configured with Preferences=Font = Tahoma windows-1252 and Query=Font = Tahoma 9 windows-1252. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from problem
Le 23/12/2010 14:36, Mark Watson a écrit : Le 22/12/2010 21:34, Mark Watson a écrit : Hello all, (Postgres 8.4.6 Windows) I am stumped as to why I cannot import this using copy from within pgadmin (the following table is created in an existing database with an encoding of WIN1252 and the Postgres server_encoding is UTF8) : CREATE TABLE test ( col_descr text ) WITH ( OIDS=FALSE ); ALTER TABLE test OWNER TO postgres; set client_encoding = 'WIN1252'; COPY test FROM 'C:\\pgtemp\\test.txt' with delimiter as '|' csv; select * from test; --- col_descr -- -- (empty row) The file test.txt contains 1 line of 2 characters: éÉ (acute accented lowercase and uppercase e, hex(E9C9), valid win1252 characters. Any help would be appreciated. It would be so much easier if you told us your error message. -- Guillaume http://www.postgresql.fr http://dalibo.com Another update : After toying around with this, I think this is a subject for the PgAdmin list. It looks like a display problem in pgAdmin. The characters are being displayed with a client_encoding of UTF8 but not with a client_encoding of WIN1252. My application, which sets client_encoding to WIN1252, receives and displays the characters correctly. By the way, my pgAdmin is configured with Preferences=Font = Tahoma windows-1252 and Query=Font = Tahoma 9 windows-1252. pgAdmin sets the client encoding to UTF-8 and only displays UTF-8. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from problem
Le 23/12/2010 14:36, Mark Watson a écrit : Le 22/12/2010 21:34, Mark Watson a écrit : Hello all, (Postgres 8.4.6 Windows) I am stumped as to why I cannot import this using copy from within pgadmin (the following table is created in an existing database with an encoding of WIN1252 and the Postgres server_encoding is UTF8) : CREATE TABLE test ( col_descr text ) WITH ( OIDS=FALSE ); ALTER TABLE test OWNER TO postgres; set client_encoding = 'WIN1252'; COPY test FROM 'C:\\pgtemp\\test.txt' with delimiter as '|' csv; select * from test; --- col_descr -- -- (empty row) The file test.txt contains 1 line of 2 characters: éÉ (acute accented lowercase and uppercase e, hex(E9C9), valid win1252 characters. Any help would be appreciated. It would be so much easier if you told us your error message. -- Guillaume http://www.postgresql.fr http://dalibo.com Another update : After toying around with this, I think this is a subject for the PgAdmin list. It looks like a display problem in pgAdmin. The characters are being displayed with a client_encoding of UTF8 but not with a client_encoding of WIN1252. My application, which sets client_encoding to WIN1252, receives and displays the characters correctly. By the way, my pgAdmin is configured with Preferences=Font = Tahoma windows-1252 and Query=Font = Tahoma 9 windows-1252. pgAdmin sets the client encoding to UTF-8 and only displays UTF-8. -- Guillaume http://www.postgresql.fr http://dalibo.com Thanks, Guillaume! That answers everything. Happy holidays to all! -Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from problem
On Wednesday 22 December 2010 12:34:58 pm Mark Watson wrote: Hello all, (Postgres 8.4.6 Windows) I am stumped as to why I cannot import this using copy from within pgadmin (the following table is created in an existing database with an encoding of WIN1252 and the Postgres server_encoding is UTF8) : CREATE TABLE test ( col_descr text ) WITH ( OIDS=FALSE ); ALTER TABLE test OWNER TO postgres; set client_encoding = 'WIN1252'; COPY test FROM 'C:\\pgtemp\\test.txt' with delimiter as '|' csv; select * from test; --- col_descr -- -- (empty row) The file test.txt contains 1 line of 2 characters: éÉ (acute accented lowercase and uppercase e, hex(E9C9), valid win1252 characters. Any help would be appreciated. Mark Is there anything in the database logs? Are the two characters separated by '|'? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from problem
Le 22/12/2010 21:34, Mark Watson a écrit : Hello all, (Postgres 8.4.6 Windows) I am stumped as to why I cannot import this using copy from within pgadmin (the following table is created in an existing database with an encoding of WIN1252 and the Postgres server_encoding is UTF8) : CREATE TABLE test ( col_descr text ) WITH ( OIDS=FALSE ); ALTER TABLE test OWNER TO postgres; set client_encoding = 'WIN1252'; COPY test FROM 'C:\\pgtemp\\test.txt' with delimiter as '|' csv; select * from test; --- col_descr -- -- (empty row) The file test.txt contains 1 line of 2 characters: éÉ (acute accented lowercase and uppercase e, hex(E9C9), valid win1252 characters. Any help would be appreciated. It would be so much easier if you told us your error message. -- Guillaume http://www.postgresql.fr http://dalibo.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copy From suggestion
Thanks, Adrian, Ill try a windows compile of pgloader sometime during the holidays. Its true that I already have a solution (export = 65000 row chunks, import into Excel, export via Excel puts quotes around the text columns), but something faster and more efficient would really help in this case. -Mark _ De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] De la part de Adrian Klaver Envoyé : 18 décembre 2010 18:05 À : pgsql-general@postgresql.org Cc : Mark Watson Objet : Re: [GENERAL] Copy From suggestion On Friday 17 December 2010 7:46:12 am Mark Watson wrote: Hello all, Firstly, I apologise if this is not the correct list for this subject. Lately, I've been working on a data conversion, importing into Postgres using Copy From. The text file I'm copying from is produced from an ancient program and produces either a tab or semi-colon delimited file. One file contains about 1.8M rows and has a 'comments' column. The exporting program, which I am forced to use, does not surround this column with quotes and this column contains cr/lf characters, which I must deal with (and have dealt with) before I can import the file via Copy. Hence to my suggestion: I was envisioning a parameter DELIMITER_COUNT which, if one was 100% confident that all columns are accounted for in the input file, could be used to alleviate the need to deal with cr/lf's in varchar and text columns. i.e., if copy loaded a line with fewer delimiters than delimiter_count, the next line from the text file would be read and the assignment of columns would continue for the current row/column. Just curious as to the thoughts out there. Thanks to all for this excellent product, and a merry Christmas/holiday period to all. Mark Watson A suggestion,give pgloader a look; http://pgloader.projects.postgresql.org/ If I am following you it might already have the solution to the multi-line problem. In particular read the History section of the docs. Thanks, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1170 / Virus Database: 426/3325 - Release Date: 12/19/10
Re: [GENERAL] Copy From suggestion
With OpenOffice.org that 65K limit goes away as well... I don't know why it is still like that today for MS Office... It is almost 2011 and they still think 64K is enough? :-) -- Jorge Godoy jgo...@gmail.com On Mon, Dec 20, 2010 at 11:49, Mark Watson mark.wat...@jurisconcept.cawrote: Thanks, Adrian, I’ll try a windows compile of pgloader sometime during the holidays. It’s true that I already have a solution (export = 65000 row chunks, import into Excel, export via Excel puts quotes around the text columns), but something faster and more efficient would really help in this case. -Mark -- *De :* pgsql-general-ow...@postgresql.org [mailto: pgsql-general-ow...@postgresql.org] *De la part de* Adrian Klaver *Envoyé :* 18 décembre 2010 18:05 *À :* pgsql-general@postgresql.org *Cc :* Mark Watson *Objet :* Re: [GENERAL] Copy From suggestion On Friday 17 December 2010 7:46:12 am Mark Watson wrote: Hello all, Firstly, I apologise if this is not the correct list for this subject. Lately, I've been working on a data conversion, importing into Postgres using Copy From. The text file I'm copying from is produced from an ancient program and produces either a tab or semi-colon delimited file. One file contains about 1.8M rows and has a 'comments' column. The exporting program, which I am forced to use, does not surround this column with quotes and this column contains cr/lf characters, which I must deal with (and have dealt with) before I can import the file via Copy. Hence to my suggestion: I was envisioning a parameter DELIMITER_COUNT which, if one was 100% confident that all columns are accounted for in the input file, could be used to alleviate the need to deal with cr/lf's in varchar and text columns. i.e., if copy loaded a line with fewer delimiters than delimiter_count, the next line from the text file would be read and the assignment of columns would continue for the current row/column. Just curious as to the thoughts out there. Thanks to all for this excellent product, and a merry Christmas/holiday period to all. Mark Watson A suggestion,give pgloader a look; http://pgloader.projects.postgresql.org/ If I am following you it might already have the solution to the multi-line problem. In particular read the History section of the docs. Thanks, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1170 / Virus Database: 426/3325 - Release Date: 12/19/10
Re: [GENERAL] Copy From suggestion
On Monday 20. December 2010 15.24.58 Jorge Godoy wrote: With OpenOffice.org that 65K limit goes away as well... I don't know why it is still like that today for MS Office... It is almost 2011 and they still think 64K is enough? :-) Maybe there's an uncrippled «Professional» or «Enterprise» version costing an arm and a leg? ;) regards, Leif B. Kristensen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copy From suggestion
On Monday 20 December 2010 7:09:23 am Leif Biberg Kristensen wrote: On Monday 20. December 2010 15.24.58 Jorge Godoy wrote: With OpenOffice.org that 65K limit goes away as well... I don't know why it is still like that today for MS Office... It is almost 2011 and they still think 64K is enough? :-) Maybe there's an uncrippled «Professional» or «Enterprise» version costing an arm and a leg? ;) regards, Leif B. Kristensen FYI with Office 2007 that limit went to a little over 1 million rows. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copy From suggestion
On Friday 17 December 2010 7:46:12 am Mark Watson wrote: Hello all, Firstly, I apologise if this is not the correct list for this subject. Lately, I've been working on a data conversion, importing into Postgres using Copy From. The text file I'm copying from is produced from an ancient program and produces either a tab or semi-colon delimited file. One file contains about 1.8M rows and has a 'comments' column. The exporting program, which I am forced to use, does not surround this column with quotes and this column contains cr/lf characters, which I must deal with (and have dealt with) before I can import the file via Copy. Hence to my suggestion: I was envisioning a parameter DELIMITER_COUNT which, if one was 100% confident that all columns are accounted for in the input file, could be used to alleviate the need to deal with cr/lf's in varchar and text columns. i.e., if copy loaded a line with fewer delimiters than delimiter_count, the next line from the text file would be read and the assignment of columns would continue for the current row/column. Just curious as to the thoughts out there. Thanks to all for this excellent product, and a merry Christmas/holiday period to all. Mark Watson A suggestion,give pgloader a look; http://pgloader.projects.postgresql.org/ If I am following you it might already have the solution to the multi-line problem. In particular read the History section of the docs. Thanks, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY FROM and INSERT INTO rules
Le lundi 06 décembre 2010 à 18:27 -0600, Sairam Krishnamurthy a écrit : You should start a new thread for this Is there a way to call a rule when I use COPY FROM instead of INSERT INTO from the doc : COPY FROM will invoke any triggers and check constraints on the destination table. However, it will not invoke rules. http://www.postgresql.org/docs/9.0/static/sql-copy.html -- Vincent Veyron http://marica.fr/ Progiciel de gestion des dossiers de contentieux et d'assurance pour le service juridique -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY FROM and INSERT INTO rules
From the 8.3 docs... Be aware that COPY ignores rules. ... COPY does fire triggers, so you can use it normally if you use the trigger approach. HTH, Brent Wood All, I have a rule written on a temp table which will copy the valuesinserted into it to another table applying a function. The temp tablewill be discarded then. The rules I have written works when I useInsert into the temp table. But when I use bulk copy COPY FROM, therule doesn't get triggered and data is inserted only into the temptable that I created. Is there a way to call a rule when I use COPY FROM instead of INSERTINTO TIA, Sairam Brent Wood DBA/GIS consultant NIWA, Wellington New Zealand Please consider the environment before printing this email. NIWA is the trading name of the National Institute of Water Atmospheric Research Ltd.
Re: [GENERAL] COPY question
On 21 October 2010 23:28, Raymond O'Donnell r...@iol.ie wrote: On 21/10/2010 22:16, Szymon Guz wrote: Hi, I still get the same error while using COPY FROM 'file'. I have to pass the full directory for this to work, example: COPY first (a,b,c) FROM '1st_file.csv' with csv header delimiter ','; ERROR: could not open file 1st_file.csv for reading: No such file or directory The same when I use: '.1st_file.csv' or './1st_file.csv'. No errors when I give the full path. Yes, that's documented behaviour - you need to give the full path. I've noticed that already. Btw, I could use the file name, but it won't be searched in my current directory. The path will be interpreted relative to the working directory of the server process (normally the cluster's data directory), not the client's working directory. Giving the full path is stupid, how can I use just the file name? Why is it stupid? Because I can't just move my scripts/queries to some other directory, where I have files with the same names, but different content (just for tests). Thanks for the answer. regards Szymon
Re: [GENERAL] COPY question
On 21/10/2010 22:16, Szymon Guz wrote: Hi, I still get the same error while using COPY FROM 'file'. I have to pass the full directory for this to work, example: COPY first (a,b,c) FROM '1st_file.csv' with csv header delimiter ','; ERROR: could not open file 1st_file.csv for reading: No such file or directory The same when I use: '.1st_file.csv' or './1st_file.csv'. No errors when I give the full path. Yes, that's documented behaviour - you need to give the full path. Giving the full path is stupid, how can I use just the file name? Why is it stupid? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY question
On 10/22/2010 05:16 AM, Szymon Guz wrote: Hi, I still get the same error while using COPY FROM 'file'. I have to pass the full directory for this to work, example: Sounds like you want to be using psql's \copy, not the server side COPY. \copy is aware of your current working directory and doesn't require the server to have permission to access the file - or even be on the same machine. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copy From csv file with double quotes as null
Hi All, I am apparently totally misreading how to import data using the COPY FROM command, can someone give assistance ? I have two issues, both dealing with double quotes as NULL. The data is CSV with NULL being represented by a double quote (e.g. ) in all columns of the table. ISSUE A) The following command bombs: COPY testdata FROM 'c:/temp/test.csv' CSV HEADER; with the following error: ERROR: invalid input syntax for type double precision: CONTEXT: COPY testdata, line 7, column latitude: ** Error ** ERROR: invalid input syntax for type double precision: SQL state: 22P02 Context: COPY testdata, line 7, column latitude: So, latitude is a double precision column and I think that PostgreSQL is interpreting the double quote as a NULL string and then it can not be placed into that column because it is a double precision column. Issue B) I have an associated issue with a text value where the NULL in the data being represented by a double quote (e.g. ) is being inputed as a quote. I can not use the switch NULL AS '' because PostgreSQL says the quote character must not appear in the NULL specification Given the file sizes are huge, I would rather not have to try to preprocess the data. Is there anyway the COPY FROM command can handle this data smoothly ? -- -Don Don Catanzaro, PhD Landscape Ecologist dgcatanz...@gmail.com 16144 Sigmond Lane Lowell, AR 72745 479-751-3616
Re: [GENERAL] Copy From csv file with double quotes as null
On 9/09/2010 2:48 AM, Donald Catanzaro, PhD wrote: So, latitude is a double precision column and I think that PostgreSQL is interpreting the double quote as a NULL string No, it's interpreting it as an empty string, not NULL. I suspect that's what you meant, but NULL string is still NULL, you're getting an empty(zero-length) *non-null* string. See: http://www.postgresql.org/docs/8.4/static/sql-copy.html I'm not sure COPY ... CSV knows how to treat as null. It has options to force empty unquoted text input columns to be *non* null, but I don't see the reverse, a way to force the empty string to be treated as null. While the documentation notes that many programs produce strange and occasionally perverse CSV files, this particular format quirk doesn't seem that strange and I'm surprised to see no obvious way to handle it. Perhaps this is a case where you might need to pre-process the csv input to clean it up a bit? I usually use Python's csv module for that. I imagine ETL tools like Pentaho or Talend can do the job, but have never used them. Issue B) I have an associated issue with a text value where the NULL in the data being represented by a double quote (e.g. ) is being inputed as a quote. I can not use the switch NULL AS '' because PostgreSQL says the quote character must not appear in the NULL specification Again, PostgreSQL knows how to handle the reverse, where the empty unquoted string is null, and is the zero-length non-null string. It doesn't seem to be able to deal with as a representation of NULL, though. At least this one, unlike the double precision issue, can be handled by dropping the NOT NULL constraint on the text column concerned, then running an UPDATE tablename SET col=NULL WHERE col= and restoring the constraint. Given the file sizes are huge, I would rather not have to try to preprocess the data. Is there anyway the COPY FROM command can handle this data smoothly ? It doesn't look like it to me. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \COPY ... CSV with hex escapes
On 08/07/10 17:42, Alban Hertroys wrote:
On 8 Jul 2010, at 4:21, Craig Ringer wrote:
Yes, that's ancient. It is handled quite happily by \copy in csv mode,
except that when csv mode is active, \xnn escapes do not seem to be
processed. So I can have *either* \xnn escape processing *or* csv-style
input processing.
Anyone know of a way to get escape processing in csv mode?
And what do those hex-escaped bytes mean? Are they in text strings? AFAIK CSV
doesn't contain any information about what encoding was used to create it, so
it could be about anything; UTF-8, Win1252, ISO-8859-something, or whatever
Sybase was using.
I'm just saying, be careful what you're parsing there ;)
Thanks for that. In this case, the escapes are just bytes - what's
important is that, after unescaping, the CSV data is interpreted as
latin-1. OK, Windows-1252, but close enough.
In the end Python's csv module did the trick. I just pulled in the CSV
data, and spat out Postgresql-friendly COPY format so that I didn't need
to use the COPY ... CSV modifier and Pg would interpret the escapes
during input.
In case anyone else needs to deal with this format, here's the program I
used.
--
Craig Ringer
Tech-related writing: http://soapyfrogs.blogspot.com/
#!/usr/bin/env python
import os
import sys
import csv
class DialectSybase(csv.Dialect):
delimiter = ','
doublequote = True
escapechar = None
quotechar = '\''
quoting = csv.QUOTE_MINIMAL
lineterminator = '\n'
class DialectPgCOPY(csv.Dialect):
delimiter = '\t'
doublequote = False
escapechar = None
quotechar = None
quoting = csv.QUOTE_NONE
lineterminator = '\n'
#class DialectPgCOPY(csv.Dialect):
#delimiter = '\t'
#doublequote = True
#escapechar = '\\'
#quotechar = '\''
#quoting = csv.QUOTE_NONE
#lineterminator = '\n'
def unescape_item(item):
''' noop so far '''
#if item.find(\\X) = 0:
#print repr(item)
#return item
return item.replace(\\X,\\x)
def unescape_row(row):
newrow = []
for item in row:
newitem = item
if type(item) == str:
newitem = unescape_item(item)
newrow.append(newitem)
return newrow
def main(infn, outfn):
infile = open(infn,'r')
outfile = open(outfn,'w')
r = csv.reader( infile, dialect=DialectSybase )
w = csv.writer( outfile, dialect=DialectPgCOPY )
for row in r:
w.writerow(unescape_row(row))
if __name__ == '__main__':
print customers
main('customer.txt', 'customer_unescaped.txt')
print class
main('class.txt', 'class_unescaped.txt')
print orders
main('orders.txt', 'orders_unescaped.txt')
print items
main('items.txt', 'items_unescaped.txt')
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \COPY ... CSV with hex escapes
On 8 Jul 2010, at 4:21, Craig Ringer wrote: Yes, that's ancient. It is handled quite happily by \copy in csv mode, except that when csv mode is active, \xnn escapes do not seem to be processed. So I can have *either* \xnn escape processing *or* csv-style input processing. Anyone know of a way to get escape processing in csv mode? And what do those hex-escaped bytes mean? Are they in text strings? AFAIK CSV doesn't contain any information about what encoding was used to create it, so it could be about anything; UTF-8, Win1252, ISO-8859-something, or whatever Sybase was using. I'm just saying, be careful what you're parsing there ;) Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c359d9f286212106016419! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \COPY ... CSV with hex escapes
On Wed, Jul 7, 2010 at 9:21 PM, Craig Ringer cr...@postnewspapers.com.au wrote: Hi folks I have an odd csv input format to deal with. I'm about to put some Python together to reprocess it, but I thought I'd check in and see if I'm missing something obvious in \copy's capabilities. The input is fairly conventional comma-delimeted text with quoted fields, as output by Sybase SQL Anywhere 5.5's isql 'OUTPUT TO' filter. Yes, that's ancient. It is handled quite happily by \copy in csv mode, except that when csv mode is active, \xnn escapes do not seem to be processed. So I can have *either* \xnn escape processing *or* csv-style input processing. Anyone know of a way to get escape processing in csv mode? Don't know if you can do it directly, but this seem like one of those cases where a ETL tool like that from Pentaho (Kettle / Spoon) might be in order? One step to handle the escape chars and one to load the actual CSV... -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy/duplicate database schemas
On Thu, Jun 24, 2010 at 11:29:48AM +0800, Craig Ringer wrote: You might want to investigate internationalization options instead, where you can process your master sources to produce a list of strings, and have translators translate those strings. Your code loads the string lists, and depending on the setting for the current language decides which mapping of strings to translations to use when emitting messages. This adds significant complexity to your code, especially since (AFAIK) there aren't really any good i18n tools for Pg's SQL, PL/PgSQL, etc. But there is - whether good or not: Go to http://gitorious.org/gnumed and browse the tree under gnumed/server/SQL/. Look at the i18n schema which provides functions for registering strings for translation and translating them (i18n.upd_tx()) and using translated strings nearly transparently in your queries like so: SELECT _(label) FROM your_label_table; Which will return label translated into what the connected user registered as his preferred language or in the source language if none registered or no translation available. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy/duplicate database schemas
Merlin Moncure mmonc...@gmail.com writes: On Wed, Jun 23, 2010 at 3:21 PM, Jamie Kahgee jamie.kah...@gmail.com wrote: I have an application in a schema and now i need to create other schemas b/c the app needs to support different languages, is there an easy way to copy an entire schema to a new one (tables, contents, trigges, functions, etc..)? right now i just have schema what I want is schema schema_ar schema_ru etc... sure: 1) pg_dump -n your_schema -s 2) sed the resulting schema s/old_schema/new_schema if you are lucky, you will have few if any improper replacements, if not, tweak sed till it's right 3) cat it back into psql I think you could also : 1) pg_dump -n your_schema -s your_schema.sql 2) alter schema your_schema rename to your_schema_ar; 3) psql -f your_schema.sql 4) goto 2, pick another name Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy/duplicate database schemas
This adds significant complexity to your code, especially since (AFAIK) there aren't really any good i18n tools for Pg's SQL, PL/PgSQL, etc. But there is - whether good or not: Go to http://gitorious.org/gnumed and browse the tree under gnumed/server/SQL/. Look at the i18n schema which provides functions for registering strings for translation and translating them (i18n.upd_tx()) and using translated strings nearly transparently in your queries like so: SELECT _(label) FROM your_label_table; Which will return label translated into what the connected user registered as his preferred language or in the source language if none registered or no translation available. http://gitorious.org/gnumed/gnumed/trees/master/gnumed/gnumed/server/sql Under this link look at gmI18N*.sql, note that below the versioned directories there are several improvements and fixes but you'll get the idea. Karsten -- GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl. Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy/duplicate database schemas
On Wed, Jun 23, 2010 at 3:21 PM, Jamie Kahgee jamie.kah...@gmail.com wrote: I have an application in a schema and now i need to create other schemas b/c the app needs to support different languages, is there an easy way to copy an entire schema to a new one (tables, contents, trigges, functions, etc..)? right now i just have schema what I want is schema schema_ar schema_ru etc... sure: 1) pg_dump -n your_schema -s 2) sed the resulting schema s/old_schema/new_schema if you are lucky, you will have few if any improper replacements, if not, tweak sed till it's right 3) cat it back into psql merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy/duplicate database schemas
On 24/06/10 03:21, Jamie Kahgee wrote: I have an application in a schema and now i need to create other schemas b/c the app needs to support different languages, is there an easy way to copy an entire schema to a new one (tables, contents, trigges, functions, etc..)? Others have replied with suggestions on how to do this, so I won't repeat that - but I think you need to look at the bigger picture. If you copy your app into multiple schema then modify each to localize them, you're going to have a maintenance nightmare on your hands if you ever intend to fix bugs or add new features to your app. All the copies, including copies in languages you don't speak, will have to be kept up to date. You might want to investigate internationalization options instead, where you can process your master sources to produce a list of strings, and have translators translate those strings. Your code loads the string lists, and depending on the setting for the current language decides which mapping of strings to translations to use when emitting messages. This adds significant complexity to your code, especially since (AFAIK) there aren't really any good i18n tools for Pg's SQL, PL/PgSQL, etc. However, it'll save you a LOT of pain not to maintain five (or more - new translations are always required) versions of your code down the track. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy data from one db into another via copy psql
On 2010-05-21, Kevin Kempter kev...@consistentstate.com wrote: Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so I can load the data into a table in the second db 'inline' without writing to reading from a flat file? Yes. COPY ... TO stdout; at the source and COPY ... FROM stdin; at the destination. I use this with the COPY ( query ) TO STDOUT ; syntax when I want to copy only a few rows. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy data from one db into another via copy psql
On Thu, May 20, 2010 at 10:59 PM, Kevin Kempter kev...@consistentstate.com wrote: Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so I can load the data into a table in the second db 'inline' without writing to reading from a flat file? That's pretty much what pg_dump -t tablename -d dbname | psql dbname does. Add a -a and it's data only: pg_dump -a -t tablename -d dbname | psql dbname -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy data from one db into another via copy psql
In response to Kevin Kempter : Can I copy from one db (via COPY) and pipe the results to a psql/COPY stmt so I can load the data into a table in the second db 'inline' without writing to reading from a flat file? Yes, but keep in mind, COPY cant create the table on the destination. If the destination contains a (empty) table it's no problem. If the destination-db doesn't contain the table you can also use pg_dump: kretsch...@tux:~$ createdb new_test kretsch...@tux:~$ pg_dump -t foo test | psql -X new_test -f - SET SET SET SET SET SET SET SET SET CREATE TABLE ALTER TABLE kretsch...@tux:~$ psql new_test -c select * from foo t -- a short test (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY ERROR
paulo matadr wrote: When I try to import big file base.txt( 700MB),I get this: x=# create table arquivo_serasa_marco( varchar(3000)); x=# COPY arquivo_serasa_marco from '/usr/local/pgsql/data/base.txt'; ERROR: literal newline found in data HINT: Use \n to represent newline. CONTEXT: COPY arquivo_serasa_marco, line 2: How can find solution for this? The file does not seem to be in valid COPY format. What is the format of the file? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command: how to define a tab character as the delimiter
Thomas Kellerer spam_ea...@gmx.net writes: \copy foo (foo, bar) from foobar.txt delimiter as '\t' csv header So how can I specify a tab character if I also need to specify that my file has a header line? Type an actual tab. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command: how to define a tab character as the delimiter
Tom Lane wrote on 09.03.2010 18:21: Thomas Kellererspam_ea...@gmx.net writes: \copy foo (foo, bar) from foobar.txt delimiter as '\t' csv header So how can I specify a tab character if I also need to specify that my file has a header line? Type an actual tab. Blush That easy? Thanks Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command: how to define a tab character as the delimiter
On 09/03/2010 17:30, Thomas Kellerer wrote: Tom Lane wrote on 09.03.2010 18:21: Thomas Kellererspam_ea...@gmx.net writes: \copy foo (foo, bar) from foobar.txt delimiter as '\t' csv header So how can I specify a tab character if I also need to specify that my file has a header line? Type an actual tab. Blush That easy? This is Postgres you're talking about - of course it's that easy! :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command: how to define a tab character as the delimiter
Raymond O'Donnell wrote on 09.03.2010 18:39: This is Postgres you're talking about - of course it's that easy! :-) :) The main reason I asked, was that the manual actually claims that '\t' can be used (The following special backslash sequences are recognized by COPY FROM) As this is part of the description for the COPY command, does this maybe mean it is only valid for COPY but not for \copy? if that is the case, it should be documented somewhere). Or is this related to the value of standard_conforming_strings? Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command: how to define a tab character as the delimiter
Thomas Kellerer spam_ea...@gmx.net writes: The main reason I asked, was that the manual actually claims that '\t' can be used (The following special backslash sequences are recognized by COPY FROM) \t is recognized in the copy data, not in the command's parameters. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command: how to define a tab character as the delimiter
On 03/09/2010 10:09 AM, Thomas Kellerer wrote: Raymond O'Donnell wrote on 09.03.2010 18:39: This is Postgres you're talking about - of course it's that easy! :-) :) The main reason I asked, was that the manual actually claims that '\t' can be used (The following special backslash sequences are recognized by COPY FROM) As this is part of the description for the COPY command, does this maybe mean it is only valid for COPY but not for \copy? if that is the case, it should be documented somewhere). Or is this related to the value of standard_conforming_strings? Thomas From here: http://www.postgresql.org/docs/8.4/interactive/app-psql.html The syntax of the command is similar to that of the SQL COPY command. Note that, because of this, special parsing rules apply to the \copy command. In particular, the variable substitution rules and backslash escapes do not apply. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
As Tom says, this doesn't really address my original issue, which was not that I read the material on encoding and misunderstood it, but that I didn't even see that material because it was mixed in with a bunch of other notes on all sorts of random subjects. To address this issue in the documentation at large, I would like to see every command that has I18N/L10N-related behavior have a separate sub-head for the explanation of that behavior. That way, anyone who needs to know about that aspect (which should be everyone), just has to look for the sub-head to be sure they have found what they need to know. Whilst I know we can't do that for every single cross-command topic, it seems to me that I18N/L10N is sufficiently important to users of pg that it merits this treatment. FWIW, I think error handling/behavior also merits its own sub-heads throughout. And there should be links within the pages to sub-heads (cf. DB2's online doc). Of course, all of this would be a substantial project. Note that for the COPY command the I18N/L10N material covers both DateStyle and encoding. In respect of Bruce's proposed changes, I prefer the original wording (for the same reasons as Tom), but with the addition of the mention of the server - ... read from or written to a file directly by the server. -- Peter Headland Architect Actuate Corporation -Original Message- From: Bruce Momjian [mailto:br...@momjian.us] Sent: Monday, February 22, 2010 22:01 To: Tom Lane Cc: Peter Headland; Adrian Klaver; pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have updated the documentation to be more direct about COPY encoding behavior. Patch attached and applied. Uh, why exactly do you find that better? Processes data seems a lot vaguer to me than the previous wording. I certainly don't think that this does much to address Peter's original complaint. I thought the problem was that we said input, then output and then got to the point about the server, and I thought the reader just stopped reading that far, so I tried to shorten it so the idea was sooner, and I mentioned server at the end. It might not be better, but I tried. We don't want to highlight the input/output, we want to highlight that all input and output are controlled by the client encoding. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
Peter Headland wrote: In respect of Bruce's proposed changes, I prefer the original wording (for the same reasons as Tom), but with the addition of the mention of the server - ... read from or written to a file directly by the server. OK, done with the attached patch. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/copy.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.94 diff -c -c -r1.94 copy.sgml *** doc/src/sgml/ref/copy.sgml 23 Feb 2010 05:17:33 - 1.94 --- doc/src/sgml/ref/copy.sgml 23 Feb 2010 21:38:07 - *** *** 1,5 !-- ! $PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.94 2010/02/23 05:17:33 momjian Exp $ PostgreSQL documentation -- --- 1,5 !-- ! $PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.93 2010/02/17 04:19:39 tgl Exp $ PostgreSQL documentation -- *** *** 367,376 /para para ! commandCOPY/command always processes data according to the ! current client encoding, even if the data does not pass through ! the client but is read from or written to a file directly by the ! server. /para para --- 367,376 /para para ! Input data is interpreted according to the current client encoding, ! and output data is encoded in the the current client encoding, even ! if the data does not pass through the client but is read from or ! written to a file directly by the server. /para para -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
I have updated the documentation to be more direct about COPY encoding behavior. Patch attached and applied. --- Peter Headland wrote: Maybe the link might help? http://www.postgresql.org/docs/8.4/interactive/multibyte.html That page is too generic; what would be helpful is a section in the doc for each command that is affected by I18N/L10N considerations, that identifies how that specific command behaves. Now that I have grasped the behavior, I'm more than happy to edit the COPY doc page, if people think that would be helpful/worthwhile. -- Peter Headland Architect Actuate Corporation -Original Message- From: Adrian Klaver [mailto:akla...@comcast.net] Sent: Thursday, September 10, 2009 11:06 To: Peter Headland Cc: pgsql-general@postgresql.org; Tom Lane Subject: Re: [GENERAL] COPY command character set - Peter Headland pheadl...@actuate.com wrote: The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Rats - I read the manual page twice and that didn't register on my feeble consciousness. I suspect that I didn't look beyond the word client, since I knew I wasn't interested in client behavior and I was speed-reading. On the assumption that I am not uniquely stupid, maybe we could re-phrase this slightly, with a for example, and add a heading Localization? As a general comment, I18N/L10N is a hairy enough topic that it merits its own heading in any commands where it is an issue. How about my suggestion to add a means (extend COPY syntax) to specify encoding explicitly and handle UTF lead bytes - would that be of interest? -- Peter Headland Architect Actuate Corporation The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Seems clear enough to me. regards, tom lane Maybe the link might help? http://www.postgresql.org/docs/8.4/interactive/multibyte.html Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/copy.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.93 diff -c -c -r1.93 copy.sgml *** doc/src/sgml/ref/copy.sgml 17 Feb 2010 04:19:39 - 1.93 --- doc/src/sgml/ref/copy.sgml 23 Feb 2010 05:15:00 - *** *** 367,376 /para para ! Input data is interpreted according to the current client encoding, ! and output data is encoded in the the current client encoding, even ! if the data does not pass through the client but is read from or ! written to a file. /para para --- 367,376 /para para ! commandCOPY/command always processes data according to the ! current client encoding, even if the data does not pass through ! the client but is read from or written to a file directly by the ! server. /para para -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
Bruce Momjian br...@momjian.us writes: I have updated the documentation to be more direct about COPY encoding behavior. Patch attached and applied. Uh, why exactly do you find that better? Processes data seems a lot vaguer to me than the previous wording. I certainly don't think that this does much to address Peter's original complaint. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have updated the documentation to be more direct about COPY encoding behavior. Patch attached and applied. Uh, why exactly do you find that better? Processes data seems a lot vaguer to me than the previous wording. I certainly don't think that this does much to address Peter's original complaint. I thought the problem was that we said input, then output and then got to the point about the server, and I thought the reader just stopped reading that far, so I tried to shorten it so the idea was sooner, and I mentioned server at the end. It might not be better, but I tried. We don't want to highlight the input/output, we want to highlight that all input and output are controlled by the client encoding. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY FROM wish list
Scott Bailey arta...@comcast.net writes: PgFoundry has http://pgfoundry.org/projects/pgloader/ It is a step in the right direction but definitely not as powerful as sql*loader. Yeah, it's only offering what I needed and what I've been requested to add. So far there's support for INFORMIX UNLOAD files, mysqldump -t files, fixed with files, ragged files, and some more. And it also support python threading for some parallel workload, either loading several files at once or several chunks of the same file, and then 2 modes are possible. If you need more, try asking, you never know. I'm still on the hook to maintaining it, though I've not received any bug report in a long while. I guess it's not much used anymore. I've been proposed to replace the pgloader.conf INI file with a custom COPY command parser exposing all the options, and will consider that sometime in the future. I've been writing a Postgres equivalent that does Oracle SQL*Loader/DB2 Load style import, and external tables using CSV, fixed with and XML data sources. But its not production ready. I'm hoping SQL/MED makes it in to the next release of Postgres so I can throw it all away :) XML support in pgloader will certainly take the form of applying user given XSLT filter that outputs CSV from the XML. That's the option requiring the less code in pgloader I guess :) I'd be happy to see pgloader deprecated by PostgreSQL offering its features. Meantime, do you want to join the fun on the pgloader front? -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY FROM wish list
Marc Mamin wrote:
Hello,
Looking at the TODO List, I feel that only some aspects of the COPY FROM
command are adressed.
Could a discussion trigger some activity on this topic :o) ?
Best regards,
Marc Mamin
Here my wish list:
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ CHECKONLY (DATA_TYPES,CONSTRAINTS) ] # do not import, just
check the content
[ SKIPLINES (n) ]
[ HEADERLINE (n) ]# line conlaining the column names, must be
within the line to skip.
# must match the column list when both are
given
[ DEFERCONSTRAINTS ]
[ SKIPINVALID [FILE file ] SKIPLIMIT (n)] # when set,
invalid lines are skipped and possibly stored in file
# an exception is
triggered when more than SKIPLIMIT lines are found
[ NULLREPLACEMENT (column =value[, ...] ) ] # allow a per column
handling of null values.
# cannot be set
allong with NULL [ AS ]
# not usable for
columns that are missing in the input file ?
[ FEEDBACK (n) ] # display progress every n rows .# for
ORACLE users...
[ COMMIT (n) ]# just an idea: commit each n rows
[
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]
]
Looks like you are requesting sql*loader. My opinion is that we should
keep COPY simple, uncluttered and fast. And instead have a preprocessor
that can do all of the transforms, skipping, checking and logging.
PgFoundry has http://pgfoundry.org/projects/pgloader/
It is a step in the right direction but definitely not as powerful as
sql*loader.
I've been writing a Postgres equivalent that does Oracle SQL*Loader/DB2
Load style import, and external tables using CSV, fixed with and XML
data sources. But its not production ready. I'm hoping SQL/MED makes it
in to the next release of Postgres so I can throw it all away :)
Scott
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY FROM wish list
Marc Mamin wrote: Looking at the TODO List, I feel that only some aspects of the COPY FROM command are adressed. Could a discussion trigger some activity on this topic :o) ? (Sounds only of crickets chirping)...guess not. I would love to have FEEDBACK added. The TODO list doesn't have every COPY extension idea around on it though. http://wiki.postgresql.org/wiki/COPY for example is an implicit wishlist: fix all these things documented as troublesome. Your suggestions might make a good addition to that, perhaps in a section specifically addressing Missing Features Common to Other Database Loaders. If you don't mind a speed loss in the process, I've found pgloader to be a nicer interface for dealing with slightly odd data imports that don't match the built-in COPY restrictions, it does some of the things you're looking for: http://pgfoundry.org/projects/pgloader/ And pg_bulkload aims to handle some of the high-performance features: http://pgbulkload.projects.postgresql.org/ The problem with working on the COPY code, from the perspective of finding people to pay for the job, is that bulk-loading is a one-time operation for many people. Easier to just suck it up and write a set of one-off data massage tools than to try and fix the core to add these capabilties. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
that's because by default 8.4 uses integer timestamps, instead of whatever 8.3 was using. and you pretty much use something, that is suppose to be only used within the scope of the same version and hardware type (and potentially even build).
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
Chase, John jch...@mtcsc.com writes: I am working on upgrading from 8.3.7 to 8.4.1. One of the functions of our application is to export and import data, and to accomplish this I've written some functions that use COPY ... TO ... BINARY and COPY ... FROM ... BINARY. In testing the upgrade to 8.4.1 I noticed that when I import from export files that were created under 8.3.7 the timestamps are not brought in correctly. Probably you've got 8.4 compiled with integer timestamps where the 8.3 DB used float timestamps, or perhaps vice-versa. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
That makes sense, of course. I'm guessing this is because I formally used the pgInstaller and since 8.4 is not supported yet by pgInstaller I moved to the EnterpriseDB installer. The man behind the current must have done the build with different options. Would you concur? Maybe I should ask the man behind the curtain (Dave Page). Thanks! -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, October 14, 2009 10:21 AM To: Chase, John Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect Chase, John jch...@mtcsc.com writes: I am working on upgrading from 8.3.7 to 8.4.1. One of the functions of our application is to export and import data, and to accomplish this I've written some functions that use COPY ... TO ... BINARY and COPY ... FROM ... BINARY. In testing the upgrade to 8.4.1 I noticed that when I import from export files that were created under 8.3.7 the timestamps are not brought in correctly. Probably you've got 8.4 compiled with integer timestamps where the 8.3 DB used float timestamps, or perhaps vice-versa. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
Chase, John jch...@mtcsc.com writes: That makes sense, of course. I'm guessing this is because I formally used the pgInstaller and since 8.4 is not supported yet by pgInstaller I moved to the EnterpriseDB installer. The man behind the current must have done the build with different options. Would you concur? Well, there's not much guessing or asking necessary --- try show integer_datetimes on both servers. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
Wow, quick response from Dave Page. For those who may be interested, here's his answer: pgInstaller used floating point, whilst the one-click installers use (and will continue to use) the more accurate integer timestamps. -Original Message- From: Chase, John Sent: Wednesday, October 14, 2009 10:29 AM To: pgsql-general@postgresql.org Subject: RE: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect That makes sense, of course. I'm guessing this is because I formally used the pgInstaller and since 8.4 is not supported yet by pgInstaller I moved to the EnterpriseDB installer. The man behind the current must have done the build with different options. Would you concur? Maybe I should ask the man behind the curtain (Dave Page). Thanks! -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, October 14, 2009 10:21 AM To: Chase, John Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect Chase, John jch...@mtcsc.com writes: I am working on upgrading from 8.3.7 to 8.4.1. One of the functions of our application is to export and import data, and to accomplish this I've written some functions that use COPY ... TO ... BINARY and COPY ... FROM ... BINARY. In testing the upgrade to 8.4.1 I noticed that when I import from export files that were created under 8.3.7 the timestamps are not brought in correctly. Probably you've got 8.4 compiled with integer timestamps where the 8.3 DB used float timestamps, or perhaps vice-versa. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
On 14/10/2009 15:28, Chase, John wrote: That makes sense, of course. I'm guessing this is because I formally used the pgInstaller and since 8.4 is not supported yet by pgInstaller I moved to the EnterpriseDB installer. The man behind the current must As I understand it, pgInstaller is going to be maintained for pre-8.4 versions only; the only installer for 8.4+ is EnterpriseDB's one-click installer. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY binary
Nathaniel napt...@yahoo.co.uk writes: When using PQputCopyData and PQgetCopyData to send and receive binary data from postgres, would you include/expect headers and trailers (as well as the tuples themselves) as you would in a binary file named 'file_name' if you were executing the SQL COPY BINARY table_name FROM/TO 'file_name'? Yes. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
set client_encoding = 'utf8'; copy from stdin/to stdout; What if I want to do this on the server side (because it's much, much faster)? Does COPY use the default encoding of the database? If not, what? If this is a restrictive as it appears, and there are no outstanding enhancements planned in this area, I might be interested in improving this command to allow specifying the encoding and to have it do obvious stuff like recognize UTF lead bytes automatically. At the very least, the documentation needs some work to explain these subtleties. -- Peter Headland Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, September 09, 2009 19:14 To: Peter Headland Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set Peter Headland pheadl...@actuate.com writes: The documentation of the COPY command does not state what character set(s) are recognized or written. I need to import and export UTF-8 data; how can I do that? set client_encoding = 'utf8'; copy from stdin/to stdout; regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
Peter Headland pheadl...@actuate.com writes: set client_encoding = 'utf8'; copy from stdin/to stdout; What if I want to do this on the server side (because it's much, much faster)? Does COPY use the default encoding of the database? If not, what? If this is a restrictive as it appears, and there are no outstanding enhancements planned in this area, I might be interested in improving this command to allow specifying the encoding and to have it do obvious stuff like recognize UTF lead bytes automatically. At the very least, the documentation needs some work to explain these subtleties. The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Seems clear enough to me. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Rats - I read the manual page twice and that didn't register on my feeble consciousness. I suspect that I didn't look beyond the word client, since I knew I wasn't interested in client behavior and I was speed-reading. On the assumption that I am not uniquely stupid, maybe we could re-phrase this slightly, with a for example, and add a heading Localization? As a general comment, I18N/L10N is a hairy enough topic that it merits its own heading in any commands where it is an issue. How about my suggestion to add a means (extend COPY syntax) to specify encoding explicitly and handle UTF lead bytes - would that be of interest? -- Peter Headland Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, September 10, 2009 10:38 To: Peter Headland Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set Peter Headland pheadl...@actuate.com writes: set client_encoding = 'utf8'; copy from stdin/to stdout; What if I want to do this on the server side (because it's much, much faster)? Does COPY use the default encoding of the database? If not, what? If this is a restrictive as it appears, and there are no outstanding enhancements planned in this area, I might be interested in improving this command to allow specifying the encoding and to have it do obvious stuff like recognize UTF lead bytes automatically. At the very least, the documentation needs some work to explain these subtleties. The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Seems clear enough to me. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
- Peter Headland pheadl...@actuate.com wrote: The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Rats - I read the manual page twice and that didn't register on my feeble consciousness. I suspect that I didn't look beyond the word client, since I knew I wasn't interested in client behavior and I was speed-reading. On the assumption that I am not uniquely stupid, maybe we could re-phrase this slightly, with a for example, and add a heading Localization? As a general comment, I18N/L10N is a hairy enough topic that it merits its own heading in any commands where it is an issue. How about my suggestion to add a means (extend COPY syntax) to specify encoding explicitly and handle UTF lead bytes - would that be of interest? -- Peter Headland Architect Actuate Corporation The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Seems clear enough to me. regards, tom lane Maybe the link might help? http://www.postgresql.org/docs/8.4/interactive/multibyte.html Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
Peter Headland pheadl...@actuate.com writes: How about my suggestion to add a means (extend COPY syntax) to specify encoding explicitly and handle UTF lead bytes - would that be of interest? There are no lead bytes in UTF-8, and we make no pretense of handling UTF-16, so I don't think we'd be interested in some hack that cleans up misencoding problems. The idea of overriding client_encoding has been suggested before. I don't remember if it was rejected or is just languishing on the TODO list. I'd be a little worried about sending clients data in an encoding they aren't expecting, but if it only works for I/O to a file it might be okay. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
There are no lead bytes in UTF-8 Sorry, sloppy use of terminology. I should have said UTF signatures aka the byte order mark. IOW, the magic number bytes commonly found at the front of UTF encoded files: UTF-16 little-endian FF FE UTF-16 big-endian FE FF UTF-8 EF BB BF These tend to be inserted automatically by text editors, so it would be advantageous to have them handled automatically by COPY (at least as an option). Right now, if I edit a UTF-8 file then load it with COPY, I get errors or bad data if the editor chose to add the 3 signature bytes. Whilst UTF-16 is not supported internally, COPY seems to be a legitimate special case, because it is used for migration to/from other tools that may emit or expect UTF-16. ISTR that Postgres uses UCI? If so it would be near-trivial to allow COPY to read and write UTF-16. If done via a syntax extension to COPY (which I think is the most desirable implementation), this would have no adverse effect on any other capability. It also seems sufficiently isolated from sensitive/complex areas of the code that it might make a suitable first project for someone who is interested in becoming a contributor... -- Peter Headland Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, September 10, 2009 11:13 To: Peter Headland Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set Peter Headland pheadl...@actuate.com writes: How about my suggestion to add a means (extend COPY syntax) to specify encoding explicitly and handle UTF lead bytes - would that be of interest? There are no lead bytes in UTF-8, and we make no pretense of handling UTF-16, so I don't think we'd be interested in some hack that cleans up misencoding problems. The idea of overriding client_encoding has been suggested before. I don't remember if it was rejected or is just languishing on the TODO list. I'd be a little worried about sending clients data in an encoding they aren't expecting, but if it only works for I/O to a file it might be okay. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
Maybe the link might help? http://www.postgresql.org/docs/8.4/interactive/multibyte.html That page is too generic; what would be helpful is a section in the doc for each command that is affected by I18N/L10N considerations, that identifies how that specific command behaves. Now that I have grasped the behavior, I'm more than happy to edit the COPY doc page, if people think that would be helpful/worthwhile. -- Peter Headland Architect Actuate Corporation -Original Message- From: Adrian Klaver [mailto:akla...@comcast.net] Sent: Thursday, September 10, 2009 11:06 To: Peter Headland Cc: pgsql-general@postgresql.org; Tom Lane Subject: Re: [GENERAL] COPY command character set - Peter Headland pheadl...@actuate.com wrote: The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Rats - I read the manual page twice and that didn't register on my feeble consciousness. I suspect that I didn't look beyond the word client, since I knew I wasn't interested in client behavior and I was speed-reading. On the assumption that I am not uniquely stupid, maybe we could re-phrase this slightly, with a for example, and add a heading Localization? As a general comment, I18N/L10N is a hairy enough topic that it merits its own heading in any commands where it is an issue. How about my suggestion to add a means (extend COPY syntax) to specify encoding explicitly and handle UTF lead bytes - would that be of interest? -- Peter Headland Architect Actuate Corporation The COPY command reference page saith Input data is interpreted according to the current client encoding, and output data is encoded in the the current client encoding, even if the data does not pass through the client but is read from or written to a file. Seems clear enough to me. regards, tom lane Maybe the link might help? http://www.postgresql.org/docs/8.4/interactive/multibyte.html Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
Peter Headland wrote: As a general comment, I18N/L10N is a hairy enough topic that it merits its own heading in any commands where it is an issue. I agree, this seems a good idea because people is often confused by this. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command character set
Peter Headland pheadl...@actuate.com writes: The documentation of the COPY command does not state what character set(s) are recognized or written. I need to import and export UTF-8 data; how can I do that? set client_encoding = 'utf8'; copy from stdin/to stdout; regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy: unexpected response (4)
Neil Best nb...@ci.uchicago.edu writes: psql:copy.sql:8059525: \copy: unexpected response (4) psql:copy.sql:8059525: \copy: unexpected response (4) psql:copy.sql:8059525: \copy: unexpected response (4) psql:copy.sql:8059525: \copy: unexpected response (4) psql:copy.sql:8059525: \copy: unexpected response (4) psql:copy.sql:8059525: \copy: unexpected response (4) psql:copy.sql:8059525: \copy: unexpected response (4) psql:copy.sql:8059525: \copy: unexpected response (4) Hmm. It looks like psql could get into an infinite loop if the server failed to exit COPY IN mode for some reason, but it's not at all clear how that could happen (or what to do about it). What server version and what psql version is this? What does the server's log show? Can you provide a reproducible test case? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy: unexpected response (4)
Tom Lane-2 wrote: Hmm. It looks like psql could get into an infinite loop if the server failed to exit COPY IN mode for some reason, but it's not at all clear how that could happen (or what to do about it). What server version and what psql version is this? What does the server's log show? Can you provide a reproducible test case? Maybe it is a networking issue after all. I also thought to check the server logs and found this: 2009-08-06 16:52:16 CDTCONTEXT: COPY gm1, line 415306, column gm1c: 743.5 2009-08-06 16:52:16 CDTSTATEMENT: COPY gm1 FROM STDIN CSV 2009-08-06 16:52:51 CDTLOG: checkpoints are occurring too frequently (7 seconds apart) 2009-08-06 16:52:51 CDTHINT: Consider increasing the configuration parameter checkpoint_segments. 2009-08-06 16:53:02 CDTLOG: checkpoints are occurring too frequently (11 seconds apart) 2009-08-06 16:53:02 CDTHINT: Consider increasing the configuration parameter checkpoint_segments. 2009-08-06 16:53:20 CDTERROR: invalid input syntax for integer: 68.84 2009-08-06 16:53:20 CDTCONTEXT: COPY gm4, line 411272, column gm4c: 68.84 2009-08-06 16:53:20 CDTSTATEMENT: COPY gm4 FROM STDIN CSV 2009-08-06 16:53:20 CDTLOG: SSL renegotiation failure 2009-08-06 16:53:20 CDTSTATEMENT: COPY gm4 FROM STDIN CSV 2009-08-06 16:53:20 CDTLOG: SSL error: unexpected record 2009-08-06 16:53:20 CDTSTATEMENT: COPY gm4 FROM STDIN CSV 2009-08-06 16:53:20 CDTLOG: could not send data to client: Connection reset by peer 2009-08-06 16:53:20 CDTSTATEMENT: COPY gm4 FROM STDIN CSV 2009-08-06 16:53:20 CDTLOG: SSL error: unexpected record 2009-08-06 16:53:20 CDTLOG: could not receive data from client: Connection reset by peer 2009-08-06 16:53:20 CDTLOG: incomplete message from client 2009-08-06 17:38:21 CDTLOG: could not receive data from client: Operation timed out 2009-08-06 17:38:21 CDTLOG: unexpected EOF on client connection (END) As for the versions: monthly= select version(); version -- PostgreSQL 8.3.7 on i386-apple-darwin9.6.0, compiled by GCC $ psql --version psql (PostgreSQL) 8.1.11 Could this mismatch cause the problem? I will find out if it's feasible to copy my data to the server to rule out these client and network vagaries. Should I be concerned abou the checkpoint messages? I haven't looked up what those are all about yet. -- View this message in context: http://www.nabble.com/%5Ccopy%3A-unexpected-response-%284%29-tp24866027p24867582.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy: unexpected response (4)
Neil Best nb...@ci.uchicago.edu writes: Tom Lane-2 wrote: Hmm. It looks like psql could get into an infinite loop if the server failed to exit COPY IN mode for some reason, but it's not at all clear how that could happen (or what to do about it). What server version and what psql version is this? What does the server's log show? Can you provide a reproducible test case? Maybe it is a networking issue after all. I also thought to check the server logs and found this: 2009-08-06 16:53:20 CDTERROR: invalid input syntax for integer: 68.84 2009-08-06 16:53:20 CDTCONTEXT: COPY gm4, line 411272, column gm4c: 68.84 2009-08-06 16:53:20 CDTSTATEMENT: COPY gm4 FROM STDIN CSV 2009-08-06 16:53:20 CDTLOG: SSL renegotiation failure 2009-08-06 16:53:20 CDTSTATEMENT: COPY gm4 FROM STDIN CSV 2009-08-06 16:53:20 CDTLOG: SSL error: unexpected record 2009-08-06 16:53:20 CDTSTATEMENT: COPY gm4 FROM STDIN CSV 2009-08-06 16:53:20 CDTLOG: could not send data to client: Connection reset by peer Hmm, so it looks like the connection dropped and libpq failed to recognize that, or maybe libpq was okay but psql needs to check a bit more carefully here. I'll take a look. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy: unexpected response (4)
I wrote:
Hmm, so it looks like the connection dropped and libpq failed to
recognize that, or maybe libpq was okay but psql needs to check a bit
more carefully here. I'll take a look.
I could not reproduce this problem in testing, but after eyeballing
the code awhile I have a theory. It looks like it is possible for
PQputCopyEnd to fail and leave the PGconn in COPY_IN state, but this
only happens (1) if the output buffer contained at least 8K already,
causing pqSendSome to be invoked from pqPutMsgEnd, and (2) pqSendSome
returned failure. In that situation the loop in copy.c becomes
infinite, since there's no mechanism for getting out of COPY_IN state.
This case would be relatively difficult to trigger, but it seems to fit
all the facts, if we assume that the connection had failed for some
reason just at that point. BTW, the SSL renegotiation failure bit
suggests that it could have been an OpenSSL bug not a real network
lossage, so you might want to see how up-to-date your openssl libraries
are.
Anyway, it seems to me that the most appropriate fix is to add some
code to that loop, along the lines of
/*
* Make sure we have pumped libpq dry of results; else it may still be
in
* ASYNC_BUSY state, leading to false readings in, eg, get_prompt().
*/
while ((result = PQgetResult(pset.db)) != NULL)
{
success = false;
psql_error(\\copy: unexpected response (%d)\n,
PQresultStatus(result));
+ /* if still in COPY IN state, try to get out of it */
+ if (PQresultStatus(result) == PGRES_COPY_IN)
+ PQputCopyEnd(conn, _(trying to exit copy mode));
PQclear(result);
}
This would cover this particular case and perhaps others as well.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy: unexpected response (4)
On Fri, Aug 7, 2009 at 12:33 PM, Tom Lanet...@sss.pgh.pa.us wrote: BTW, the SSL renegotiation failure bit suggests that it could have been an OpenSSL bug not a real network lossage, so you might want to see how up-to-date your openssl libraries are. Thanks for your comments, Tom. The operation seems more reliable if I move the data to the server and do it across a local connection, which I presume does not involve SSL, so that may be the weak link as you surmise. Would you expect the SSL library problem more likely to be on the server or the client, or is it just hard to say? Does either of them have a facility that exposes the SSL version information or do I have to go to the OS for that? Incidentally, I have not experienced any sort of instability in my interactive sessions over an SSL connection, so is it related to the \copy operation itself, the higher volume of data across the connection, or the fact that I am asking it to do multiple \copies in rapid succession, would you say? I expect it's hard to say definitively, but maybe you or someone else can say something about likelihoods. I appreciate the information. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command error
De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] De la part de Andrew Maracini Envoyé : 3 août 2009 11:46 À : pgsql-general@postgresql.org Objet : [GENERAL] \copy command error hi, I can't seem to get the \copy command to work. Here's my syntax: GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ',' ERROR extra data after last expected column Line 1 It is taking all of the fields and grouping them into one field, the .csv has about 4700 records, here is what the first two look like: Jon,JF,Doe,GIS Coordinator,Miami University,Ohio,Oxford,OH,United States,10/25/02 Janet,,Doe,PhD Student and Professional Geologist,York University,Toronto,ON,Canada,9/25/07 The table has 9 fields mostly varchar and one date field. I'm running 8.3 on Windows Vista Ultimate thanks. Andy Hi Andy, Your CSV lines have 10 fields (10 comma separated values). Probably, in the data you are exporting, the City/State is one field, but the comma is being exported, thus giving you 10 fields. If this is the case, exporting the City/state field wrapped in quotation marks should do the truck, or use a different delimiter, like Tab Mark
Re: [GENERAL] \copy command error
Mark Watson wrote: *De :* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] *De la part de* Andrew Maracini *Envoyé :* 3 août 2009 11:46 *À :* pgsql-general@postgresql.org *Objet :* [GENERAL] \copy command error hi, I can't seem to get the \copy command to work. Here's my syntax: GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ',' ERROR extra data after last expected column Line 1 It is taking all of the fields and grouping them into one field, the .csv has about 4700 records, here is what Maybe the error is \c because '\c' is used to connect to another database. try the same line without '\' only : copy gisp from d:/projects/gisp/gisp.csv' delimiters ','; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command error
On Mon, Aug 03, 2009 at 01:25:56PM -0400, Mark Watson wrote: Andrew Maracini wrote: GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ',' If this is the case, exporting the City/state field wrapped in quotation marks should do the trick You'll want to use the real CSV parser then, the code in psql would look like this: \copy gisp FROM 'd:/projects/gisp/gisp.csv' WITH CSV PG and MS Excel have an almost identical definition of what a CSV file should look like, opening the file in Excel is always a good quick check as to why PG isn't liking the file. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] \copy command error
On Mon, Aug 03, 2009 at 01:18:06PM -0500, erobles wrote: Maybe the error is \c because '\c' is used to connect to another database. try the same line without '\' only : copy gisp from d:/projects/gisp/gisp.csv' delimiters ','; \copy is a special command in psql that does a copy from the system that psql is running in, rather than a normal COPY command that runs on the server. I believe \copy is implemented as a COPY FROM STDIN... with psql automatically piping the data over the connection for you. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command on windows???
Andreas wrote on 17.07.2009 20:06: Hi, I'd like to read a csv file into PG 8.4. COPY relations FROM E'd:\\relations.csv' CSV HEADER; It throws (translated): ERROR: can't open file d:\relations.csv for reading file or directory not found Try COPY relations FROM 'd:/relations.csv' CSV HEADER; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY 'invalid byte sequence for encoding UTF8: 0xff'
Chris Worley wrote: Hello, I get the following error when running a sql script containing a COPY command: ERROR: invalid byte sequence for encoding UTF8: 0xff... The data I have contains binary data from a tcp dump Does anybody know how the dump pulls a column with binary data? It is a varchar column No, if it it binary data, you need a column of type bytea and you need to escape the appropriate characters in your input. For example, if I create a table with a single bytea column called foo and insert the a record with value ^F^O^O then dump it, the dump will have the following: COPY byteatest (foo) FROM stdin; \\006\\017\\017 \. See http://www.postgresql.org/docs/8.3/static/datatype-binary.html for the characters that need to be escaped. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY 'invalid byte sequence for encoding UTF8: 0xff'
On Tue, Apr 21, 2009 at 1:39 PM, Steve Crawford scrawf...@pinpointresearch.com wrote: Chris Worley wrote: Hello, I get the following error when running a sql script containing a COPY command: ERROR: invalid byte sequence for encoding UTF8: 0xff... The data I have contains binary data from a tcp dump Does anybody know how the dump pulls a column with binary data? It is a varchar column No, if it it binary data, you need a column of type bytea and you need to escape the appropriate characters in your input. For example, if I create a table with a single bytea column called foo and insert the a record with value ^F^O^O then dump it, the dump will have the following: COPY byteatest (foo) FROM stdin; \\006\\017\\017 \. How does pg_dump and pg_restonre handle everything with no errors? The column is basically telnet buffers which contains escape sequences so i have bytes such as 0xff, 0x1b... Piping the output through iconv helped formatting some of the data, but it appears I am still left with some, such as 0xff. It would be nice to know what sql pg_dump uses to create the contents inside of the copy command. Any idea what that is? -chris worley See http://www.postgresql.org/docs/8.3/static/datatype-binary.html for the characters that need to be escaped. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY 'invalid byte sequence for encoding UTF8: 0xff'
Chris Worley wrote: ... For example, if I create a table with a single bytea column called foo and insert the a record with value ^F^O^O then dump it, the dump will have the following: COPY byteatest (foo) FROM stdin; \\006\\017\\017 \. How does pg_dump and pg_restonre handle everything with no errors? See above. It escapes the data so it can be represented in a query. The Ctrl-F in the bytea column is converted for insertion purposes to its 3-digit octal equivalent of 006 represented as single escaped character in the data as \\006. The Ctrl-O is \\017. Both \\006 and \\017 are single characters. You _can_ escape all characters - ie instead of 'A' you could use '\\101' but you are only _required_ to escape those certain characters listed in the documentation. Again, see http://www.postgresql.org/docs/8.3/static/datatype-binary.html. The column is basically telnet buffers which contains escape sequences so i have bytes such as 0xff, 0x1b... Piping the output through iconv helped formatting some of the data, but it appears I am still left with some, such as 0xff. This makes no sense. Binary data could be an executable, a video, a photo, audio data, etc. but it is not linguistic characters where a character-set interpretation is appropriate. You may pipe it through iconv and find there are fewer errors in the attempted import but that does not imply that output is in any way improved by the transformation. (I can also feed sox to a JPEG photo, tell it that the data is mu-law, and have it create an MP3 but the result would just be painful noise.) It would be nice to know what sql pg_dump uses to create the contents inside of the copy command. Any idea what that is? Yes, it escapes those bytea characters that require escaping and leaves the others alone. Here is the same example from before with the string ^F^O^Obar (foo in control-characters, bar in lowercase) in the bytea column: COPY byteatest (foo) FROM stdin; \\006\\017\\017bar \. You need to do the same in your program. Most client interfaces have an escape-bytea function that will handle it for you. In C you could use PQescapeByteaConn while PHP uses pg_escape_bytea. Find the appropriate function for your language. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY 'invalid byte sequence for encoding UTF8: 0xff'
hmm, I was shelling out and using psql and piping the data to another file. Not using the dbi stuff with perl. Guess i can use a regular expression and review the link you sent me and escape them my self. Don't *ahem* quote me on this as I haven't been using Perl for a while but it should be something similar to: $quoted_data = $dbh-quote($data, PG_BYTEA) If not, I'm sure the documentation for the quote function will set things straight. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from with trigger
Well that's a bummer, ok. Thanks. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Sunday, April 05, 2009 10:27 PM To: Chris spotts Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] copy from with trigger Chris spotts rfu...@gmail.com writes: I'm trying to copy from a tab delimited file. The dates inside the file are Unix timestamp style dates. I thought the following script would do the trick, but it just gives me an error saying ERROR: invalid input syntax for type timestamp: 1238736600 CONTEXT: COPY testtable line 1, column acquire_time: 1238736600 A trigger isn't going to help in the least for that; the data has to be a valid timestamp before the trigger will ever see it. The usual trick for this type of conversion is to load the data into a temporary table that has simple column types (eg integer or text) and then do your transformations during an INSERT/SELECT into the real target table. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from with trigger
Chris Spotts escribió: That's a dead link for me. Yes, because the message was very new and the archive indexer hadn't picked it up yet :-) Try again now. -Original Message- From: Alvaro Herrera [mailto:alvhe...@commandprompt.com] Sent: Monday, April 06, 2009 12:42 PM To: Chris Spotts Cc: 'Tom Lane'; pgsql-general@postgresql.org Subject: Re: [GENERAL] copy from with trigger Chris Spotts escribió: Well that's a bummer, ok. Thanks. See also http://archives.postgresql.org/message-id/20090406173912.GB4525%40alvh.no-ip .org -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from with trigger
Chris Spotts escribió: Well that's a bummer, ok. Thanks. See also http://archives.postgresql.org/message-id/20090406173912.GB4525%40alvh.no-ip.org -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from with trigger
That's a dead link for me. -Original Message- From: Alvaro Herrera [mailto:alvhe...@commandprompt.com] Sent: Monday, April 06, 2009 12:42 PM To: Chris Spotts Cc: 'Tom Lane'; pgsql-general@postgresql.org Subject: Re: [GENERAL] copy from with trigger Chris Spotts escribió: Well that's a bummer, ok. Thanks. See also http://archives.postgresql.org/message-id/20090406173912.GB4525%40alvh.no-ip .org -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] copy from with trigger
Chris spotts rfu...@gmail.com writes: I'm trying to copy from a tab delimited file. The dates inside the file are Unix timestamp style dates. I thought the following script would do the trick, but it just gives me an error saying ERROR: invalid input syntax for type timestamp: 1238736600 CONTEXT: COPY testtable line 1, column acquire_time: 1238736600 A trigger isn't going to help in the least for that; the data has to be a valid timestamp before the trigger will ever see it. The usual trick for this type of conversion is to load the data into a temporary table that has simple column types (eg integer or text) and then do your transformations during an INSERT/SELECT into the real target table. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command question
On 17/03/2009 14:45, Ivano Luberti wrote: Hi all, executing the following command inside pgAdmin on my Windows Vista (please avoid comment, I pray you) : copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV Try putting an 'E' in front of the path, like this: from E'c:\\temp\\anagraficaANIDIs.csv' with csv; Also, remember that the file needs to be on the same machine as the server; if you're running pgAdmin on a different machine, this won't work. HTH, Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] COPY command question
Thanks but it keeps on not finding the file: the warning has disappeared ERROR: could not open file c:\temp\anagraficaANIDIs.csv for reading: No such file or directory ** Errore ** ERROR: could not open file c:\temp\anagraficaANIDIs.csv for reading: No such file or directory Stato SQL: 58P01 I have also tried uppercasing C without success. I'm sure about the path because I have copied and pasted from the properties window. Raymond O'Donnell ha scritto: On 17/03/2009 14:45, Ivano Luberti wrote: Hi all, executing the following command inside pgAdmin on my Windows Vista (please avoid comment, I pray you) : copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV Try putting an 'E' in front of the path, like this: from E'c:\\temp\\anagraficaANIDIs.csv' with csv; Also, remember that the file needs to be on the same machine as the server; if you're running pgAdmin on a different machine, this won't work. HTH, Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it == -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
