[BUGS] Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.
Hi to all,
I have a problem storing 1973/06/03 date.
If I send this statement
select to_date('03/06/1973','dd/mm/');
in the psql interface I obtain
to_date
1973-06-02
I test this statement with Postgres 7.3.2 and 7.3.4 packaged withMandrake 9.1
and Mandrake 9.2RC1 and obtain the same result.
Can anyone help me?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.
On my 7.4 this select works fine
testdb011=> select to_date('03/06/1973','dd/mm/');
to_date
1973-06-03
Pavel
On Wed, 10 Sep 2003, Torello Querci wrote:
> Hi to all,
>
> I have a problem storing 1973/06/03 date.
>
> If I send this statement
>
> select to_date('03/06/1973','dd/mm/');
>
> in the psql interface I obtain
>
> to_date
>
> 1973-06-02
>
> I test this statement with Postgres 7.3.2 and 7.3.4 packaged withMandrake 9.1
> and Mandrake 9.2RC1 and obtain the same result.
>
> Can anyone help me?
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.
On Wed, Sep 10, 2003 at 12:52:22PM +0200, Torello Querci wrote:
> Hi to all,
>
> I have a problem storing 1973/06/03 date.
>
> If I send this statement
>
> select to_date('03/06/1973','dd/mm/');
>
> in the psql interface I obtain
>
> to_date
>
> 1973-06-02
>
> I test this statement with Postgres 7.3.2 and 7.3.4 packaged withMandrake 9.1
> and Mandrake 9.2RC1 and obtain the same result.
>
> Can anyone help me?
What's happen if you try:
test=# select '03/06/1973'::date;
date
1973-06-03
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[BUGS] Foreign key constraint still active after table row removed
POSTGRESQL BUG REPORT TEMPLATE Your name : Christoph Jäger Your email address : christoph.jaeger ( at ) dhl ( dot ) com System Configuration - Architecture (example: Intel Pentium) : Intel Celeron 800 Mhz Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.10 PostgreSQL version (example: PostgreSQL-7.3.4): postgresql 7.1.3 Compiler used (example: gcc 2.95.2) : unknown Please enter a FULL description of your problem: I get the following error-message when trying to update a row in one of my tables: > update systemcode set name='48h' where id=740; ERROR: constraint : table orgunit does not have an attribute country_code_sc There are two tables involved: CREATE TABLE systemcode ( id INT4 NOT NULL PRIMARY KEY, typeTEXT NOT NULL, nameTEXT NOT NULL, description TEXT, deleted BOOLEAN ); CREATE TABLE orgunit ( id INT4 NOT NULL PRIMARY KEY, codeTEXT NOT NULL, iata_code TEXT NOT NULL, nameTEXT NOT NULL, description TEXT NOT NULL, street TEXT, cityTEXT, zip TEXT, country_code_sc INT4, operating_hours TEXT, deleted BOOLEAN, FOREIGN KEY (country_code_sc) REFERENCES systemcode(id) ); This is how the tables were originally created. Both were filled with data. Then I decided to drop some columns (also the column country_code_sc) from the orgunit table: BEGIN; CREATE TABLE temp AS SELECT id, code, name, description, operating_hours, location_id, deleted FROM orgunit; ALTER TABLE orgunit RENAME TO orgunit_old; DROP INDEX orgunit_pkey; CREATE TABLE orgunit ( id INT4 NOT NULL PRIMARY KEY, codeTEXT NOT NULL, nameTEXT NOT NULL, description TEXT NOT NULL, operating_hours TEXT, location_id INT4, deleted BOOLEAN, FOREIGN KEY (location_id) REFERENCES location(id) ); CREATE UNIQUE INDEX orgunit_code_idx ON orgunit (code); INSERT INTO orgunit SELECT * FROM temp; DROP TABLE temp; COMMIT; This worked fine, until I found out, that I can no longer issue UPDATE statements for the systemcode table: > update systemcode set name='48h' where id=740; ERROR: constraint : table orgunit does not have an attribute country_code_sc It seems the old constraint used for the foreign key (orgunit.country_code_sc -> systemcode.id) was not removed when I change the orgunit table structure. The orgunit table no longer has a country_code_sc field, but the constraint still wants to check it. I did this on my development machine, running postgresql 7.1.3. Before I can issue the table structure change on the production machine also, I need to know how I can resolve this problem. On this production machine I run postgres 7.2.3. Maybe this problem is already fixed on this version, but I do not want to try it out and leave my production system in an inconsistent state in case it does not work. The table pg_trigger shows three rows, which seem to point to this no longer valid constraint, but I do not think it is a good idea to fiddle with this unless one really knows how this all works together. I found references to similar problems, and the solution was something like dropping the table, recreate it and fill it with data again. The problem here is, the orgunit table was already recreated (and this seemed to start my problems), and the systemcode table is used as foreign key in a lot of other tables, and I do not really want to recreate all the other foreign key constraints after recreating the systemcode table. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- The problem may be reproduced by following the steps described above. If you know how this problem might be fixed, list the solution below: - Sorry, I do not know how the problem may be fixed. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.
Am Mittwoch, 10. September 2003 12:52 schrieb Torello Querci:
> Hi to all,
>
> I have a problem storing 1973/06/03 date.
>
> If I send this statement
>
> select to_date('03/06/1973','dd/mm/');
>
> in the psql interface I obtain
>
> to_date
>
> 1973-06-02
>
> I test this statement with Postgres 7.3.2 and 7.3.4 packaged withMandrake
> 9.1 and Mandrake 9.2RC1 and obtain the same result.
>
> Can anyone help me?
Could be Mandrake or compiler problem.
# select version();
version
---
PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
# select to_date('03/06/1973','dd/mm/');
to_date
1973-06-03
(1 row)
This is on Debian woody with backported postgres from testing.
HTH,
Daniel
--
Daniel Schreiber | ICQ: 220903493
GPG encrypted Mail welcome! Key ID: 25A6B489
Chemnitzer Linux-Tag:
http://www.tu-chemnitz.de/linux/tag/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.
Torello Querci <[EMAIL PROTECTED]> writes:
> If I send this statement
> select to_date('03/06/1973','dd/mm/');
> in the psql interface I obtain
> to_date
>
> 1973-06-02
What timezone are you in? What do you get from
select to_timestamp('03/06/1973','dd/mm/');
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] help with mac osx 10.2.6
Tom, I got a reply back from someone on the apple developers list. > gcc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes ^^^ This is wrong. You need to use -no-cpp-precomp. It says this in the release notes for the December tools updater, as well as a number of other things... if you haven't read the release notes carefully, then I would read them again if I were you. so I tried make -no-cpp-precomp it started the make and failed here: make[3]: *** No rule to make target `../../../src/interfaces/libpq/libpq.a', needed by `pg_dump'. Stop. make[2]: *** [all] Error 2 any ideas... do you have an ETA for the return of your mac... we need you Ted --- Tom Lane <[EMAIL PROTECTED]> wrote: > Theodore Petrosky <[EMAIL PROTECTED]> writes: > > any ideas... i believe the problem lies with the > > december 2002 update to the developers tools. > > There is some recent traffic in our archives > suggesting that Apple's > gcc 3.3 is busted. I have not been able to check > this out because > my Powerbook is in the shop (still!?!?! snif). > Please feel free to > investigate and propose a workaround, if there is > one. > > regards, tom lane __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Foreign key constraint still active after table row removed
"Christoph Jaeger" <[EMAIL PROTECTED]> writes: > PostgreSQL version (example: PostgreSQL-7.3.4): postgresql 7.1.3 7.1.3 is ancient history, and no it doesn't have defenses against you changing a column definition that a foreign key linkage refers to. I'd recommend updating to 7.3.4. > The table pg_trigger shows three rows, which seem to point to this no > longer valid constraint, but I do not think it is a good idea to fiddle > with this unless one really knows how this all works together. In 7.1, drop the triggers and you're done. AFAIR this would also be necessary in 7.2. In 7.3 you could have just dropped the columns you wanted to drop, and not had all these problems. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.
I think this is what you wanted.
select to_char('03/06/1973'::date,'dd/mm/');
you were casting the string to a date and returning a
date. so it would return it in a format that your
system is set to return.
agencysacks=# select
to_char('03/06/1973'::date,'dd/mm/');
to_char
06/03/1973
(1 row)
Ted
--- Karel Zak <[EMAIL PROTECTED]> wrote:
> On Wed, Sep 10, 2003 at 12:52:22PM +0200, Torello
> Querci wrote:
> > Hi to all,
> >
> > I have a problem storing 1973/06/03 date.
> >
> > If I send this statement
> >
> > select to_date('03/06/1973','dd/mm/');
> >
> > in the psql interface I obtain
> >
> > to_date
> >
> > 1973-06-02
> >
> > I test this statement with Postgres 7.3.2 and
> 7.3.4 packaged withMandrake 9.1
> > and Mandrake 9.2RC1 and obtain the same result.
> >
> > Can anyone help me?
>
> What's happen if you try:
>
> test=# select '03/06/1973'::date;
> date
>
> 1973-06-03
>
>
> --
> Karel Zak <[EMAIL PROTECTED]>
> http://home.zf.jcu.cz/~zakkr/
>
> ---(end of
> broadcast)---
> TIP 7: don't forget to increase your free space map settings
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] the OS X december update saga....
If anyone is interested, the only solution I have found is not use it. I refer to the december 2002 update to OS X developers tools which includes the gcc 3.3 compiler. the solution if you have installed it is to set your mac back to 3.1. su gcc_select 3.1 su postgres ./configure (with what you want) make ... etc. i'm sure when Tom gets his mac back from the shop (he obviously didn't send it to tekserve in NY) we will see his magic to fix this issue Ted __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.
This is not a bug, but simply due to postgres date formatting. Observe
thus:
gbi=# set datestyle to 'iso';
SET
gbi=# select to_date('03/06/1973','dd/mm/');
to_date
1973-06-03
(1 row)
gbi=# set datestyle to 'us';
SET
gbi=# select to_date('03/06/1973','dd/mm/');
to_date
1973-06-03
(1 row)
gbi=# set datestyle to 'sql';
SET
gbi=# select to_date('03/06/1973','dd/mm/');
to_date
06/03/1973
(1 row)
datestyle can be permanently set in postgresql.conf, or by the
environment variable 'PG_DATESTYLE';
On Wed, 2003-09-10 at 03:52, Torello Querci wrote:
> Hi to all,
>
> I have a problem storing 1973/06/03 date.
>
> If I send this statement
>
> select to_date('03/06/1973','dd/mm/');
>
> in the psql interface I obtain
>
> to_date
>
> 1973-06-02
>
> I test this statement with Postgres 7.3.2 and 7.3.4 packaged withMandrake 9.1
> and Mandrake 9.2RC1 and obtain the same result.
>
> Can anyone help me?
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
--
Jord Tanner <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] Date 1973/06/03 Conversion Problem in 7.3.4 and 7.3.2.
Jord Tanner <[EMAIL PROTECTED]> writes: > This is not a bug, but simply due to postgres date formatting. Yes, it's a bug, but it's specific to the Europe/Rome timezone setting. I've not yet had a chance to figure out the details. regards, tom lane ---(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
