FAQ
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

Search Discussions

  • John R Pierce at Jun 21, 2011 at 8:10 pm

    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
  • Scott Frankel at Jun 22, 2011 at 12:59 am
    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
  • Michael Glaesemann at Jun 21, 2011 at 8:25 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 21, '11 at 7:50p
activeJun 22, '11 at 12:59a
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase