Thread: psql patch for datestyle
At present, dates are put into a dump in the format specified by the
default datestyle. This is not portable between installations.
This patch sets DATESTYLE to ISO at the start of a pg_dump, so that the
dates written into the dump will be restorable onto any database,
regardless of how its default datestyle is set.
Index: pg_dump.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.317
diff -u -r1.317 pg_dump.c
--- pgsql-orig/src/bin/pg_dump/pg_dump.c 2003/02/13 04:54:16 1.317
+++ pgsql/src/bin/pg_dump/pg_dump.c 2003/03/03 06:22:34
@@ -546,6 +546,13 @@
PQerrorMessage(g_conn));
PQclear(res);
+ /* Set the datestyle to ISO to ensure the dump's portability */
+ res = PQexec(g_conn, "SET DATESTYLE = ISO");
+ if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
+ exit_horribly(g_fout, NULL, "could not set datestyle to
ISO: %s",
+ PQerrorMessage(g_conn));
+ PQclear(res);
+
/*
* If supported, set extra_float_digits so that we can dump
float data
* exactly (given correctly implemented float I/O code, anyway)
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"A new commandment I give to you, that you love one
another, even as I have loved you."
John 13:34
Oliver Elphick <olly@lfix.co.uk> writes:
> This patch sets DATESTYLE to ISO at the start of a pg_dump, so that the
> dates written into the dump will be restorable onto any database,
> regardless of how its default datestyle is set.
I have forgotten: will an ISO-style date be read in correctly regardless
of the datestyle setting at load time? Or do we need to make pg_dump
issue SET DATESTYLE in the dump script as well?
regards, tom lane
On Mon, 2003-03-03 at 14:32, Tom Lane wrote: > Oliver Elphick <olly@lfix.co.uk> writes: > > This patch sets DATESTYLE to ISO at the start of a pg_dump, so that the > > dates written into the dump will be restorable onto any database, > > regardless of how its default datestyle is set. > > I have forgotten: will an ISO-style date be read in correctly regardless > of the datestyle setting at load time? Or do we need to make pg_dump > issue SET DATESTYLE in the dump script as well? I tested it by changing the datestyle in postgresql.conf to postgres,us and it read in OK there. On input, any recognisable date format can be used, but where there is ambiguity between US and European style, there can be problems. With ISO format there can be no ambiguity. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C ======================================== "A new commandment I give to you, that you love one another, even as I have loved you." John 13:34
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Oliver Elphick wrote:
> At present, dates are put into a dump in the format specified by the
> default datestyle. This is not portable between installations.
>
> This patch sets DATESTYLE to ISO at the start of a pg_dump, so that the
> dates written into the dump will be restorable onto any database,
> regardless of how its default datestyle is set.
>
> Index: pg_dump.c
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
> retrieving revision 1.317
> diff -u -r1.317 pg_dump.c
> --- pgsql-orig/src/bin/pg_dump/pg_dump.c 2003/02/13 04:54:16 1.317
> +++ pgsql/src/bin/pg_dump/pg_dump.c 2003/03/03 06:22:34
> @@ -546,6 +546,13 @@
> PQerrorMessage(g_conn));
> PQclear(res);
>
> + /* Set the datestyle to ISO to ensure the dump's portability */
> + res = PQexec(g_conn, "SET DATESTYLE = ISO");
> + if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
> + exit_horribly(g_fout, NULL, "could not set datestyle to
> ISO: %s",
> + PQerrorMessage(g_conn));
> + PQclear(res);
> +
> /*
> * If supported, set extra_float_digits so that we can dump
> float data
> * exactly (given correctly implemented float I/O code, anyway)
>
>
>
> --
> Oliver Elphick Oliver.Elphick@lfix.co.uk
> Isle of Wight, UK http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
> ========================================
> "A new commandment I give to you, that you love one
> another, even as I have loved you."
> John 13:34
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- Oliver Elphick wrote: > At present, dates are put into a dump in the format specified by the > default datestyle. This is not portable between installations. > > This patch sets DATESTYLE to ISO at the start of a pg_dump, so that the > dates written into the dump will be restorable onto any database, > regardless of how its default datestyle is set. > > Index: pg_dump.c > =================================================================== > RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v > retrieving revision 1.317 > diff -u -r1.317 pg_dump.c > --- pgsql-orig/src/bin/pg_dump/pg_dump.c 2003/02/13 04:54:16 1.317 > +++ pgsql/src/bin/pg_dump/pg_dump.c 2003/03/03 06:22:34 > @@ -546,6 +546,13 @@ > PQerrorMessage(g_conn)); > PQclear(res); > > + /* Set the datestyle to ISO to ensure the dump's portability */ > + res = PQexec(g_conn, "SET DATESTYLE = ISO"); > + if (!res || PQresultStatus(res) != PGRES_COMMAND_OK) > + exit_horribly(g_fout, NULL, "could not set datestyle to > ISO: %s", > + PQerrorMessage(g_conn)); > + PQclear(res); > + > /* > * If supported, set extra_float_digits so that we can dump > float data > * exactly (given correctly implemented float I/O code, anyway) > > > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight, UK http://www.lfix.co.uk/oliver > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "A new commandment I give to you, that you love one > another, even as I have loved you." > John 13:34 > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073