[SQL] postmaster admin questions
Hi, we are using several Windows-clients to access a pg database on a linux machine. When we want to recreate/change the database "dropdb" says that there still are users connected to it. How can I see which users are connected and how can I disconnect them/ get my db dropped anyway?? Thank you, Markus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Order by email address by domain ?
Hi, I just want to order by a listing of email address by domain like : [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Is it possible and how ? Thanks ! -- Hervé Piedvache Elma Ingenierie Informatique 6, rue du Faubourg Saint-Honoré F-75008 - Paris - France http://www.elma.fr Tel: +33-1-44949901 Fax: +33-1-44949902 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Order by email address
Herve, > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > > Is it possible and how ? Given the relational rule of Atomicity (each discrete piece of information shall have its own column or row), the solution is for you to make "e-mail id" and "domain" seperate fields. Then you can sort: ORDER BY mailbox, domain If this is a legacy database, and splitting the field is not an option for you due to exisiting applications/policy, then you'll need to write a custom sorting function: CREATE FUNCTION email_sort (VARCHAR) RETURNS CHAR(120) AS ' DECLARE email_addr ALIAS for $1; mail_box CHAR(60); mail_domain CHAR(60); BEGIN mail_box := CAST(SUBSTR(email_addr, 1, (STRPOS(email_addr, ''@'', 1) -1)) AS CHAR(60)); mail_domain := CAST(SUBSTR(email_addr, (STRPOS(email_addr, ''@'', 1) + 1), 60) AS CHAR(60)); RETURN mail_box || mail_domain; END;' LANGUAGE 'plpgsql'; Then: SELECT user_id, email, email_sort(email) as sortcol FROM users ORDER BY sortcol; However, this solution has a number of problems for data integrity down the line. If e-mail addresses are that important to your application, I greatly encourage you to split the field. -Josh Berkus P.S. Roberto, please add the above to our function library. __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Order by email address
Herve, Etc. Ooops! A couple of misteaks in that last post: > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > [EMAIL PROTECTED] > > > > Is it possible and how ? > > Given the relational rule of Atomicity (each discrete piece of > information shall have its own column or row), the solution is for > you > to make "e-mail id" and "domain" seperate fields. Then you can sort: > > ORDER BY mailbox, domain I meant: ORDER BY domain, mailbox > > If this is a legacy database, and splitting the field is not an > option > for you due to exisiting applications/policy, then you'll need to > write > a custom sorting function: > > CREATE FUNCTION email_sort (VARCHAR) > RETURNS CHAR(120) AS ' > DECLARE > email_addr ALIAS for $1; > mail_box CHAR(60); > mail_domain CHAR(60); > BEGIN > mail_box := CAST(SUBSTR(email_addr, 1, (STRPOS(email_addr, ''@'', 1) > -1)) AS CHAR(60)); > mail_domain := CAST(SUBSTR(email_addr, (STRPOS(email_addr, ''@'', 1) > + > 1), 60) AS CHAR(60)); > RETURN mail_box || mail_domain; I meant: RETURN mail_domain || mail_box; > END;' > LANGUAGE 'plpgsql'; > > Then: > > SELECT user_id, email, email_sort(email) as sortcol > FROM users > ORDER BY sortcol; > -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] RE: Order by email address by domain ?
I assume that you want to do this within the database, i.e. with a query. You would need to use a function, but I don't think a builtin function would do it. Try: CREATE FUNCTION email_order (text) RETURNS text AS ' select substr ($1, strpos ($1, ''@'') + 1) || substr ($1, 1, strpos ($1, ''@'') -1) ' LANGUAGE 'sql'; Then do: SELECT . FROM ... ORDER BY email_order (fieldname); I am assuming text datatype; substitute as appropriate. That's doubled single quotes around the @ symbol, by the way. > -Original Message- > From: Hervé Piedvache [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, May 10, 2001 1:38 PM > To: [EMAIL PROTECTED] > Subject: Order by email address by domain ? > > Hi, > > I just want to order by a listing of email address by domain like : > > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > [EMAIL PROTECTED] > > Is it possible and how ? > > Thanks ! > -- > Hervé Piedvache > > Elma Ingenierie Informatique > 6, rue du Faubourg Saint-Honoré > F-75008 - Paris - France > http://www.elma.fr > Tel: +33-1-44949901 > Fax: +33-1-44949902 > Email: [EMAIL PROTECTED] > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(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
[SQL] Escape Quotes
When using apostrophies the PostgreSQL string seems to like an escape character as follows: update client set code = 'O\'SHEA' where clientid = 2; The ANSI-92 standard seems to suggest that this could/should be handled by ::= ' ::= update client set code = 'O''SHEA' where clientid = 2; Is it possible to get/configure PostgreSQL to handle as within a dleimited string? Keith Gray ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Order by email address by domain ?
Why is everyone else suggesting new functions? This works (in 6.5.3):
ORDER BY lower(substring(email from position('@' in email)+1 )),
lower(email)
remove the lower() functions if you don't need them (I had mixed case
addresses).
I am guessing/assuming that it's cheaper to just use entire email address
in second key rather than extract before the '@' character.
Frank
At 08:37 PM 5/10/01 +0200, you wrote:
>Hi,
>
>I just want to order by a listing of email address by domain like :
>
>[EMAIL PROTECTED]
>[EMAIL PROTECTED]
>[EMAIL PROTECTED]
>[EMAIL PROTECTED]
>[EMAIL PROTECTED]
>
>Is it possible and how ?
>
>Thanks !
>--
>Hervé Piedvache
>
>Elma Ingenierie Informatique
>6, rue du Faubourg Saint-Honoré
>F-75008 - Paris - France
>http://www.elma.fr
>Tel: +33-1-44949901
>Fax: +33-1-44949902
>Email: [EMAIL PROTECTED]
>
>---(end of broadcast)---
>TIP 6: Have you searched our list archives?
>
>http://www.postgresql.org/search.mpl
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Escape Quotes
Keith Gray <[EMAIL PROTECTED]> writes: > Is it possible to get/configure PostgreSQL to handle > as within a dleimited string? We already do. regression=# select 'O''SHEA'; ?column? -- O'SHEA (1 row) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Escape Quotes
Keith Gray <[EMAIL PROTECTED]> writes: > This may be a problem in "ipgsql" then?? I guess. What is that, anyway? > ...or is it different in update from select? Nope, a literal is a literal. 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
Re: [SQL] Escape Quotes
Tom Lane wrote: > > Keith Gray <[EMAIL PROTECTED]> writes: > > Is it possible to get/configure PostgreSQL to handle > > as within a dleimited string? > > We already do. > > regression=# select 'O''SHEA'; > ?column? > -- > O'SHEA > (1 row) > > regards, tom lane Sorry, This may be a problem in "ipgsql" then?? ...or is it different in update from select? Keith ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: Escape Quotes
> > > Is it possible to get/configure PostgreSQL to handle > > > as within a delimited string? > > > > We already do. > > > > This may be a problem in "ipgsql" then?? > ...or is it different in update from select? The problem is in ipqsql... it doesn't handle update, but does handle select. Both work fine for psql (linux). Keith. ---(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] Re: Escape Quotes
On Fri, May 11, 2001 at 01:17:41PM +1000, Keith Gray wrote: > > The problem is in ipqsql... What the heck is ipsql?? -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer In Borland you are never bored! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] ipsql (was - Escape Quotes)
Roberto Mello wrote: > > What the heck is ipsql?? > > -Roberto ipgsql A Win32 client for interactive Postgres session Keith README Interactive PostgreSQL presents comfortable windows environment to execute sql queries, edit tables data, view tables list and structure, execute sql scripts, etc. This application design on Delphi using PostgreSQL components, which included in Winzeos Library: http://www.zeos.dn.ua/download/winzeos-latest.zip PARAMETERS Connect parameters for Interactive PostgreSQL stored in the ipgsql.ini file in the same directory that ipgsql.exe You can change its manually or using File/Options dialog Parameters short description: [Preferences] PgSQLPort= ; PostgreSQL port number (default 5432) IsAlive=1 ; Open alive queries (default 1) IsCached=0; Use cached updates (default 0) QueryAll=0; Query all records when open (default 0) AutoCommit=0 ; Auto commit updates (default 0) AutoRecovery=0; Auto rollback transaction when ; errors occured (default 1) Login= ; PostgreSQL login(no default) Host= ; Host name (no default) DataBase=; Database name (no default) EXTRA FEATURES Program supports PostgreSQL Large Objects. In postgreSQL database they represented as Oid fields which store LO handle. In IPgSql these fields translate to Blob field. You may store in Blob fields text, images or any binary data. Open blob field editor by double click in grid or choose View/Blob Editor menu item. LICENCING Zeos Library is distributed with the GPL licence and costs you nothing. DOWNLOADS The latest version can be found on: http://www.zeos.dn.ua/download/ipgsql-latest.zip KNOWN BUGS 1. When your sql query crash and AutoRecovery set to false end transaction manually. If AutoRecovery set to true after crash transaction is auto rollbacked. 2. IPgSql works only with PostgreSQL 6.5+ CREDITS - Steve Wei for idea and support designing PostgreSQL components Yours, Sergey Seroukhov, Chief Engineering of Capella Development Group. <[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])
[SQL] Question about Committed Isolation
Regarding "Committed Isolation" on PostgreSql Ver 7.1 Users Guide, is that possible a "Dead Lock" happened when two concurrent transactions are waiting each other? And how to avoid or fix it? Jack ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Client Applications come with PostGreSQL V7.1
Is there any Windows version of all Client Applications come with PostGreSQL V7.1? Jack ---(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
