Thread: COPY FROM and multiple null strings
Hi, I've run into a little snag in my data loads, and I come seeking advice before I resort to an ugly workaround... I have a function which runs COPY FROM several times. My problem is that my data provider uses symbols to represent nulls - there is a different symbol for each different data type. For instance a null date would be '1900-01-01' and a null string would be '#'. Is there any way to define multiple null strings in the COPY FROM command? As always, any suggestions would be much appreciated. Thanks, Mark
Angva wrote: > Hi, > > I've run into a little snag in my data loads, and I come seeking > advice before I resort to an ugly workaround... > > I have a function which runs COPY FROM several times. My problem is > that my data provider uses symbols to represent nulls - there is a > different symbol for each different data type. For instance a null > date would be '1900-01-01' and a null string would be '#'. Is there > any way to define multiple null strings in the COPY FROM command? > > As always, any suggestions would be much appreciated. Man... that sounds like eating elementary school spinach... you remember the kind that slithered down your throat? There are two immediate ways I can think of to handle this. 1. Write a parser in <insert favorite language) and clean the file before insertion 2. copy everything into text columns and clean from there. Joshua D. Drake > > Thanks, > Mark > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Good Afternoon All- I come from the other DBMS from California where extremely large datasets could be partitioned into separate and distinctpartition tables Lets say for example I have Property Table which contains a tera worth of data and to gain performnce in the indexing I *could*partition this table so that All Cities which are A-M are in Partition1PropertyTable All Cities which are N-Z are in Parition2PropertyTable Can I achieve this in Postgres DB? Thanks, Martin -- --------------------------------------------------------------------------- This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressedand may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you arenot the intended recipient, you are notified that any dissemination, distribution or copying of this communication isstrictly prohibited. --------------------------------------------------------------------------- Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiquéet peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document,nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire.
Martin Gainty wrote: > Good Afternoon All- > I come from the other DBMS from California where extremely large datasets could be partitioned into separate and distinctpartition tables > Lets say for example I have Property Table which contains a tera worth of data and to gain performnce in the indexing I*could* partition this table so that > All Cities which are A-M are in Partition1PropertyTable > All Cities which are N-Z are in Parition2PropertyTable > Can I achieve this in Postgres DB? http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html Joshua D. Drake > Thanks, > Martin -- > --------------------------------------------------------------------------- > This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it isaddressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If youare not the intended recipient, you are notified that any dissemination, distribution or copying of this communicationis strictly prohibited. > --------------------------------------------------------------------------- > Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiquéet peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document,nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Good Stuff! Thanks Josh! M-- --------------------------------------------------------------------------- This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is addressedand may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you arenot the intended recipient, you are notified that any dissemination, distribution or copying of this communication isstrictly prohibited. --------------------------------------------------------------------------- Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiquéet peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document,nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire. ----- Original Message ----- From: "Joshua D. Drake" <jd@commandprompt.com> To: "Martin Gainty" <mgainty@hotmail.com> Cc: <pgsql-general@postgresql.org> Sent: Saturday, March 03, 2007 3:26 PM Subject: Re: [GENERAL] Partition Table > Martin Gainty wrote: >> Good Afternoon All- >> I come from the other DBMS from California where extremely large datasets could be partitioned into separate and distinctpartition tables >> Lets say for example I have Property Table which contains a tera worth of data and to gain performnce in the indexingI *could* partition this table so that >> All Cities which are A-M are in Partition1PropertyTable >> All Cities which are N-Z are in Parition2PropertyTable >> Can I achieve this in Postgres DB? > > http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html > > Joshua D. Drake > > >> Thanks, >> Martin -- >> --------------------------------------------------------------------------- >> This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it isaddressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If youare not the intended recipient, you are notified that any dissemination, distribution or copying of this communicationis strictly prohibited. >> --------------------------------------------------------------------------- >> Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire indiquéet peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce document,nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire. >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> > > > -- > > === The PostgreSQL Company: Command Prompt, Inc. === > Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 > Providing the most comprehensive PostgreSQL solutions since 1997 > http://www.commandprompt.com/ > > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL Replication: http://www.commandprompt.com/products/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
On Mar 3, 2:12 pm, j...@commandprompt.com ("Joshua D. Drake") wrote:
> There are two immediate ways I can think of to handle this.
>
> 1. Write a parser in <insert favorite language) and clean the file
> before insertion
>
> 2. copy everything into text columns and clean from there.
>
> Joshua D. Drake
That's what I feared. :) Thanks, Joshua.
Mark