Обсуждение: pipe text to copy statement stdin input

Поиск
Список
Период
Сортировка

pipe text to copy statement stdin input

От
Scott Frankel
Дата:
Hi all,

Is there a way to pipe text into a COPY statement's stdin input using
cmd-line psql?

I'm using the following syntax to enter large strings of text into a
table.  The text itself has a json-like syntax that has the potential
for carrying numerous special characters.

    COPY mytable(name, description, text) FROM stdin;
    <the text>
    \.

Problem is that my terminal's copy-paste buffer is much smaller than
the text I need to insert.

Note:
- I do not have superuser perms for the db, so passing a file instead
of stdin is not an option.

- Ditto for using \i to import a file.

- The db is password protected, so invoking `psql` as a non-
interactive command may not be possible.  Right?

- If I'm wrong, anyone have example syntax of how to create a valid
COPY statement?  I've found an interesting OSX cmd-line util that
copies/pastes between Terminal and the "pasteboard."   Though I think
this just gets bitten by the file restriction anyway, eg:

    % cat bigfile.txt > pbcopy
    % psql DBNAME USERNAME (PASSWORD???) <<EOF
        COPY mytable(name, description, text) FROM stdin;
       pbpaste > stdin(???)
        \.



pqsl 8.3
OSX 10.5.8
Terminal

Suggestions greatly appreciated!

Thanks
Scott

Re: pipe text to copy statement stdin input

От
John R Pierce
Дата:
On 06/21/11 12:43 PM, Scott Frankel wrote:
>
> Hi all,
>
> Is there a way to pipe text into a COPY statement's stdin input using
> cmd-line psql?
>
> I'm using the following syntax to enter large strings of text into a
> table.  The text itself has a json-like syntax that has the potential
> for carrying numerous special characters.
>
>     COPY mytable(name, description, text) FROM stdin;
> <the text>
>     \.
>
> Problem is that my terminal's copy-paste buffer is much smaller than
> the text I need to insert.
>
> Note:
> - I do not have superuser perms for the db, so passing a file instead
> of stdin is not an option.
>
> - Ditto for using \i to import a file.
>
> - The db is password protected, so invoking `psql` as a
> non-interactive command may not be possible.  Right?
>
> - If I'm wrong, anyone have example syntax of how to create a valid
> COPY statement?  I've found an interesting OSX cmd-line util that
> copies/pastes between Terminal and the "pasteboard."   Though I think
> this just gets bitten by the file restriction anyway, eg:
>
>     % cat bigfile.txt > pbcopy
>     % psql DBNAME USERNAME (PASSWORD???) <<EOF
>         COPY mytable(name, description, text) FROM stdin;
>       pbpaste > stdin(???)
>         \.
>

You can get around the password issue via .pgpass, put this file in your
home directory with permissions 600, and lines like...

     hostname:port:database:username:password

You may replace any fields with *, so like...

     localhost:*:*:youruser:yourpassword

To copy data from a file, use the \copy command in psql, create a .SQL
file like...

    \copy yourtable(name,description,text) from stdin
    val,val,val
    val,val,val
    ...
    \.

then execute this file like

    $ psql -f yourfile.sql -d dbname

There is no file size restriction here, as it reads that file as its
going and streams it to the sql COPY command...

(note indents are purely to show verbatim stuff from my mail text, there
are no idents in these files)

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Re: pipe text to copy statement stdin input

От
Michael Glaesemann
Дата:
On Jun 21, 2011, at 15:43, Scott Frankel wrote:

> Is there a way to pipe text into a COPY statement's stdin input using cmd-line psql?

cat myfile | psql -c "COPY mytable (name, description, text) FROM stdin"

> - The db is password protected, so invoking `psql` as a non-interactive command may not be possible.  Right?

Use a .pgpass file


Michael Glaesemann
grzm seespotcode net

Re: pipe text to copy statement stdin input

От
Scott Frankel
Дата:
John, Michael,

Thanks for the thorough tips.  Worked perfectly!  The .pgpass file is
quite useful.  Could've saved myself a lot of typing the past few years!

Note that since I already prepared a CSV formated file for the COPY
statement, once I created the .pgpass file, I opted for Michael's
suggestion; eg:

    cat myfile | psql -c "COPY mytable (name, description, text) FROM
stdin"

Thanks!
Scott




On Jun 21, 2011, at 1:10 PM, John R Pierce wrote:

> On 06/21/11 12:43 PM, Scott Frankel wrote:
>>
>> Hi all,
>>
>> Is there a way to pipe text into a COPY statement's stdin input
>> using cmd-line psql?
>>
>> I'm using the following syntax to enter large strings of text into
>> a table.  The text itself has a json-like syntax that has the
>> potential for carrying numerous special characters.
>>
>>    COPY mytable(name, description, text) FROM stdin;
>> <the text>
>>    \.
>>
>> Problem is that my terminal's copy-paste buffer is much smaller
>> than the text I need to insert.
>>
>> Note:
>> - I do not have superuser perms for the db, so passing a file
>> instead of stdin is not an option.
>>
>> - Ditto for using \i to import a file.
>>
>> - The db is password protected, so invoking `psql` as a non-
>> interactive command may not be possible.  Right?
>>
>> - If I'm wrong, anyone have example syntax of how to create a valid
>> COPY statement?  I've found an interesting OSX cmd-line util that
>> copies/pastes between Terminal and the "pasteboard."   Though I
>> think this just gets bitten by the file restriction anyway, eg:
>>
>>    % cat bigfile.txt > pbcopy
>>    % psql DBNAME USERNAME (PASSWORD???) <<EOF
>>        COPY mytable(name, description, text) FROM stdin;
>>      pbpaste > stdin(???)
>>        \.
>>
>
> You can get around the password issue via .pgpass, put this file in
> your home directory with permissions 600, and lines like...
>
>    hostname:port:database:username:password
>
> You may replace any fields with *, so like...
>
>    localhost:*:*:youruser:yourpassword
>
> To copy data from a file, use the \copy command in psql, create
> a .SQL file like...
>
>   \copy yourtable(name,description,text) from stdin
>   val,val,val
>   val,val,val
>   ...
>   \.
>
> then execute this file like
>
>   $ psql -f yourfile.sql -d dbname
>
> There is no file size restriction here, as it reads that file as its
> going and streams it to the sql COPY command...
>
> (note indents are purely to show verbatim stuff from my mail text,
> there are no idents in these files)
>
> --
> john r pierce                            N 37, W 122
> santa cruz ca                         mid-left coast
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>