Re: [SQL] How to represent a tree-structure in a relational database
On Thu, 14 Dec 2000, Tulassay Zsolt wrote: > > You can find the article dealing with this at > http://www.utdt.edu/~mig/sql-trees > sorry i pasted in the wrong url (this was mentioned in an earlier post) the correct one is: A look at SQL Trees (by Joe Celko) http://www.dbmsmag.com/9603d06.html Zsolt Tulassay
[SQL] to_timestamp, problem
Hi,
Can anyone explain to me why this doesn't work. Seems to be some stupid (my)
mistake:
mtldb=# SELECT to_timestamp('05121445482000', 'MMDDHHMISS');
to_timestamp
2000-05-12 14:45:48+02
(1 row)
mtldb=# SELECT to_timestamp('2512144548', 'MMDDHHMISS');
to_timestamp
--
invalid
(1 row)
pg 7.0.3, linux 2.2
tia
mazek
Marcin Mazurek
--
Kierownik Działu Systemowego
MULTINET SA o/Poznan
http://www.multinet.pl/
Re: [SQL] to_timestamp, problem
On Fri, 15 Dec 2000, Marcin Mazurek wrote:
> Hi,
> Can anyone explain to me why this doesn't work. Seems to be some stupid (my)
> mistake:
>
> mtldb=# SELECT to_timestamp('05121445482000', 'MMDDHHMISS');
> to_timestamp
>
> 2000-05-12 14:45:48+02
> (1 row)
>
> mtldb=# SELECT to_timestamp('2512144548', 'MMDDHHMISS');
> to_timestamp
> --
> invalid
> (1 row)
>
> pg 7.0.3, linux 2.2
You are right. It is already know bug (feature:-) in 7.0.x, in this
version, to_timestamp() expect that year not must be exactly
4-digits but can be greater. A solution is use some separator like
'-MMDDHHMISS' or use as last in format template (as in your
first example).
In 7.1 is better analyse that fix it:
test=# SELECT version();
version
--
PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC 2.95.2
(1 row)
test=# SELECT to_timestamp('2512144548', 'MMDDHHMISS');
to_timestamp
2000-05-12 14:45:48+02
(1 row)
Karel
Re: [SQL] How to represent a tree-structure in a relationaldatabase
> > I once started writing a small paper on this subject; it is still in a > > rather preliminary state. > > > > You can download the draft (and some ill documented code, 53kB) from > > http://www.utdt.edu/~mig/sql-trees > ah, this looks very, very nice! Thanks. > on page 5ff you describe the Postgres implementation, but the URL (page > 5 bottom) is't complete -- can i find the files somewhere? > Included is a "tree_general.sql", but this seems not to be complete and > not the same version as the ps-file (First draft, may 6, 2000): in the > draft there is written about an base 160 encoding, tree_general.sql uses > base 159 encoding ;) Sorry, I never got around to completing this, or thinking any further. My other files are definitely not in a usable state right now. I hope to be able to improve things over the (southern) summer holidays, so there may be news soon - but do not hold your breadth! I can't remember why I switched from base 160 to base 159; my guess now is that I got confused at coding time between the base and the maximal number (base-1): ie, it may be a mistake. > What's against using all characters >= 32, excluding special characters > with special meaning in LIKE and regexps? With base 208 encoding it's > possible to have 43264 elements on each level. Nothing, I guess. I probably got some kind of "start counting at zero" blockage when I started, and never really looked back on it, my shame. Hey, I told you it was rather preliminary ... Thanks for pointing it out. > i guess, with base 160 encoding there might be a problem: if postgres is > compiled with --enable-locale (e.g. for german umlauts), the ordering > isn't according to the ASCII number of the character, so for this > purpose it's needed to build the encoding table according to the locate > settings. Or simply sort it according the locale settings. Yes indeed; never thought about that one. Cheers Miguel
[SQL] Confused by timezones
Sorry, I am trying to find my way in formatting timestamps for different
timezones and I am a little confused.
[ PostgreSQL 7.0.0 on alphaev6-dec-osf4.0f, compiled by cc ]
Let's imagine
CREATE TABLE tztest (id SERIAL, v TEXT, ts TIMESTAMP DEFAULT now());
How can I format a
SELECT to_char(ts,'DD/MM/ HH:MI:SS')
in order to have the accompanying timezone for the timestamp?
If I select the ISO format, I ofcourse have it ('2000-12-15
13:09:59+02')
but I cannot find a to_char element for it, either in offset or codes
(which I'd prefer).
Is this possible?
On a more general ground, I checked the 'Date/Time Data Types' section
of the user manual, but I don't manage to have the expected behaviour,
with either the PGTZ env variable or the SET TIMEZONE command. Here's an
example (my default is EET i.e. +02):
village=# select ts from tztest;
ts
2000-12-15 13:09:59+02
(1 row)
village=# set TimeZone TO PST;
SET VARIABLE
village=# select ts from tztest;
ts
2000-12-15 13:09:59+02
(1
row)
or maybe I just don't understand the whole picture...
P.S. Ofcourse I can use external functions, e.g. Date::Manip since I
code in Perl, but I'd prefer to leave this task to the database itself.
--
Alessio F. Bragadini[EMAIL PROTECTED]
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
Re: [SQL] Confused by timezones
On Fri, 15 Dec 2000, Alessio Bragadini wrote:
> Sorry, I am trying to find my way in formatting timestamps for different
> timezones and I am a little confused.
>
> [ PostgreSQL 7.0.0 on alphaev6-dec-osf4.0f, compiled by cc ]
>
> Let's imagine
> CREATE TABLE tztest (id SERIAL, v TEXT, ts TIMESTAMP DEFAULT now());
>
> How can I format a
> SELECT to_char(ts,'DD/MM/ HH:MI:SS')
> in order to have the accompanying timezone for the timestamp?
> If I select the ISO format, I ofcourse have it ('2000-12-15
> 13:09:59+02')
> but I cannot find a to_char element for it, either in offset or codes
> (which I'd prefer).
> Is this possible?
Yes it's possible, but in freezed 7.1 *only*. It's 'TZ' and output is
abbreviation of timezone, +02 (digit version) is not supported.
test=# SELECT to_char(now(), 'DD/MM/ HH:MI:SS TZ');
to_char
-
15/12/2000 01:29:14 CET
(1 row)
> village=# select ts from tztest;
>ts
>
> 2000-12-15 13:09:59+02
> (1 row)
>
> village=# set TimeZone TO PST;
> SET VARIABLE
> village=# select ts from tztest;
>ts
>
> 2000-12-15 13:09:59+02
> (1
> row)
>
> or maybe I just don't understand the whole picture...
You must use same names (definitions) as are used in your OS
(an example on Linux at /usr/share/zoneinfo)
test=# set TimeZone TO 'Japan';
SET VARIABLE
test=# select now();
now
2000-12-15 21:40:52+09
(1 row)
test=# set TimeZone TO 'EST';
SET VARIABLE
test=# select now();
now
2000-12-15 07:41:18-05
(1 row)
test=# set TimeZone TO 'GMT';
SET VARIABLE
test=# select now();
now
2000-12-15 12:41:29+00
(1 row)
Karel
Re: [SQL] Confused by timezones
Karel Zak wrote: > Yes it's possible, but in freezed 7.1 *only*. It's 'TZ' and output is Thanks, on my experimental 7.1 works perfectly, another reason to switch as soon as possible. :-) > You must use same names (definitions) as are used in your OS > (an example on Linux at /usr/share/zoneinfo) In 7.1 works. Is it supposed to work also in 7.0? Because then it would be a configuration problem on my main system. Since I have a website with registered users, with associated timezone, I would like to show all timestamps based on the user's timezone, and the best way would be to set a session configuration. My only fear is that Apache::DBI (which reuses the same connection for different pages) could intermix such information. Any comment on this would be appreciated. Thanks -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
[SQL] readline ??
Hope my message doesn't bother you. I want to use readline with pgsql7.02 on mandrake 7.2. LM7.2 installed both packages, readline/devel & postgres. How to make psql know about readline? Thanks Lucian
Re: [Re: [SQL] postgres]
Thank you very much Reberto, It appears that your co-workers are not inerested in potential funding. For the rude onesmaybe/perhaps people like myself were givin the email address ever think of that. A potential client that is having second thoughts. Roberto Mello <[EMAIL PROTECTED]> wrote: On Wed, Dec 13, 2000 at 04:44:55PM -0800, Josh Berkus wrote: > Mr. Daoust, > > You have reached the PostgreSQL SQL developers mailing list. We are > not PostgreSQL sales people, and we have no marketing information to > sell you. Please have a clue. Errr... forgive me, but maybe we could be help the PostgreSQL team by kindly directing people to their corporate website, so they can purchase services that are going to fund the project. As long as the project is happy I am happy. Just some thoughts. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto Get your own FREE, personal Netscape WebMail account today at http://home.netscape.com/webmail
Re: [SQL] Confused by timezones
Alessio Bragadini <[EMAIL PROTECTED]> writes: > village=# set TimeZone TO PST; I'm guessing that's not a legal timezone name on your platform. On my box I have to spell it "PST8PDT" ... note that the displayed abbreviation is not the same as the name used to set the timezone. regards, tom lane
Re: [SQL] Confused by timezones
Alessio Bragadini <[EMAIL PROTECTED]> writes: >> You must use same names (definitions) as are used in your OS >> (an example on Linux at /usr/share/zoneinfo) > In 7.1 works. Is it supposed to work also in 7.0? Yes; as far as I know this hasn't changed... regards, tom lane
Re: [Re: [SQL] postgres]
On 14 Dec 2000, Marc Daoust wrote: > Thank you very much Reberto, > > It appears that your co-workers are not inerested in potential funding. > For the rude onesmaybe/perhaps people like myself were givin the email > address ever think of that. > > A potential client that is having second thoughts. PostgreSQL does have two companies that do work on it and for support (PgSQL, Inc and Great Bridge, pgsql.com and greatbridge.com respectively), however the project is an open source one and most of us here on the mailing list do not belong to either organization. Probably pgsql-general would be a more appropriate list in general for these sorts of questions although there isn't a huge amount of segmentation between them. You can also follow the commercial support link on the website (www.postgresql.org).
Re: [SQL] postgres
> On Wed, Dec 13, 2000 at 04:44:55PM -0800, Josh Berkus wrote: > > Mr. Daoust, > > > > You have reached the PostgreSQL SQL developers mailing list. We are > > not PostgreSQL sales people, and we have no marketing information to > > sell you. Please have a clue. > > Errr... forgive me, but maybe we could be help the PostgreSQL team by > kindly directing people to their corporate website, so they can purchase > services that are going to fund the project. > As long as the project is happy I am happy. My guess is that corporate support is mostly for serious PostgreSQL users, not new people. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] readline ??
On Fri, 15 Dec 2000, vs wrote: > Hope my message doesn't bother you. > I want to use readline with pgsql7.02 on mandrake 7.2. > LM7.2 installed both packages, readline/devel & postgres. > How to make psql know about readline? If you are using a binary installation of Postgres (you installed via RPM), it may not have the readline support compiled in, so you might want to grab the source and rebuild -- it'll pick up the readline stuff during the ./configure phase -- Brett http://www.chapelperilous.net/~bmccoy/ --- Democracy means simply the bludgeoning of the people by the people for the people. -- Oscar Wilde
[SQL] Re: full text index
On Fri, 15 Dec 2000, Artur Rataj wrote: > Hello, > > I have a question about a full text index. > I have created such index over a text field. I have stored > substrings of each word in the text field, so that for `example' they > would be `example', `xample' and so on to `le'. The index has been > physically ordered by string, indices were created on it, and `vacuum' was > performed, and then I tried a statement like: > > select fserial from fti where string ~ '^xample'; > > And it had a speed comparable to a sequential scan of the original table > using the `~*' operator. > > `Explain' for that statement shows: > > Index Scan using fti_key on fti > (cost=19249.90 rows=1 width=4) > > The original table has about 4.000 rows, and the full text index > table has about 1.000.000 rows. > > Why the query that uses the full text index is so slow, in compare to > a sequential scan? > > Best regards > > Artur Rataj > > Now I have compared the statements more exactly -- the one that uses fti is much slower: using fti index, about 150 rows found: about 80s sequential scan, about 130 rows found: about 5s
Re: [Re: [SQL] postgres]
If the person really does want commercial support, there is a "Commercial Support" page on www.postgresql.org. > Thank you very much Reberto, > > It appears that your co-workers are not inerested in potential funding. > For the rude onesmaybe/perhaps people like myself were givin the email > address ever think of that. > > A potential client that is having second thoughts. > > > > > Roberto Mello <[EMAIL PROTECTED]> wrote: > On Wed, Dec 13, 2000 at 04:44:55PM -0800, Josh Berkus wrote: > > Mr. Daoust, > > > > You have reached the PostgreSQL SQL developers mailing list. We are > > not PostgreSQL sales people, and we have no marketing information to > > sell you. Please have a clue. > > Errr... forgive me, but maybe we could be help the PostgreSQL team by > kindly directing people to their corporate website, so they can purchase > services that are going to fund the project. > As long as the project is happy I am happy. > > Just some thoughts. > > -Roberto > > -- > Computer Science Utah State University > Space Dynamics Laboratory Web Developer > USU Free Software & GNU/Linux Clubhttp://fslc.usu.edu > My home page - http://www.brasileiro.net/roberto > > > > Get your own FREE, personal Netscape WebMail account today at >http://home.netscape.com/webmail > -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] full text index
Hello, I have a question about a full text index. I have created such index over a text field. I have stored substrings of each word in the text field, so that for `example' they would be `example', `xample' and so on to `le'. The index has been physically ordered by string, indices were created on it, and `vacuum' was performed, and then I tried a statement like: select fserial from fti where string ~ '^xample'; And it had a speed comparable to a sequential scan of the original table using the `~*' operator. `Explain' for that statement shows: Index Scan using fti_key on fti (cost=19249.90 rows=1 width=4) The original table has about 4.000 rows, and the full text index table has about 1.000.000 rows. Why the query that uses the full text index is so slow, in compare to a sequential scan? Best regards Artur Rataj
[SQL] Use of indexes in plpgsql functions
> I have a table with 650k rows with an index on URL (pg v7.0.0 on
> i686-pc-linux-gnu)
>
> When using psql the select query behaves as expected i.e. takes < 1 second
> (and explain tells me it is using the correct index)
>
> However when I put this into a pl function it takes about 2.5 mins, Has
> anyone had any similar problems/solutions or is it just that I am over
> looking something??? (I know there is an update but again when executed
> seperately it takes approx 1 sec)
>
> Regards
>
> Graham
>
> details as follows: -
>
> SELECT now(); SELECT count(*) FROM statistics WHERE url ='XXX' and
> website_id =1035; SELECT now();
>
> now
> --
> 2000-12-15 19:17:34+00
>
> count
> -
> 421
> (1 row)
>
> now
> --
> 2000-12-15 19:17:35+00
> (1 row)
>
> CREATE FUNCTION get_url_hits (varchar, int4) RETURNS int4 AS '
> DECLARE
> num INT4;
> BEGIN
> SELECT count(*) INTO num FROM statistics WHERE url = $1 and
> website_id = $2;
>
> IF num > 0 THEN
> UPDATE site_url SET hits = num, last_updated = now() where
> website_id = $2 and url = $1;
> END IF;
> RETURN num;
> END;' LANGUAGE 'plpgsql';
>
> select now(); select get_url_hits ('XXX', 1001); select now();
>
> now
> --
> 2000-12-15 19:21:40+00
> (1 row)
>
> get_url_hits
>
> 421
> (1 row)
>
> now
> --
> 2000-12-15 19:24:06+00
> (1 row)
>
>
>
>
>
>
winmail.dat
Re: [SQL] Use of indexes in plpgsql functions
"Graham Vickrage" <[EMAIL PROTECTED]> writes: >> CREATE FUNCTION get_url_hits (varchar, int4) RETURNS int4 AS ' >> DECLARE >> num INT4; >> BEGIN >> SELECT count(*) INTO num FROM statistics WHERE url = $1 and >> website_id = $2; > [ is slow ] A possible gotcha is if the url and website_id columns are declared as something other than varchar and int4 respectively. The planner's not very smart about optimizing cross-datatype comparisons into indexscans. When you write out a query with constants you are protected from this because the constants have their types adjusted, but when you write "url = $1" you'd best make sure $1 is declared exactly the same way as url. 7.1 fixes some instances of this gotcha, but not all of 'em IIRC. regards, tom lane
Re: [SQL] How to represent a tree-structure in a relationaldatabase
Hi, miguel sofer wrote: > > Sorry, I never got around to completing this, or thinking any further. My > other files are definitely not in a usable state right now. I hope to be > able > to improve things over the (southern) summer holidays, so there may be > news > soon - but do not hold your breadth! OK, no prob. I start to implement smth in Perl, so if it works ok and someone is interested in it i can post it here. and I'll try to use a kind of base255 encoding; it seams that postgres only dislike the 0 byte, so if this works there is no real encoding necessary -- simply start at 1 instead of 0. I hoppe this works :) Ciao Alvar -- Alvar C.H. Freude | [EMAIL PROTECTED] Demo: http://www.online-demonstration.org/ | Mach mit! Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit Blast-EN: http://www.a-blast.org/ | Blast/english
