Thread: query option in COPY postgres
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.
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.
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
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.
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