On 08/12/2016 09:34 AM, Edmundo Robles wrote:
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.
I think there is a bigger problem. From one of attempts: ERROR: cannot COPY to/from client in PL/pgSQL
On Fri, Aug 12, 2016 at 11:11 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> <mailto:adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> <mailto:adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> -- Adrian Klaver adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
