Re: [GENERAL] Wrong string length from unicode database in Borland's app
TL Alex Guryanow [EMAIL PROTECTED] writes: When pg-server is version 7.1.3 windows app works fine, but when pg-server is version 7.4.6 or 8.0beta4 under certain conditions the app receives strings with wrong lengths. TL Are both servers set up with the same database encoding? I think the answer is yes. For both servers the command initdb was executed only with parameter DATADIR. At the same time the locale is set up to ru_RU.cp1251. But by creating the database I specify parameter -E UNICODE and psql -l shows that the database is in UNICODE encoding. One time I have forgotten to specify '-E UNICODE' by executing createdb and windows app worked fine with 7.4.6 TL (Is the 7.1 TL server even compiled to support non-ASCII encodings?) Here is the fragment of config.status from 7.1.3 source directory ./configure --prefix=/db/pgsql-713 --enable-locale --enable-multibyte --with-perl But by executing the query select volume, trim(number) from issue where mag_id = 25403; the datagrid component (that displays query's results) contains in second column values with length of 32769. TL If you try the same query in plain psql, what do you get? I get all ok. For example, the query select volume, length( trim( number ) ) from issue where mag_id = 25403; shows in second column values from 5 to 7 TL What is in TL the wrong-length value, exactly? 'N 1-2' The appropriate volume column contains 'Evf. 120' where 'E' is 'E with ascent' (I don't know how to write them in this letter). pg_dump writes the following sequence of bytes (in hex-format) for this value: C3 89 76 66 2E 20 31 32 30 and 'N 1-2' is 4E 20 31 2D 32 Best regards, Alex TL regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to make a good documentation of a database ?
Yes, it seems interesting. (Is import of sql database possible ?) But for now, I'll stick to postgresql_autodoc. Thanks all the same, Bill, I'll try to keep an eye on this project. On Thu, Nov 18, 2004 at 04:55:06PM +, Bill Harris wrote: [EMAIL PROTECTED] (David Pradier) writes: I'd like to make it a little more orthodox (lots and lots of constraints, yeah !!), but I need a tool to make a documentation about every column, at least, as some column are really vicious (like, they are a foreign key to a table which depends on the type of another column...). Would Druid (http://druid.sourceforge.net/) help? Bill -- Bill Harris Facilitated Systems http://facilitatedsystems.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- [EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.45.20.17.98 ---(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] infinite recursion detected in rules for relation ...
Tom Lane wrote: =?ISO-8859-1?Q?Sebastian_B=F6ck?= [EMAIL PROTECTED] writes: infinite recursion detected in rules for relation ... If you need a patch immediately, here it is. *** src/backend/rewrite/rewriteHandler.c.orig Sat Nov 6 12:46:35 2004 --- src/backend/rewrite/rewriteHandler.c Sat Nov 20 12:47:21 2004 *** *** 1267,1272 --- 1267,1274 newstuff = RewriteQuery(pt, rewrite_events); rewritten = list_concat(rewritten, newstuff); } + + rewrite_events = list_delete_first(rewrite_events); } } regards, tom lane Thanks for the quick patch! Everything is working now. Sebastian ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] COMMIT within function?
On Sun, 21 Nov 2004 20:10:03 -0700, Michael Fuhr [EMAIL PROTECTED] wrote: http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING BEGIN DELETE FROM values WHERE value_id = r.value_id; EXCEPTION WHEN foreign_key_violation THEN NULL; END; Ahh, exactly what I was looking for. :) The thing I didn't notice was that, while exception causes rollback to BEGIN, it does not mean to the beginning of the function. In other words I didn't nest BEGIN...END blocks and all I got from using exceptions was that they did not show any errors. :) Thank you! I am now enlightened. This works perfect, exactly as I hoped it would. :) Regards, dawid ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] How to handle larger databases?
Yes, I would like to hear about this as well, especially since all my character strings are defined as varchar. On Monday 22 November 2004 02:09 am, Patrick B Kelly saith: On Nov 19, 2004, at 2:37 AM, Jerry III wrote: Do not use variable length types. Why do you suggest not using variable length types? Patrick B. Kelly -- http://patrickbkelly.org ---(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 -- Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to handle larger databases?
On Mon, Nov 22, 2004 at 02:09:49AM -0500, Patrick B Kelly wrote: On Nov 19, 2004, at 2:37 AM, Jerry III wrote: Do not use variable length types. Why do you suggest not using variable length types? Especially since PostgreSQL has no fixed length string types, so following that advice would exclude any strings. That's kind of useless. -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpvf7IqjJhFV.pgp Description: PGP signature
Re: [GENERAL] Delete very slow after deletion of many rows in dependent
Hi Stephan, caching of the execution plan is a good hint. We'll try it in a new connection. Best Regards Cornelius Stephan Szabo wrote: On Sun, 21 Nov 2004, Cornelius Buschka wrote: Hi, we saw the following problem: We deleted all rows from a table B referencing table A (~50 records). No problem, but the following try to delete all records from table A (~18) lead to a never ending statement. We found out, that vacuuming table B after delete did the trick. It seems to us the database has to do scan thru deleted records on B while deleting from A. Why did it last so long? An index on B.a_fk did not lead to imporvements. The query plan did not help. An index seems to help for me. It's still kinda slow, but the real time for the delete on A goes from more minutes than I was willing to wait to about 19s. However, if you'd already run the key without the index, refilled the table, made the index and tried it again, it probably wouldn't have used the index because it tries to cache the plan on first use in each session (you'd need to start a new session to try again). -- Cornelius Buschka arcus(x) GmbH Hein-Hoyer-Straße 75 fon: +49 (0)40.333 102 92 D-20359 Hamburg fax: +49 (0)40.333 102 93 http://www.arcusx.commailto:[EMAIL PROTECTED] ---(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] How to handle larger databases?
Especially since PostgreSQL has no fixed length string types, so following that advice would exclude any strings. That's kind of useless. char(n) ? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to handle larger databases?
On Mon, Nov 22, 2004 at 11:33:35AM +, Matt wrote: Especially since PostgreSQL has no fixed length string types, so following that advice would exclude any strings. That's kind of useless. char(n) ? Is not fixed length. The actual size varies by encoding. Consider the string: zeeën Latin-9 5 bytes UTF-8 6 bytes UTF-16 10 bytes But it should still fit in a char(5), wouldn't you agree? In postgresql there is no difference in storage method between text, varchar(n) and char(n). -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpQHknaAF0CO.pgp Description: PGP signature
[GENERAL] Oid to text...
Hi, Given the Oid restype of a Resdom object, is there any system table I could query in order to retrieve the text representation of this type? If for example a Resdom restype is 23 how can I get the string integer or something like that? Are these mappings stored anyware? Thanks in advance, Ntinos Katsaros PS: I searched the archives but my queries did not give anything... ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Oid to text...
On 22 Nov 2004 14:25:26 +0200, Katsaros Kwn/nos [EMAIL PROTECTED] wrote: Hi, Given the Oid restype of a Resdom object, is there any system table I could query in order to retrieve the text representation of this type? If for example a Resdom restype is 23 how can I get the string integer or something like that? Are these mappings stored anyware? pg_catalog.pg_type ? Ian Barwick ---(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] Tsearch2 and Unicode?
Hi!
I dug through my list-archives - I actually used to have the very same problem
that you described: special chars being swallowed by tsearch2-functions. The
source of the problem was that I had INITDB'ed my cluster with [EMAIL
PROTECTED] as locale, whereas my databases used Unicode encoding. This does not
work correctly. I had to dump, initdb to the correct UTF-8-locale (de_DE.UTF-8
in my case) and reload to get tsearch2 to work correctly. You may find the
original discussion here:
http://archives.postgresql.org/pgsql-general/2004-07/msg00620.php
If you wish to find out which locale was used during INITDB for your cluster,
you may use the pg_controldata program that's supplied with PostgreSQL.
Kind regards
Markus
-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von
Dawid Kuroczko
Gesendet: Mittwoch, 17. November 2004 17:17
An: Pgsql General
Betreff: [GENERAL] Tsearch2 and Unicode?
I'm trying to use tsearch2 with database which is in
'UNICODE' encoding.
It works fine for English text, but as I intend to search
Polish texts I did:
insert into pg_ts_cfg('default_polish', 'default',
'pl_PL.UTF-8'); (and I updated other pg_ts_* tables as
written in manual).
However, Polish-specific chars are being eaten alive, it seems.
I.e. doing select to_tsvector('default_polish', body) from
messages; results in list of words but with national chars stripped...
I wonder, am I doing something wrong, or just tsearch2
doesn't grok Unicode, despite the locales setting? This also
is a good question regarding ispell_dict and its feelings
regarding Unicode, but that's another story.
Assuming Unicode unsupported means I should perhaps... oh,
convert the data to iso8859 prior feeding it to_tsvector()...
interesting idea, but so far I have failed to actually do
it. Maybe store the data as 'bytea' and add a column with
encoding information (assuming I don't want to recreate whole
database with new encoding, and that I want to use unicode
for some columns (so I don't have to keep encoding with every
text everywhere...).
And while we are at it, how do you feel -- an extra column
with tsvector and its index -- would it be OK to keep it away
from my data (so I can safely get rid of them if need be)?
[ I intend to keep index of around 2 000 000 records, few KBs
of text each ]...
Regards,
Dawid Kuroczko
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(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] How to handle larger databases?
Latin-9 5 bytes UTF-8 6 bytes UTF-16 10 bytes But it should still fit in a char(5), wouldn't you agree? Got you. In postgresql there is no difference in storage method between text, varchar(n) and char(n). Learn something new every day. Thanks! Matt ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Tsearch2 and Unicode?
Markus,
it'd be nice if you (or somebody) wrtite a note about unicode, so it
could be added to tsearch2 documentation. It will help people and save
time and hair :)
Oleg
On Mon, 22 Nov 2004, Markus Wollny wrote:
Hi!
I dug through my list-archives - I actually used to have the very same problem
that you described: special chars being swallowed by tsearch2-functions. The
source of the problem was that I had INITDB'ed my cluster with [EMAIL
PROTECTED] as locale, whereas my databases used Unicode encoding. This does not
work correctly. I had to dump, initdb to the correct UTF-8-locale (de_DE.UTF-8
in my case) and reload to get tsearch2 to work correctly. You may find the
original discussion here:
http://archives.postgresql.org/pgsql-general/2004-07/msg00620.php
If you wish to find out which locale was used during INITDB for your cluster,
you may use the pg_controldata program that's supplied with PostgreSQL.
Kind regards
Markus
-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von
Dawid Kuroczko
Gesendet: Mittwoch, 17. November 2004 17:17
An: Pgsql General
Betreff: [GENERAL] Tsearch2 and Unicode?
I'm trying to use tsearch2 with database which is in
'UNICODE' encoding.
It works fine for English text, but as I intend to search
Polish texts I did:
insert into pg_ts_cfg('default_polish', 'default',
'pl_PL.UTF-8'); (and I updated other pg_ts_* tables as
written in manual).
However, Polish-specific chars are being eaten alive, it seems.
I.e. doing select to_tsvector('default_polish', body) from
messages; results in list of words but with national chars stripped...
I wonder, am I doing something wrong, or just tsearch2
doesn't grok Unicode, despite the locales setting? This also
is a good question regarding ispell_dict and its feelings
regarding Unicode, but that's another story.
Assuming Unicode unsupported means I should perhaps... oh,
convert the data to iso8859 prior feeding it to_tsvector()...
interesting idea, but so far I have failed to actually do
it. Maybe store the data as 'bytea' and add a column with
encoding information (assuming I don't want to recreate whole
database with new encoding, and that I want to use unicode
for some columns (so I don't have to keep encoding with every
text everywhere...).
And while we are at it, how do you feel -- an extra column
with tsvector and its index -- would it be OK to keep it away
from my data (so I can safely get rid of them if need be)?
[ I intend to keep index of around 2 000 000 records, few KBs
of text each ]...
Regards,
Dawid Kuroczko
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(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
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Help with syntax for timestamp addition
New to Postgres 7.3 from 7.0. Am having some trouble with a query that worked in 7.0 but not in 7.3.can't seem to figure out the syntax or find info about how to do this anywhere. Consider for the following query: - 'number' is an integer - 'procedures' is the table name - 'date' is a timestamp - 'numdays' is an integer SELECT number FROM procedures WHERE date + numdays = CURRENT_TIMESTAMP; In 7.0 this works with no problem...finding all rows where the date plus some number of days is less than the current. But in 7.3 I get: ERROR: Unable to identify an operator '+' for types 'timestamp without time zone' and 'integer' You will have to retype this query using an explicit cast I've never had to create casts before so I'm not too sure how to work this casting into the querykeep getting various syntax errors no matter what I try. If I try to incorporate intervals, I also get errors. I just can't seem to find good examples in any documentation. Any help is appreciated. -Scott ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to handle larger databases?
Matt wrote: Latin-9 5 bytes UTF-8 6 bytes UTF-16 10 bytes But it should still fit in a char(5), wouldn't you agree? Got you. In postgresql there is no difference in storage method between text, varchar(n) and char(n). Learn something new every day. Thanks! So that would say the previous statements are not accurate? That is, there's no problem with using a varchar? -- Until later, Geoffrey ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Help with syntax for timestamp addition
Have a try at this syntax SELECT number FROM procedures WHERE date + CAST( numdays || ' days' AS interval ) = CURRENT_TIMESTAMP; Patrick -- - Patrick Fiche email : [EMAIL PROTECTED] tél : 01 69 29 36 18 -- - -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Scott Nixon Sent: lundi 22 novembre 2004 14:56 To: [EMAIL PROTECTED] Subject: [GENERAL] Help with syntax for timestamp addition New to Postgres 7.3 from 7.0. Am having some trouble with a query that worked in 7.0 but not in 7.3.can't seem to figure out the syntax or find info about how to do this anywhere. Consider for the following query: - 'number' is an integer - 'procedures' is the table name - 'date' is a timestamp - 'numdays' is an integer SELECT number FROM procedures WHERE date + numdays = CURRENT_TIMESTAMP; In 7.0 this works with no problem...finding all rows where the date plus some number of days is less than the current. But in 7.3 I get: ERROR: Unable to identify an operator '+' for types 'timestamp without time zone' and 'integer' You will have to retype this query using an explicit cast I've never had to create casts before so I'm not too sure how to work this casting into the querykeep getting various syntax errors no matter what I try. If I try to incorporate intervals, I also get errors. I just can't seem to find good examples in any documentation. Any help is appreciated. -Scott ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Protected by Polesoft Lockspam http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Help with syntax for timestamp addition
Have a try at this syntax SELECT number FROM procedures WHERE date + CAST( numdays || ' days' AS interval ) = CURRENT_TIMESTAMP; Patrick Cool! Thanksthat works perfectly. -Scott ---(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] Help with syntax for timestamp addition
On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche [EMAIL PROTECTED] wrote: Have a try at this syntax SELECT number FROM procedures WHERE date + CAST( numdays || ' days' AS interval ) = CURRENT_TIMESTAMP; Just for the record you could write it like this too: SELECT number FROM procedures WHERE date + (numdays || ' days')::interval = CURRENT_TIMESTAMP; Ian Barwick ---(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] Help with syntax for timestamp addition
Ian Barwick wrote: On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche [EMAIL PROTECTED] wrote: Have a try at this syntax SELECT number FROM procedures WHERE date + CAST( numdays || ' days' AS interval ) = CURRENT_TIMESTAMP; Just for the record you could write it like this too: SELECT number FROM procedures WHERE date + (numdays || ' days')::interval = CURRENT_TIMESTAMP; Just to add to the record, the mathematically sound way to write this query would be this: SELECT number FROM procedures WHERE date + numdays * interval '1 day' = current_timestamp; -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Help with syntax for timestamp addition
Just to add to the record, the mathematically sound way to write this query would be this: SELECT number FROM procedures WHERE date + numdays * interval '1 day' = current_timestamp; Thanks for that Peter! That's a lot closer than what I originally had...I didn't think about doing that but it makes sense. Is there any advantage/disadvantages to using this method or the other? On Mon, 2004-11-22 at 10:26, Peter Eisentraut wrote: Ian Barwick wrote: On Mon, 22 Nov 2004 15:12:26 +0100, Patrick Fiche [EMAIL PROTECTED] wrote: Have a try at this syntax SELECT number FROM procedures WHERE date + CAST( numdays || ' days' AS interval ) = CURRENT_TIMESTAMP; Just for the record you could write it like this too: SELECT number FROM procedures WHERE date + (numdays || ' days')::interval = CURRENT_TIMESTAMP; Just to add to the record, the mathematically sound way to write this query would be this: SELECT number FROM procedures WHERE date + numdays * interval '1 day' = current_timestamp; -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Wrong string length from unicode database in Borland's app
Alex Guryanow [EMAIL PROTECTED] writes: When pg-server is version 7.1.3 windows app works fine, but when pg-server is version 7.4.6 or 8.0beta4 under certain conditions the app receives strings with wrong lengths. TL If you try the same query in plain psql, what do you get? I get all ok. In that case it would seem to be an ODBC driver issue. Unfortunately it looks like you mistyped the pgsql-odbc mailing list address; I'd suggest reposting the details over there. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Storing every scrabble board possible
Here's an interesting discussing about storing every possible scrabble board: http://www.livejournal.com/users/stenz/117914.html Note that PostgreSQL ends up being 12x larger than a theoretical custom storage format, which isn't too bad considering the gymnastics going on in the custom storage format. Also note the author's original brute-force method is 5x larger than an improved method using PostgreSQL. So, is anyone currently running a PostgreSQL database that's 6TB and 150B rows? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(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] Oid to text...
Katsaros Kwn/nos [EMAIL PROTECTED] writes: Given the Oid restype of a Resdom object, is there any system table I could query in order to retrieve the text representation of this type? If you're talking about C code inside the backend, format_type_be() is the usual subroutine. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Help with syntax for timestamp addition
Scott Nixon [EMAIL PROTECTED] writes: Am having some trouble with a query that worked in 7.0 but not in 7.3.can't seem to figure out the syntax or find info about how to do this anywhere. SELECT number FROM procedures WHERE date + numdays = CURRENT_TIMESTAMP; In 7.0 this works with no problem... (Tries it...) The reason it worked in 7.0 was that 7.0 would implicitly down-convert the timestamp value to a value of type date, and then apply the date-plus-integer operator. The operator is still there, but later versions are less willing to apply information-losing type coercions implicitly. So the exact equivalent of what you were doing before is ... WHERE CAST(date AS date) + numdays = CURRENT_TIMESTAMP; The comparison portion of this will require an up-conversion from date back to timestamp, which is inefficient and pointless (not to mention that it exposes you to daylight-savings-transition issues, because CURRENT_TIMESTAMP is timestamp with time zone). So I think what you probably *really* want is ... WHERE CAST(date AS date) + numdays = CURRENT_DATE; which keeps both the addition and the comparison as simple date operations with no sub-day resolution and no timezone funnies. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Help with syntax for timestamp addition
So I think what you probably *really* want is ... WHERE CAST(date AS date) + numdays = CURRENT_DATE; Thanks Tom! Yeah, I guess you are right on that point. I hadn't thought about that. The implementation of this wouldn't be affected since this query is buried in a script that runs out of cron once a day, but I suppose I might as well do it right if I'm going to do it. On Mon, 2004-11-22 at 11:31, Tom Lane wrote: Scott Nixon [EMAIL PROTECTED] writes: Am having some trouble with a query that worked in 7.0 but not in 7.3.can't seem to figure out the syntax or find info about how to do this anywhere. SELECT number FROM procedures WHERE date + numdays = CURRENT_TIMESTAMP; In 7.0 this works with no problem... (Tries it...) The reason it worked in 7.0 was that 7.0 would implicitly down-convert the timestamp value to a value of type date, and then apply the date-plus-integer operator. The operator is still there, but later versions are less willing to apply information-losing type coercions implicitly. So the exact equivalent of what you were doing before is ... WHERE CAST(date AS date) + numdays = CURRENT_TIMESTAMP; The comparison portion of this will require an up-conversion from date back to timestamp, which is inefficient and pointless (not to mention that it exposes you to daylight-savings-transition issues, because CURRENT_TIMESTAMP is timestamp with time zone). So I think what you probably *really* want is ... WHERE CAST(date AS date) + numdays = CURRENT_DATE; which keeps both the addition and the comparison as simple date operations with no sub-day resolution and no timezone funnies. regards, tom lane -- __ D. Scott Nixon LSSi Corp. email: [EMAIL PROTECTED] url: http://www.lssi.net/~snixon phone: (919) 466-6834 fax: (919) 466-6810 __ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] SELECT duplicates in a table
I've look for a solution to this, but have only been able to find solutions to delete duplicate entries in a table by deleting entries not returned by SELECT DISTINCT. What sql should I use to SELECT entries in a table that have two particular column values that match? For example, my_table has name, phone number, identification_number, zip code, date of birth, and city I want to SELECT rows from this table that have the same values in identification and date of birth (duplicates) so I can have the user look at them in order to figure out which one to delete. I tried something like: $db_sql = SELECT * FROM my_table GROUP BY identification_number HAVING count(date_of_birth) 1 ORDER BY name but that doesn't seem to work. Thanks, Bruce ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SELECT duplicates in a table
Try SELECT * FROM mytable WHERE (identification_number,date_of_birth) IN (SELECT identification_number , date_of_birth FROM mytable m2 GROUP BY identification_number,data_of_birth HAVING COUNT(*) 1 ) There are other ways of doing it, perhaps more efficient. Vincent I've look for a solution to this, but have only been able to find solutions to delete duplicate entries in a table by deleting entries not returned by SELECT DISTINCT. What sql should I use to SELECT entries in a table that have two particular column values that match? For example, my_table has name, phone number, identification_number, zip code, date of birth, and city I want to SELECT rows from this table that have the same values in identification and date of birth (duplicates) so I can have the user look at them in order to figure out which one to delete. I tried something like: $db_sql = SELECT * FROM my_table GROUP BY identification_number HAVING count(date_of_birth) 1 ORDER BY name but that doesn't seem to work. Thanks, Bruce ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT duplicates in a table
Thanks. Worked like a charm! Bruce [EMAIL PROTECTED] wrote: Try SELECT * FROM mytable WHERE (identification_number,date_of_birth) IN (SELECT identification_number , date_of_birth FROM mytable m2 GROUP BY identification_number,data_of_birth HAVING COUNT(*) 1 ) There are other ways of doing it, perhaps more efficient. Vincent I've look for a solution to this, but have only been able to find solutions to delete duplicate entries in a table by deleting entries not returned by SELECT DISTINCT. What sql should I use to SELECT entries in a table that have two particular column values that match? For example, my_table has name, phone number, identification_number, zip code, date of birth, and city I want to SELECT rows from this table that have the same values in identification and date of birth (duplicates) so I can have the user look at them in order to figure out which one to delete. I tried something like: $db_sql = SELECT * FROM my_table GROUP BY identification_number HAVING count(date_of_birth) 1 ORDER BY name but that doesn't seem to work. Thanks, Bruce ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SELECT duplicates in a table
Assuming identification_number is a unique (primary) key... select * from my_table where date_of_birth in (select date_of_birth from my_table group by date_of_birth having count(*) 1) Or - it may be quicker to do... select * from my_table a where exists (select 'x' from my_table b where a.date_of_birth = b.date_of_birth group by b.date_of_birth having count(*) 1) Kall, Bruce A. wrote: I've look for a solution to this, but have only been able to find solutions to delete duplicate entries in a table by deleting entries not returned by SELECT DISTINCT. What sql should I use to SELECT entries in a table that have two particular column values that match? For example, my_table has name, phone number, identification_number, zip code, date of birth, and city I want to SELECT rows from this table that have the same values in identification and date of birth (duplicates) so I can have the user look at them in order to figure out which one to delete. I tried something like: $db_sql = SELECT * FROM my_table GROUP BY identification_number HAVING count(date_of_birth) 1 ORDER BY name but that doesn't seem to work. Thanks, Bruce ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Postgres-Windows -Perl DBI
Hello all, Anyone has perl DBI working with Postgres 8-beta on Windows. I get the following error when i compile. I have POSTGRES_LIB and POSTGRES_INCLUDE set to the right locations. The file 'libpq-fe.hdoes exist in the POSTGRES_LIB directory.. Copyright (C) Microsoft Corp 1988-1998. All rights reserved. Microsoft (R) Program Maintenance Utility Version 6.00.8168.0Copyright (C) Microsoft Corp 1988-1998. All rights reserved. cl -c -IC:\pgsql\postgresql-7.4.5\src\include -nologo -Gf -W3 -MD -Zi-DNDEBUG -O1 -DWIN32 -D_CONSOLE -DNO_STRICT -DHAVE_DES_FCRYPT -DNO_HASH_SEED -DPERL_IMPLICIT_CONTEXT -DPERL_IMPLICIT_SYS -DUSE_PERLIO -DPERL_MSVCRT_READFIX -MD-Zi -DNDEBUG -O1 -DVERSION=\"1.9.0\" -DXS_VERSION=\"1.9.0\" "-IC:\Perl\lib\CORE" Pg.cPg.cPg.xs(16) : fatal error C1083: Cannot open include file: 'libpq-fe.h': No such file or directoryNMAKE : fatal error U1077: 'cl' : return code '0x2'Stop. Any thoughts ? - goutam "Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe." Sir Albert Einstein Goutam Paruchuri Database Consultant, O'NEIL ASSOCIATES, INC. http://www.oneil.com495 Byers Rd.Miamisburg, Ohio 45342-3662Phone: (937) 865-0846 ext. 3051Fax: (937) 865-5858 Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited.
[GENERAL] Foriegn Keys?
Hello all, I have a database system in which i truncate a main table to update the information. I would like to setup some foriegn keys referencing back to that table but am concerned as to how the truncating of the Parent table will effect the keys? Would all I have to do is insure that I vacuum the db after I truncate and repopulate the table? Brian C. Doyle Director, Internet Services United Merchant Processing Association http://www.umpa-us.com 1-800-555-9665 ext 212
[GENERAL] primary key and the default index operator class
I am trying to figure out how I can change the default operator class of the index created for my primary key field. Is it even possible since I am not able to find the syntax? Or do I need to create the primary key and then an additional index with the operator class I want. (It would be a waste of space) Thanks Sally ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] null value of type java.sql.Time
Occasionally I want to store a null value for my java.sql.Time-- Time column in Postgresql. update event set game_clock=null where event_id=1; I can retreive the record with the null value (type Time) if I select on the primary key, select game_clock from event where event_id = 1; but when I try to select on the null column value, I get zero records. select * from event where game_clock=null; How can I retreive records with null values for a column? thanks, Phil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] null value of type java.sql.Time
phil campaigne wrote: Occasionally I want to store a null value for my java.sql.Time-- Time column in Postgresql. update event set game_clock=null where event_id=1; I can retreive the record with the null value (type Time) if I select on the primary key, select game_clock from event where event_id = 1; but when I try to select on the null column value, I get zero records. select * from event where game_clock=null; Try select * from event where game_clock is null; A null value cannot be used in a comparison since it's undefined. You have to explicitly query for something that has no value, hence the different syntax. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How to handle larger databases?
On Mon, 2004-11-22 at 08:59 -0500, Geoffrey wrote: So that would say the previous statements are not accurate? That is, there's no problem with using a varchar? Right; there is no reason to prefer CHAR(n) over VARCHAR(n), unless you need whitespace padding. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] null value of type java.sql.Time
Try: SELECT * FROM event WHERE game_clock IS NULL; Greg Williamson DBA GlobeXplorer LLC -Original Message- From: phil campaigne [mailto:[EMAIL PROTECTED] Sent: Monday, November 22, 2004 2:33 PM To: [EMAIL PROTECTED] Subject: [GENERAL] null value of type java.sql.Time Occasionally I want to store a null value for my java.sql.Time-- Time column in Postgresql. update event set game_clock=null where event_id=1; I can retreive the record with the null value (type Time) if I select on the primary key, select game_clock from event where event_id = 1; but when I try to select on the null column value, I get zero records. select * from event where game_clock=null; How can I retreive records with null values for a column? thanks, Phil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PostgreSQL 8.0.0 Beta 5 Now Available
Its been almost 4 weeks since Beta4, and alot of work, involving alot of bug fixes, and documentation improvements, to the source tree, we have just released our 5th Beta of 8.0.0. All of our major Open Items have now been completed, and we're slowly entering the final stages, involving alot of testing and documentation changes. For a complete list of changes/improvement since Beta 1 was released, please see: ftp://ftp.postgresql.org/pub/source/v8.0.0beta/ChangeLog-Beta4-to-Beta5 That said, Beta 5 is currently available for download on all mirrors: http://www.postgresql.org/mirrors-ftp.html David Fetter has also updated the Bittorrent Site with the latest beta, available: http://bt.postgresql.org As with all releases, the success of this release falls in the your hands ... to go from Beta - Release, we need as many people out there to put it through her paces as possible, on as many platforms as possible. We urge anyone, and everyone, to download a copy and run her through her regression tests, and report any/all problems, and bugs, to [EMAIL PROTECTED] The more bugs we can find, and eliminate, during Beta, the more successful the Release will be ... Once more, on behalf of all of the developers, Happy Bug Hunting ... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to make a good documentation of a database ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 David Pradier [EMAIL PROTECTED] writes: Yes, it seems interesting. (Is import of sql database possible ?) But for now, I'll stick to postgresql_autodoc. I think it may be. I've (at least) once, as a test, used it to document an existing PostgreSQL database. I've also designed more than one database in Druid and then exported the result to PostgreSQL. Thanks all the same, Bill, I'll try to keep an eye on this project. You're welcome. Bill - -- Bill Harris Facilitated Systems http://facilitatedsystems.com/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: For more information, see http://www.gnupg.org iD4DBQFBolDl3J3HaQTDvd8RAqLTAJUTUi5JVuFgEG83CUmfjCPkJ5viAJ9/c7Rb YXIGIjhZLiI1/jU6ijlviA== =X+ai -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] How to list databases with SQL statement?
Hello, Is there a way to list all databases which belong to the current user with an SQL query? Regards M.Marinov -- One Planet, One Internet. We Are All Connected. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How to list databases with SQL statement?
On Saturday 20 November 2004 02:39, Marian D Marinov wrote: Hello, Is there a way to list all databases which belong to the current user with an SQL query? I am not sure about a SQL Query. But if you login to the database using the command line psql interface and run \l it will give you output similar to this: intsys= \l List of databases Name| Owner | Encoding ---+--+--- intsys| intsys | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (3 rows) intsys= Not sure if this will help you. Regards M.Marinov Q -- Quinton Delpeche Internal Systems Developer Softline VIP Telephone: +27 12 420 7000 Direct:+27 12 420 7007 Facsimile: +27 12 420 7344 http://www.vippayroll.co.za/ Anarchy may not be the best form of government, but it's better than no government at all. pgp7gzpg48XJA.pgp Description: PGP signature
Re: [GENERAL] How to list databases with SQL statement?
On Sat, Nov 20, 2004 at 12:39:38AM +, Marian D Marinov wrote: Is there a way to list all databases which belong to the current user with an SQL query? Such information is in the system catalogs: http://www.postgresql.org/docs/7.4/static/catalogs.html If you run psql with the -E option, you can see the queries that psql makes when you issue commands like \l to show the list of databases and their owners. You can then copy those queries and modify them to meet your needs. The name of the current user is available as CURRENT_USER and SESSION_USER -- see the Miscellaneous Functions documentation for the difference: http://www.postgresql.org/docs/7.4/static/functions-misc.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to list databases with SQL statement?
On Nov 19, 2004, at 7:39 PM, Marian D Marinov wrote: Hello, Is there a way to list all databases which belong to the current user with an SQL query? select datname, usename from pg_catalog.pg_database, pg_catalog.pg_user where datdba = usesysid and usename = current_user; Best, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL]
subscribeend
