Thread: Error at dynamic generated copy...
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','
**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.
Regards and thanks in advance.
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@aklaver.com
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);
The secondary problem.
Could not the above be written as:
copy tablefoo (id_foo, foo_name, foo_lastname, foo_age) FROM stdin
DELIMITER as '|' ;
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.
>
>
> Regards and thanks in advance.
--
Adrian Klaver
adrian.klaver@aklaver.com
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.
On Fri, Aug 12, 2016 at 9:43 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
The above seems to be the real problem.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.
Can you describe more what you are doing when you INSERT?--
Regards and thanks in advance.
Adrian Klaver
adrian.klaver@aklaver.com
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.
So are all the other INSERTs this slow also?
Using COPY to 'game' the system does not look like a long term solution
to me. Lack of system resources will eventually prevail and then what
happens?
>
>
>
> On Fri, Aug 12, 2016 at 9:43 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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.klaver@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 Klaveradrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.<adrian.klaver@aklaver.com <mailto:adrian.klaver@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 Klavercom>
--
Adrian Klaver
adrian.klaver@aklaver.com
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@aklaver.com <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> <mailto:adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>>
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com