Обсуждение: pasting a lot of commands to psql
Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)What are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>...I `cat` it, copy it to buffer, go to my beloved psql and insert it there.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)What are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
This might seem a bit basic, but as long as you have a psql session, why not just use\i your_file_name>...I `cat` it, copy it to buffer, go to my beloved psql and insert it there.On Thu, Jul 7, 2016 at 1:26 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)What are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
This might seem a bit basic, but as long as you have a psql session, why not just use\i your_file_nameNo need to cat, copy & paste!--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
> \i your_file_nameI actually mentioned this way in my original posting. In my case it would involve copy-paste anyway - to create a file on a system where I have psql opened.I may do it that way. But why copy-paste directly to psql results in this behaviour?2016-07-07 20:30 GMT+03:00 Melvin Davidson <melvin6925@gmail.com>:This might seem a bit basic, but as long as you have a psql session, why not just use\i your_file_name>...I `cat` it, copy it to buffer, go to my beloved psql and insert it there.On Thu, Jul 7, 2016 at 1:26 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)What are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
This might seem a bit basic, but as long as you have a psql session, why not just use\i your_file_nameNo need to cat, copy & paste!--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
It would help if you provided the version of PostgreSQL and the O/S.
Have you also tried simplifying the sql for multiple values in one statement?
eg:
INSERT INTO oko_topsites
VALUES
('russian_federation','ati.su',0,NULL,5),
('russian_federation','audit-it.ru',0,NULL,5),
...
...
('russian_federation','calorizator.ru',0,NULL,5);
--
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
![](http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif)
Dmitry Shalashov <skaurus@gmail.com> writes: > Let say that I have some sql file with like hundred of simple statements in > it. I `cat` it, copy it to buffer, go to my beloved psql and insert it > there. > But somewhere after first few lines it screws over: Yeah, I've noticed there's a limit on the amount you can paste into psql, at least on some platforms. AFAICT this must be a readline bug/limitation, or maybe something about the X cutbuffer protocol; there's nothing in psql itself that would even know that a paste is happening. You might have better luck with "psql -n", or maybe not. regards, tom lane
Hi Sameer,I am trying to copy-paste (and execute) random snippets of SQL to psql console.
There is another ways to do it, which do not involve copy-paste, but I am wondering why is copy-paste won't work. What exactly is happening there...
2016-07-07 20:26 GMT+03:00 Sameer Kumar <sameer.kumar@ashnik.com>:On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);
b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'
b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)What are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
Hi Sameer,I am trying to copy-paste (and execute) random snippets of SQL to psql console.There is another ways to do it, which do not involve copy-paste, but I am wondering why is copy-paste won't work. What exactly is happening there...2016-07-07 20:26 GMT+03:00 Sameer Kumar <sameer.kumar@ashnik.com>:On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)
What are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
On Thu, Jul 7, 2016 at 1:30 PM, Dmitry Shalashov <skaurus@gmail.com> wrote:Hi Sameer,I am trying to copy-paste (and execute) random snippets of SQL to psql console.There is another ways to do it, which do not involve copy-paste, but I am wondering why is copy-paste won't work. What exactly is happening there...2016-07-07 20:26 GMT+03:00 Sameer Kumar <sameer.kumar@ashnik.com>:On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)My personal favorite for this exact thing is to use '\e'When you are in psql, if you \e (on *nix) it will open a temp file in whatever your $EDITOR variable is set ( I use vim).
Paste your data, then save-close the file. It will put you back into psql and execute the command for you.--ScottWhat are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
--
It would help if you provided the version of PostgreSQL and the O/S.On Thu, Jul 7, 2016 at 1:33 PM, Dmitry Shalashov <skaurus@gmail.com> wrote:> \i your_file_nameI actually mentioned this way in my original posting. In my case it would involve copy-paste anyway - to create a file on a system where I have psql opened.I may do it that way. But why copy-paste directly to psql results in this behaviour?2016-07-07 20:30 GMT+03:00 Melvin Davidson <melvin6925@gmail.com>:This might seem a bit basic, but as long as you have a psql session, why not just use\i your_file_name>...I `cat` it, copy it to buffer, go to my beloved psql and insert it there.On Thu, Jul 7, 2016 at 1:26 PM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)What are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
This might seem a bit basic, but as long as you have a psql session, why not just use\i your_file_nameNo need to cat, copy & paste!--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Have you also tried simplifying the sql for multiple values in one statement?
eg:
INSERT INTO oko_topsites
VALUES
('russian_federation','ati.su',0,NULL,5),
('russian_federation','audit-it.ru',0,NULL,5),
...
...
('russian_federation','calorizator.ru',0,NULL,5);
--Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
On 7/7/2016 10:30 AM, Dmitry Shalashov wrote: > > There is another ways to do it, which do not involve copy-paste, but I > am wondering why is copy-paste won't work. What exactly is happening > there... probably your OS's console terminal emulation is overrunning on the large paste. many moving parts between the OS desktop clipboard, and psql. how about using psql -f file_of_commands.sql ? -- john r pierce, recycling bits in santa cruz
> at least on some platforms. AFAICT this must be a readline
> bug/limitation, or maybe something about the X cutbuffer protocol
Dmitry Shalashov <skaurus@gmail.com> writes:
> Let say that I have some sql file with like hundred of simple statements in
> it. I `cat` it, copy it to buffer, go to my beloved psql and insert it
> there.
> But somewhere after first few lines it screws over:
Yeah, I've noticed there's a limit on the amount you can paste into psql,
at least on some platforms. AFAICT this must be a readline
bug/limitation, or maybe something about the X cutbuffer protocol;
there's nothing in psql itself that would even know that a paste is
happening.
You might have better luck with "psql -n", or maybe not.
regards, tom lane
> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'
On Fri, Jul 8, 2016 at 1:31 AM Dmitry Shalashov <skaurus@gmail.com> wrote:Hi Sameer,I am trying to copy-paste (and execute) random snippets of SQL to psql console.Why? Is it some migration of data? You are better off exporting the data to csv and use COPY command.There is another ways to do it, which do not involve copy-paste, but I am wondering why is copy-paste won't work. What exactly is happening there...Have you looked at this line in the file-Either the line in file lacks a closing parenthesis or may be your terminal is not able to process so many characters so fast and hence it is missing/skipping on some characters. I have experiences this behavior, not just with psql but with usual shell when I use utterly slow VPN or when I use screen share tools like TeamViewer or WebEx etc over slow network2016-07-07 20:26 GMT+03:00 Sameer Kumar <sameer.kumar@ashnik.com>:On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);The below is broken...b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)What are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
On Thu, Jul 7, 2016 at 1:30 PM, Dmitry Shalashov <skaurus@gmail.com> wrote:Hi Sameer,I am trying to copy-paste (and execute) random snippets of SQL to psql console.There is another ways to do it, which do not involve copy-paste, but I am wondering why is copy-paste won't work. What exactly is happening there...2016-07-07 20:26 GMT+03:00 Sameer Kumar <sameer.kumar@ashnik.com>:On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, <skaurus@gmail.com> wrote:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)My personal favorite for this exact thing is to use '\e'When you are in psql, if you \e (on *nix) it will open a temp file in whatever your $EDITOR variable is set ( I use vim). Paste your data, then save-close the file. It will put you back into psql and execute the command for you.--ScottWhat are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
--
Oh sweet! Thanks!2016-07-07 20:39 GMT+03:00 Scott Mead <scottm@openscg.com>:On Thu, Jul 7, 2016 at 1:30 PM, Dmitry Shalashov <skaurus@gmail.com> wrote:Hi Sameer,I am trying to copy-paste (and execute) random snippets of SQL to psql console.There is another ways to do it, which do not involve copy-paste, but I am wondering why is copy-paste won't work. What exactly is happening there...2016-07-07 20:26 GMT+03:00 Sameer Kumar <sameer.kumar@ashnik.com>:Hi everyone.Let say that I have some sql file with like hundred of simple statements in it. I `cat` it, copy it to buffer, go to my beloved psql and insert it there.But somewhere after first few lines it screws over:b2b=> BEGIN;b2b=> INSERT INTO oko_topsites VALUES('russian_federation','ati.su',0,NULL,5);INSERT INTO oko_topsites VALUES('russian_federation'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','atn.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','audit-it.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','aup.ru',0,NULL,5);'b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autocentre.ua',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','autodoc.ru',0,NULL,5);b2b=> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'b2b(> INSERT INTO oko_topsites VALUES('russian_federation','calorizator.ru',0,NULL,5)Unclosed quotes, unclosed parenthesis - anyway it wont work.How to safely insert big number of statements to psql at once?I am aware about "execute this file" \i option of psql, that is not the answer I am looking for, thanks :-)My personal favorite for this exact thing is to use '\e'When you are in psql, if you \e (on *nix) it will open a temp file in whatever your $EDITOR variable is set ( I use vim). Paste your data, then save-close the file. It will put you back into psql and execute the command for you.--ScottWhat are you exactly aiming to do?Have you tried -psql < myfile----Best RegardsSameer Kumar | DB Solution ArchitectASHNIK PTE. LTD.101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
--
On 07/07/2016 10:41 AM, Dmitry Shalashov wrote: >> Have you also tried simplifying the sql for multiple values in one > statement? > this is another possible solution, but it's not answering my general > "why is this happening" question. When I have seen this I put it down to encoding issues. It happens most often when I cut and paste from places like this list, where sometimes strange/hidden characters are swept up in the cut and then do not translate into the paste. > > This is happening with PostgreSQL 9.5.3 and psql 9.5.3, but actually I > am seeing this for a few years... Since I started to use PostgreSQL > actively I believe. > > > Dmitry Shalashov, surfingbird.ru <http://surfingbird.ru> & relap.io > <http://relap.io> > > 2016-07-07 20:36 GMT+03:00 Melvin Davidson <melvin6925@gmail.com > <mailto:melvin6925@gmail.com>>: > > > > On Thu, Jul 7, 2016 at 1:33 PM, Dmitry Shalashov <skaurus@gmail.com > <mailto:skaurus@gmail.com>> wrote: > > > *\i your_file_name* > * > * > I actually mentioned this way in my original posting. In my case > it would involve copy-paste anyway - to create a file on a > system where I have psql opened. > I may do it that way. But why copy-paste directly to psql > results in this behaviour? > > > Dmitry Shalashov, surfingbird.ru > <http://surfingbird.ru> & relap.io <http://relap.io> > > 2016-07-07 20:30 GMT+03:00 Melvin Davidson <melvin6925@gmail.com > <mailto:melvin6925@gmail.com>>: > > This might seem a bit basic, but as long as you have a psql > session, why not just use > \i your_file_name > > On Thu, Jul 7, 2016 at 1:26 PM, Sameer Kumar > <sameer.kumar@ashnik.com <mailto:sameer.kumar@ashnik.com>> > wrote: > > > > On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, > <skaurus@gmail.com <mailto:skaurus@gmail.com>> wrote: > > Hi everyone. > > Let say that I have some sql file with like hundred > of simple statements in it. I `cat` it, copy it to > buffer, go to my beloved psql and insert it there. > But somewhere after first few lines it screws over: > > b2b=> BEGIN; > b2b=> INSERT INTO oko_topsites > VALUES('russian_federation','ati.su > <http://ati.su>',0,NULL,5); > INSERT INTO oko_topsites > VALUES('russian_federation'b2b=> INSERT INTO > oko_topsites VALUES('russian_federation','atn.ua > <http://atn.ua>',0,NULL,5); > b2b=> INSERT INTO oko_topsites > VALUES('russian_federation','audit-it.ru > <http://audit-it.ru>',0,NULL,5); > b2b=> INSERT INTO oko_topsites > VALUES('russian_federation','aup.ru > <http://aup.ru>',0,NULL,5); > ' > b2b=> INSERT INTO oko_topsites > VALUES('russian_federation','autocentre.ua > <http://autocentre.ua>',0,NULL,5); > > b2b=> INSERT INTO oko_topsites > VALUES('russian_federation','autodoc.ru > <http://autodoc.ru>',0,NULL,5); > > b2b=> INSERT INTO oko_topsites > VALUES('russian_federation','calend.ru > <http://calend.ru>' > > b2b(> INSERT INTO oko_topsites > VALUES('russian_federation','calorizator.ru > <http://calorizator.ru>',0,NULL,5) > > Unclosed quotes, unclosed parenthesis - anyway it > wont work. > > How to safely insert big number of statements to > psql at once? > I am aware about "execute this file" \i option of > psql, that is not the answer I am looking for, > thanks :-) > > > What are you exactly aiming to do? > > Have you tried - > psql < myfile > > > > Dmitry Shalashov, surfingbird.ru > <http://surfingbird.ru> & relap.io <http://relap.io> > > -- > -- > Best Regards > Sameer Kumar | DB Solution Architect > *ASHNIK PTE. LTD.* > > 101 Cecil Street, #11-11 Tong Eng Building, Singapore > 069 533 > > T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com > > > >...I `cat` it, copy it to buffer, go to my beloved psql and > insert it there. > This might seem a bit basic, but as long as you have a psql > session, why not just use > *\i your_file_name * > > No need to cat, copy & paste! > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. > > > > It would help if you provided the version of PostgreSQL and the O/S. > > Have you also tried simplifying the sql for multiple values in one > statement? > eg: > INSERT INTO oko_topsites > VALUES > ('russian_federation','ati.su <http://ati.su>',0,NULL,5), > ('russian_federation','audit-it.ru <http://audit-it.ru>',0,NULL,5), > ... > ... > ('russian_federation','calorizator.ru > <http://calorizator.ru>',0,NULL,5); > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. > > -- Adrian Klaver adrian.klaver@aklaver.com
Tom Lane wrote: > You might have better luck with "psql -n", or maybe not. I've wished sometimes for a "\set READLINE off" psql metacommand for this kind of thing. It's pretty annoying when the text being pasted contains tabs and readline uses to do completion. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Tom Lane wrote: > >> You might have better luck with "psql -n", or maybe not. > > I've wished sometimes for a "\set READLINE off" psql metacommand for > this kind of thing. It's pretty annoying when the text being pasted > contains tabs and readline uses to do completion. Agreed. I've looked at this problem extensively and concur that readline is the culprit; I don't think there's any solution on our end besides filing a bug with the readline. I also agree with the upthread suggestion that the best workaround today is to \e into a non-readline based editor (vim qualifies). Having said that, at least on linux/gnome, very long pastes can cause severe performance issues as well. So for large pastes I go with psql -f. merlin
Merlin Moncure wrote: > On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > > Tom Lane wrote: > > > >> You might have better luck with "psql -n", or maybe not. > > > > I've wished sometimes for a "\set READLINE off" psql metacommand for > > this kind of thing. It's pretty annoying when the text being pasted > > contains tabs and readline uses to do completion. > > Agreed. I've looked at this problem extensively and concur that > readline is the culprit; I don't think there's any solution on our end > besides filing a bug with the readline. I also agree with the > upthread suggestion that the best workaround today is to \e into a > non-readline based editor (vim qualifies). Having said that, at least > on linux/gnome, very long pastes can cause severe performance issues > as well. So for large pastes I go with psql -f. Hmm, I was doing megabyte-long pastes (longest one over 5 MB) just a few weeks ago and was pleasantly surprised to discover that they worked just fine with no noticeable performance problem. I was pasting skype logs directly from the Linux skype client window into an xterm running cat, with obviously no readline involved. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Agreed. I've looked at this problem extensively and concur that > readline is the culprit; I don't think there's any solution on our end > besides filing a bug with the readline. I also agree with the > upthread suggestion that the best workaround today is to \e into a > non-readline based editor (vim qualifies). Having said that, at least > on linux/gnome, very long pastes can cause severe performance issues > as well. So for large pastes I go with psql -f.Hmm, I was doing megabyte-long pastes (longest one over 5 MB) just a few weeks ago and was pleasantly surprised to discover that they worked just fine with no noticeable performance problem. I was pasting skype logs directly from the Linux skype client window into an xterm running cat, with obviously no readline involved.
yeah, long pastes generally work fine with ssh sessions, too, using ssh clients like putty or securecrt.
-- john r pierce, recycling bits in santa cruz
On Thu, Jul 7, 2016 at 2:48 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Merlin Moncure wrote: >> On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: >> > Tom Lane wrote: >> > >> >> You might have better luck with "psql -n", or maybe not. >> > >> > I've wished sometimes for a "\set READLINE off" psql metacommand for >> > this kind of thing. It's pretty annoying when the text being pasted >> > contains tabs and readline uses to do completion. >> >> Agreed. I've looked at this problem extensively and concur that >> readline is the culprit; I don't think there's any solution on our end >> besides filing a bug with the readline. I also agree with the >> upthread suggestion that the best workaround today is to \e into a >> non-readline based editor (vim qualifies). Having said that, at least >> on linux/gnome, very long pastes can cause severe performance issues >> as well. So for large pastes I go with psql -f. > > Hmm, I was doing megabyte-long pastes (longest one over 5 MB) just a few > weeks ago and was pleasantly surprised to discover that they worked just > fine with no noticeable performance problem. I was pasting skype logs > directly from the Linux skype client window into an xterm running cat, > with obviously no readline involved. Might be a 'xterm vs Mate Terminal' problem. Using raw xterm performance is great. I like some of the creature comforts of the mate terminal though. merlin
On 7/7/2016 10:36 AM, Melvin Davidson wrote: > It would help if you provided the version of PostgreSQL and the O/S. we still don't know what OS you're using here, and if its something like linux, what desktop environment you're using (gnome, kde, mate, etc) ? -- john r pierce, recycling bits in santa cruz
Merlin Moncure wrote: > Might be a 'xterm vs Mate Terminal' problem. Using raw xterm > performance is great. I like some of the creature comforts of the > mate terminal though. Heh. I've been using lxterminal for a couple of weeks now and I find some of these comfort features rather uncomfortable. Haven't tried megabyte pastes. But yeah, it might be the terminal. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes: > Tom Lane wrote: >> You might have better luck with "psql -n", or maybe not. > I've wished sometimes for a "\set READLINE off" psql metacommand for > this kind of thing. It's pretty annoying when the text being pasted > contains tabs and readline uses to do completion. Seems like the readline aspect of that wouldn't be too hard. The fun part would be agreeing on how we want history to behave if you flip this on/off during a session. Which lines get added to history? Does ~/.psql_history get written at all if READLINE is off at the end of the session? A spec that seems reasonable and probably not too hard to implement is: 1. ~/.psql_history is read the first time you turn on READLINE during a session. 2. Input lines are added to history only when READLINE is on. 3. ~/.psql_history is written at session exit if any lines have been added to history during the session (regardless of whether READLINE is currently on). People might complain about point 2, but I think it would be rather difficult to do otherwise, seeing that the active history storage is mostly under libreadline's control. Also, other definitions might lead to retaining a history buffer even in totally non-interactive sessions (where READLINE never gets turned on). That doesn't sound like what we'd want. Another definitional issue is whether turning on READLINE does anything if stdin is not a tty. I'd vote no. regards, tom lane
Hi: On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: >> You might have better luck with "psql -n", or maybe not. > I've wished sometimes for a "\set READLINE off" psql metacommand for > this kind of thing. It's pretty annoying when the text being pasted > contains tabs and readline uses to do completion. Doesn't 'cat | psql ' disable it? I use it with other programs for these purpose ( as well as things like ls | cat to avoid colors/wordwrapping, just makes the program see a non-tty on stidn/stdout ). Francisco Olarte.
Francisco Olarte <folarte@peoplecall.com> writes: > On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: >> I've wished sometimes for a "\set READLINE off" psql metacommand for >> this kind of thing. It's pretty annoying when the text being pasted >> contains tabs and readline uses to do completion. > Doesn't 'cat | psql ' disable it? Sure, but you could as well use 'psql -n'. I think the point is to be able to turn it on and off without starting a fresh session. (Admittedly, maybe there's not a lot of usability gain there.) regards, tom lane
Tom Lane wrote: > Francisco Olarte <folarte@peoplecall.com> writes: > > On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > >> I've wished sometimes for a "\set READLINE off" psql metacommand for > >> this kind of thing. It's pretty annoying when the text being pasted > >> contains tabs and readline uses to do completion. > > > Doesn't 'cat | psql ' disable it? > > Sure, but you could as well use 'psql -n'. I think the point is to be > able to turn it on and off without starting a fresh session. (Admittedly, > maybe there's not a lot of usability gain there.) If your command line already connected to the correct server/database, with the correct login role, then yeah you can do that. If you have to switch role (say the role that runs the commands is not a login role), it's not so convenient to disconnect and launch a new psql. Now of course this not a huge new feature, but a usability improvement only -- but psql is full of small usability features and they make it a pleasure to use. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services