[SQL] Chaning locale sorting order for statements
Hello, is it possible to change the locale sorting order in statements ? I need this for a multilingual web application, where I'm not able to set the locale in the session or elsewhere. In Oracle I would use something like that : SELECT * FROM table1 ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH'); Does postgres has something similar ? Thx berger ---(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: [SQL] Chaning locale sorting order for statements
Uz.ytkownik Albrecht Berger napisa?: In Oracle I would use something like that : SELECT * FROM table1 ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH'); Does postgres has something similar ? I think, you have to create function NLSSORT by your own. C language would be nice for performance. It is possible to create operator which compares two strings, but I don't know how to tell him what the language is used now. Regards, Tomasz Myrta ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Chaning locale sorting order for statements
I am not sure about comparation performance. I have big problems with sort performance in pg by comparing varchar. Also I am not sure if it is possible to get this new funktion to replace the standart sort. regards, Ivan. Tomasz Myrta wrote: > Uz.ytkownik Albrecht Berger napisa?: > > In Oracle I would use something like that : > > SELECT * FROM table1 > > ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH'); > > > > Does postgres has something similar ? > I think, you have to create function NLSSORT by your own. > C language would be nice for performance. > It is possible to create operator which compares two strings, but I > don't know how to tell him what the language is used now. > Regards, > Tomasz Myrta > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Proposal of hierarchical queries, a la Oracle
Evgen,
read info about ltree module
http://www.sai.msu.su/~megera/postgres/gist/ltree/
Do you have implemented indexed access ?
regards,
Oleg
On Sat, 16 Nov 2002, Evgen Potemkin wrote:
> I was have made a module like ltree, i don't know how you implement it,
> but mine doesn't fit.
>
> The problem : for ex. you need to select a tree, but with childs of
> each parent is sorted.
> in my module i've defined a tree path as set of
> numbers separated by dot ('1.234.456.789'::hier for ex.), a comparision
> operators <,>,<=,<=,= , operators for checking for ancestors/descedants
> <<,>>,<<=,>>=.
> create table t (field text, node::hier);
> then i do "select field from t where where node <<'1' order by node";
> 'order by' - for tuples being in order of tree (first is root,then first
> child, then first child of first child, .. so on). works perfectly well.
>
> THE MATTER OF PROBLEM:
> tuples can't be sorted alphabetically by 'field', because of 'order by node'
> clause. 'order by node,field' doesn't really work because 'node' is first
> key, if 'order by field,node' - then we don't get a tree.
>
> if i do nodes on one level is equal, then childs of one node in some cases
> are migrate to another node of same level :) as far as i understand problem
> is in pg's sort. it's not a bug but feature of sort alghorithm.
>
> The minor problem is that such tree a bit difficult to maintain. for ex. if i need
> to move some node to elsewhere in tree, i need to update all childs, sub
> childs, ... of this node.
>
> regards
> ---
> .evgen
>
> On Fri, 15 Nov 2002, Oleg Bartunov wrote:
>
> > Evgen,
> >
> > you'd need to post your message and patch to hackers mailing list.
> > btw, did you try contrib/ltree module ?
> >
>
> > 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
> >
> >
>
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])
[SQL] DATE TIME INDEX ?
I have been tring to use index on timestamps: I have a table with a timestamp filed with index on it. I nned to extract all the ids of the table with datarx >= a date <= a date or between 2 dates I found that the only way to force postgres to use index is: explain select id,datarx::date from docs where datarx between '2002-11-13' and '2002-11-14' ; Index Scan using idx_documenti_datarx on documenti (cost=0.00..7.86 rows=2 width=12) In other cases the index is not used: explain select id,datarx::date from docs where datarx >= '2002-11-16'; Seq Scan on documenti (cost=0.00..12.01 rows=107 width=12) explain select id,datarx::date from docs where datarx::date between '2002-11-13' and '2002-11-13' ; Seq Scan on documenti (cost=0.00..16.02 rows=36 width=12) I found that the only way to force postgres to use index is: explain select id,datarx::date from docs where datarx between '2002-11-13' and '2002-11-14' ; Index Scan using idx_documenti_datarx on documenti (cost=0.00..7.86 rows=2 width=12) Do I need to use some other functons o trick? Thanks in advance Alex ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] INDEX PROBLEMS ?
On postgres 7.2.3 I have found what follows: explain select * from documents where iddoc>1; Seq Scan on lotti (cost=0.00..831.79 rows=26783 width=98) EXPLAIN explain select * from documents where iddoc=1; Index Scan using lotti_pkey on lotti (cost=0.00..2.26 rows=1 width=98) Why index is not used for operators > and < Alex ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] DATE TIME INDEX ?
PostgreSQL Server <[EMAIL PROTECTED]> writes: > I found that the only way to force postgres to use index is: > explain select id,datarx::date from docs where datarx between '2002-11-13' and >'2002-11-14' ; > Index Scan using idx_documenti_datarx on documenti (cost=0.00..7.86 rows=2 width=12) Given the small cost estimates, you either are testing on a toy table, or you have not run ANALYZE since loading up the table. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] INDEX PROBLEMS ?
Alex, > On postgres 7.2.3 I have found what follows: > > explain select * from documents where iddoc>1; > > Seq Scan on lotti (cost=0.00..831.79 rows=26783 width=98) > > EXPLAIN > explain select * from documents where iddoc=1; > > Index Scan using lotti_pkey on lotti (cost=0.00..2.26 rows=1 > width=98) > > Why index is not used for operators > and < Because ">" is returning 26,000 rows. When you return that many rows, a Seq Scan is faster. -Josh ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Proposal of hierarchical queries, a la Oracle
Evgren, > I want to propose the patch for adding the hierarchical queries > posibility. > It allows to construct queries a la Oracle for ex: > SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; You'll be thrilled to know that this has already been implemented in PostgreSQL 7.3, as a /contrib module, by Joe Conway. Download 7.3b5 now if you can't wait; Joe would like more people to test his module, anyway. -Josh Berkus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] execute a query in an other db
> > Hi, > > > > When I'm connected to a postgreSQL db ( e.g. myDB ), how could I execute > > "select * from myTable;" in an other db (e.g. myOtherDB). > > > > In other words, what is the postgreSQL equivalent of the MS SQL Server > > statement "USE myOtherDB SELECT * FROM myTable" ? > > Stock postgresql doesn't support cross database queries. > There is a contrib package which does. > > In the example you gave, you were combining information from two databases. > If it is the case that your application will be combining data from the > two databases, then you can open a separate connection to each database > and use the appropiate connection for your queries. Thank you for your answer. Do you know the name of this package ? Thx Lilian ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Proposal of hierarchical queries, a la Oracle
I was have made a module like ltree, i don't know how you implement it,
but mine doesn't fit.
The problem : for ex. you need to select a tree, but with childs of
each parent is sorted.
in my module i've defined a tree path as set of
numbers separated by dot ('1.234.456.789'::hier for ex.), a comparision
operators <,>,<=,<=,= , operators for checking for ancestors/descedants
<<,>>,<<=,>>=.
create table t (field text, node::hier);
then i do "select field from t where where node <<'1' order by node";
'order by' - for tuples being in order of tree (first is root,then first
child, then first child of first child, .. so on). works perfectly well.
THE MATTER OF PROBLEM:
tuples can't be sorted alphabetically by 'field', because of 'order by node'
clause. 'order by node,field' doesn't really work because 'node' is first
key, if 'order by field,node' - then we don't get a tree.
if i do nodes on one level is equal, then childs of one node in some cases
are migrate to another node of same level :) as far as i understand problem
is in pg's sort. it's not a bug but feature of sort alghorithm.
The minor problem is that such tree a bit difficult to maintain. for ex. if i need
to move some node to elsewhere in tree, i need to update all childs, sub
childs, ... of this node.
regards
---
.evgen
On Fri, 15 Nov 2002, Oleg Bartunov wrote:
> Evgen,
>
> you'd need to post your message and patch to hackers mailing list.
> btw, did you try contrib/ltree module ?
>
> 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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Problems invoking psql. Help please.
Hey folks: I am able to consistently start and stop the postgreSQL server and to access it across our office network with pgAdmin II. I have had no luck invoking the psql command line prompt, from where I can enter queries and start to surmount the learning curve from background with mySQL to my next step with postgreSQL. Can anyone help me figure out, please, what this is about and what I can do about it? I've copied the shell dialogue below. Everything in: /usr/lib/postgresql/bin is owned by root:root. Who should these files be owned by? The database engine is invoked as postgres, which is the user which created the databse. Do I harm anything if I chown postgres:postgres for everything in that directory? Will this get me past this error and to a psql prompt? I am operating on a Debian Woody Platform, with postgreSQL 7.2.1 and ODBC driver 7.1.9. All help is appreciated. Thanks. -- Hugh Esco hesco@biko:~$ su postgres Password: postgres@biko:/home/hesco$ locate psql /usr/bin/psql /usr/lib/odbc/libodbcpsqlS.so /usr/share/man/man1/psql.1.gz /var/home/hesco/.psql_history postgres@biko:/home/hesco$ ./psql sh: ./psql: No such file or directory postgres@biko:/home/hesco$ psql env: /usr/lib/postgresql/bin/readpgenv: Permission denied No database specified postgres@biko:/home/hesco$ psql ggp_test env: /usr/lib/postgresql/bin/readpgenv: Permission denied Could not execv /usr/lib/postgresql/bin/psql postgres@biko:/home/hesco$ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Problems invoking psql. Help please.
One more note: when I first got the error: env: /usr/lib/postgresql/bin/readpgenv: Permission denied I looked and determined that no such file existed, so I created an empty (one character) file by that name with a shell level echo command and set the permissions to match those of every other file in the directory. What should that file really contain? -- Hugh Hey folks: I am able to consistently start and stop the postgreSQL server and to access it across our office network with pgAdmin II. I have had no luck invoking the psql command line prompt, from where I can enter queries and start to surmount the learning curve from background with mySQL to my next step with postgreSQL. Can anyone help me figure out, please, what this is about and what I can do about it? I've copied the shell dialogue below. Everything in: /usr/lib/postgresql/bin is owned by root:root. Who should these files be owned by? The database engine is invoked as postgres, which is the user which created the databse. Do I harm anything if I chown postgres:postgres for everything in that directory? Will this get me past this error and to a psql prompt? I am operating on a Debian Woody Platform, with postgreSQL 7.2.1 and ODBC driver 7.1.9. All help is appreciated. Thanks. -- Hugh Esco hesco@biko:~$ su postgres Password: postgres@biko:/home/hesco$ locate psql /usr/bin/psql /usr/lib/odbc/libodbcpsqlS.so /usr/share/man/man1/psql.1.gz /var/home/hesco/.psql_history postgres@biko:/home/hesco$ ./psql sh: ./psql: No such file or directory postgres@biko:/home/hesco$ psql env: /usr/lib/postgresql/bin/readpgenv: Permission denied No database specified postgres@biko:/home/hesco$ psql ggp_test env: /usr/lib/postgresql/bin/readpgenv: Permission denied Could not execv /usr/lib/postgresql/bin/psql postgres@biko:/home/hesco$ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Proposal of hierarchical queries, a la Oracle
Josh Berkus wrote: Evgren, I want to propose the patch for adding the hierarchical queries posibility. It allows to construct queries a la Oracle for ex: SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond; You'll be thrilled to know that this has already been implemented in PostgreSQL 7.3, as a /contrib module, by Joe Conway. Download 7.3b5 now if you can't wait; Joe would like more people to test his module, anyway. I have it on my personal TODO to approach this for 7.4, but I'll be happy to focus on other things if you'd like to take this on. The connectby() function in contrib/tablefunc could be a good starting point for you. Joe ---(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: [SQL] execute a query in an other db
LR wrote: Thank you for your answer. Do you know the name of this package ? Thx Lilian see contrib/dblink. I'd highly recommend using 7.3 (finishing beta, soon to be a release candidate) if you can. It is much better than the one in 7.2 and the syntax has changed some. Joe ---(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
