Re: pipe text to copy statement stdin input

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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>