Re: [GENERAL] pasting a lot of commands to psql

[Alvaro Herrera]

Tom Lane wrote:
> Francisco Olarte  writes:
> > On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera  
> > 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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pasting a lot of commands to psql

[Tom Lane]

Francisco Olarte  writes:
> On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera  
> 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


-- 
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] pasting a lot of commands to psql

[Francisco Olarte]

Hi:

On Thu, Jul 7, 2016 at 8:18 PM, Alvaro Herrera  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.


-- 
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] pasting a lot of commands to psql

[Tom Lane]

Alvaro Herrera  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


-- 
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] pasting a lot of commands to psql

[Alvaro Herrera]

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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pasting a lot of commands to psql

[John R Pierce]

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



--
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] pasting a lot of commands to psql

[Merlin Moncure]

On Thu, Jul 7, 2016 at 2:48 PM, Alvaro Herrera  wrote:
> Merlin Moncure wrote:
>> On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera  
>> 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


-- 
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] pasting a lot of commands to psql

[John R Pierce]

On 7/7/2016 12:48 PM, Alvaro Herrera wrote:

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

Re: [GENERAL] pasting a lot of commands to psql

[Alvaro Herrera]

Merlin Moncure wrote:
> On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera  
> 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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pasting a lot of commands to psql

[Merlin Moncure]

On Thu, Jul 7, 2016 at 1:18 PM, Alvaro Herrera  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


-- 
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] pasting a lot of commands to psql

[Alvaro Herrera]

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 Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] pasting a lot of commands to psql

[Adrian Klaver]

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  & relap.io


2016-07-07 20:36 GMT+03:00 Melvin Davidson >:



On Thu, Jul 7, 2016 at 1:33 PM, Dmitry Shalashov > 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
 & relap.io 

2016-07-07 20:30 GMT+03:00 Melvin Davidson >:

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
>
wrote:



On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov,
> 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



Dmitry Shalashov, surfingbird.ru
 & 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
 

Re: [GENERAL] pasting a lot of commands to psql

[Rob Sargent]

If, on the wild chance you're an emacs user, if the section is in a 
".sql" buffer Ctrl-C Ctrl-B will send the entire buffer.  Not sure if 
there's a practical limit on the number of line/statements, but I've 
used this with a couple of "pages" worth of sql.


On 07/07/2016 11:48 AM, Dmitry Shalashov wrote:

Oh sweet! Thanks!


Dmitry Shalashov, surfingbird.ru  & relap.io 



2016-07-07 20:39 GMT+03:00 Scott Mead >:




On Thu, Jul 7, 2016 at 1:30 PM, Dmitry Shalashov
> 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...


Dmitry Shalashov, surfingbird.ru  &
relap.io 

2016-07-07 20:26 GMT+03:00 Sameer Kumar
>:



On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov,
> 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.

--Scott


What are you exactly aiming to do?

Have you tried -
psql  < myfile



Dmitry Shalashov, surfingbird.ru
 & 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





-- 
--

Scott Mead
Sr. Architect
/OpenSCG /
http://openscg.com

Re: [GENERAL] pasting a lot of commands to psql

[Dmitry Shalashov]

Oh sweet! Thanks!


Dmitry Shalashov, surfingbird.ru & relap.io

2016-07-07 20:39 GMT+03:00 Scott Mead :

>
>
> On Thu, Jul 7, 2016 at 1:30 PM, Dmitry Shalashov 
> 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...
>>
>>
>> Dmitry Shalashov, surfingbird.ru & relap.io
>>
>> 2016-07-07 20:26 GMT+03:00 Sameer Kumar :
>>
>>>
>>>
>>> On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, 
>>> 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.
>
> --Scott
>
>
>>
>>> What are you exactly aiming to do?
>>>
>>> Have you tried -
>>> psql  < myfile
>>>
>>>
>>>
 Dmitry Shalashov, surfingbird.ru & 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
>>>
>>
>>
>
>
> --
> --
> Scott Mead
> Sr. Architect
> *OpenSCG *
> http://openscg.com
>

Re: [GENERAL] pasting a lot of commands to psql

[Dmitry Shalashov]

> Have you looked at this line in the file-
> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'

in file it's perfectly fine. It's something that happens during paste
brokes it.


Dmitry Shalashov, surfingbird.ru & relap.io

2016-07-07 20:37 GMT+03:00 Sameer Kumar :

>
>
> On Fri, Jul 8, 2016 at 1:31 AM Dmitry Shalashov  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-
> INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'
>
> 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 network
>
>
>> Dmitry Shalashov, surfingbird.ru & relap.io
>>
>> 2016-07-07 20:26 GMT+03:00 Sameer Kumar :
>>
>>>
>>>
>>> On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, 
>>> 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
>>>
>>>
>>>
 Dmitry Shalashov, surfingbird.ru & 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
>>>
>>
>> --
> --
> 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
>

Re: [GENERAL] pasting a lot of commands to psql

[Dmitry Shalashov]

> 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

That would be sad... Actually if you don't know the answer then I suppose I
can't do anything about this issue :-) Gotta use another approaches like \i.

However I have to add that this glitch could happen after five lines or
twenty lines, not really looks like some hard limit of something.
Initially I suspected that "INSERT 0 1" answers after each statement got in
the way, but `\set QUIET` didn't solved this problem.


Dmitry Shalashov, surfingbird.ru & relap.io

2016-07-07 20:37 GMT+03:00 Tom Lane :

> Dmitry Shalashov  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
>

Re: [GENERAL] pasting a lot of commands to psql

[John R Pierce]

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



--
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] pasting a lot of commands to psql

[Dmitry Shalashov]

> 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.

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 & relap.io

2016-07-07 20:36 GMT+03:00 Melvin Davidson :

>
>
> On Thu, Jul 7, 2016 at 1:33 PM, Dmitry Shalashov 
> 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 & relap.io
>>
>> 2016-07-07 20:30 GMT+03:00 Melvin Davidson :
>>
>>> 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 
>>> wrote:
>>>


 On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, 
 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



> Dmitry Shalashov, surfingbird.ru & 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',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.
>

Re: [GENERAL] pasting a lot of commands to psql

[Scott Mead]

On Thu, Jul 7, 2016 at 1:39 PM, Scott Mead  wrote:

>
>
> On Thu, Jul 7, 2016 at 1:30 PM, Dmitry Shalashov 
> 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...
>>
>>
>> Dmitry Shalashov, surfingbird.ru & relap.io
>>
>> 2016-07-07 20:26 GMT+03:00 Sameer Kumar :
>>
>>>
>>>
>>> On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, 
>>> 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).
>

on windows, it actually pops up notepad.exe.  Save and close, same behavior.

--Scott


> Paste your data, then save-close the file.  It will put you back into psql
> and execute the command for you.
>
> --Scott
>
>
>>
>>> What are you exactly aiming to do?
>>>
>>> Have you tried -
>>> psql  < myfile
>>>
>>>
>>>
 Dmitry Shalashov, surfingbird.ru & 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
>>>
>>
>>
>
>
> --
> --
> Scott Mead
> Sr. Architect
> *OpenSCG *
> http://openscg.com
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] pasting a lot of commands to psql

[Scott Mead]

On Thu, Jul 7, 2016 at 1:30 PM, Dmitry Shalashov  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...
>
>
> Dmitry Shalashov, surfingbird.ru & relap.io
>
> 2016-07-07 20:26 GMT+03:00 Sameer Kumar :
>
>>
>>
>> On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, 
>> 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.

--Scott


>
>> What are you exactly aiming to do?
>>
>> Have you tried -
>> psql  < myfile
>>
>>
>>
>>> Dmitry Shalashov, surfingbird.ru & 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
>>
>
>


-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] pasting a lot of commands to psql

[Sameer Kumar]

On Fri, Jul 8, 2016 at 1:31 AM Dmitry Shalashov  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-
INSERT INTO oko_topsites VALUES('russian_federation','calend.ru'

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 network


> Dmitry Shalashov, surfingbird.ru & relap.io
>
> 2016-07-07 20:26 GMT+03:00 Sameer Kumar :
>
>>
>>
>> On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, 
>> 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
>>
>>
>>
>>> Dmitry Shalashov, surfingbird.ru & 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
>>
>
> --
--
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

Re: [GENERAL] pasting a lot of commands to psql

[Tom Lane]

Dmitry Shalashov  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


-- 
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] pasting a lot of commands to psql

[Melvin Davidson]

On Thu, Jul 7, 2016 at 1:33 PM, Dmitry Shalashov  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 & relap.io
>
> 2016-07-07 20:30 GMT+03:00 Melvin Davidson :
>
>> 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 
>> wrote:
>>
>>>
>>>
>>> On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, 
>>> 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
>>>
>>>
>>>
 Dmitry Shalashov, surfingbird.ru & 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',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.

Re: [GENERAL] pasting a lot of commands to psql

[Dmitry Shalashov]

> *\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 & relap.io

2016-07-07 20:30 GMT+03:00 Melvin Davidson :

> 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 
> wrote:
>
>>
>>
>> On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov, 
>> 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
>>
>>
>>
>>> Dmitry Shalashov, surfingbird.ru & 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.
>

Re: [GENERAL] pasting a lot of commands to psql

[Dmitry Shalashov]

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...


Dmitry Shalashov, surfingbird.ru & relap.io

2016-07-07 20:26 GMT+03:00 Sameer Kumar :

>
>
> On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov,  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
>
>
>
>> Dmitry Shalashov, surfingbird.ru & 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
>

Re: [GENERAL] pasting a lot of commands to psql

[Melvin Davidson]

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 
wrote:

>
>
> On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov,  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
>
>
>
>> Dmitry Shalashov, surfingbird.ru & 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.

Re: [GENERAL] pasting a lot of commands to psql

[Sameer Kumar]

On Fri, 8 Jul 2016, 1:23 a.m. Dmitry Shalashov,  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



> Dmitry Shalashov, surfingbird.ru & 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

[GENERAL] pasting a lot of commands to psql

[Dmitry Shalashov]

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 :-)

Dmitry Shalashov, surfingbird.ru & relap.io