don't worry. I still working in this copy, how can i do to insert newlines to generate the copy dynamically and avoid the syntax error.
On Fri, Aug 12, 2016 at 11:11 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 08/12/2016 08:15 AM, Edmundo Robles wrote: > >> Adrian, your'e right, the real problem is the slow insert, I have >> many devices reporting to the server and saving their state each >> minute so there is a moment where i reach the limit of connections and >> the monitor device send a exception and crash. >> >> >> The table grows a lot, current have more than 13,000,000 records, plus >> have many indexes, that the reason why is slow to insert That's why i >> tried to use copy. >> > > > My COPY example turned out to be a bust, so ignore. Sorry. > > >> >> >> On Fri, Aug 12, 2016 at 9:43 AM, Adrian Klaver >> <adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> wrote: >> >> On 08/12/2016 07:11 AM, Edmundo Robles wrote: >> >> Hi! >> I hope you could help me... >> I tried to generate the next copy instruction in a function: >> >> copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin >> DELIMITER as '|' ; >> 12060157|John|Doe|33 >> \. >> >> >> ** The commands to generate the copy are: >> CREATE OR REPLACE FUNCTION insert_tablefoo(id integer, name >> varchar, >> lastname varchar, age integer) >> >> ... declarations ... >> >> >> qry=format('copy tablefoo (id_foo, foo_name, foo_lastname, >> foo_age) >> FROM stdin DELIMITER as '||chr(39) || '|' || chr(39) || ';' || >> chr(13) >> ||'%s|%s|%s|%s' || chr(13) || '\.',id,name,lastname,age); >> >> execute(qry); >> >> >> ... more declarations >> end <- end function, returns an integer. >> >> >> ** then, i call the function: >> select insert_tablefoo(12321,'John','Doe',33); >> >> **and get the next error: >> >> ERROR: syntax error at or near "12321" >> LINE 2: 12321|John|Doe|33 >> >> ********** Error ********** >> >> >> after many tests, the problem is concatenate the newline, >> because if >> the instruction generated is copied to a file and insert >> manually the >> newline the query works well. >> >> i tried with chr(10), '\n', '\r', split the qry string ( >> execute >> qry_copy || E'\\n' || qry_data || E'\\n\\.') >> but always get the same error :( >> >> >> >> ERROR: syntax error at or near "12321" >> SQL state: 42601 >> >> >> >> >> ** By the way, i tried to replace an insert with copy >> because the >> insert takes more than 3 minutes to insert a single record. >> >> >> The above seems to be the real problem. >> >> Can you describe more what you are doing when you INSERT? >> >> >> >> >> Regards and thanks in advance. >> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
