Re: [GENERAL] date format
On 05/03/2007 12:16, Joshua D. Drake wrote: I didn't really explain my point here. You an use datestyle from a session, or you can reload the server. You do not need to *restart*. Yes, that's what I was getting at as well - I was in a hurry at the time and "restart" was the wrong word to use. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] date format
Peter Eisentraut wrote: Am Sonntag, 4. März 2007 17:16 schrieb Raymond O'Donnell: Probably a silly question, but did you remember to restart the server after changing the datestyle setting? You don't need to do that. I didn't really explain my point here. You an use datestyle from a session, or you can reload the server. You do not need to *restart*. However, he didn't mention if he had done any of that. He just said he set it which doesn't really mean anything if he didn't change it from the setting or reload the server. J ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] date format
Am Sonntag, 4. März 2007 17:16 schrieb Raymond O'Donnell: > Probably a silly question, but did you remember to restart the server > after changing the datestyle setting? You don't need to do that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] date format
Garry Saddington schrieb: I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still rendered in the format (y,m,d) . How can I change this behaviour? regards garry See my answer on the Zope list. The style however influences the way dates are parsed by default, so you need to take care when you feed dates from your application to database. You should also use to_date() to make it explicit. Regards Tino ---(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] date format
On 04/03/2007 19:26, Raymond O'Donnell wrote: Ahh! Then you need to use to_char(). The datestyle setting in http://www.postgresql.org/docs/8.2/static/functions-formatting.html --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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] date format
On 04/03/2007 18:56, Garry Saddington wrote: # select now(); now --- 2007-03-04 18:07:54.626267+01 (1 row) Which is precisely what I expect. Which is precisely what I get, but I want 04-03-2007. Ahh! Then you need to use to_char(). The datestyle setting in postgresql.conf differentiates *only* between European and American date formats - i.e. the relative order of day and month. It doesn't set the exact output format. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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] date format
On Sunday 04 March 2007 17:09, Martijn van Oosterhout wrote: > On Sun, Mar 04, 2007 at 05:05:44PM +, Garry Saddington wrote: > > On Sunday 04 March 2007 16:16, Raymond O'Donnell wrote: > > > On 04/03/2007 16:16, Garry Saddington wrote: > > > > I tried pgAdmin3 on windows to run 'select now()' and it still does > > > > not return the correct datestyle. > > > > > > Probably a silly question, but did you remember to restart the server > > > after changing the datestyle setting? > > > > yes > > regards > > Can you show us your exact output so we don't have to guess? I get > this: > > # set datestyle=iso,dmy; > SET > # select now(); > now > --- > 2007-03-04 18:07:54.626267+01 > (1 row) > > Which is precisely what I expect. Which is precisely what I get, but I want 04-03-2007. regards Garry ---(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] date format
Garry Saddington wrote: I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still rendered in the format (y,m,d) . How can I change this behaviour? Did you restart PostgreSQL after making the change? You can also set it on the fly per session. Joshua D. Drake regards garry ---(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] date format
On Sun, Mar 04, 2007 at 05:05:44PM +, Garry Saddington wrote: > On Sunday 04 March 2007 16:16, Raymond O'Donnell wrote: > > On 04/03/2007 16:16, Garry Saddington wrote: > > > I tried pgAdmin3 on windows to run 'select now()' and it still does not > > > return the correct datestyle. > > > > Probably a silly question, but did you remember to restart the server > > after changing the datestyle setting? > yes > regards Can you show us your exact output so we don't have to guess? I get this: # set datestyle=iso,dmy; SET # select now(); now --- 2007-03-04 18:07:54.626267+01 (1 row) Which is precisely what I expect. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] date format
On Sunday 04 March 2007 16:16, Raymond O'Donnell wrote: > On 04/03/2007 16:16, Garry Saddington wrote: > > I tried pgAdmin3 on windows to run 'select now()' and it still does not > > return the correct datestyle. > > Probably a silly question, but did you remember to restart the server > after changing the datestyle setting? yes regards garry > Ray. > > --- > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > [EMAIL PROTECTED] > --- > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings ---(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] date format
On 04/03/2007 16:16, Garry Saddington wrote: I tried pgAdmin3 on windows to run 'select now()' and it still does not return the correct datestyle. Probably a silly question, but did you remember to restart the server after changing the datestyle setting? Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] date format
On Sunday 04 March 2007 15:28, Raymond O'Donnell wrote: > On 04/03/2007 12:58, Jorge Godoy wrote: > > Garry Saddington <[EMAIL PROTECTED]> writes: > >> I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is > >> still rendered in the format (y,m,d) . How can I change this behaviour? > > > > ISO means y-m-d... > > The default postgresql.conf that is installed has > >datestyle = iso,mdy > > though commented out - on a new installation I uncomment this line as a > matter of course and change it to "iso,dmy" and it works fine. > > Are you sure that PostgreSQL is presenting the dates in the wrong > format? Could there be another layer that's swapping the month and year > around? - try SELECTing some date values from psql and see what you get. > I tried pgAdmin3 on windows to run 'select now()' and it still does not return the correct datestyle. Regards Garry ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] date format
On 04/03/2007 12:58, Jorge Godoy wrote: Garry Saddington <[EMAIL PROTECTED]> writes: I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still rendered in the format (y,m,d) . How can I change this behaviour? ISO means y-m-d... The default postgresql.conf that is installed has datestyle = iso,mdy though commented out - on a new installation I uncomment this line as a matter of course and change it to "iso,dmy" and it works fine. Are you sure that PostgreSQL is presenting the dates in the wrong format? Could there be another layer that's swapping the month and year around? - try SELECTing some date values from psql and see what you get. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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] date format
Garry Saddington <[EMAIL PROTECTED]> writes: > I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is > still > rendered in the format (y,m,d) . How can I change this behaviour? ISO means y-m-d... -- Jorge Godoy <[EMAIL PROTECTED]> ---(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] date format
I have 'datestyle ISO,DMY' set in postgresql.conf but the date output is still rendered in the format (y,m,d) . How can I change this behaviour? regards garry ---(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] date format
David Rysdam wrote: David Rysdam wrote: I could swear (but I don't think I can prove at this point) that 8.0 beta3 returned timestamps with milliseconds, like this: -MM-DD HH24:MI:SS.MS But 8.1 isn't doing that. I see functions to format the date, but that would require me to change all my existing SQL to specifically ask for the milliseconds. There's also the "set datestyle" thing, but the granularity doesn't exist to specify an exact format such as the above. Is there a way to tell postgres *exactly* what I want the *default* date output format to be? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Nevermind, the documentation just seems to be unclear. I do get .MS back in ISO format (despite that not being shown as part of the ISO format). It's just that none of my dates have milliseconds on them, which is simply a bug I'll have to find. ---(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 OK, sorry to flood on this, but there is still a problem. If I have no milliseconds, I'd like to get back ".000" at the end of the output. Since MS are indeed part of the ISO format, why don't get I get zeros? Alternatively, if MS aren't part of the ISO format, why do I get non-zeros? ---(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] date format
David Rysdam wrote: I could swear (but I don't think I can prove at this point) that 8.0 beta3 returned timestamps with milliseconds, like this: -MM-DD HH24:MI:SS.MS But 8.1 isn't doing that. I see functions to format the date, but that would require me to change all my existing SQL to specifically ask for the milliseconds. There's also the "set datestyle" thing, but the granularity doesn't exist to specify an exact format such as the above. Is there a way to tell postgres *exactly* what I want the *default* date output format to be? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Nevermind, the documentation just seems to be unclear. I do get .MS back in ISO format (despite that not being shown as part of the ISO format). It's just that none of my dates have milliseconds on them, which is simply a bug I'll have to find. ---(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
[GENERAL] date format
I could swear (but I don't think I can prove at this point) that 8.0 beta3 returned timestamps with milliseconds, like this: -MM-DD HH24:MI:SS.MS But 8.1 isn't doing that. I see functions to format the date, but that would require me to change all my existing SQL to specifically ask for the milliseconds. There's also the "set datestyle" thing, but the granularity doesn't exist to specify an exact format such as the above. Is there a way to tell postgres *exactly* what I want the *default* date output format to be? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Date format for bulk copy
On Wed, Oct 13, 2004 at 03:37:14PM -0400, David Rysdam wrote: > Michael Fuhr wrote: > > > >I'd probably choose to extend PostgreSQL rather than hack what > >already exists, though. > > By "extend PostgreSQL" do you mean create a custom input_function for > timestamp? Are there docs that give hints for replacing the input > function of an existing type? Someone else replied similarly, but I'm > afraid I'm not familiar enough with PG to decipher it all. I'd been thinking about your idea of creating a custom type or providing a custom input function for a standard type. I've done simple tests where I updated a type's typinput field in pg_catalog.pg_type and it worked, but I don't know what problems that might cause. One thing that comes to mind is that I don't know if pg_dump/pg_dumpall would recognize such a change for a standard type. The more I think about it, the less I'm convinced that it's a good idea. I like Pierre-Frédéric Caillaud's suggestion to have a filter script query the database for field positions and types and reformat the fields as appropriate. That would allow the filter to have knowledge of which fields should be reformatted without having to program that logic explicitly. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Date format for bulk copy
On Wed, Oct 13, 2004 at 08:36:50PM +0200, Pierre-Fr?d?ric Caillaud wrote: > > You can have your script make a query in the database to fetch the > data types of the fields and then know which ones are to be transformed > and how. The script would take as arguments a dump file and a > database,schema.table, would read the file and pipe the transformed data > into a psql with a COPY FROM stdin command... could save you a lot of work > no ? That's a good idea, and it would mean not having to hack PostgreSQL or create custom types or custom input functions for standard types. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Date format for bulk copy
Michael Fuhr wrote: On Wed, Oct 13, 2004 at 01:32:01PM -0400, David Rysdam wrote: Michael Fuhr wrote: You could filter the data through a script that reformats certain fields, then feed the reformatted data to PostgreSQL. This is usually a trivial task for Perl, awk, sed, or the like. Right, I *can* do this. But then I have to build knowledge into that script so it can find each of these date fields (there's like 20 of them across 10 different files) and then update that knowledge each time it changes. In your case that's a reasonable argument against filtering the data with a script. Using a regular expression in the script might reduce or eliminate the need for some of the logic, but then you'd run the risk of reformatting data that shouldn't have been touched. I'm still leaning towards just making postgres accept at ':' delimiter for milliseconds. Based on your requirements, that might indeed be a better solution. I'd probably choose to extend PostgreSQL rather than hack what already exists, though. Doing the latter might break something else and you have to remember to add the hack every time you upgrade the server software. That can cause headaches for whoever inherits the system from you unless it's well-documented. By "extend PostgreSQL" do you mean create a custom input_function for timestamp? Are there docs that give hints for replacing the input function of an existing type? Someone else replied similarly, but I'm afraid I'm not familiar enough with PG to decipher it all. Why not the user-defined type with associated user-defined input function? If filtering the data is awkward, then that might be a better way to go. I think I will, when I get to that point. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Date format for bulk copy
Right, I *can* do this. But then I have to build knowledge into that script so it can find each of these date fields (there's like 20 of them across 10 different files) and then update that knowledge each time it changes. In your case that's a reasonable argument against filtering the data with a script. Using a regular expression in the script might reduce or eliminate the need for some of the logic, but then you'd run the risk of reformatting data that shouldn't have been touched. Yes, but : You can have your script make a query in the database to fetch the data types of the fields and then know which ones are to be transformed and how. The script would take as arguments a dump file and a database,schema.table, would read the file and pipe the transformed data into a psql with a COPY FROM stdin command... could save you a lot of work no ? A bonus is that your script can complain if it detects incompatibilities, and be more fool-proof. Plu ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Date format for bulk copy
On Wed, Oct 13, 2004 at 01:32:01PM -0400, David Rysdam wrote: > Michael Fuhr wrote: > >You could filter the data through a script that reformats certain > >fields, then feed the reformatted data to PostgreSQL. This is > >usually a trivial task for Perl, awk, sed, or the like. > > > Right, I *can* do this. But then I have to build knowledge into that > script so it can find each of these date fields (there's like 20 of them > across 10 different files) and then update that knowledge each time it > changes. In your case that's a reasonable argument against filtering the data with a script. Using a regular expression in the script might reduce or eliminate the need for some of the logic, but then you'd run the risk of reformatting data that shouldn't have been touched. > I'm still leaning towards just making postgres accept at ':' > delimiter for milliseconds. Based on your requirements, that might indeed be a better solution. I'd probably choose to extend PostgreSQL rather than hack what already exists, though. Doing the latter might break something else and you have to remember to add the hack every time you upgrade the server software. That can cause headaches for whoever inherits the system from you unless it's well-documented. > Also, how much would a secondary script slow down the bulk copy, > if any? Probably some, but perhaps not enough to be significant. I'd expect the database to be the bottleneck, but I'd have to run tests to say for certain. > >Sounds like Sybase is dumping in hex, whereas PostgreSQL expects > >octal. If you can't change the dump format, then again, filtering > >the data through a script might work. > > > Oh, so I can load binary data into PG if it's ASCII-encoded octal? Yes -- see the "Binary Data Types" documentation: http://www.postgresql.org/docs/7.4/static/datatype-binary.html > Why not the user-defined type with associated user-defined input function? If filtering the data is awkward, then that might be a better way to go. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Date format for bulk copy
Greg Stark wrote:
David Rysdam <[EMAIL PROTECTED]> writes:
In my brute force port, I just bulk copied the date
fields into temporary tables and then did a to_timestamp(field, 'Mon DD
HH:MI:SS:MSAM').
Again, I created a temporary table and did a decode(field, 'hex') to the
real table.
This is the standard approach. You're rather lucky these are the only
data representation changes you've had to do so far. I fear you'll run into
more and more complex changes over time and trying to avoid the temporary
table will get harder and harder.
No, I think I'm OK there. These are programmatically-generated values
and I've already been through them all once. Just the millisecond issue
and the hex binary issue AFAIK.
If it were me I would consider processing the files in perl. It should be
pretty easy to do both of these modifications very quickly.
Very quick and easy to do one time. A little trickier to handle in an
elegant, maintainable way for the dozens of data reloads I do every
month for GBs of data onto two different server types.
If you really want to go with a custom C code then you might be able to just
grab the byteain/byteaout functions from src/backend/util/adt/varlena into a
separate module and create new functions with modified names. Load it with
CREATE FUNCTION byteain ... AS 'my_bytea_funcs.so' 'my_byteain';
Or maybe create the function as my_byteain in postgres and then update the
catalog entries somehow. I'm not sure how to do that but it shouldn't be too
hard. And it might make it easier to do the substitution for the data load and
then undo the change afterwards.
Why not create a type and then define the load function to be the
equivalent of "decode('hex')"?
Doing the same for timmestamp is a bit trickier but you could copy
ParseDateTime from datetime.c as a static function for your module.
Be careful though, test this out thoroughly on a test database. I'm not sure
of all the impacts of altering the in/out functions for data types. I expect
it would break pg_dump, for example. And I would worry about the statistics
tables too.
This is kind of a hybrid of my suggestions and the problems are a hybrid
as well. :)
1) Just change the timestamp type so that it allows a ':' delimiter for
milliseconds. Potential problems: Other parts of the code won't expect
it. People don't want that.
2) Create a new type. Potential problem: Things like date ranges
probably wouldn't work anymore, since the server wouldn't know it's a
date now.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Date format for bulk copy
Michael Fuhr wrote: On Wed, Oct 13, 2004 at 10:06:58AM -0400, David Rysdam wrote: Sybase bulk copies the date fields out in this format: Mar 4 1973 10:28:00:000AM Postgresql's COPY (or psql \copy) doesn't like that format. You could filter the data through a script that reformats certain fields, then feed the reformatted data to PostgreSQL. This is usually a trivial task for Perl, awk, sed, or the like. Right, I *can* do this. But then I have to build knowledge into that script so it can find each of these date fields (there's like 20 of them across 10 different files) and then update that knowledge each time it changes. I'm still leaning towards just making postgres accept at ':' delimiter for milliseconds. Also, how much would a secondary script slow down the bulk copy, if any? I have a similarish problem with another field type. In Sybase it's a binary format. In postgres it is a binary format (bytea). But Sybase bcps the data out in ASCII. Sybase recognizes that when it is a binary field and auto-converts the ASCII back to binary. Postgres doesn't. Again, I created a temporary table and did a decode(field, 'hex') to the real table. Sounds like Sybase is dumping in hex, whereas PostgreSQL expects octal. If you can't change the dump format, then again, filtering the data through a script might work. Oh, so I can load binary data into PG if it's ASCII-encoded octal? Why not the user-defined type with associated user-defined input function? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Date format for bulk copy
On Wed, Oct 13, 2004 at 10:06:58AM -0400, David Rysdam wrote: > Sybase bulk copies the date fields out in this format: > > Mar 4 1973 10:28:00:000AM > > Postgresql's COPY (or psql \copy) doesn't like that format. You could filter the data through a script that reformats certain fields, then feed the reformatted data to PostgreSQL. This is usually a trivial task for Perl, awk, sed, or the like. > I have a similarish problem with another field type. In Sybase it's a > binary format. In postgres it is a binary format (bytea). But Sybase > bcps the data out in ASCII. Sybase recognizes that when it is a binary > field and auto-converts the ASCII back to binary. Postgres doesn't. > Again, I created a temporary table and did a decode(field, 'hex') to the > real table. Sounds like Sybase is dumping in hex, whereas PostgreSQL expects octal. If you can't change the dump format, then again, filtering the data through a script might work. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: 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] Date format for bulk copy
David Rysdam <[EMAIL PROTECTED]> writes: > In my brute force port, I just bulk copied the date > fields into temporary tables and then did a to_timestamp(field, 'Mon DD > HH:MI:SS:MSAM'). > Again, I created a temporary table and did a decode(field, 'hex') to the > real table. This is the standard approach. You're rather lucky these are the only data representation changes you've had to do so far. I fear you'll run into more and more complex changes over time and trying to avoid the temporary table will get harder and harder. If it were me I would consider processing the files in perl. It should be pretty easy to do both of these modifications very quickly. If you really want to go with a custom C code then you might be able to just grab the byteain/byteaout functions from src/backend/util/adt/varlena into a separate module and create new functions with modified names. Load it with CREATE FUNCTION byteain ... AS 'my_bytea_funcs.so' 'my_byteain'; Or maybe create the function as my_byteain in postgres and then update the catalog entries somehow. I'm not sure how to do that but it shouldn't be too hard. And it might make it easier to do the substitution for the data load and then undo the change afterwards. Doing the same for timmestamp is a bit trickier but you could copy ParseDateTime from datetime.c as a static function for your module. Be careful though, test this out thoroughly on a test database. I'm not sure of all the impacts of altering the in/out functions for data types. I expect it would break pg_dump, for example. And I would worry about the statistics tables too. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Date format for bulk copy
I have a large amount of data that I copy in and out of Sybase very often. Now I also want to copy this data in and out of postgres. I have an existing script that creates the entire database(s) from scratch in Sybase and then uses the Sybase bulk copy tool "bcp" to copy the data in. I already did a brute force port of this script to postgres once, but I'm trying to do it more elegantly now that I know what issues I'm going to run into. One of them is date formats in the bcp files. Sybase bulk copies the date fields out in this format: Mar 4 1973 10:28:00:000AM Postgresql's COPY (or psql \copy) doesn't like that format. In particular, it doesn't like the millisecond field at the end. If I understand the docs correctly, postgres wants the millisecond field to be proceeded by a decimal point instead of a colon. In my brute force port, I just bulk copied the date fields into temporary tables and then did a to_timestamp(field, 'Mon DD HH:MI:SS:MSAM'). That worked, but required a lot of additional logic in my script to handle the temp tables and conversions. I'd hate to have to keep all that overhead in there to basically handle a conversion of a colon to a decimal point. So my questions are these: 0) I thought of creating a user-defined data type for this, but it seems like overkill, especially if I'd have to provide all kinds of helper functions for things like date incrementation or comparison or whatever. Am I off track? 1) Are there any tools out there that allow for specifying the field format of a COPY? 2) If not, is it reasonable or unreasonable to modify the postgresql source (I'm running Beta 3) to handle a colon as a millisecond delimiter? (If so, where do I look?) 3) If I did create such a patch, would the postgresql accept it into the tree? I have a similarish problem with another field type. In Sybase it's a binary format. In postgres it is a binary format (bytea). But Sybase bcps the data out in ASCII. Sybase recognizes that when it is a binary field and auto-converts the ASCII back to binary. Postgres doesn't. Again, I created a temporary table and did a decode(field, 'hex') to the real table. It seems reasonable to expect to be able to bulk copy ASCII-encoded binary values into binary fields. Probably this field is best described by a user-defined type? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] date format problem
hi bill Null fields shouldnt be the cause of the problem.. A null text field gets translated to a null datetime.. The problem must be because ur database might be specifying the field to be NOT NULL.. Check it out .. If thats the cause there is no way but to drop that constraint.. Hope this helps Anand Raman - Original Message - From: Bill Barnes <[EMAIL PROTECTED]> To: Anand Raman <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, May 03, 2000 1:51 PM Subject: RE: [GENERAL] date format problem > Hello Anand: > > Thanks for the input. > > The problem turned out to be date fields with null values. Disappointing! > A lot of my Sybase procedures tested for null dates. > > So my workaround was to edit the null dates to a pseudo date. This is least > got my database poplulated. Will try the cast for queries and updates. > > I don't know which SQL, Postresql or Sybase, is an extension or non-compliant. > > Bill Barnes > > >= Original Message From "Anand Raman" <[EMAIL PROTECTED]> = > >HI bill > >Try to cast the field to the datetime type > > > >'ur_string_goes_here'::datetime > > > >Hope this will help > >Anand Raman > > > >- Original Message - > >From: Bill Barnes <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Sent: Friday, April 28, 2000 6:09 PM > >Subject: [GENERAL] date format problem > > > > > >> Hello List: > >> > >> Using 6.5.3 on SuSE 6.4. > >> > >> Built a table with a date field as type 'datetime'. > >> > >> I try to copy a text file to the table and get a 'bad date format' > >> Dates in the text file may be null, but are in the form 1997-12-15. > >> Have tried several of the date field types with the same result. > >> The copy executes properly if I redefine the date field to varchar. > >> > >> Thanks for your help. > >> Bill Barnes > >> > >> > >> This e-mail has been sent to you courtesy of OperaMail, a > >> free web-based service from Opera Software, makers of > >> the award-winning Web Browser - http://www.operasoftware.com > >> > >> > > > This e-mail has been sent to you courtesy of OperaMail, a > free web-based service from Opera Software, makers of > the award-winning Web Browser - http://www.operasoftware.com > >
RE: [GENERAL] date format problem
I dunno, I'm using 6.5.3 and have no probs pgdumping, pguploading and using
null dates - moved my app to another server etc, and the null dates are
still there. The null dates are dumped as \N which presumably means NULL
(but for some reason typing \N (two characters - backslash then N) works
for the postgres superuser default password).
So perhaps you could convert dates you want null to \N.
I'm using perl and DBI, and things work fine - a null date is returned as a
perl undefined value (which is different from zero).
e.g.
if not defined($date) {
$date="unknown";
} else {
$date=htdate($date);
}
Cheerio,
Link.
At 04:21 AM 03-05-2000 -0400, Bill Barnes wrote:
>Hello Anand:
>
>Thanks for the input.
>
>The problem turned out to be date fields with null values. Disappointing!
>A lot of my Sybase procedures tested for null dates.
>
>So my workaround was to edit the null dates to a pseudo date. This is least
>got my database poplulated. Will try the cast for queries and updates.
>
>I don't know which SQL, Postresql or Sybase, is an extension or
non-compliant.
>
>Bill Barnes
>
>>= Original Message From "Anand Raman" <[EMAIL PROTECTED]> =
>>HI bill
>>Try to cast the field to the datetime type
>>
>>'ur_string_goes_here'::datetime
>>
>>Hope this will help
>>Anand Raman
>>
>>----- Original Message -
>>From: Bill Barnes <[EMAIL PROTECTED]>
>>To: <[EMAIL PROTECTED]>
>>Sent: Friday, April 28, 2000 6:09 PM
>>Subject: [GENERAL] date format problem
>>
>>
>>> Hello List:
>>>
>>> Using 6.5.3 on SuSE 6.4.
>>>
>>> Built a table with a date field as type 'datetime'.
>>>
>>> I try to copy a text file to the table and get a 'bad date format'
>>> Dates in the text file may be null, but are in the form 1997-12-15.
>>> Have tried several of the date field types with the same result.
>>> The copy executes properly if I redefine the date field to varchar.
>>>
>>> Thanks for your help.
>>> Bill Barnes
>>>
>>>
>>> This e-mail has been sent to you courtesy of OperaMail, a
>>> free web-based service from Opera Software, makers of
>>> the award-winning Web Browser - http://www.operasoftware.com
>>>
>>>
>
>
>This e-mail has been sent to you courtesy of OperaMail, a
>free web-based service from Opera Software, makers of
>the award-winning Web Browser - http://www.operasoftware.com
>
>
>
>
RE: [GENERAL] date format problem
Hello Anand: Thanks for the input. The problem turned out to be date fields with null values. Disappointing! A lot of my Sybase procedures tested for null dates. So my workaround was to edit the null dates to a pseudo date. This is least got my database poplulated. Will try the cast for queries and updates. I don't know which SQL, Postresql or Sybase, is an extension or non-compliant. Bill Barnes >= Original Message From "Anand Raman" <[EMAIL PROTECTED]> = >HI bill >Try to cast the field to the datetime type > >'ur_string_goes_here'::datetime > >Hope this will help >Anand Raman > >- Original Message - >From: Bill Barnes <[EMAIL PROTECTED]> >To: <[EMAIL PROTECTED]> >Sent: Friday, April 28, 2000 6:09 PM >Subject: [GENERAL] date format problem > > >> Hello List: >> >> Using 6.5.3 on SuSE 6.4. >> >> Built a table with a date field as type 'datetime'. >> >> I try to copy a text file to the table and get a 'bad date format' >> Dates in the text file may be null, but are in the form 1997-12-15. >> Have tried several of the date field types with the same result. >> The copy executes properly if I redefine the date field to varchar. >> >> Thanks for your help. >> Bill Barnes >> >> >> This e-mail has been sent to you courtesy of OperaMail, a >> free web-based service from Opera Software, makers of >> the award-winning Web Browser - http://www.operasoftware.com >> >> This e-mail has been sent to you courtesy of OperaMail, a free web-based service from Opera Software, makers of the award-winning Web Browser - http://www.operasoftware.com
Re: [GENERAL] date format problem
HI bill Try to cast the field to the datetime type 'ur_string_goes_here'::datetime Hope this will help Anand Raman - Original Message - From: Bill Barnes <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, April 28, 2000 6:09 PM Subject: [GENERAL] date format problem > Hello List: > > Using 6.5.3 on SuSE 6.4. > > Built a table with a date field as type 'datetime'. > > I try to copy a text file to the table and get a 'bad date format' > Dates in the text file may be null, but are in the form 1997-12-15. > Have tried several of the date field types with the same result. > The copy executes properly if I redefine the date field to varchar. > > Thanks for your help. > Bill Barnes > > > This e-mail has been sent to you courtesy of OperaMail, a > free web-based service from Opera Software, makers of > the award-winning Web Browser - http://www.operasoftware.com > >
[GENERAL] date format and copy command
Using 6.5.3 on SuSE 6.4 and program kpgsql. Dumped data to a ';' delimited file wherein the date looks like '1997-12-27'. The date field may be null. Created the appropriate table using variously date/datetime/timestamp for data type, set datestyle to ISO/SQL/others. The copy command is copy tablename from '/usr/.../namefile.txt' using delimiters ';'; The pgsql response is 'bad date format'. Recommendations, please. Thanks, Bill Barnes
[GENERAL] Date format
Someone posted a message here asking how you set the date style. We went
through
- environment variable PGDATESTYLE
- SET DateStyle TO
- the -e flag to the backend
but of course if you are using ISO it isn't necessary!! There is no
ambiguity in
test=> create table tab (t datetime);
CREATE
test=> insert into tab values('2000-1-2');
INSERT 416553 1
test=> select * from tab;
t
--
Sun 02 Jan 00:00:00 2000 GMT
(1 row)
The -e exists because there would be ambiguity between European/US, but with
year first, it must be -mm-dd (at least for sufficiently large years).
Sorry I can't remember your email address.
Cheers,
Patrick
