Re: [SQL] discover a toast table name
when i try that i´v got an error ERROR: "pg_toast_26474986" is a system table. call REINDEX under standalone postgres with -O -P options so I kill postmaster and up it standalone. but the reindex command doesn´t have options. and postmaster doesn´t have these ones too. tnx. Eric Anderson Martins Miranda Net Admin @ Via Net SAO - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, May 27, 2003 11:20 AM Subject: Re: [SQL] discover a toast table name > "Eric Anderson Vianet SAO" <[EMAIL PROTECTED]> writes: > > How can I discover which toast table referenced to a phisic table? > > Look at the base table's pg_class row. reltoastrelid is the OID of the > corresponding toast table. So, for example, if I thought "text_tbl" had > such a problem: > > regression=# select relname from pg_class where > regression-# oid = (select reltoastrelid from pg_class where relname = 'text_tbl'); > relname > - > pg_toast_163219 > (1 row) > > regression=# reindex table pg_toast.pg_toast_163219; > REINDEX > regression=# > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] little doubt
Shoul I thank the guys who help me? Or to post other message saying ´it worked well etc´? or it will flood the lists? I´m new(bie) on the lists. tnx Eric Anderson Martins MirandaNet Admin @ Via Net SAO
Re: [SQL] [Fwd: SQL book]
Jodi, > I am looking to purchase a SQL book to use with our postgresql database. > Can anyone recommend a good one? See: http://techdocs.postgresql.org/techdocs/bookreviews.php (HEY EVERYONE ELSE: I could use some more book reviews. It's 100 words, it's not hard ... e-mail me!) -- Josh Berkus Aglio Database Solutions San Francisco ---(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] little doubt
Eric, > Shoul I thank the guys who help me? > Or to post other message saying ´it worked well etc´? > or it will flood the lists? I suggest sending a thank-you directly to the people who helped you, but not to the list. -- Josh Berkus Aglio Database Solutions San Francisco ---(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] [Fwd: SQL book]
Thomas, > I use SQL In A Nutshell as I have to switch gears between Oracle, Postgres > and MySQL. Also I have limited time for reading and am a very cheap guy. > It goes for $30 US. FYI, as I mention in my review, I found that SQL in a Nutshell omits several important SQL commands. But it does give you a good general syntax comparison. Jodi, SQL in a Nutshell is a reference for people who already know SQL ... not a primer. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [PHP] faster output from php and postgres
So, I have the same problem, but I need all authors for each publication
to show up in it's own column. I tried the full join query from a
suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
of ALL authors at once.
I'll do some RTFMing of the joins.. and post any results I get
BUT, right now, looping over each publication with php isn't taking that
long. I would like to know how to make this query, though!
Please let me know how to get a result like:
|All Authors|Title|Source|Year|Type|Length|Keywords|
If there is a way on the SQL side to do this, that is ;>
Here we go:
CREATE TABLE "author" (
"auth_id" integer DEFAULT
nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
"first" text,
"last" text,
"auth_last_updated" timestamp with time zone,
Constraint "author_temp_pkey" Primary Key ("auth_id")
);
CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
(auth_last_updated);
CREATE TABLE "publication" (
"copyis" text,
"pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
NOT NULL,
"title" text,
"source" text,
"year" text,
"month" text,
"length" text,
"type" text,
"keywords" text,
"copyright" text,
"abstract" text,
"pdformat" text,
"pub_last_updated" timestamp with time zone
);
CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
(pub_id);
CREATE INDEX keywords_publication_key ON publication USING btree
(keywords);
CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
btree (pub_last_updated);
CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
(pub_id);
CREATE TABLE "pub_auth" (
"pub_auth_id" integer DEFAULT
nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
"pub_id" integer,
"auth_id" integer,
Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
);
CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
On Sat, 24 May 2003, Frank Bax wrote:
> Finding previous examples of complex joins in archives is not likely an
> easy thing to find.
>
> pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
>
> Change [database] to the name of your database - this command will dump out
> schema relative to your request. Post the results to this list. Then ask
> us the question "how do I write a SELECT that produces...[ you finish this
> sentence]". Question probably more appropriate to the list you mentioned,
> but I expect there are people here who are just as capable of answering the
> question. I've even seen examples where the process goes through several
> emails before SQL produces desired results exactly.
>
> >How would a join make this easier?
>
> I have always found that one properly constructed complex query is always
> "cheaper" in runtime than numerous queries inside a foreach loop. Your
> final query will likely include joining a table to itself (this can
> sometimes be a difficult concept to grasp).
>
> Frank
>
>
> At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
>
>
> >I'm glad this came up, because I have the same type of problem. Except,
> >I don't see how a join can work... of course, I'm not really schooled in
> >this stuff.
> >
> >I also have three tables: author, publication, and pub_auth.
> >
> >There are multiple authors for some publications, so it is necessary to
> >check each publication selected for ALL authors. I'm doing this with a
> >foreach loop on the result of each publication key returned.
> >
> >How would a join make this easier?
> >
> >I'm browsing the pgsql-sql archives now, but that may take a week. I'm
> >not sure what to search for...
> >
-Chadwick
---(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] little doubt
On Tuesday 27 May 2003 4:37 pm, Eric Anderson Vianet SAO wrote: > Shoul I thank the guys who help me? A private "thank you" is always appreciated (well, by me anyway), or a public "thanks to all who..." > Or to post other message saying ´it worked well etc´? > or it will flood the lists? If possible, summarise the problem and solution, write a good subject line "SOLVED: ." and post that to the list so others might find it if they search the archives. > I´m new(bie) on the lists. Best thanks is to see if you can't contribute something back yourself - there's always someone who knows less than you. Especially if you have time to scan for unanswered questions that are a day or two old. Even if all you can do is help someone rephrase their question, that's useful. HTH -- Richard Huxton ---(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] alter column witdh
At 10:38 AM 5/27/03, Eric Anderson Vianet SAO wrote: >how can I alter the column width http://techdocs.postgresql.org/techdocs/updatingcolumns.php fbax=# create table tablename (columnname text); CREATE fbax=# \d tablename Table "tablename" Attribute | Type | Modifier +--+-- columnname | text | fbax=# \q pg_dump -o -c -s -t tablename fbax | sed "s:\"columnname\" text:\"columnname\" varchar(2):" | psql Frank ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PHP] faster output from php and postgres
On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote: > So, I have the same problem, but I need all authors for each publication > to show up in it's own column. I tried the full join query from a > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead > of ALL authors at once. > > I'll do some RTFMing of the joins.. and post any results I get > > BUT, right now, looping over each publication with php isn't taking that > long. I would like to know how to make this query, though! > > Please let me know how to get a result like: > > > |All Authors|Title|Source|Year|Type|Length|Keywords| Well, if you search the archives for terms "text", "concat", "aggregate" you should come up with one solution. This involves writing your own aggregate function, like SUM() but for text. Don't worry, it's not difficult. The only issue is that you won't be able to guarantee the order of authors in the field. There might be something on this in my PostgreSQL Notes on http://techdocs.postgresql.org/ too - can't remember for sure. -- Richard Huxton ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PHP] faster output from php and postgres
On Tue, 27 May 2003, Richard Huxton wrote: > On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote: > > So, I have the same problem, but I need all authors for each publication > > to show up in it's own column. I tried the full join query from a > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead > > of ALL authors at once. > > > > I'll do some RTFMing of the joins.. and post any results I get > > > > BUT, right now, looping over each publication with php isn't taking that > > long. I would like to know how to make this query, though! > > > > Please let me know how to get a result like: > > > > > > |All Authors|Title|Source|Year|Type|Length|Keywords| > > Well, if you search the archives for terms "text", "concat", "aggregate" you > should come up with one solution. This involves writing your own aggregate > function, like SUM() but for text. Don't worry, it's not difficult. The only > issue is that you won't be able to guarantee the order of authors in the > field. > > There might be something on this in my PostgreSQL Notes on > http://techdocs.postgresql.org/ too - can't remember for sure. > > -- > Richard Huxton A function is definitely in need. But I already have PHP doing something of this sort. I realize that PostgreSQL would most likely do it faster. What I meant by the result output above was that there would be _many_ results, but for each publication only ONE column for ALL authors in EACH row. (*whew*) So perhaps a function, but not an aggregate function. I'll look into functions next, as it seems that there is no single SQL statement that can do this... I can't see how. -Chadwick ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [PHP] faster output from php and postgres
On Tue, 2003-05-27 at 14:19, Richard Huxton wrote: > On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote: > > So, I have the same problem, but I need all authors for each publication > > to show up in it's own column. I tried the full join query from a > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead > > of ALL authors at once. > > > > I'll do some RTFMing of the joins.. and post any results I get > > > > BUT, right now, looping over each publication with php isn't taking that > > long. I would like to know how to make this query, though! > > > > Please let me know how to get a result like: > > > > > > |All Authors|Title|Source|Year|Type|Length|Keywords| > > Well, if you search the archives for terms "text", "concat", "aggregate" you > should come up with one solution. This involves writing your own aggregate > function, like SUM() but for text. Don't worry, it's not difficult. The only > issue is that you won't be able to guarantee the order of authors in the > field. If order is required: SELECT custom_aggregate(author) as authors FROM (SELECT author FROM table ORDER BY author) AS tab; The above should give you authors in alphabetical order if custom_aggregate() was written to concatenate text. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [SQL] [PHP] faster output from php and postgres
KISS
why not use PHP to concatenate the authors while pub_id is the same???
If you insist on having each author in its own column,
put them at the end and concatenate with .
jll
Chadwick Rolfs wrote:
>
> So, I have the same problem, but I need all authors for each publication
> to show up in it's own column. I tried the full join query from a
> suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
> of ALL authors at once.
>
> I'll do some RTFMing of the joins.. and post any results I get
>
> BUT, right now, looping over each publication with php isn't taking that
> long. I would like to know how to make this query, though!
>
> Please let me know how to get a result like:
>
> |All Authors|Title|Source|Year|Type|Length|Keywords|
>
>
> If there is a way on the SQL side to do this, that is ;>
>
> Here we go:
>
> CREATE TABLE "author" (
> "auth_id" integer DEFAULT
> nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
> "first" text,
> "last" text,
> "auth_last_updated" timestamp with time zone,
> Constraint "author_temp_pkey" Primary Key ("auth_id")
> );
>
> CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
>
> CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
> (auth_last_updated);
>
> CREATE TABLE "publication" (
> "copyis" text,
> "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
> NOT NULL,
> "title" text,
> "source" text,
> "year" text,
> "month" text,
> "length" text,
> "type" text,
> "keywords" text,
> "copyright" text,
> "abstract" text,
> "pdformat" text,
> "pub_last_updated" timestamp with time zone
> );
>
> CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
> (pub_id);
>
> CREATE INDEX keywords_publication_key ON publication USING btree
> (keywords);
>
> CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
> btree (pub_last_updated);
>
> CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
> (pub_id);
>
> CREATE TABLE "pub_auth" (
> "pub_auth_id" integer DEFAULT
> nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
> "pub_id" integer,
> "auth_id" integer,
> Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
> );
>
> CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
>
> CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
>
> On Sat, 24 May 2003, Frank Bax wrote:
>
> > Finding previous examples of complex joins in archives is not likely an
> > easy thing to find.
> >
> > pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
> >
> > Change [database] to the name of your database - this command will dump out
> > schema relative to your request. Post the results to this list. Then ask
> > us the question "how do I write a SELECT that produces...[ you finish this
> > sentence]". Question probably more appropriate to the list you mentioned,
> > but I expect there are people here who are just as capable of answering the
> > question. I've even seen examples where the process goes through several
> > emails before SQL produces desired results exactly.
> >
> > >How would a join make this easier?
> >
> > I have always found that one properly constructed complex query is always
> > "cheaper" in runtime than numerous queries inside a foreach loop. Your
> > final query will likely include joining a table to itself (this can
> > sometimes be a difficult concept to grasp).
> >
> > Frank
> >
> >
> > At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
> >
> >
> > >I'm glad this came up, because I have the same type of problem. Except,
> > >I don't see how a join can work... of course, I'm not really schooled in
> > >this stuff.
> > >
> > >I also have three tables: author, publication, and pub_auth.
> > >
> > >There are multiple authors for some publications, so it is necessary to
> > >check each publication selected for ALL authors. I'm doing this with a
> > >foreach loop on the result of each publication key returned.
> > >
> > >How would a join make this easier?
> > >
> > >I'm browsing the pgsql-sql archives now, but that may take a week. I'm
> > >not sure what to search for...
> > >
>
> -Chadwick
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] [PHP] faster output from php and postgres
Yes, this may be better than foreach()ing through each publication
returned.
But a plpgsql function may suit these needs much better.
On Tue, 27 May 2003, Jean-Luc Lachance wrote:
> KISS
>
> why not use PHP to concatenate the authors while pub_id is the same???
> If you insist on having each author in its own column,
> put them at the end and concatenate with .
>
> jll
>
>
> Chadwick Rolfs wrote:
> >
> > So, I have the same problem, but I need all authors for each publication
> > to show up in it's own column. I tried the full join query from a
> > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead
> > of ALL authors at once.
> >
> > I'll do some RTFMing of the joins.. and post any results I get
> >
> > BUT, right now, looping over each publication with php isn't taking that
> > long. I would like to know how to make this query, though!
> >
> > Please let me know how to get a result like:
> >
> > |All Authors|Title|Source|Year|Type|Length|Keywords|
> >
> >
> > If there is a way on the SQL side to do this, that is ;>
> >
> > Here we go:
> >
> > CREATE TABLE "author" (
> > "auth_id" integer DEFAULT
> > nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
> > "first" text,
> > "last" text,
> > "auth_last_updated" timestamp with time zone,
> > Constraint "author_temp_pkey" Primary Key ("auth_id")
> > );
> >
> > CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
> >
> > CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
> > (auth_last_updated);
> >
> > CREATE TABLE "publication" (
> > "copyis" text,
> > "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
> > NOT NULL,
> > "title" text,
> > "source" text,
> > "year" text,
> > "month" text,
> > "length" text,
> > "type" text,
> > "keywords" text,
> > "copyright" text,
> > "abstract" text,
> > "pdformat" text,
> > "pub_last_updated" timestamp with time zone
> > );
> >
> > CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
> > (pub_id);
> >
> > CREATE INDEX keywords_publication_key ON publication USING btree
> > (keywords);
> >
> > CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
> > btree (pub_last_updated);
> >
> > CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
> > (pub_id);
> >
> > CREATE TABLE "pub_auth" (
> > "pub_auth_id" integer DEFAULT
> > nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
> > "pub_id" integer,
> > "auth_id" integer,
> > Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
> > );
> >
> > CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
> >
> > CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
> >
> > On Sat, 24 May 2003, Frank Bax wrote:
> >
> > > Finding previous examples of complex joins in archives is not likely an
> > > easy thing to find.
> > >
> > > pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
> > >
> > > Change [database] to the name of your database - this command will dump out
> > > schema relative to your request. Post the results to this list. Then ask
> > > us the question "how do I write a SELECT that produces...[ you finish this
> > > sentence]". Question probably more appropriate to the list you mentioned,
> > > but I expect there are people here who are just as capable of answering the
> > > question. I've even seen examples where the process goes through several
> > > emails before SQL produces desired results exactly.
> > >
> > > >How would a join make this easier?
> > >
> > > I have always found that one properly constructed complex query is always
> > > "cheaper" in runtime than numerous queries inside a foreach loop. Your
> > > final query will likely include joining a table to itself (this can
> > > sometimes be a difficult concept to grasp).
> > >
> > > Frank
> > >
> > >
> > > At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
> > >
> > >
> > > >I'm glad this came up, because I have the same type of problem. Except,
> > > >I don't see how a join can work... of course, I'm not really schooled in
> > > >this stuff.
> > > >
> > > >I also have three tables: author, publication, and pub_auth.
> > > >
> > > >There are multiple authors for some publications, so it is necessary to
> > > >check each publication selected for ALL authors. I'm doing this with a
> > > >foreach loop on the result of each publication key returned.
> > > >
> > > >How would a join make this easier?
> > > >
> > > >I'm browsing the pgsql-sql archives now, but that may take a week. I'm
> > > >not sure what to search for...
> > > >
> >
> > -Chadwick
> >
> > ---(end of broadcast)---
[SQL] Getting rid of accents..
Is there any easy way for converting accented text to closest text without accents in postgresql ? eg: BÂLÂ MORGHÂB to BALA MORGHAB Regds Mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(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] Getting rid of accents..
Hi Mallah, I had this problem once, and put together this bunch of regexes. It's by no means optimal, but should solve 90% and would easily be adapted into a plperl function. Begin perl: $value =~ s/[\xc0-\xc6]/A/g; $value =~ s/[\xc7]/C/g; $value =~ s/[\xc8-\xcb]/E/g; $value =~ s/[\xcc-\xcf]/I/g; $value =~ s/[\xd1]/N/g; $value =~ s/[\xd2-\xd6\xd8]/O/g; $value =~ s/[\xd9-\xdc]/U/g; $value =~ s/[\xdd]/Y/g; $value =~ s/[\xe0-\xe6]/a/g; $value =~ s/[\xe7]/c/g; $value =~ s/[\xe8-\xeb]/e/g; $value =~ s/[\xec-\xef]/i/g; $value =~ s/[\xf1]/n/g; $value =~ s/[\xf2-\xf6\xd8]/o/g; $value =~ s/[\xf9-\xfc]/u/g; $value =~ s/[\xfd\xff]/y/g; On Tuesday, May 27, 2003, at 04:55 PM, <[EMAIL PROTECTED]> wrote: Is there any easy way for converting accented text to closest text without accents in postgresql ? eg: BÂLÂ MORGHÂB to BALA MORGHAB Regds Mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(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 ---(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] Getting rid of accents..
Full disclosure on previously posted Perl code: I think I may have cribbed all or part of that previous code from something (Perl cookbook?). In any case, the issue is essentially a mapping of which ascii codes "look like" low-ascii, so I don't think there are any authorship issues. Best, Randall On Tuesday, May 27, 2003, at 04:55 PM, <[EMAIL PROTECTED]> wrote: Is there any easy way for converting accented text to closest text without accents in postgresql ? eg: BÂLÂ MORGHÂB to BALA MORGHAB Regds Mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Getting rid of accents..
On Tuesday 27 May 2003 22:55, [EMAIL PROTECTED] wrote:
> Is there any easy way for converting accented text to
> closest text without accents in postgresql ?
>
> eg:
>
> BÂLÂ MORGHÂB to BALA MORGHAB
Have you looked at to_ascii()? Something along the lines of
select to_ascii('Â', 'LATIN1')
Ian Barwick
[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] Empty queries guaranteed to work?
Tom Lane mentioned in this post that an empty query can be sent to the server to determine whether the connection is still good: http://archives.postgresql.org/pgsql-hackers/2001-10/msg00643.php Is a query of "" guaranteed to work as long as the connection is good? What about ";" or " "? Background: I am maintaining some postgres client code (C++/libpq) that, during exception handling for a failed query, must determine whether the database connection is still good. This check is currently done by sending a "select version()" query and checking the result. However, even that simple query fails with PGRES_FATAL_ERROR when the connection is still good, if executed in an aborted transaction. (I have no idea why in the world a fatal error would be reported, when the connection is perfectly good and a rollback is all that's needed.) I need to be able to distinguish this situation from a real fatal error, and PQstatus() appears to be unreliable for this purpose. A "" query that returns PGRES_EMPTY_QUERY seems to be a good indicator that the connection is good, even within aborted transactions. Now I just need to know whether this is documented and guaranteed to work. Suggestions of alternative methods are welcome. ---(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] Getting rid of accents..
Thanks Ian ,
Indeed its the simplest/easiest solution to this problem i feel.
i did tried to_ascii function but was not specifying 'LATIN1'
do i was getting error
tradein_clients=# SELECT to_ascii('BÂLÂ MORGHÂB' );
ERROR: pg_to_ascii(): unsupported encoding from SQL_ASCII
would u like to explain it ?
Then i created a new database in latin1 encoding loaded the data
used to_ascii to convert , copied the data to new file and reloaded
back to original database ;-) .oO (tiring )
Lucas Thanks for that perl stuff too i will use it in some program
that stuffs arbitary text into database.
Regds
Mallah.
> On Tuesday 27 May 2003 22:55, [EMAIL PROTECTED] wrote:
>> Is there any easy way for converting accented text to
>> closest text without accents in postgresql ?
>>
>> eg:
>>
>> BÂLÂ MORGHÂB to BALA MORGHAB
>
> Have you looked at to_ascii()? Something along the lines of
>
> select to_ascii('Â', 'LATIN1')
>
>
> Ian Barwick
> [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])
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(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
