agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedquery option in COPY postgres
5+ messages / 3 participants
[nested] [flat]
* query option in COPY postgres
@ 2015-11-06 20:22 masyaf <kavtaradze.s@gmail.com>
2015-11-06 22:02 ` Re: query option in COPY postgres David G. Johnston <david.g.johnston@gmail.com>
2015-11-06 22:40 ` Re: query option in COPY postgres Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 5+ messages in thread
From: masyaf @ 2015-11-06 20:22 UTC (permalink / raw)
To: pgsql-sql
According to COPY documentation , I can use query option when exporting data.
Can I use query VALUES when loading data into tables from txt file? If I
have 2 columns and I want to insert record manually in the first
column(SentenceID) and insert data in the second column(Sentence) from text
file. Is it something like:
copy foo1(Sentence) | (VALUES (2339)) from '/path/to/sentence.txt' with
delimiter '*';
How to use exactly options in COPY command, it has {} | [] symbols in
documentation.
--
View this message in context: http://postgresql.nabble.com/query-option-in-COPY-postgres-tp5873015.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query option in COPY postgres
2015-11-06 20:22 query option in COPY postgres masyaf <kavtaradze.s@gmail.com>
@ 2015-11-06 22:02 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2015-11-06 22:02 UTC (permalink / raw)
To: masyaf <kavtaradze.s@gmail.com>; +Cc: pgsql-sql
On Fri, Nov 6, 2015 at 1:22 PM, masyaf <kavtaradze.s@gmail.com> wrote:
> According to COPY documentation , I can use query option when exporting
> data.
> Can I use query VALUES when loading data into tables from txt file? If I
> have 2 columns and I want to insert record manually in the first
> column(SentenceID) and insert data in the second column(Sentence) from text
> file. Is it something like:
>
> copy foo1(Sentence) | (VALUES (2339)) from '/path/to/sentence.txt' with
> delimiter '*';
>
Directly? No. the entirity of the data being imported must exist within
the specified input file. If the file contains fewer columns than the
target table, or in a differing order, you can specify column names. Any
columns not specified will take on their default value. If the column is a
"serial" column is will get its default value from the corresponding
sequence.
> How to use exactly options in COPY command, it has {} | [] symbols in
> documentation.
>
{} - means required; choose one of the listed options
[] - mean options; choose on of the listed options (possibly only one)
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query option in COPY postgres
2015-11-06 20:22 query option in COPY postgres masyaf <kavtaradze.s@gmail.com>
@ 2015-11-06 22:40 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-11-06 22:54 ` Re: query option in COPY postgres masyaf <kavtaradze.s@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2015-11-06 22:40 UTC (permalink / raw)
To: masyaf <kavtaradze.s@gmail.com>; pgsql-sql
On 11/06/2015 12:22 PM, masyaf wrote:
> According to COPY documentation , I can use query option when exporting data.
> Can I use query VALUES when loading data into tables from txt file? If I
> have 2 columns and I want to insert record manually in the first
> column(SentenceID) and insert data in the second column(Sentence) from text
> file. Is it something like:
>
> copy foo1(Sentence) | (VALUES (2339)) from '/path/to/sentence.txt' with
> delimiter '*';
In addition to what David posted, in Postgres 9.3+ you have option of
doing COPY FROM a program instead of a file. This opens the possibility
of using an external program to get your data into the form and order
you need and then have COPY pull from that.
>
> How to use exactly options in COPY command, it has {} | [] symbols in
> documentation.
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/query-option-in-COPY-postgres-tp5873015.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query option in COPY postgres
2015-11-06 20:22 query option in COPY postgres masyaf <kavtaradze.s@gmail.com>
2015-11-06 22:40 ` Re: query option in COPY postgres Adrian Klaver <adrian.klaver@aklaver.com>
@ 2015-11-06 22:54 ` masyaf <kavtaradze.s@gmail.com>
2015-11-09 17:13 ` Re: query option in COPY postgres Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 5+ messages in thread
From: masyaf @ 2015-11-06 22:54 UTC (permalink / raw)
To: pgsql-sql
Can you give any details about that?
--
View this message in context: http://postgresql.nabble.com/query-option-in-COPY-postgres-tp5873015p5873038.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query option in COPY postgres
2015-11-06 20:22 query option in COPY postgres masyaf <kavtaradze.s@gmail.com>
2015-11-06 22:40 ` Re: query option in COPY postgres Adrian Klaver <adrian.klaver@aklaver.com>
2015-11-06 22:54 ` Re: query option in COPY postgres masyaf <kavtaradze.s@gmail.com>
@ 2015-11-09 17:13 ` Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 5+ messages in thread
From: Adrian Klaver @ 2015-11-09 17:13 UTC (permalink / raw)
To: masyaf <kavtaradze.s@gmail.com>; pgsql-sql
On 11/06/2015 02:54 PM, masyaf wrote:
> Can you give any details about that?
>
I am assuming you are referring to COPY FROM a program.
Take a look at the docs:
http://www.postgresql.org/docs/9.4/interactive/sql-copy.html
"When PROGRAM is specified, the server executes the given command and
reads from the standard output of the program, or writes to the standard
input of the program. The command must be specified from the viewpoint
of the server, and be executable by the PostgreSQL user. When STDIN or
STDOUT is specified, data is transmitted via the connection between the
client and the server."
In my case I would write the program in Python and output CSV. Every
time I do that though, I just go ahead and use the copy_from() method of
the psycopg2 cursor and never get to the above.
>
>
> --
> View this message in context: http://postgresql.nabble.com/query-option-in-COPY-postgres-tp5873015p5873038.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-11-09 17:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-06 20:22 query option in COPY postgres masyaf <kavtaradze.s@gmail.com>
2015-11-06 22:02 ` David G. Johnston <david.g.johnston@gmail.com>
2015-11-06 22:40 ` Adrian Klaver <adrian.klaver@aklaver.com>
2015-11-06 22:54 ` masyaf <kavtaradze.s@gmail.com>
2015-11-09 17:13 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox