[GENERAL] Error while importing CSV file
am new at dealing with PostgreSQL I was using PGAdmin and needed to export some data from a query I had run and then import it in another DB. *my columns names* : id (int4), name (varchar), time_starp(timestamp) so I followed these instructions http://www.question-defense.com/2010/10/15/how-to-export-from-pgadmin-export-pgadmin-data-to-csv till Export Configuration window , I had to choose the ; coz the , didn't separate the columns right in the csv file. but when I import this file to another postgreSQL DB by using: *COPY Table FROM '[insert .csv dir here]' USING DELIMITERS ',' CSV WITH HEADER* I got this error : *ERROR: invalid input syntax for integer: id;name;timestamp* am not sure whats wrong here -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5458103.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] Error while importing CSV file
On Feb 5, 2012, at 11:04, Shadin_ w_war...@hotmail.com wrote: am new at dealing with PostgreSQL I was using PGAdmin and needed to export some data from a query I had run and then import it in another DB. *my columns names* : id (int4), name (varchar), time_starp(timestamp) so I followed these instructions http://www.question-defense.com/2010/10/15/how-to-export-from-pgadmin-export-pgadmin-data-to-csv till Export Configuration window , I had to choose the ; coz the , didn't separate the columns right in the csv file. but when I import this file to another postgreSQL DB by using: *COPY Table FROM '[insert .csv dir here]' USING DELIMITERS ',' CSV WITH HEADER* I got this error : *ERROR: invalid input syntax for integer: id;name;timestamp* am not sure whats wrong here id;name;timestamp ... USING DELIMITERS ',' ... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuumlo fails pgsql ver 8.3
Gday all, I have a large database with many large objects, linked to a single table. I have been trying to backup the database so I can migrate to a later version, but the backup has been failing due to problems within pg_largeobject. I am not surprised at these errors, because the server is not protected by UPS and has had several power outages (I'm working on this with the database owner!) over the past few years. Recently I tried to perform a vacuumlo, as I thought this might clear up the problems, and the process fails after a long time with the following log: F:\PostgreSQL\8.3\binvacuumlo -U xxx -W xxx Password: Failed to remove lo 317198793: ERROR: large object 317198793 does not exist Failed to remove lo 4065905288: ERROR: current transaction is aborted, commands ignored until end of transaction block Failed to remove lo 0: ERROR: current transaction is aborted, commands ignored until end of transaction block F:\PostgreSQL\8.3\bin When I added the -v option, there were many removing lo x messages before the above messages appeared. I have previously tried to reindex pg_largeobject, but that process failed as well. Can someone offer please offer some advice on how to resolve this? TIA. Pat Heuvel (in Australia)
Re: [GENERAL] vacuumlo fails pgsql ver 8.3
Pat Heuvel pheu...@tpg.com.au writes: [ vacuumlo fails ] When I added the -v option, there were many removing lo x messages before the above messages appeared. I have previously tried to reindex pg_largeobject, but that process failed as well. You need to get the index consistent before trying vacuumlo; that program is not designed to deal with inconsistent catalogs. What exactly happens when you try to reindex pg_largeobject? 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
[GENERAL] Large object rights management
Hi all, I wrote an application that store a large quantity of files in the database as large binary objects. There are around 50 tables (all in one schema) and only one table host all these large objects. Every user connect to database using his own user, so all users are parts of the same group role for granting permissione to every application user. My problem is about large object permissions introduced in postgresql 9.0, since my application permits to everyone to insert/update/delete any large object. Now, since 9.0, deleting a large object is only possible for the user that owns it. I know that 9.0 also introduced an option for reverting this behaviour as it was in 8.4, but I wonder if there is any other way of sharing and deleting large objects in 9.0. Thanks, Giuseppe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] .psql_history file is messed up and control-r does not work
My .psql_history contains lines of the form. select\040sum(price)\040from\040products\040p\040join\040 My psql client is 9.1.2 on Mac OS and server is linux 9.0.5 Is the version mismatch messing up this .psql_history file? Also control-r to search the history isn't working at psql prompt from Mac although it does work if I log in to server where client and server are the same version. Thanks for any assistance in this issue. mr. wu -- 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] .psql_history file is messed up and control-r does not work
zhong ming wu mr.z.m...@gmail.com writes: My .psql_history contains lines of the form. select\040sum(price)\040from\040products\040p\040join\040 Yeah, that's what it will look like if psql is using Apple's libedit library; it's unrelated to the server. I think libedit doesn't support control-r either, not totally sure though. In any case there are some known bugs in libedit that Apple's not been terribly swift to fix. I'd suggest installing GNU readline and linking psql against that instead. 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] .psql_history file is messed up and control-r does not work
Yeah, that's what it will look like if psql is using Apple's libedit library; it's unrelated to the server. I think libedit doesn't support control-r either, not totally sure though. In any case there are some known bugs in libedit that Apple's not been terribly swift to fix. I'd suggest installing GNU readline and linking psql against that instead. Thanks. compiling with readline solves both problems. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] sql query bug???
Is this a bug (in 9.0.4), or have I just gone cross-eyed from too much work? The query: select t0.ICD9, t0.Description, count(*) from (select distinct Person_Id, ICD9, Description from PatientDiagnoses) as t0 group by (t0.ICD9, t0.Description) order by count(*) desc limit 10; The error: column t0.ICD9 must appear in the GROUP BY clause or be used in an aggregate function Huh? FWIW, I'm not providing the PatientDiagnoses def because it's a complex query involving 3-way union of 5-way joins--intended for end-user querying. Of note, this query works (and performance is good enough as well): select ICD9, count(*) from (select distinct Person_Id, ICD9 from PatientDiagnoses) as t0 group by ICD9 order by count(*) desc limit 10; -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SPI functions to work with the Execution Plan
I need to develop for my diploma thesis an extension that allows me to travel the implementation plan once you pass through the phase of planning and optimization. SPI functions that could be used to obtain and work with the plan? Fin a la injusticia, LIBERTAD AHORA A NUESTROS CINCO COMPATRIOTAS QUE SE ENCUENTRAN INJUSTAMENTE EN PRISIONES DE LOS EEUU! http://www.antiterroristas.cu http://justiciaparaloscinco.wordpress.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] Error while importing CSV file
Thanks but still i get this : *ERROR: invalid input syntax for integer: id* (1st col only) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459334.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] Error while importing CSV file
OK that problem solved Thanks so much but I had another problem *ERROR: value too long for type character varying(100)* although in the original file it was varying(25) only !! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459345.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] Error while importing CSV file
Is this ERROR thrown for all the rows ? Try the following. select max(length(column_name)) from table_name; It seems that some value is bigger than the define size. Date: Sun, 5 Feb 2012 21:14:40 -0800 From: w_war...@hotmail.com To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Error while importing CSV file OK that problem solved Thanks so much but I had another problem *ERROR: value too long for type character varying(100)* although in the original file it was varying(25) only !! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459345.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] sql query bug???
Scott Ribe scott_r...@elevated-dev.com writes: Is this a bug (in 9.0.4), or have I just gone cross-eyed from too much work? The query: select t0.ICD9, t0.Description, count(*) from (select distinct Person_Id, ICD9, Description from PatientDiagnoses) as t0 group by (t0.ICD9, t0.Description) order by count(*) desc limit 10; The error: column t0.ICD9 must appear in the GROUP BY clause or be used in an aggregate function Drop the parentheses in the GROUP BY. As is, this is requesting to group by the composite value ROW(t0.ICD9, t0.Description). You could argue that that's sufficient to determine both of those column values, but PG doesn't make that deduction (and, I think, is not required to by spec). 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] SPI functions to work with the Execution Plan
Osmel Barreras =?utf-8?Q?Pi=C3=B1era?= obarre...@estudiantes.uci.cu writes: I need to develop for my diploma thesis an extension that allows me to travel the implementation plan once you pass through the phase of planning and optimization. SPI functions that could be used to obtain and work with the plan? SPI is not meant to expose anything like that. You could install a planner_hook though, and look at the plan after calling standard_planner. 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
[GENERAL] Triggering a function on table overwrite
Hi Normally when I need to run a function during an insert I make it a trigger function of that table. However, in this case, I need to overwrite the table with which the trigger is attached. I would appreciate any suggestions on how to do this. Bob
Re: [GENERAL] Error while importing CSV file
no not all of rows is that gonna work ? thanks -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459396.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] sql query bug???
On Feb 5, 2012, at 10:46 PM, Tom Lane wrote: Drop the parentheses in the GROUP BY. I had the suspicion that it was some kind of a late-night brain fart ;-) I don't know where the hell the parens came from, since I've *NEVER* put spurious parens in a group by clause before. But it took someone pointing it out to me to get me to notice that irregularity. Fatigue... One more day of super-crunch and then I get to take a break... Thanks. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Error while importing CSV file
I've tried a lot of sizes but I still have messages in my log saying: * --- ERROR: value too long for type character varying(200) --- * Why is this? There are no other varchar(200) columns in my DB at all, no other table. Only this column used to be 25 characters and using the alter type command I changed it to 200. It looks alright, the column also accepts the value, but the error is logged. I'm confused. How to stop the error? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Error-while-importing-CSV-file-tp5458103p5459427.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
