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

[Nicolas Paris]

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 have made a little comparison test:

column1: character varying
column2: integer
column3: boolean
10 000 000 tuples

Type| Create file time   | Bulk load time   | Total
Time  |   File size
Binary | 11137 milliseconds |  21661 milliseconds | 32798 milliseconds |
250 MO
CSV | 23226 milliseconds |  22192 milliseconds |  45418 milliseconds |
179 MO


Binary format is definitely faster and safer
- faster because writing binary is faster than text file. I guess the bulk
load time bottleneck is the network, then this is equivalent for both
format. It is two time faster to load a binary when the file is on the
database server.
- safer thanks to the format (each value is preceded by its lenght) more
robust thant CSV and separators (that can be present in the text).


Code has been based on :

-
https://github.com/uwescience/myria/blob/master/src/edu/washington/escience/myria/PostgresBinaryTupleWriter.java
-
https://github.com/bytefish/PgBulkInsert/tree/master/PgBulkInsert/src/main/de/bytefish/pgbulkinsert/pgsql/handlers

Thanks,

2016-05-10 15:08 GMT+02:00 Cat :

> 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
> > > converting CSV to binary and then loading it to PostgreSQL will be more
> > > convenient than loading directly from CSV... as quicker as it can be,
> you
> > > have anyway to load data from CSV.
> > >
> > ​
> > Right, ETL process means huge business logic.
> > get the data (csv or other) -> transform it -> produce a binary -> copy
> > from binary from stdin ​
> >
> > Producing 100GO CSVs, is a waste of time.
>
> Ah. You need to fiddle with the data. Then you need to weigh the pros of
> something agnostic to Postgres's internals to something that needs to be
> aware of them.
>
> You will need to delve into the source code for data types more complex
> than INTEGER, TEXT and BYTEA (which was the majority of my data when I
> was just looking into it).
>
> --
>   "A search of his car uncovered pornography, a homemade sex aid, women's
>   stockings and a Jack Russell terrier."
> -
> http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480
>

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

[Cat]

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
> > converting CSV to binary and then loading it to PostgreSQL will be more
> > convenient than loading directly from CSV... as quicker as it can be, you
> > have anyway to load data from CSV.
> >
> ​
> Right, ETL process means huge business logic.
> get the data (csv or other) -> transform it -> produce a binary -> copy
> from binary from stdin ​
> 
> Producing 100GO CSVs, is a waste of time.

Ah. You need to fiddle with the data. Then you need to weigh the pros of
something agnostic to Postgres's internals to something that needs to be
aware of them.

You will need to delve into the source code for data types more complex
than INTEGER, TEXT and BYTEA (which was the majority of my data when I
was just looking into it).

-- 
  "A search of his car uncovered pornography, a homemade sex aid, women's 
  stockings and a Jack Russell terrier."
- 
http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Cat]

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 submit the CSV to postgres and let it deal with it.
It'll probably be faster. It'll also be more portable. The BINARY
format is what Postgres uses internally (more or less). I had to
look at the source code to figure out how to insert a timestamp
(FYI: Postgres stores timestamps as epoch based off the year 2000 not
1970 amongst other fun things).


-- 
  "A search of his car uncovered pornography, a homemade sex aid, women's 
  stockings and a Jack Russell terrier."
- 
http://www.dailytelegraph.com.au/news/wacky/indeed/story-e6frev20-118083480


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Nicolas Paris]

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 for this file ?
>> http://www.postgresql.org/docs/9.5/static/sql-copy.html
>>
>> I always create binary files with
>> COPY table TO 'path/to/file' WITH BINARY
>>
>>
> ​ Fine, this works in this way :
> postgresql -> binary
> binary -> postgresql
>
> 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
> converting CSV to binary and then loading it to PostgreSQL will be more
> convenient than loading directly from CSV... as quicker as it can be, you
> have anyway to load data from CSV.
>
​
Right, ETL process means huge business logic.
get the data (csv or other) -> transform it -> produce a binary -> copy
from binary from stdin ​

Producing 100GO CSVs, is a waste of time.



> Binary file format is briefly described in the last part of the doc you
> linked, under "Binary format", and there's also reference to source files.
>
>
> And if possible, transforming csv to binary throught java​.
>
> This is beyond my knowledge, ATM. I'm just starting with Java and JDBC is
> still in the TODO list, sorry... :-)
>
> Cheers
> Moreno.-
>

​Documentation explains a bit. Moreover, I have found a detailled answer
here :
​
http://stackoverflow.com/questions/14242117/java-library-to-write-binary-format-for-postgres-copy
​

My ultimate goal is to encapsulate it in a Talend component. (talend is an
open-source java based ETL software).

Thanks, I ll keep you aware.

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

[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 for this file ?
http://www.postgresql.org/docs/9.5/static/sql-copy.html
  

  
 I always create binary files with
COPY table TO 'path/to/file' WITH BINARY

  

 
  
  ​
Fine, this works in this way : 
postgresql -> binary
  
  binary
-> postgresql

  
  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
converting CSV to binary and then loading it to PostgreSQL will be
more convenient than loading directly from CSV... as quicker as it
can be, you have anyway to load data from CSV.

Binary file format is briefly described in the last part of the doc
you linked, under "Binary format", and there's also reference to
source files.

  

  
  
  And
if possible, transforming csv to binary throught java​.
  

  

This is beyond my knowledge, ATM. I'm just starting with Java and
JDBC is still in the TODO list, sorry... :-)

Cheers
Moreno.-
  

Re: [GENERAL] COPY command & binary format

[Pujol Mathieu]

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 maybe safer than CSVs

Thanks by advance,

Hi
Making a driver that do what you want is not difficult. You will achieve 
better performances than than loading data from CSV, and you also will 
have better precision for floating values (there is no text conversion).
In the link you provide there is a description of the file format in 
section Binary Format.

Mathieu Pujol

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

[Nicolas Paris]

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 files with
> COPY table TO 'path/to/file' WITH BINARY
>
>
​Fine, this works in this way :
postgresql -> binary
binary -> postgresql

The way I want is :
csv -> binary -> postgresql

And if possible, transforming csv to binary throught java​.

Use case is ETL process.

Re: [GENERAL] COPY command & binary format

[Sameer Kumar]

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
>>
>
>>
>> Could I create such format from java ?
>>
>
> You can use COPY JDBC API to copy to STDOUT and then compress it before
> you use usual Java file operations to write it to a file. You will have to
> follow the reverse process while reading from this file and LOADING to a
> table.
>
> But why would you want to do that?
>
>
>>
>> I guess this would be far faster, and maybe safer than CSVs
>>
>
> I don't think assumption is right. COPY is not meant for backup, it is for
> LOAD and UN-LOAD.
>
> What you probably need is pg_dump with -Fc format.
> http://www.postgresql.org/docs/current/static/app-pgdump.html
>
>

Like someone else suggested upthread you can use Binary format in COPY
command (default is text)


>
>> Thanks by advance,
>>
> --
> --
> Best Regards
> Sameer Kumar | DB Solution Architect
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Re: [GENERAL] COPY command & binary format

[Sameer Kumar]

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 API to copy to STDOUT and then compress it before you
use usual Java file operations to write it to a file. You will have to
follow the reverse process while reading from this file and LOADING to a
table.

But why would you want to do that?


>
> I guess this would be far faster, and maybe safer than CSVs
>

I don't think assumption is right. COPY is not meant for backup, it is for
LOAD and UN-LOAD.

What you probably need is pg_dump with -Fc format.
http://www.postgresql.org/docs/current/static/app-pgdump.html


>
> Thanks by advance,
>
-- 
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

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

[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 files with
COPY table TO 'path/to/file' WITH BINARY

Cheers
Moreno.-
  

[GENERAL] COPY command & binary format

[Nicolas Paris]

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)

[Pujol Mathieu]

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 allowed. Sending him a text containing escaped 
WIN1252 hex value fails, because query parser detect invalid UTF8 
sequence (which is logical).

The problem is that I can't find any way to workaround this bug.

Regards

Mathieu Pujol

Le 23/03/2015 11:46, Albe Laurenz a écrit :

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.
I build properly my file path taking care of encoding.

When I run COPY test TO 'C:/tmp/é.bin' WITH BINARY
it creates a file named é.bin which is utf8 name interpreted as local8.
It could be reproduced convert_from(convert_to('é','UTF8'),'WIN1252').
é in UTF8 \303\251
é in WIN1252\351

This command works on a database encoded in WIN1252 (same as OS) .
So it seems that COPY command don't take care of file name encoding.
Is it a bug ? a limitation ?
Thanks for your help

I didn't look at the code, but I'd say that the database encoding is
used for the file name, which is why it works when database encoding
and OS locale are the same.

I guess that it would be possible for PostgreSQL to figure out with what
OS locale the postmaster is running and to convert file names accordingly,
but it's probably not trivial since it is OS dependent.

Yours,
Laurenz Albe





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Albe Laurenz]

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.
 I build properly my file path taking care of encoding.
 
 When I run COPY test TO 'C:/tmp/é.bin' WITH BINARY
 it creates a file named é.bin which is utf8 name interpreted as local8.
 It could be reproduced convert_from(convert_to('é','UTF8'),'WIN1252').
 é in UTF8 \303\251
 é in WIN1252\351
 
 This command works on a database encoded in WIN1252 (same as OS) .
 So it seems that COPY command don't take care of file name encoding.
 Is it a bug ? a limitation ?
 Thanks for your help

I didn't look at the code, but I'd say that the database encoding is
used for the file name, which is why it works when database encoding
and OS locale are the same.

I guess that it would be possible for PostgreSQL to figure out with what
OS locale the postmaster is running and to convert file names accordingly,
but it's probably not trivial since it is OS dependent.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Pujol Mathieu]

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 path taking care of encoding.

When I run COPY test TO 'C:/tmp/é.bin' WITH BINARY
it creates a file named é.bin which is utf8 name interpreted as local8. 
It could be reproduced convert_from(convert_to('é','UTF8'),'WIN1252').

é in UTF8 \303\251
é in WIN1252\351

This command works on a database encoded in WIN1252 (same as OS) .
So it seems that COPY command don't take care of file name encoding.
Is it a bug ? a limitation ?
Thanks for your help

Mathieu PUJOL



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Siva Palanisamy]

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.

Thanks and Regards,
Siva.



::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

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

[Andreas Kretschmer]

Siva Palanisamy siv...@hcl.com 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
 views directly? Please let me know on this.

Sure, you can do that (with recent versions) with:

copy (select * from your_view) to ...


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Siva Palanisamy]

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 select

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@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 siv...@hcl.com 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
 views directly? Please let me know on this.

Sure, you can do that (with recent versions) with:

copy (select * from your_view) to ...


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Siva Palanisamy]

Hi Andreas,

FYI, I am using PostgreSQL 8.1.4. 

Thanks and 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: [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 select

Thanks and Regards,
Siva.


-Original Message-
From: pgsql-general-ow...@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 siv...@hcl.com 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
 views directly? Please let me know on this.

Sure, you can do that (with recent versions) with:

copy (select * from your_view) to ...


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

::DISCLAIMER::
---

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and
attachments please check them for viruses and defect.

---

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Raghavendra]

Nope, you need to be in latest version as Andreas said.
---
 Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/



On Thu, Aug 11, 2011 at 4:51 PM, Siva Palanisamy siv...@hcl.com wrote:

 Hi Andreas,

 FYI, I am using PostgreSQL 8.1.4.

 Thanks and 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: [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 select

 Thanks and Regards,
 Siva.


 -Original Message-
 From: pgsql-general-ow...@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 siv...@hcl.com 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
  views directly? Please let me know on this.

 Sure, you can do that (with recent versions) with:

 copy (select * from your_view) to ...


 Andreas
 --
 Really, I'm not out to destroy Microsoft. That will just be a completely
 unintentional side effect.  (Linus Torvalds)
 If I was god, I would recompile penguin with --enable-fly.   (unknown)
 Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

 ::DISCLAIMER::

 ---

 The contents of this e-mail and any attachment(s) are confidential and
 intended for the named recipient(s) only.
 It shall not attach any liability on the originator or HCL or its
 affiliates. Any views or opinions presented in
 this email are solely those of the author and may not necessarily reflect
 the opinions of HCL or its affiliates.
 Any form of reproduction, dissemination, copying, disclosure, modification,
 distribution and / or publication of
 this message without the prior written consent of the author of this e-mail
 is strictly prohibited. If you have
 received this email in error please delete it and notify the sender
 immediately. Before opening any mail and
 attachments please check them for viruses and defect.


 ---

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

 --
  Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

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

[Boszormenyi Zoltan]

Hi,

COPY (SELECT ...) appeared in 8.2.x so you need to upgrade.

Best regards,
Zoltán Böszörményi

2011-08-11 13:21 keltezéssel, Siva Palanisamy írta:
 Hi Andreas,

 FYI, I am using PostgreSQL 8.1.4. 

 Thanks and 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: [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 select

 Thanks and Regards,
 Siva.


 -Original Message-
 From: pgsql-general-ow...@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 siv...@hcl.com 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
 views directly? Please let me know on this.
 Sure, you can do that (with recent versions) with:

 copy (select * from your_view) to ...


 Andreas
 --
 Really, I'm not out to destroy Microsoft. That will just be a completely
 unintentional side effect.  (Linus Torvalds)
 If I was god, I would recompile penguin with --enable-fly.   (unknown)
 Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

 ::DISCLAIMER::
 ---

 The contents of this e-mail and any attachment(s) are confidential and 
 intended for the named recipient(s) only.
 It shall not attach any liability on the originator or HCL or its affiliates. 
 Any views or opinions presented in
 this email are solely those of the author and may not necessarily reflect the 
 opinions of HCL or its affiliates.
 Any form of reproduction, dissemination, copying, disclosure, modification, 
 distribution and / or publication of
 this message without the prior written consent of the author of this e-mail 
 is strictly prohibited. If you have
 received this email in error please delete it and notify the sender 
 immediately. Before opening any mail and
 attachments please check them for viruses and defect.

 ---



-- 
--
Zoltán Böszörményi
Cybertec Schönig  Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
 http://www.postgresql.at/


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Siva Palanisamy]

Hi,

I have installed Windows version of Postgres 9.0.4 in my windows machine to 
test the new copy command as detailed in the below e-mails. When I run the 
command in SQL Editor, I got permission error. But I am running as an 
administrator. 

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) to 'C:/emp.csv'
ERROR:  syntax error at or near \
LINE 1: \copy (select * from employee) to 'C:/emp.csv'
^
** Error **
ERROR: syntax error at or near \
SQL state: 42601

Please correct me where I am going wrong. FYI, I am running under the 
administrator accounts 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: [GENERAL] Copy command to handle view for my export requirement

Hi,

COPY (SELECT ...) appeared in 8.2.x so you need to upgrade.

Best regards,
Zoltán Böszörményi

2011-08-11 13:21 keltezéssel, Siva Palanisamy írta:
 Hi Andreas,

 FYI, I am using PostgreSQL 8.1.4. 

 Thanks and 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: [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 select

 Thanks and Regards,
 Siva.


 -Original Message-
 From: pgsql-general-ow...@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 siv...@hcl.com 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
 views directly? Please let me know on this.
 Sure, you can do that (with recent versions) with:

 copy (select * from your_view) to ...


 Andreas
 --
 Really, I'm not out to destroy Microsoft. That will just be a completely
 unintentional side effect.  (Linus Torvalds)
 If I was god, I would recompile penguin with --enable-fly.   (unknown)
 Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

 ::DISCLAIMER::
 ---

 The contents of this e-mail and any attachment(s) are confidential and 
 intended for the named recipient(s) only.
 It shall not attach any liability on the originator or HCL or its affiliates. 
 Any views or opinions presented in
 this email are solely those of the author and may not necessarily reflect the 
 opinions of HCL or its affiliates.
 Any form of reproduction, dissemination, copying, disclosure, modification, 
 distribution and / or publication of
 this message without the prior written consent of the author of this e-mail 
 is strictly prohibited. If you have
 received this email in error please delete it and notify the sender 
 immediately. Before opening any mail and
 attachments please check them for viruses and defect.

 ---



-- 
--
Zoltán Böszörményi
Cybertec Schönig  Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
 http://www.postgresql.at/


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Raghavendra]

 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) to 'C:/emp.csv'
 ERROR:  syntax error at or near \
 LINE 1: \copy (select * from employee) to 'C:/emp.csv'
^
 ** Error **
 ERROR: syntax error at or near \
 SQL state: 42601

 Please correct me where I am going wrong. FYI, I am running under the
 administrator accounts of both Windows Login and PostgreSQL.


Two things,
1. you need to have a proper permissions where the .csv file creating.
2. In windows you need to use as below
postgres=#\copy (select * from employee) to 'C:\\emp.sql'

 Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/

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

[Craig Ringer]

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-release is 8.1.23 !


Here is a list of all the fixes you are missing out on:

  http://www.postgresql.org/docs/8.1/static/release.html


I have installed Windows version of Postgres 9.0.4 in my windows machine to 
test the new copy command as detailed in the below e-mails. When I run the 
command in SQL Editor, I got permission error. But I am running as an 
administrator.

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


The COPY command (as distinct from \copy) runs on the server-side so it 
has the permissions of the postgres user. You must save the file 
somewhere the postgres user as write access. Either create a folder 
and give full control to the user postgres, or write the export 
within the existing postgresql data directory.



COMMAND: \copy (select * from employee) to 'C:/emp.csv'
ERROR:  syntax error at or near \
LINE 1: \copy (select * from employee) to 'C:/emp.csv'


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.




--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Raghavendra]

 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 documentation

[Fabio Milillo]

Hi Oisin,
I am right in the condition you described, but nowadays the 8.0
documentation is only available without comments.
I tried the way suggested by Richard Sydney-Smith (*eliminating the spaces
in the path*), but unsuccessfully.
Could you please help me?
thanks, Fabio

*hint from Richard Sydney-Smith*
(
http://postgresql.1045698.n5.nabble.com/Windows-file-path-for-copy-tt1847135.html
):
/Windows XP SP2 with Postgresql 8.0.3
Two commands on fails the other succeeds:
*Fails *:
select import_sharedata('C:\\Documents and
Settings\\Richard\\Desktop\\EzyChart-20050721');
*Succeeds*:
select import_sharedata('C:\\EzyChart-20050721'); /


Oisin Glynn wrote:
 
 I have driven myself to distraction for the last 30 minutes trying to 
 get COPY to work on Windows  XP.  The Unix style c:/afolder/afile 
 instead of c:\afolder\afile was a desperation attempt.
 
 I had tried all sorts of double slashes \\ putting the whole path in 
 quotes basically all sorts of foolishness. [...]
 Oisin
 
 
 P.S.
 I just discovered that the comments from 8.0 had the answer I was 
 looking for but these comments are not in the 8.1 docs. Should the 
 comments be rolled forward as new versions are created? Or if valid 
 comments added to the docs themselves?
 
 http://www.postgresql.org/docs/8.1/interactive/sql-copy.html
 
 http://www.postgresql.org/docs/8.0/interactive/sql-copy.html
 
 Now 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-tp1858906p4590548.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Thomas Kellerer]

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 to specify that my file has 
a header line?

Regards
Thomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

Thomas Kellerer spam_ea...@gmx.net 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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Thomas Kellerer]

Tom Lane wrote on 09.03.2010 18:21:

Thomas Kellererspam_ea...@gmx.net  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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Raymond O'Donnell]

On 09/03/2010 17:30, Thomas Kellerer wrote:
 Tom Lane wrote on 09.03.2010 18:21:
 Thomas Kellererspam_ea...@gmx.net  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?

This is Postgres you're talking about - of course it's that easy! :-)

Ray.

-- 
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Thomas Kellerer]

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 of the description for the COPY command, does this maybe mean 
it is only valid for COPY but not for \copy?
if that is the case, it should be documented somewhere).

Or is this related to the value of standard_conforming_strings?

Thomas


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Tom Lane]

Thomas Kellerer spam_ea...@gmx.net 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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

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 are recognized
by COPY FROM)

As this is part of the description for the COPY command, does this maybe
mean it is only valid for COPY but not for \copy?
if that is the case, it should be documented somewhere).

Or is this related to the value of standard_conforming_strings?

Thomas




From here:
http://www.postgresql.org/docs/8.4/interactive/app-psql.html
The syntax of the command is similar to that of the SQL COPY  command. 
Note that, because of this, special parsing rules apply to the \copy 
command. In particular, the variable substitution rules and backslash 
escapes do not apply. 


--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Peter Headland]

As Tom says, this doesn't really address my original issue, which was
not that I read the material on encoding and misunderstood it, but that
I didn't even see that material because it was mixed in with a bunch of
other notes on all sorts of random subjects.

To address this issue in the documentation at large, I would like to see
every command that has I18N/L10N-related behavior have a separate
sub-head for the explanation of that behavior. That way, anyone who
needs to know about that aspect (which should be everyone), just has to
look for the sub-head to be sure they have found what they need to know.
Whilst I know we can't do that for every single cross-command topic, it
seems to me that I18N/L10N is sufficiently important to users of pg that
it merits this treatment.

FWIW, I think error handling/behavior also merits its own sub-heads
throughout. And there should be links within the pages to sub-heads (cf.
DB2's online doc).

Of course, all of this would be a substantial project.

Note that for the COPY command the I18N/L10N material covers both
DateStyle and encoding.

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.

-- 
Peter Headland
Architect
Actuate Corporation

-Original Message-
From: Bruce Momjian [mailto:br...@momjian.us] 
Sent: Monday, February 22, 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 br...@momjian.us 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 address Peter's original complaint.

I thought the problem was that we said input, then output and then
got to the point about the server, and I thought the reader just stopped
reading that far, so I tried to shorten it so the idea was sooner, and I
mentioned server at the end.  It might not be better, but I tried.

We don't want to highlight the input/output, we want to highlight that
all input and output are controlled by the client encoding.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Bruce Momjian]

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 Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/copy.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v
retrieving revision 1.94
diff -c -c -r1.94 copy.sgml
*** doc/src/sgml/ref/copy.sgml	23 Feb 2010 05:17:33 -	1.94
--- doc/src/sgml/ref/copy.sgml	23 Feb 2010 21:38:07 -
***
*** 1,5 
  !--
! $PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.94 2010/02/23 05:17:33 momjian Exp $
  PostgreSQL documentation
  --
  
--- 1,5 
  !--
! $PostgreSQL: pgsql/doc/src/sgml/ref/copy.sgml,v 1.93 2010/02/17 04:19:39 tgl Exp $
  PostgreSQL documentation
  --
  
***
*** 367,376 
 /para
  
 para
! commandCOPY/command always processes data according to the
! current client encoding, even if the data does not pass through
! the client but is read from or written to a file directly by the
! server.
 /para
  
 para
--- 367,376 
 /para
  
 para
! 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 from or
! written to a file directly by the server.
 /para
  
 para

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Bruce Momjian]

I have updated the documentation to be more direct about COPY encoding
behavior.  Patch attached and applied.

---

Peter Headland wrote:
  Maybe the link might help?
  
  http://www.postgresql.org/docs/8.4/interactive/multibyte.html
 
 That page is too generic; what would be helpful is a section in the doc for 
 each command that is affected by I18N/L10N considerations, that identifies 
 how that specific command behaves.
 
 Now that I have grasped the behavior, I'm more than happy to edit the COPY 
 doc page, if people think that would be helpful/worthwhile.
 
 -- 
 Peter Headland
 Architect
 Actuate Corporation
 
 
 -Original Message-
 From: Adrian Klaver [mailto:akla...@comcast.net] 
 Sent: Thursday, September 10, 2009 11:06
 To: Peter Headland
 Cc: pgsql-general@postgresql.org; Tom Lane
 Subject: Re: [GENERAL] COPY command character set
 
 
 - Peter Headland pheadl...@actuate.com 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 from or
  written to a file.
  
  Rats - I read the manual page twice and that didn't register on my
  feeble consciousness. I suspect that I didn't look beyond the word
  client, since I knew I wasn't interested in client behavior and I
  was
  speed-reading. On the assumption that I am not uniquely stupid, maybe
  we
  could re-phrase this slightly, with a for example, and add a
  heading
  Localization?
  
  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.
  
  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?
  
  -- 
  Peter Headland
  Architect
  Actuate Corporation
  
 
  
  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 from or
  written to a file. 
  
  Seems clear enough to me.
  
  regards, tom lane
 
 Maybe the link might help?
 
 http://www.postgresql.org/docs/8.4/interactive/multibyte.html
 
 
 Adrian Klaver
 akla...@comcast.net
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/copy.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v
retrieving revision 1.93
diff -c -c -r1.93 copy.sgml
*** doc/src/sgml/ref/copy.sgml	17 Feb 2010 04:19:39 -	1.93
--- doc/src/sgml/ref/copy.sgml	23 Feb 2010 05:15:00 -
***
*** 367,376 
 /para
  
 para
! 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 from or
! written to a file.
 /para
  
 para
--- 367,376 
 /para
  
 para
! commandCOPY/command always processes data according to the
! current client encoding, even if the data does not pass through
! the client but is read from or written to a file directly by the
! server.
 /para
  
 para

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Tom Lane]

Bruce Momjian br...@momjian.us 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 address Peter's original complaint.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Bruce Momjian]

Tom Lane wrote:
 Bruce Momjian br...@momjian.us 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 address Peter's original complaint.

I thought the problem was that we said input, then output and then
got to the point about the server, and I thought the reader just stopped
reading that far, so I tried to shorten it so the idea was sooner, and I
mentioned server at the end.  It might not be better, but I tried.

We don't want to highlight the input/output, we want to highlight that
all input and output are controlled by the client encoding.

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com
  PG East:  http://www.enterprisedb.com/community/nav-pg-east-2010.do
  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Peter Headland]

 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 no outstanding
enhancements planned in this area, I might be interested in improving
this command to allow specifying the encoding and to have it do obvious
stuff like recognize UTF lead bytes automatically. At the very least,
the documentation needs some work to explain these subtleties.

-- 
Peter Headland
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 pheadl...@actuate.com 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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Tom Lane]

Peter Headland pheadl...@actuate.com 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 no outstanding
 enhancements planned in this area, I might be interested in improving
 this command to allow specifying the encoding and to have it do obvious
 stuff like recognize UTF lead bytes automatically. At the very least,
 the documentation needs some work to explain these subtleties.

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 from or
written to a file. 

Seems clear enough to me.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Peter Headland]

 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 from or
written to a file.

Rats - I read the manual page twice and that didn't register on my
feeble consciousness. I suspect that I didn't look beyond the word
client, since I knew I wasn't interested in client behavior and I was
speed-reading. On the assumption that I am not uniquely stupid, maybe we
could re-phrase this slightly, with a for example, and add a heading
Localization?

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.

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?

-- 
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.org
Subject: Re: [GENERAL] COPY command character set 

Peter Headland pheadl...@actuate.com 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 no outstanding
 enhancements planned in this area, I might be interested in improving
 this command to allow specifying the encoding and to have it do
obvious
 stuff like recognize UTF lead bytes automatically. At the very least,
 the documentation needs some work to explain these subtleties.

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 from or
written to a file. 

Seems clear enough to me.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Adrian Klaver]

- Peter Headland pheadl...@actuate.com 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 from or
 written to a file.
 
 Rats - I read the manual page twice and that didn't register on my
 feeble consciousness. I suspect that I didn't look beyond the word
 client, since I knew I wasn't interested in client behavior and I
 was
 speed-reading. On the assumption that I am not uniquely stupid, maybe
 we
 could re-phrase this slightly, with a for example, and add a
 heading
 Localization?
 
 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.
 
 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?
 
 -- 
 Peter Headland
 Architect
 Actuate Corporation
 

 
 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 from or
 written to a file. 
 
 Seems clear enough to me.
 
   regards, tom lane

Maybe the link might help?

http://www.postgresql.org/docs/8.4/interactive/multibyte.html


Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Tom Lane]

Peter Headland pheadl...@actuate.com 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 some hack that cleans
up misencoding problems.

The idea of overriding client_encoding has been suggested before.  I
don't remember if it was rejected or is just languishing on the TODO
list.  I'd be a little worried about sending clients data in an encoding
they aren't expecting, but if it only works for I/O to a file it might
be okay.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Peter Headland]

 There are no lead bytes in UTF-8

Sorry, sloppy use of terminology. I should have said UTF signatures
aka the byte order mark. IOW, the magic number bytes commonly found
at the front of UTF encoded files:

UTF-16 little-endian   FF FE
UTF-16 big-endian   FE FF
UTF-8   EF BB BF

These tend to be inserted automatically by text editors, so it would be
advantageous to have them handled automatically by COPY (at least as an
option). Right now, if I edit a UTF-8 file then load it with COPY, I get
errors or bad data if the editor chose to add the 3 signature bytes.

Whilst UTF-16 is not supported internally, COPY seems to be a legitimate
special case, because it is used for migration to/from other tools that
may emit or expect UTF-16. ISTR that Postgres uses UCI? If so it would
be near-trivial to allow COPY to read and write UTF-16. If done via a
syntax extension to COPY (which I think is the most desirable
implementation), this would have no adverse effect on any other
capability. It also seems sufficiently isolated from sensitive/complex
areas of the code that it might make a suitable first project for
someone who is interested in becoming a contributor...

-- 
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 Headland pheadl...@actuate.com 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 some hack that cleans
up misencoding problems.

The idea of overriding client_encoding has been suggested before.  I
don't remember if it was rejected or is just languishing on the TODO
list.  I'd be a little worried about sending clients data in an encoding
they aren't expecting, but if it only works for I/O to a file it might
be okay.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Peter Headland]

 Maybe the link might help?
 
 http://www.postgresql.org/docs/8.4/interactive/multibyte.html

That page is too generic; what would be helpful is a section in the doc for 
each command that is affected by I18N/L10N considerations, that identifies how 
that specific command behaves.

Now that I have grasped the behavior, I'm more than happy to edit the COPY doc 
page, if people think that would be helpful/worthwhile.

-- 
Peter Headland
Architect
Actuate Corporation


-Original Message-
From: Adrian Klaver [mailto:akla...@comcast.net] 
Sent: Thursday, September 10, 2009 11:06
To: Peter Headland
Cc: pgsql-general@postgresql.org; Tom Lane
Subject: Re: [GENERAL] COPY command character set


- Peter Headland pheadl...@actuate.com 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 from or
 written to a file.
 
 Rats - I read the manual page twice and that didn't register on my
 feeble consciousness. I suspect that I didn't look beyond the word
 client, since I knew I wasn't interested in client behavior and I
 was
 speed-reading. On the assumption that I am not uniquely stupid, maybe
 we
 could re-phrase this slightly, with a for example, and add a
 heading
 Localization?
 
 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.
 
 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?
 
 -- 
 Peter Headland
 Architect
 Actuate Corporation
 

 
 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 from or
 written to a file. 
 
 Seems clear enough to me.
 
   regards, tom lane

Maybe the link might help?

http://www.postgresql.org/docs/8.4/interactive/multibyte.html


Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command character set

[Alvaro Herrera]

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.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] COPY command character set

[Peter Headland]

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 character set

[Tom Lane]

Peter Headland pheadl...@actuate.com 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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] \copy command error

[Andrew Maracini]

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 what the first two look like:

Jon,JF,Doe,GIS Coordinator,Miami University,Ohio,Oxford,OH,United
States,10/25/02
Janet,,Doe,PhD Student and Professional Geologist,York
University,Toronto,ON,Canada,9/25/07

The table has 9 fields mostly varchar and one date field.

I'm running 8.3 on Windows Vista Ultimate

thanks.

Andy

-- 
Andrew Maracini, GISP/AICP
Superior GIS Solutions LLC

3309 N.Casaloma Dr. #114
Appleton, WI 54913

http://www.superiorgissolutions.com
920-574-2090
906-361-4132 (cell)

Re: [GENERAL] \copy command error

[Mark Watson]

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 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 what the first two look like:
Jon,JF,Doe,GIS Coordinator,Miami University,Ohio,Oxford,OH,United
States,10/25/02
Janet,,Doe,PhD Student and Professional Geologist,York
University,Toronto,ON,Canada,9/25/07
The table has 9 fields mostly varchar and one date field.
I'm running 8.3 on Windows Vista Ultimate

thanks.

Andy


Hi Andy,

Your CSV lines have 10 fields (10 comma separated values). Probably, in the
data you are exporting, the City/State is one field, but the comma is being
exported, thus giving you 10 fields. If this is the case, exporting the
City/state field wrapped in quotation marks should do the truck, or use a
different delimiter, like Tab

Mark

Re: [GENERAL] \copy command error

[erobles]

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'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 what


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  ',';

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] \copy command error

[Sam Mason]

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 then, the code in psql would look
like this:

  \copy gisp FROM 'd:/projects/gisp/gisp.csv' WITH CSV

PG and MS Excel have an almost identical definition of what a CSV file
should look like, opening the file in Excel is always a good quick check
as to why PG isn't liking the file.

-- 
  Sam  http://samason.me.uk/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] \copy command error

[Sam Mason]

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 copy from the system that
psql is running in, rather than a normal COPY command that runs on the
server.

I believe \copy is implemented as a COPY FROM STDIN... with psql
automatically piping the data over the connection for you.

-- 
  Sam  http://samason.me.uk/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] COPY command on windows???

[Andreas]

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 references to permission errors.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Thomas Kellerer]

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 HEADER;


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] COPY command question

[Ivano Luberti]

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 from 'C:\\temp\\anagraficaANIDIs.csv'...
^
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.
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 directory
Stato SQL: 58P01


The problem is the file C:\temp\anagraficaANIDIs.csv is there and I have
granted reading rights to everyone .
Any suyggestion?






-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command question

[Raymond O'Donnell]

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'c:\\temp\\anagraficaANIDIs.csv' with csv;

Also, remember that the file needs to be on the same machine as the
server; if you're running pgAdmin on a different machine, this won't work.

HTH,

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command question

[Ivano Luberti]

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 directory
Stato SQL: 58P01

I have also tried uppercasing C without success.
I'm sure about the path because I have copied and pasted from the
properties window.








Raymond O'Donnell ha scritto:
 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'c:\\temp\\anagraficaANIDIs.csv' with csv;

 Also, remember that the file needs to be on the same machine as the
 server; if you're running pgAdmin on a different machine, this won't work.

 HTH,

 Ray.

 --
 Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
 r...@iol.ie
 Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
 --

   

-- 
==
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command question

[Raymond O'Donnell]

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 -
is this the case?

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command question

[Ivano Luberti]

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 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 -
 is this the case?

 Ray.

 --
 Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
 r...@iol.ie
 Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
 --

   

-- 
==
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command question

[Raymond O'Donnell]

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 frustration
not long agoso I was feeling your pain. :-)

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPy command question

[Scott Marlowe]

On Wed, Feb 11, 2009 at 11:22 AM, SHARMILA JOTHIRAJAH
sharmi...@yahoo.com 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 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 resultset) or any other way?

If that java program can provide direct input to postgresql then yes.
If everything has to be a prepared statement etc then no.  Assuming
your java framework allows you just throw input at the database, you'd
be able to just give it the input line by line.

 I know I could get the Oracle rows in a csv format but
 Im trying to get it done without any file in between ?

 In short is it possible to use this 'COPY' command to migrate my tables'
 data from Oracle to Postgresql without using any file
  in between?

Sure, I can do it in PHP.  I've done it in PHP.  If your java
connectors have the facility to throw raw sql at pgsql then it should
work.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPy command question

[SHARMILA JOTHIRAJAH]

Thanks all
This is my simple java code
public class copy{
  public static void main(String[] args) throws Exception
    {
  Connection connection1=null;
  Connection connection2=null;
  Statement stmt;
  String driverName1=org.postgresql.Driver;
  Class.forName(driverName2);
  connection1=DriverManager.getConnection(args[0],args[1],args[2]);
  pstmt=connection1.prepareStatement(select employee_id  
||','||employee_name from Employee);
  ResultSet rs1=pstmt.executeQuery();

  while (rs1.next())
  {
   System.out.println(rs1.getString(1));
    }
  stmt.close();
   connection1.close();
    }
}

And I pipe this to the psql like this
 ant/bin/ant copy -emacs | sed '1,3d'|sed '$d'|sed 'N;$!P;$!D;$d'|psql -c copy 
employee from STDIN WITH null 'NULL' DELIMITER ',' EMP

ant/bin/ant copy -emacs - I run it using ant
 sed '1,3d'|sed '$d'|sed 'N;$!P;$!D;$d  trim the unnecessary ant outputs 
like the 1st 2 lines and last 2 ines and any blank lines using 'sed'
so that my final output will be just the data with a 'comma' delimiter that I 
feed it to the psql COPY command...

It seems to work... I havent checked the performance for big tables...Im not 
sure how it scales for big tables... Do you know any other way of improving my 
java code to retrieve the data fast or in batches ?

Also does COPY treat timestamp  LOBs data different?

Thanks
Sharmila


--- On Thu, 2/12/09, Scott Marlowe scott.marl...@gmail.com wrote:
From: Scott Marlowe scott.marl...@gmail.com
Subject: Re: [GENERAL] COPy command question
To: sharmi...@yahoo.com
Cc: General postgres mailing list pgsql-general@postgresql.org
Date: Thursday, February 12, 2009, 1:35 PM

On Wed, Feb 11, 2009 at 11:22 AM, SHARMILA JOTHIRAJAH
sharmi...@yahoo.com 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 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 resultset) or any other way?

If that java program can provide direct input to postgresql then yes.
If everything has to be a prepared statement etc then no.  Assuming
your java framework allows you just throw input at the database, you'd
be able to just give it the input line by line.

 I know I could get the Oracle rows in a csv format but
 Im trying to get it done without any file in between ?

 In short is it possible to use this 'COPY' command to migrate my
tables'
 data from Oracle to Postgresql without using any file
  in between?

Sure, I can do it in PHP.  I've done it in PHP.  If your java
connectors have the facility to throw raw sql at pgsql then it should
work.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



  

[GENERAL] COPy command question

[SHARMILA JOTHIRAJAH]

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 from 'file' or 'STDIN'.
Is it possible for the COPY command to take its input from a
java program(which contains the oracle resultset) or any other way?

I know I could get the Oracle rows in a csv format but 
Im trying to get it done without any file in between ? 

In short is it possible to use this 'COPY' command to migrate my tables' 
data from Oracle to Postgresql without using any file in between?

Thanks
Sharmila







  

Re: [GENERAL] COPY command question

[Sam Mason]

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 resultset) or any other way?

STDIN just means from the same place as the rest of the SQL has come
from.  For example, if you're using JDBC, it would be possible do
generate a string containing:

  COPY tbl (col1,col2) FROM STDIN WITH CSV;
  1,2
  4,7
  12,37
  \.

and execute() the whole string.  There appear to be patches[1] available so
you can stream directly into the database rather than having to generate
a large strings to pass in.

-- 
  Sam  http://samason.me.uk/
 
 [1] http://kato.iki.fi/sw/db/postgresql/jdbc/copy/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] COPY command question

[Kedar]

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 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 resultset) or any other way?
   



STDIN just means from the same place as the rest of the SQL has come
from.  For example, if you're using JDBC, it would be possible do
generate a string containing:

 COPY tbl (col1,col2) FROM STDIN WITH CSV;
 1,2
 4,7
 12,37
 \.

and execute() the whole string.  There appear to be patches[1] available so
you can stream directly into the database rather than having to generate
a large strings to pass in.

 




--
Thanks  Regards 


Kedar Parikh
Netcore Solutions Pvt. Ltd.

Tel: +91 (22) 6662 8135
Mob: +91 9819634734
Email: ke...@netcore.co.in
Web: www.netcore.co.in 

Re: [GENERAL] copy command - date

[Raj A]

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 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(col1,col4,col7, ...

 But the long and the short of it is that COPY doesn't see any column
 delimiters at all in this file.  We're guessing as to what the OP
 intends the columns to be, but whatever he wants, he needs something
 other than an uncertain number of spaces to separate them ...

 regards, tom lane

 ---(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 not
match


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

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

[Decibel!]

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. (This is usually in the case of updates
 to specific rows and there be a timestamp indicating it has been changed
 etc.)
 
 In MySQL, the mysqlimport util has the --replace function which will
 replace the data if there is any event of a duplicate.
 
 Does PG support this?

No; you'll need to COPY into a temporary or staging table and then
proceed from there. Alternatively, you could use
http://pgfoundry.org/projects/pgloader/.
-- 
Decibel!, aka Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)


pgpyQNuNDvD9l.pgp
Description: PGP signature

Re: [GENERAL] copy command - date

[Scott Marlowe]

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(data, 24, 2)||substr(data, 32, 6) as
 inspection_date

I didn't have to do anything special, just copied it in:

create table g (ts timestamp);
set datestyle=ISO, MDY;
copy g (ts) from stdin;
06/12/04 12:00:00
\.
select * from g;
 ts
-
 2004-06-12 12:00:00
delete from g;
set datestyle=ISO, DMY;
copy g (ts) from stdin;
06/12/04 12:00:00
\.
 select * from g;
 ts
-
 2004-12-06 12:00:00

---(end of broadcast)---
TIP 6: explain analyze is your friend

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

[Ow Mun Heng]

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 timestamp indicating it has been changed
etc.)

In MySQL, the mysqlimport util has the --replace function which will
replace the data if there is any event of a duplicate.

Does PG support this?



---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] copy command - date

[novice]

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

  Table pm.maintenance
 Column  |   Type   |
Modifiers
-+--+--
 maintenance_id  | integer  | not null default
nextval('maintenance_maintenance_id_seq'::regclass)
 meter_id| integer  |
 status  | character(11)|
 inspection_date | timestamp with time zone |
Indexes:
maintenance_pkey PRIMARY KEY, btree (maintenance_id)

Thanks!

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] copy command - date

[Tom Lane]

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

What sort of trouble, exactly?

I'm guessing that you might need to set DateStyle to tell Postgres what
the date field ordering is, but without seeing any error messages that's
strictly a guess.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] copy command - date

[novice]

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: 3665   OK
 SM 07/07/13 06:09

  Table pm.maintenance
 Column  |   Type   |
Modifiers
-+--+--
 maintenance_id  | integer  | not null default
nextval('maintenance_maintenance_id_seq'::regclass)
 meter_id| integer  |
 status  | character(3) |
 inspector   | character(2) |
 inspection_date | timestamp with time zone |



On 13/08/07, Tom Lane [EMAIL PROTECTED] wrote:
 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

 What sort of trouble, exactly?

 I'm guessing that you might need to set DateStyle to tell Postgres what
 the date field ordering is, but without seeing any error messages that's
 strictly a guess.

 regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] copy command - date

[Tom Lane]

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 is mostly that you don't have tabs
between the fields.  I don't think COPY can be taught to parse this
input directly --- you need to preprocess the file to split the fields
apart.

BTW: after you get it split into fields, you're also going to find that
OK is not valid input for the integer meter_id column.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] copy command - date

[Paul Lambert]

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 maintenance_id: 3665   OK
 SM 07/07/13 06:09



That's not complaining about the date, that is complaining that your 
input file does not contain the maintenance_id column.


--
Paul Lambert
Database Administrator
AutoLedgers

---(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 not
  match

Re: [GENERAL] copy command - date

[Michael Glaesemann]

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 maintenance, line 1, column maintenance_id: 3665   OK
 SM 07/07/13 06:09


I'd say your tabs have been converted to spaces so the COPY command  
is not delimiting the fields as you expect.


Michael Glaesemann
grzm seespotcode net



---(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 not
  match

Re: [GENERAL] copy command - date

[Paul Lambert]

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, line 1, column maintenance_id: 3665   OK
 SM 07/07/13 06:09



That's not complaining about the date, that is complaining that your 
input file does not contain the maintenance_id column.




I don't think copy allows you to leave columns out of your input file - 
even if they belong to a sequence.


You could try something like:

-- Create a temp table with everything but the sequence column.
CREATE TABLE maintenance_load AS
   SELECT meter_id,status,inspector,inspection_date
   FROM maintenance
   WHERE 1=0;

-- Copy data from file into temp table.
COPY maintenance_load FROM 'd:/temp/file.txt';

-- Insert data from temp table into main table, which will
-- generate the value for the sequence field.
INSERT INTO maintenance (meter_id,status,inspector,inspection_date)
   (SELECT * from maintenance_load);

-- Drop temp table.
DROP TABLE maintenance_load;

Also, not sure if it was your mail client or not, but the data you have 
supplied was space-separated, you probably want to make sure the actual 
data file is tab-separated, otherwise it's going to think it's all part 
of one field.



--
Paul Lambert
Database Administrator
AutoLedgers


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] copy command - date

[novice]

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 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
 
 
  That's not complaining about the date, that is complaining that your
  input file does not contain the maintenance_id column.
 

 I don't think copy allows you to leave columns out of your input file -
 even if they belong to a sequence.

 You could try something like:

 -- Create a temp table with everything but the sequence column.
 CREATE TABLE maintenance_load AS
 SELECT meter_id,status,inspector,inspection_date
 FROM maintenance
 WHERE 1=0;

 -- Copy data from file into temp table.
 COPY maintenance_load FROM 'd:/temp/file.txt';

 -- Insert data from temp table into main table, which will
 -- generate the value for the sequence field.
 INSERT INTO maintenance (meter_id,status,inspector,inspection_date)
 (SELECT * from maintenance_load);

 -- Drop temp table.
 DROP TABLE maintenance_load;

 Also, not sure if it was your mail client or not, but the data you have
 supplied was space-separated, you probably want to make sure the actual
 data file is tab-separated, otherwise it's going to think it's all part
 of one field.


 --
 Paul Lambert
 Database Administrator
 AutoLedgers


 ---(end of broadcast)---
 TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] copy command - date

[Tom Lane]

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(col1,col4,col7, ...

But the long and the short of it is that COPY doesn't see any column
delimiters at all in this file.  We're guessing as to what the OP
intends the columns to be, but whatever he wants, he needs something
other than an uncertain number of spaces to separate them ...

regards, tom lane

---(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 not
   match

Re: [GENERAL] copy command - date

[novice]

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 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(col1,col4,col7, ...

 But the long and the short of it is that COPY doesn't see any column
 delimiters at all in this file.  We're guessing as to what the OP
 intends the columns to be, but whatever he wants, he needs something
 other than an uncertain number of spaces to separate them ...

 regards, tom lane

 ---(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 not
match


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] copy command - date

[novice]

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 13/08/07, novice [EMAIL PROTECTED] wrote:
 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 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(col1,col4,col7, ...
 
  But the long and the short of it is that COPY doesn't see any column
  delimiters at all in this file.  We're guessing as to what the OP
  intends the columns to be, but whatever he wants, he needs something
  other than an uncertain number of spaces to separate them ...
 
  regards, tom lane
 
  ---(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 not
 match
 


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] copy command - date

[Tino Wildenhain]

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
inspection_date


You could try to_date() - see:

http://www.postgresql.org/docs/8.2/static/functions-formatting.html

Regards
Tino

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

[GENERAL] COPY Command and a non superuser user?

[Warren]

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 not
  match

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

[Scott Marlowe]

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 TM


---(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 not
  match

Re: [GENERAL] COPY command details

[Gerald Timothy G Quimpo]

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 rebuild 
 all of the indexes in the database.
 
 On the other hand, if I am to partition the data into 
 several tables then it might not be such a big deal since 
 I am only adding and never deleting... This makes it a 
 little more of a pain in the ass.

I am leaning toward a policy of always partitioning large
tables.  I haven't found the time to do it properly yet, 
thinking about it, hoping that someone who'se done it will
chime in with their rules of thumb.

Like Benjamin, I have a database that is close to 600GB 
for 2.25 years of data, and if I were to add the other
4 years of data that we have archived away, will easily go
into the terabyte range.  There are a few individual tables
which approach 100GB all by themselves.

As it is, I can't afford to do reindex or even backup
(pg_dump or any other method) or other administrative tasks
on those tables since the processes take too long (there are
workarounds, i could backup single tables at slack times,
which would allow me to do a complete backup (but not
self-consistent as a set) over the course of a week or so.

So I'm leaning toward partitioning, perhaps selecting 
partition rules so that no table will be larger than
around 5GB, at which point, reindex or admin procedures
that take exclusive locks now become only minor 
inconveniences rather than showstoppers.

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? performance issues with
LVM, etc?

tiger

-- 
Gerald Timothy Quimpo   [EMAIL PROTECTED]
Business Systems Development, KFC/Mr Donut/Ramcar

   There is nothing more dreadful than imagination without taste.
-- Johann Wolfgang von Goethe


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] COPY command details

[Ron Johnson]

-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? performance issues with
 LVM, etc?

If PG allows partitioned tables to span schemas, then I'd do that,
so that only one (relatively small, say quarterly or semi-annual)
schema is ever hot and needs to be backed up.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGESHPS9HxQb37XmcRAmZFAKCkRvh6fMlU4CUj25F5BT2L56C0fgCgn5wS
c+h/WQR3WHy9BvZ6lryIIqQ=
=OdEt
-END PGP SIGNATURE-

---(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 not
   match

[GENERAL] COPY command details

[Benjamin Arai]

Hi,

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, or not at all?


Benjamin

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] COPY command details

[A. Kretschmer]

am  Thu, dem 29.03.2007, um  0:13:09 -0700 mailte Benjamin Arai folgendes:
 Hi,
 
 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, or not at all?

after, i think.

test=# create table foo (id int primary key, name text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for 
table foo
CREATE TABLE
test=*# copy foo from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
 1a
 2b
 2c
 \.
ERROR:  duplicate key violates unique constraint foo_pkey
CONTEXT:  COPY foo, line 3: 2  c
test=*#


I can type the wrong key and the error occurs later with the finaly \.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: - Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] COPY command details

[Tom Lane]

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, or not at all?

 after, i think.

 test=# create table foo (id int primary key, name text);
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for 
 table foo
 CREATE TABLE
 test=*# copy foo from stdin;
 Enter data to be copied followed by a newline.
 End with a backslash and a period on a line by itself.
 1a
 2b
 2c
 \.
 ERROR:  duplicate key violates unique constraint foo_pkey
 CONTEXT:  COPY foo, line 3: 2  c
 test=*#

 I can type the wrong key and the error occurs later with the finaly \.

No, during is the right answer.  The above only demonstrates that
libpq buffers COPY data in larger-than-one-line units --- once the
data gets to the backend it's inserted and checked a row at a time.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] COPY command details

[A. Kretschmer]

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 or something similar.

Drop the index(es), do the COPIES, recreate the index(es). You can do this
within a transaction.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: - Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] COPY command details

[Benjamin Arai]

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:

am  Thu, dem 29.03.2007, um  0:13:09 -0700 mailte Benjamin Arai  
folgendes:

Hi,

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, or not at all?


after, i think.

test=# create table foo (id int primary key, name text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
foo_pkey for table foo

CREATE TABLE
test=*# copy foo from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

1a
2b
2c
\.

ERROR:  duplicate key violates unique constraint foo_pkey
CONTEXT:  COPY foo, line 3: 2  c
test=*#


I can type the wrong key and the error occurs later with the finaly \.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: - Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of  
broadcast)---

TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that  
your

   message can get through to the mailing list cleanly




---(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 not
  match

Re: [GENERAL] COPY command details

[Benjamin Arai]

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:

am  Thu, dem 29.03.2007, um  0:13:09 -0700 mailte Benjamin Arai  
folgendes:

Hi,

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, or not at all?


after, i think.

test=# create table foo (id int primary key, name text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
foo_pkey for table foo

CREATE TABLE
test=*# copy foo from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

1a
2b
2c
\.

ERROR:  duplicate key violates unique constraint foo_pkey
CONTEXT:  COPY foo, line 3: 2  c
test=*#


I can type the wrong key and the error occurs later with the finaly \.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: - Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of  
broadcast)---

TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that  
your

   message can get through to the mailing list cleanly




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] COPY command details

[Bruce Momjian]

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 COPY, and then recreate the index.  That is
done often.

---


 
 Benjamin
 
 On Mar 29, 2007, at 1:03 AM, A. Kretschmer wrote:
 
  am  Thu, dem 29.03.2007, um  0:13:09 -0700 mailte Benjamin Arai  
  folgendes:
  Hi,
 
  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, or not at all?
 
  after, i think.
 
  test=# create table foo (id int primary key, name text);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
  foo_pkey for table foo
  CREATE TABLE
  test=*# copy foo from stdin;
  Enter data to be copied followed by a newline.
  End with a backslash and a period on a line by itself.
  1a
  2b
  2c
  \.
  ERROR:  duplicate key violates unique constraint foo_pkey
  CONTEXT:  COPY foo, line 3: 2  c
  test=*#
 
 
  I can type the wrong key and the error occurs later with the finaly \.
 
 
  Andreas
  -- 
  Andreas Kretschmer
  Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: - Header)
  GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
 
  ---(end of  
  broadcast)---
  TIP 1: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that  
  your
 message can get through to the mailing list cleanly
 
 
 
 ---(end of broadcast)---
 TIP 2: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian  [EMAIL PROTECTED]  http://momjian.us
  EnterpriseDB   http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] COPY command details

[Benjamin Arai]

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 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 COPY, and then recreate the index.  That is
done often.

---


  

Benjamin

On Mar 29, 2007, at 1:03 AM, A. Kretschmer wrote:


am  Thu, dem 29.03.2007, um  0:13:09 -0700 mailte Benjamin Arai  
folgendes:
  

Hi,

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, or not at all?


after, i think.

test=# create table foo (id int primary key, name text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
foo_pkey for table foo

CREATE TABLE
test=*# copy foo from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
  

1a
2b
2c
\.
  

ERROR:  duplicate key violates unique constraint foo_pkey
CONTEXT:  COPY foo, line 3: 2  c
test=*#


I can type the wrong key and the error occurs later with the finaly \.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: - Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of  
broadcast)---

TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that  
your

   message can get through to the mailing list cleanly

  

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster



  


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] COPY command details

[Ron Johnson]

-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 update on the index via a  
 vacuum or something similar.
 
 Sure, drop the index, do the COPY, and then recreate the index.  That is
 done often.

ALTER INDEX  MAINTENANCE IS DISABLED would be helpful, so that
you don't have to be dragging around (possibly changing) SQL around
everywhere.

So, an example would be from an evening load job would be:
  ALTER INDEX foobar_idx MAINTENANCE IS DISABLED;
  COPY foo FROM 'blarg';
  REINDEX INDEX foobar_idx;

So if the DBA decides that foobar_idx needs different fields, you
don't have to edit the evening load job when the index changes.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGDB7xS9HxQb37XmcRAqzuAJwK9LATewVE6GwJg/us6p5KzznWAgCfSZ9J
xtqWwHsVMvjuoSYP+/rEfNE=
=nJ+F
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] COPY command details

[Ron Johnson]

-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 updates during data load, and it actually works very well.

The down side is that if there's a unique value constraint failure,
you don't know which record it failed on.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGDCClS9HxQb37XmcRAo0pAKCwqYwXOAPIXK25L9zhWhtGMFi/hwCgtA+x
zgc5Bz8wrVQ5UGocGe5v3s4=
=aFmR
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] COPY command details

[Bruce Momjian]

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.

---


 
 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 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 COPY, and then recreate the index.  That is
  done often.
 
  ---
 
 

  Benjamin
 
  On Mar 29, 2007, at 1:03 AM, A. Kretschmer wrote:
 
  
  am  Thu, dem 29.03.2007, um  0:13:09 -0700 mailte Benjamin Arai  
  folgendes:

  Hi,
 
  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, or not at all?
  
  after, i think.
 
  test=# create table foo (id int primary key, name text);
  NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index  
  foo_pkey for table foo
  CREATE TABLE
  test=*# copy foo from stdin;
  Enter data to be copied followed by a newline.
  End with a backslash and a period on a line by itself.

  1a
  2b
  2c
  \.

  ERROR:  duplicate key violates unique constraint foo_pkey
  CONTEXT:  COPY foo, line 3: 2  c
  test=*#
 
 
  I can type the wrong key and the error occurs later with the finaly \.
 
 
  Andreas
  -- 
  Andreas Kretschmer
  Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: - Header)
  GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
 
  ---(end of  
  broadcast)---
  TIP 1: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that  
  your
 message can get through to the mailing list cleanly
 

  ---(end of broadcast)---
  TIP 2: Don't 'kill -9' the postmaster
  
 


-- 
  Bruce Momjian  [EMAIL PROTECTED]  http://momjian.us
  EnterpriseDB   http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] COPY command details

[Tom Lane]

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 adding a large number
of rows to the index incrementally.  Also, you don't end up with a
physically disordered index, so there is some ongoing performance
benefit.

regards, tom lane

---(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 not
   match

Re: [GENERAL] COPY command details

[Ron Johnson]

-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 economy.  I don't have numbers at hand, but a
 full rebuild can be substantially faster than adding a large number
 of rows to the index incrementally.  Also, you don't end up with a
 physically disordered index, so there is some ongoing performance
 benefit.

But deferring the index updates allows you to play games with the
the index input data, such as presorting it in order to take
advantage of locality of data.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGDGC5S9HxQb37XmcRAk1nAJwNb72P1ZBFxA8jv2d7eo2GOMTvYQCgukr7
QbOAq/Sd88ZHeOTOt+pAgcM=
=A1+E
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] COPY command details

[Benjamin Arai]

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 range and I am adding about 10gigs of data a week.  At 
some point its going to take longer than a week to rebuild all of the 
indexes in the database.


On the other hand, if I am to partition the data into several tables 
then it might not be such a big deal since I am only adding and never 
deleting... This makes it a little more of a pain in the ass.


Benjamin

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 economy.  I don't have numbers at hand, but a
full rebuild can be substantially faster than adding a large number
of rows to the index incrementally.  Also, you don't end up with a
physically disordered index, so there is some ongoing performance
benefit.

regards, tom lane

---(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 not
   match

  


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] COPY command details

[Benjamin Arai]

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 code.  The only part that was a little bit of work was 
to allow for easy access to the data for the data for the devs.  I did 
this by writing a few PL functions to automatically union  the tables 
and produce results. So the function like getData(startData,enddate) 
would run a union query for the respective date ranges.


Benjamin

P.S. If I am doing anything that can be improved for the data access 
portion please let me know and feel free to voice your opinions.  I am 
always looking for new ways to make this particular database faster.


Gerald Timothy G Quimpo wrote:

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 rebuild 
all of the indexes in the database.


On the other hand, if I am to partition the data into 
several tables then it might not be such a big deal since 
I am only adding and never deleting... This makes it a 
little more of a pain in the ass.



I am leaning toward a policy of always partitioning large
tables.  I haven't found the time to do it properly yet, 
thinking about it, hoping that someone who'se done it will

chime in with their rules of thumb.

Like Benjamin, I have a database that is close to 600GB 
for 2.25 years of data, and if I were to add the other

4 years of data that we have archived away, will easily go
into the terabyte range.  There are a few individual tables
which approach 100GB all by themselves.

As it is, I can't afford to do reindex or even backup
(pg_dump or any other method) or other administrative tasks
on those tables since the processes take too long (there are
workarounds, i could backup single tables at slack times,
which would allow me to do a complete backup (but not
self-consistent as a set) over the course of a week or so.

So I'm leaning toward partitioning, perhaps selecting 
partition rules so that no table will be larger than

around 5GB, at which point, reindex or admin procedures
that take exclusive locks now become only minor 
inconveniences rather than showstoppers.


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? performance issues with
LVM, etc?

tiger

  


---(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 not
  match