Re: [SPAM] [GENERAL] COPY command & binary format

Well the job is done. The talend component is working ( https://github.com/parisni/talend/tree/master/tPostgresqlOutputBulkAPHP). It allows creating a file (binary or csv) locally, and then use the COPY function with "FROM STDIN" that does not need to push the file on a remote database server. I h

Re: [SPAM] [GENERAL] COPY command & binary format

On Tue, May 10, 2016 at 03:00:55PM +0200, Nicolas Paris wrote: > > The way I want is : > > csv -> binary -> postgresql > > > > Is this just to be quicker or are you going to add some business logic > > while converting CSV data? > > As you mentioned ETL, I assume the second, as I don't think that >

Re: [SPAM] [GENERAL] COPY command & binary format

On Tue, May 10, 2016 at 01:38:12PM +0200, Nicolas Paris wrote: > The way I want is : > csv -> binary -> postgresql > > And if possible, transforming csv to binary throught java​. > > Use case is ETL process. Not sure what the point would be tbh if the data is already in CSV. You might aswell sub

Re: [SPAM] [GENERAL] COPY command & binary format

2016-05-10 14:47 GMT+02:00 Moreno Andreo : > Il 10/05/2016 13:38, Nicolas Paris ha scritto: > > 2016-05-10 13:04 GMT+02:00 Moreno Andreo : > >> Il 10/05/2016 12:56, Nicolas Paris ha scritto: >> >> Hello, >> >> What is the way to build a binary format (instead of a csv) ? Is there >> specification

Re: [SPAM] [GENERAL] COPY command & binary format

Il 10/05/2016 13:38, Nicolas Paris ha scritto: 2016-05-10 13:04 GMT+02:00 Moreno Andreo : Il 10/05/2016 12:56, Nicolas Paris ha scritto:

Re: [GENERAL] COPY command & binary format

Le 10/05/2016 à 12:56, Nicolas Paris a écrit : Hello, What is the way to build a binary format (instead of a csv) ? Is there specification for this file ? http://www.postgresql.org/docs/9.5/static/sql-copy.html Could I create such format from java ? I guess this would be far faster, and ma

Re: [SPAM] [GENERAL] COPY command & binary format

2016-05-10 13:04 GMT+02:00 Moreno Andreo : > Il 10/05/2016 12:56, Nicolas Paris ha scritto: > > Hello, > > What is the way to build a binary format (instead of a csv) ? Is there > specification for this file ? > http://www.postgresql.org/docs/9.5/static/sql-copy.html > > I always create binary fil

Re: [GENERAL] COPY command & binary format

On Tue, May 10, 2016 at 4:36 PM Sameer Kumar wrote: > On Tue, May 10, 2016 at 4:26 PM Nicolas Paris wrote: > >> Hello, >> >> What is the way to build a binary format (instead of a csv) ? Is there >> specification for this file ? >> http://www.postgresql.org/docs/9.5/static/sql-copy.html >> > >>

Re: [GENERAL] COPY command & binary format

On Tue, May 10, 2016 at 4:26 PM Nicolas Paris wrote: > Hello, > > What is the way to build a binary format (instead of a csv) ? Is there > specification for this file ? > http://www.postgresql.org/docs/9.5/static/sql-copy.html > > > Could I create such format from java ? > You can use COPY JDBC

Re: [SPAM] [GENERAL] COPY command & binary format

Il 10/05/2016 12:56, Nicolas Paris ha scritto: Hello, What is the way to build a binary format (instead of a csv) ? Is there specification for this file ? http://www.postgresql.org/docs/9.5/static/sq

[GENERAL] COPY command & binary format

Hello, What is the way to build a binary format (instead of a csv) ? Is there specification for this file ? http://www.postgresql.org/docs/9.5/static/sql-copy.html Could I create such format from java ? I guess this would be far faster, and maybe safer than CSVs Thanks by advance,

Re: [GENERAL] COPY command file name encoding issue (UTF8/WIN1252)

Maybe a new option could be added to let caller specifies the file name encoding, it may know it because he create the source/destination file. I tried to give him a WIN1252 text by doing COPY "test" TO convert_from(convert_to('C:/tmp/é.bin','UTF8'),'WIN1252') WITH BINARY but this call is not al

Re: [GENERAL] COPY command file name encoding issue (UTF8/WIN1252)

Pujol Mathieu wrote: > I have a problem using COPY command with a file name containing non > ASCII characters. > I use Postgres 9.3.5 x64 on a Windows 7. > OS local encoding is WIN1252. > My database is encoded in UTF8. > I initiate client connection with libpq, connection encoding is set to UTF8.

[GENERAL] COPY command file name encoding issue (UTF8/WIN1252)

Hi, I have a problem using COPY command with a file name containing non ASCII characters. I use Postgres 9.3.5 x64 on a Windows 7. OS local encoding is WIN1252. My database is encoded in UTF8. I initiate client connection with libpq, connection encoding is set to UTF8. I build properly my file

Re: [GENERAL] Copy command to handle view for my export requirement

> > > You are not using psql. "\copy" is a psql command. I don't think it's > supported by PgAdmin III, though I could be wrong. > > Right, '\copy' is not supported in PgAdmin III. --Raghav

Re: [GENERAL] Copy command to handle view for my export requirement

On 11/08/2011 7:56 PM, Siva Palanisamy wrote: > FYI, I am using PostgreSQL 8.1.4. Argh, ogod why?!?!?! That version is *totally* unsupported on Windows. Not only that, but you're running an ancient point-release - you are missing *19* patch releases worth of bug fixes. The latest point-releas

Re: [GENERAL] Copy command to handle view for my export requirement

> > COMMAND: copy (select * from employee) to 'C:/emp.csv' > ERROR: could not open file "C:/emp.csv" for writing: Permission denied > ** Error ** > ERROR: could not open file "C:/emp.csv" for writing: Permission denied > SQL state: 42501 > > COMMAND: \copy (select * from employee)

Re: [GENERAL] Copy command to handle view for my export requirement

ounts of both Windows Login and PostgreSQL. Thanks and Regards, Siva. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Boszormenyi Zoltan Sent: Thursday, August 11, 2011 5:11 PM To: pgsql-general@postgresql.org Subject: Re: [GEN

Re: [GENERAL] Copy command to handle view for my export requirement

-- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Siva Palanisamy > Sent: Thursday, August 11, 2011 4:48 PM > To: Andreas Kretschmer; pgsql-general@postgresql.org > Subject: Re: [GENERAL] Copy command to handle view for my export r

Re: [GENERAL] Copy command to handle view for my export requirement

Regards, > Siva. > > > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Siva Palanisamy > Sent: Thursday, August 11, 2011 4:48 PM > To: Andreas Kretschmer; pgsql-general@postgresql.org > Subject: Re: [G

Re: [GENERAL] Copy command to handle view for my export requirement

@postgresql.org Subject: Re: [GENERAL] Copy command to handle view for my export requirement Hi Andreas, I tried the command as below. It failed. Please correct me. \copy (select * from view1) to '/sample.csv' delimiters ',' csv header; ERROR: \copy: parse error at "se

Re: [GENERAL] Copy command to handle view for my export requirement

@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andreas Kretschmer Sent: Thursday, August 11, 2011 2:23 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Copy command to handle view for my export requirement Siva Palanisamy wrote: > Hi All, > > > >

Re: [GENERAL] Copy command to handle view for my export requirement

Siva Palanisamy wrote: > Hi All, > > > > I understand that copy and \copy commands in PostgreSQL work only for tables. > I > want it to export the data from varies tables. Instead, I can create a view > for > the list of tables. Can the copy or \copy commands be utilized to operate on > vie

[GENERAL] Copy command to handle view for my export requirement

Hi All, I understand that copy and \copy commands in PostgreSQL work only for tables. I want it to export the data from varies tables. Instead, I can create a view for the list of tables. Can the copy or \copy commands be utilized to operate on views directly? Please let me know on this. Thank

Re: [GENERAL] COPY command documentation

happily using COPY, > Oisin > > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- View this message in context: http://postgresql.1045698.n5.nabble.com/GENERAL-COPY-command-documentation-tp1858906p

Re: [GENERAL] \copy command: how to define a tab character as the delimiter

On 03/09/2010 10:09 AM, Thomas Kellerer wrote: Raymond O'Donnell wrote on 09.03.2010 18:39: This is Postgres you're talking about - of course it's that easy! :-) :) The main reason I asked, was that the manual actually claims that '\t' can be used ("The following special backslash sequences a

Re: [GENERAL] \copy command: how to define a tab character as the delimiter

Thomas Kellerer writes: > The main reason I asked, was that the manual actually claims that '\t' can be > used ("The following special backslash sequences are recognized by COPY FROM") \t is recognized in the copy data, not in the command's parameters. regards, tom lane

Re: [GENERAL] \copy command: how to define a tab character as the delimiter

Raymond O'Donnell wrote on 09.03.2010 18:39: This is Postgres you're talking about - of course it's that easy! :-) :) The main reason I asked, was that the manual actually claims that '\t' can be used ("The following special backslash sequences are recognized by COPY FROM") As this is part o

Re: [GENERAL] \copy command: how to define a tab character as the delimiter

On 09/03/2010 17:30, Thomas Kellerer wrote: > Tom Lane wrote on 09.03.2010 18:21: >> Thomas Kellerer writes: >>> \copy foo (foo, bar) from foobar.txt delimiter as '\t' csv header >> >>> So how can I specify a tab character if I also need to specify that >>> my file has a header line? >> >> Type an

Re: [GENERAL] \copy command: how to define a tab character as the delimiter

Tom Lane wrote on 09.03.2010 18:21: Thomas Kellerer writes: \copy foo (foo, bar) from foobar.txt delimiter as '\t' csv header So how can I specify a tab character if I also need to specify that my file has a header line? Type an actual tab. Blush That easy? Thanks Thomas -- Se

Re: [GENERAL] \copy command: how to define a tab character as the delimiter

Thomas Kellerer writes: > \copy foo (foo, bar) from foobar.txt delimiter as '\t' csv header > So how can I specify a tab character if I also need to specify that my file > has a header line? Type an actual tab. regards, tom lane -- Sent via pgsql-general mailing list

[GENERAL] \copy command: how to define a tab character as the delimiter

Hi, I tried to import a text file using the \copy command in psql using the following: \copy foo (foo, bar) from foobar.txt delimiter as '\t' csv header but that gives me an error: ERROR: COPY delimiter must be a single one-byte character So how can I specify a tab character if I also need

Re: [GENERAL] COPY command character set

Peter Headland wrote: > In respect of Bruce's proposed changes, I prefer the original wording > (for the same reasons as Tom), but with the addition of the mention of > the server - "... read from or written to a file directly by the > server". OK, done with the attached patch. -- Bruce Momjia

Re: [GENERAL] COPY command character set

, 2010 22:01 To: Tom Lane Cc: Peter Headland; Adrian Klaver; pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set Tom Lane wrote: > Bruce Momjian writes: > > I have updated the documentation to be more direct about COPY encoding > > behavior. Patch attached

Re: [GENERAL] COPY command character set

Tom Lane wrote: > Bruce Momjian writes: > > I have updated the documentation to be more direct about COPY encoding > > behavior. Patch attached and applied. > > Uh, why exactly do you find that better? "Processes data" seems a lot > vaguer to me than the previous wording. I certainly don't thi

Re: [GENERAL] COPY command character set

Bruce Momjian writes: > I have updated the documentation to be more direct about COPY encoding > behavior. Patch attached and applied. Uh, why exactly do you find that better? "Processes data" seems a lot vaguer to me than the previous wording. I certainly don't think that this does much to ad

Re: [GENERAL] COPY command character set

ber 10, 2009 11:06 > To: Peter Headland > Cc: pgsql-general@postgresql.org; Tom Lane > Subject: Re: [GENERAL] COPY command character set > > > - "Peter Headland" wrote: > > > > The COPY command reference page saith > > > > > >I

Re: [GENERAL] COPY command character set

Peter Headland wrote: > As a general comment, I18N/L10N is a hairy enough topic that it merits > its own heading in any commands where it is an issue. I agree, this seems a good idea because people is often confused by this. -- Alvaro Herrerahttp://www.CommandPro

Re: [GENERAL] COPY command character set

2009 11:06 To: Peter Headland Cc: pgsql-general@postgresql.org; Tom Lane Subject: Re: [GENERAL] COPY command character set - "Peter Headland" wrote: > > The COPY command reference page saith > > > >Input data is interpreted according to the current client > e

Re: [GENERAL] COPY command character set

ontributor... -- Peter Headland Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, September 10, 2009 11:13 To: Peter Headland Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set "Peter Headla

Re: [GENERAL] COPY command character set

"Peter Headland" writes: > How about my suggestion to add a means (extend COPY syntax) to specify > encoding explicitly and handle UTF lead bytes - would that be of > interest? There are no lead bytes in UTF-8, and we make no pretense of handling UTF-16, so I don't think we'd be interested in som

Re: [GENERAL] COPY command character set

- "Peter Headland" wrote: > > The COPY command reference page saith > > > >Input data is interpreted according to the current client > encoding, > >and output data is encoded in the the current client encoding, > even > >if the data does not pass through the client but is read fr

Re: [GENERAL] COPY command character set

yntax) to specify encoding explicitly and handle UTF lead bytes - would that be of interest? -- Peter Headland Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, September 10, 2009 10:38 To: Peter Headland Cc: pgsql-general@postgresql

Re: [GENERAL] COPY command character set

"Peter Headland" writes: >> set client_encoding = 'utf8'; >> copy from stdin/to stdout; > What if I want to do this on the server side (because it's much, much > faster)? Does COPY use the default encoding of the database? If not, > what? > If this is a restrictive as it appears, and there are n

Re: [GENERAL] COPY command character set

and Architect Actuate Corporation -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, September 09, 2009 19:14 To: Peter Headland Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] COPY command character set "Peter Headland" writes: > The document

Re: [GENERAL] COPY command character set

"Peter Headland" writes: > The documentation of the COPY command does not state what character > set(s) are recognized or written. I need to import and export UTF-8 > data; how can I do that? set client_encoding = 'utf8'; copy from stdin/to stdout; regards, tom lane --

[GENERAL] COPY command character set

The documentation of the COPY command does not state what character set(s) are recognized or written. I need to import and export UTF-8 data; how can I do that? -- Peter Headland Architect Actuate Corporation

Re: [GENERAL] \copy command error

On Mon, Aug 03, 2009 at 01:18:06PM -0500, erobles wrote: > Maybe the error is \c because '\c' is used to connect to another > database. > try the same line without '\' only : > copy gisp from d:/projects/gisp/gisp.csv' delimiters ','; \copy is a special command in psql that does a cop

Re: [GENERAL] \copy command error

On Mon, Aug 03, 2009 at 01:25:56PM -0400, Mark Watson wrote: > Andrew Maracini wrote: > >GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ',' > > If this is the case, exporting the > City/state field wrapped in quotation marks should do the trick You'll want to use the real CSV parser

Re: [GENERAL] \copy command error

Mark Watson wrote: *>De :* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] *De la part de* Andrew Maracini >*Envoyé :* 3 août 2009 11:46 >*À :* pgsql-general@postgresql.org >*Objet :* [GENERAL] \copy command error > >hi, > >I can

Re: [GENERAL] \copy command error

>De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] De la part de Andrew Maracini >Envoyé : 3 août 2009 11:46 >À : pgsql-general@postgresql.org >Objet : [GENERAL] \copy command error > >hi, > >I can't seem to get the \copy command

[GENERAL] \copy command error

hi, I can't seem to get the \copy command to work. Here's my syntax: GISCI# \copy gisp from 'd:/projects/gisp/gisp.csv' delimiter ',' ERROR extra data after last expected column Line 1 It is taking all of the fields and grouping them into one field, the .csv has about 4700 records, here is wh

Re: [GENERAL] COPY command on windows???

Andreas wrote on 17.07.2009 20:06: Hi, I'd like to read a csv file into PG 8.4. COPY relations FROM E'd:\\relations.csv' CSV HEADER; It throws (translated): ERROR: can't open file >>d:\relations.csv<< for reading file or directory not found Try COPY relations FROM 'd:/relations.csv' CSV HEAD

[GENERAL] COPY command on windows???

Hi, I'd like to read a csv file into PG 8.4. COPY relations FROM E'd:\\relations.csv' CSV HEADER; It throws (translated): ERROR: can't open file >>d:\relations.csv<< for reading file or directory not found The PG doc doesn't describe the path-syntax for windows. With google I only find referenc

Re: [GENERAL] COPY command question

On 17/03/2009 15:28, Ivano Luberti wrote: > I'm sorry, you are right that is the problem > I had interpreted that as the file should reside on the same machine > where pgAdmin (or another client) runs , not the server. > > Thank you again You're welcome! That actually cost me a half-hour or so of

Re: [GENERAL] COPY command question

I'm sorry, you are right that is the problem I had interpreted that as the file should reside on the same machine where pgAdmin (or another client) runs , not the server. Thank you again Raymond O'Donnell ha scritto: > On 17/03/2009 15:04, Ivano Luberti wrote: > >> Thanks but it keeps on not

Re: [GENERAL] COPY command question

On 17/03/2009 15:04, Ivano Luberti wrote: > Thanks but it keeps on not finding the file: the warning has disappeared > > > ERROR: could not open file "c:\temp\anagraficaANIDIs.csv" for reading: > No such file or directory You haven't said whether the file is on the same machine as the server -

Re: [GENERAL] COPY command question

Thanks but it keeps on not finding the file: the warning has disappeared ERROR: could not open file "c:\temp\anagraficaANIDIs.csv" for reading: No such file or directory ** Errore ** ERROR: could not open file "c:\temp\anagraficaANIDIs.csv" for reading: No such file or director

Re: [GENERAL] COPY command question

On 17/03/2009 14:45, Ivano Luberti wrote: > Hi all, executing the following command inside pgAdmin on my Windows > Vista (please avoid comment, I pray you) : > > copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV Try putting an 'E' in front of the path, like this: from E

[GENERAL] COPY command question

Hi all, executing the following command inside pgAdmin on my Windows Vista (please avoid comment, I pray you) : copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV I get the following error: WARNING: nonstandard use of \\ in a string literal LINE 1: copy anagrafica_import fro

Re: [GENERAL] COPy command question

ches ? Also does COPY treat timestamp & LOBs data different? Thanks Sharmila --- On Thu, 2/12/09, Scott Marlowe wrote: From: Scott Marlowe Subject: Re: [GENERAL] COPy command question To: sharmi...@yahoo.com Cc: "General postgres mailing list" Date: Thursday, February 12, 2009, 1:3

Re: [GENERAL] COPy command question

On Wed, Feb 11, 2009 at 11:22 AM, SHARMILA JOTHIRAJAH wrote: > Hi, > A question about the Postgresql's COPY command. > > This is the syntax of this command from the manual > > COPY tablename [ ( column [, ...] ) ] > FROM { 'filename' | STDIN } > [ [ WITH ] > . > I want to migr

Re: [GENERAL] COPY command question

Yes should work perfectly as suggested by Sam, chk this for jdbc support: http://kato.iki.fi/sw/db/postgresql/jdbc/copy/ Sam Mason wrote: On Wed, Feb 11, 2009 at 10:22:23AM -0800, Sharmila Jothirajah wrote: I want to migrate my tables from Oracle to Postgres. The COPY FROM command can tak

Re: [GENERAL] COPY command question

On Wed, Feb 11, 2009 at 10:22:23AM -0800, Sharmila Jothirajah wrote: > I want to migrate my tables from Oracle to Postgres. > The COPY FROM command can take input from 'file' or 'STDIN'. > Is it possible for the COPY command to take its input from a > java program(which contains the oracle results

[GENERAL] COPy command question

Hi, A question about the Postgresql's COPY command. This is the syntax of this command from the manual COPY tablename [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] . I want to migrate my tables from Oracle to Postgres. The COPY FROM command can take input fro

Re: [GENERAL] copy command - date

Thanks again guys =) I've managed to use temp table to load the data and create new table/s Now, how do I convert a text field with 'YY/MM/DD' to date field 'DD/MM/YY'? On 13/08/07, Tom Lane <[EMAIL PROTECTED]> wrote: > Paul Lambert <[EMAIL PROTECTED]> writes: > >> novice wrote: > >>> db5=> \copy

Re: [GENERAL] Copy command and duplicate items (Support Replace?)

On Tue, Aug 14, 2007 at 10:50:33AM +0800, Ow Mun Heng wrote: > Hi, > > Writing a script to pull data from SQL server into a flat-file (or just > piped in directly to PG using Perl DBI) > > Just wondering if the copy command is able to do a replace if there are > existing data in the Db already. (

[GENERAL] Copy command and duplicate items (Support Replace?)

Hi, Writing a script to pull data from SQL server into a flat-file (or just piped in directly to PG using Perl DBI) Just wondering if the copy command is able to do a replace if there are existing data in the Db already. (This is usually in the case of updates to specific rows and there be a time

Re: [GENERAL] copy command - date

On 8/12/07, novice <[EMAIL PROTECTED]> wrote: > I resolved it by doing this - is there another more efficient method? > And yes, the text file I am working with doesn't have any TABs > > 5162 OK SM 06/12/04 06:12 > > substr("data", 30, 2)||'-'||substr("data", 27, > 2)||'-20'||substr("da

Re: [GENERAL] copy command - date

novice schrieb: I resolved it by doing this - is there another more efficient method? And yes, the text file I am working with doesn't have any TABs 5162 OK SM 06/12/04 06:12 substr("data", 30, 2)||'-'||substr("data", 27, 2)||'-20'||substr("data", 24, 2)||substr("data", 32, 6) as in

Re: [GENERAL] copy command - date

I resolved it by doing this - is there another more efficient method? And yes, the text file I am working with doesn't have any TABs 5162 OK SM 06/12/04 06:12 substr("data", 30, 2)||'-'||substr("data", 27, 2)||'-20'||substr("data", 24, 2)||substr("data", 32, 6) as inspection_date On

Re: [GENERAL] copy command - date

Thanks again guys =) I've managed to use temp table to load the data and create new table/s Now, how do I convert a text field with 'YY/MM/DD' to date field 'DD/MM/YY'? On 13/08/07, Tom Lane <[EMAIL PROTECTED]> wrote: > Paul Lambert <[EMAIL PROTECTED]> writes: > >> novice wrote: > >>> db5=> \copy

Re: [GENERAL] copy command - date

Paul Lambert <[EMAIL PROTECTED]> writes: >> novice wrote: >>> db5=> \copy maintenance FROM test.txt > I don't think copy allows you to leave columns out of your input file - > even if they belong to a sequence. Well, it does, but you have to specify which ones are being provided, eg \copy tab(c

Re: [GENERAL] copy command - date

Thank you! That was exactly what I was looking for =) On 13/08/07, Paul Lambert <[EMAIL PROTECTED]> wrote: > Paul Lambert wrote: > > novice wrote: > >> I'm using pg version 8.2.4. What is the best method to load this data? > >> I have just a little over 55,000 entries. > >> > >> db5=> \copy mai

Re: [GENERAL] copy command - date

Paul Lambert wrote: novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5=> \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: "3665 OK SM 07/07/13 06:09" CONTEXT: COPY maintenance

Re: [GENERAL] copy command - date

On Aug 12, 2007, at 20:49 , novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5=> \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: "3665 OK SM 07/07/13 06:09" CONTEXT: COPY

Re: [GENERAL] copy command - date

novice wrote: I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5=> \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: "3665 OK SM 07/07/13 06:09" CONTEXT: COPY maintenance, line 1, column main

Re: [GENERAL] copy command - date

novice <[EMAIL PROTECTED]> writes: > db5=> \copy maintenance FROM test.txt > ERROR: invalid input syntax for integer: "3665 OK SM > 07/07/13 06:09" > CONTEXT: COPY maintenance, line 1, column maintenance_id: "3665 OK > SM 07/07/13 06:09" It looks to me like your problem i

Re: [GENERAL] copy command - date

I'm using pg version 8.2.4. What is the best method to load this data? I have just a little over 55,000 entries. db5=> \copy maintenance FROM test.txt ERROR: invalid input syntax for integer: "3665 OK SM 07/07/13 06:09" CONTEXT: COPY maintenance, line 1, column maintenance_id: "366

Re: [GENERAL] copy command - date

novice <[EMAIL PROTECTED]> writes: > I'm having trouble loading the date field. Should I convert it first > or should I be using a text processor before loading the data in? > 3665 OK SM 07/07/13 06:09 > 5162 OK SM 07/02/12 06:10 > 3665 OK SM 07/06/19 06:10 Wh

[GENERAL] copy command - date

What is the best method to load the following? I'm having trouble loading the date field. Should I convert it first or should I be using a text processor before loading the data in? 3665 OK SM 07/07/13 06:09 5162 OK SM 07/02/12 06:10 3665 OK SM 07/06/19 06:10

Re: [GENERAL] COPY Command and a non superuser user?

Warren wrote: How do I get a non superuser user to be able to run the COPY command? You can copy to / from the stdin. non-superusers cannot run copy to / from a file, since the copy to / from a file does so with the access authority of the postgres user and could be used to do "bad things" T

[GENERAL] COPY Command and a non superuser user?

How do I get a non superuser user to be able to run the COPY command? -- Thanks, Warren ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do

Re: [GENERAL] COPY command details

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/29/07 23:56, Gerald Timothy G Quimpo wrote: [snip] > > How do people take consistent backups of very large > databases on Linux/FreeBSD? I'm aware of PITR, but > might not be able to set aside a box with enough > drives for it. LVM Snapshot?

Re: [GENERAL] COPY command details

On Thu, 2007-03-29 at 21:30 -0700, Benjamin Arai wrote: > Rebuilding an index can't be the PostgreSQL solution for all > cases. I am dealing with databases in the hundreds of gigs > range and I am adding about 10gigs of data a week. At > some point its going to take longer than a week to rebuil

Re: [GENERAL] COPY command details

On Thu, 2007-03-29 at 22:15 -0700, Benjamin Arai wrote: > I have one system which I have used partitioning. For this particular > case I have tons of data over about (50 years). What I did is wrote > small loader that breaks data in tables based on date, so I have tables > like abc_2000, abc_2

Re: [GENERAL] COPY command details

On Thu, 2007-03-29 at 21:30 -0700, Benjamin Arai wrote: > Rebuilding an index can't be the PostgreSQL solution for all > cases. I am dealing with databases in the hundreds of gigs > range and I am adding about 10gigs of data a week. At > some point its going to take longer than a week to rebuil

Re: [GENERAL] COPY command details

I have one system which I have used partitioning. For this particular case I have tons of data over about (50 years). What I did is wrote small loader that breaks data in tables based on date, so I have tables like abc_2000, abc_2001 etc. The loading script is only a couple hundred lines of

Re: [GENERAL] COPY command details

I agree, this is true if I cannot defer index updates. But if it is possible to defer index updates until the end then I should be able to achieve some sort of speedup. Rebuilding an index can't be the PostgreSQL solution for all cases. I am dealing with databases in the hundreds of gigs rang

Re: [GENERAL] COPY command details

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/29/07 18:35, Tom Lane wrote: > Benjamin Arai <[EMAIL PROTECTED]> writes: >> I would prefer not to drop the index because the database is several >> hundred gigs. I would prefer to incrementally add to the index. > > This may well be false econ

Re: [GENERAL] COPY command details

Benjamin Arai <[EMAIL PROTECTED]> writes: > I would prefer not to drop the index because the database is several > hundred gigs. I would prefer to incrementally add to the index. This may well be false economy. I don't have numbers at hand, but a full rebuild can be substantially faster than ad

Re: [GENERAL] COPY command details

Benjamin Arai wrote: > I would prefer not to drop the index because the database is several > hundred gigs. I would prefer to incrementally add to the index. I know of now way to do that in a batch, unless you go with partitioned tables. -

Re: [GENERAL] COPY command details

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/29/07 14:51, Benjamin Arai wrote: > I would prefer not to drop the index because the database is several > hundred gigs. I would prefer to incrementally add to the index. Some RDBMSs (well, one that I know of) has the ability to defer index upd

Re: [GENERAL] COPY command details

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/29/07 14:41, Bruce Momjian wrote: > Benjamin Arai wrote: >> So, is there a way to defer the index updating until a later period >> of time. More specifically, I would like to do several COPIES to a >> running database, then afterward force a

Re: [GENERAL] COPY command details

I would prefer not to drop the index because the database is several hundred gigs. I would prefer to incrementally add to the index. Benjamin Bruce Momjian wrote: Benjamin Arai wrote: So, is there a way to defer the index updating until a later period of time. More specifically, I would

Re: [GENERAL] COPY command details

Benjamin Arai wrote: > So, is there a way to defer the index updating until a later period > of time. More specifically, I would like to do several COPIES to a > running database, then afterward force a update on the index via a > vacuum or something similar. Sure, drop the index, do the CO

Re: [GENERAL] COPY command details

So, is there a way to defer the index updating until a later period of time. More specifically, I would like to do several COPIES to a running database, then afterward force a update on the index via a vacuum or something similar. Benjamin On Mar 29, 2007, at 1:03 AM, A. Kretschmer wrote:

Re: [GENERAL] COPY command details

So, is there a way to defer the index updating until a later period of time. More specifically, I would like to do several COPIES to a running database, then afterward force a update on the index via a vacuum or something similar. Benjamin On Mar 29, 2007, at 1:03 AM, A. Kretschmer wrote:

Re: [GENERAL] COPY command details

am Thu, dem 29.03.2007, um 10:02:49 -0700 mailte Benjamin Arai folgendes: > So, is there a way to defer the index updating until a later period > of time. More specifically, I would like to do several COPIES to a > running database, then afterward force a update on the index via a > vacuum

Re: [GENERAL] COPY command details

"A. Kretschmer" <[EMAIL PROTECTED]> writes: > am Thu, dem 29.03.2007, um 0:13:09 -0700 mailte Benjamin Arai folgendes: >> If I have a PostgreSQL table with records and logical indexes already >> created, if I use COPY to load additional data, does the COPY update >> the indexes during, after,

  1   2   >