Re: [SQL] [pg-sql] Character encoding

[Vivien Malerba]

Le jeu 25/09/2003 à 17:20, BenLaKnet a écrit :
> How is it possible to convert a database in SQL_ASCII to UNICODE ?
> 
> Thx a lot

I did a pg_dump to a file, converted that file to UTF-8 encoding using
iconv, created a new DB using the "-E unicode" option, and imported the
data into that new DB from the converted dump file.

There is maybe a better solution, but this one works.

Vivien


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] RFC: i18n2ascii(TEXT) stored procedure

[scott.marlowe]

On Thu, 25 Sep 2003, Michael A Nachbaur wrote:

> I've created the following stored procedure to allow me to do 
> international-insensitive text searches, e.g. a search for "Resume" would 
> match the text "Résumé".
> 
> I wanted to know:
> 
> a) am I missing any characters that need to be converted?  My first (and only 
> language) is English, so I'm in the dark when that is concerned;
> b) is there a better and/or faster way of implementing this?  I don't want 
> searches to bog down (at least too badly) as a result of this.
> 
> CREATE OR REPLACE FUNCTION i18n2ascii (TEXT) RETURNS TEXT AS '
> my ($source) = @_;
> $source =~ 
> tr/áàâäéèêëíìîïóòôöúùûüÁÀÂÄÉÈÊËÍÌÎÏÓÒÔÖÚÙÛÜ//;
> return $source;
> ' LANGUAGE 'plperl';


You could probably accomplish the same thing without using perl via the 
built in function translate().  Look in the functions-string.html in the 
7.3.x documentation.

Also, the regex version of substring() is quite powerful.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] How to generate object DDL of the database objects

[Tom Lane]

"Kumar" <[EMAIL PROTECTED]> writes:
> Any body could pls share their idea on creating object DDL for the postgres=
>  data objects from the Postgres Server 7.3.4 running on RH Linux 7.2.

Perhaps you are looking for "pg_dump -s".

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] tsearch2 question

[Wei Weng]

But then when I do a psql < tsearch2.sql, it complains:

bash-2.05a$ psql testdb < tsearch2.sql 
SET
BEGIN
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
'pg_ts_dict_pkey' for table 'pg_ts_dict'
CREATE TABLE
ERROR:  stat failed on file '$libdir/tsearch2': No such file or directory
ERROR:  current transaction is aborted, queries ignored until end of 
transaction block
ERROR:  current transaction is aborted, queries ignored until end of 
transaction block
...
(and the ERROR message repeats many times)

Where did I do wrong??

Thanks

Wei


On Thu, 25 Sep 2003, Tom Lane wrote:

> Wei Weng <[EMAIL PROTECTED]> writes:
> > In my tsearch2.sql there are statements like :
> > --dict interface
> > CREATE FUNCTION lexize(oid, text) 
> > returns _text
> > as '$libdir/tsearch2'
> > language 'C'
> > with (isstrict);
> 
> > I don't think $libdir is the real value that we want.
> 
> Yes it is.
> 
>   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 7: don't forget to increase your free space map settings

Re: [SQL] tsearch2 question

[Tom Lane]

Wei Weng <[EMAIL PROTECTED]> writes:
> But then when I do a psql < tsearch2.sql, it complains:
> bash-2.05a$ psql testdb < tsearch2.sql 
> ERROR:  stat failed on file '$libdir/tsearch2': No such file or directory
> Where did I do wrong??

Did you do "make install" after building tsearch2?

If you did, maybe it installed tsearch2.so in the wrong place?
("pg_config --pkglibdir" will tell you what the backend thinks
"$libdir" means.)

regards, tom lane

---(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: [SQL] tsearch2 question

[Wei Weng]

When I run psql < tsearch2.sql, is psql going to substitute $libdir 
internally with what $libdir really is (in my case, it would be 
/usr/lib/pgsql)? 

Thanks

Wei


On Fri, 26 Sep 2003, Tom Lane wrote:

> Wei Weng <[EMAIL PROTECTED]> writes:
> > But then when I do a psql < tsearch2.sql, it complains:
> > bash-2.05a$ psql testdb < tsearch2.sql 
> > ERROR:  stat failed on file '$libdir/tsearch2': No such file or directory
> > Where did I do wrong??
> 
> Did you do "make install" after building tsearch2?
> 
> If you did, maybe it installed tsearch2.so in the wrong place?
> ("pg_config --pkglibdir" will tell you what the backend thinks
> "$libdir" means.)
> 
>   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] tsearch2 question

[Tom Lane]

Wei Weng <[EMAIL PROTECTED]> writes:
> When I run psql < tsearch2.sql, is psql going to substitute $libdir 
> internally with what $libdir really is (in my case, it would be 
> /usr/lib/pgsql)? 

Not psql, the backend.  The point of this is that your CREATE FUNCTION
definition can be platform-independent ...

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] Case Insensitive comparison

[Roberto Mello]

On Thu, Sep 25, 2003 at 08:46:39PM -0700, Josh Berkus wrote:
> 
> NULLIF is the converse of COALESCE().

Oh, ooops! My apologies.
 
> Any idea when you're going to overhaul the CookBook?   

*sighs*

The software is pretty much ready. I'll have time to install and configure
it next week, after my exams.

-Roberto

-- 
+|Roberto Mello   -http://www.brasileiro.net/  |--+
+   Computer Science Graduate Student, Utah State University  +
+   USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +

---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] Capturing pgsql ERRORS/NOTICES to file

[George Weaver]

I am in the process of creating a batch file 
that will update some functions in a database for a remote user similar 
to:
 
psql -o output dbname < 
functionupdate.sql
 
Is there any way to save any ERROR and 
NOTICE messages to a file?  
 
The -o option doesn't capture this 
information.
 
Thanks,
George

Re: [SQL] Capturing pgsql ERRORS/NOTICES to file

[Josh Berkus]

George,

> I am in the process of creating a batch file that will update some
> functions in a database for a remote user similar to:
>
> psql -o output dbname < functionupdate.sql
>
> Is there any way to save any ERROR and NOTICE messages to a file?
>
> The -o option doesn't capture this information.

You have to use command shell redirects.

For example, I commonly do in bash
psql -o output dbname < functionupdate.sql >out.dump
... which sends all the command responses to a file, allowing me to read only 
the errors on the screen.

See a guide to your shell for more creative redirection.

-- 
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] tsearch2 question

[Wei Weng]

When I ran psql testdb < untsearch2.sql

I got the following error message:

psql:untsearch2.sql:15: ERROR:  RemoveAggregate: aggregate stat(tsvector) 
does not exist

I didn't really do anything before this. Only dropped the trigger and gist 
index I created (in order to use tsearch2), and alter table testtb drop 
column idxtest tsvector.

Where did I do wrong?

Thanks

Wei


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Capturing pgsql ERRORS/NOTICES to file

[George Weaver]

Hi Josh,

Thanks for the reply.

What I am trying to achieve is to have errors go to a file, rather than show
up on the screen.

Is this possible?

George

- Original Message - 
From: "Josh Berkus" <[EMAIL PROTECTED]>
To: "George Weaver" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, September 26, 2003 1:33 PM
Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file


> George,
>
> > I am in the process of creating a batch file that will update some
> > functions in a database for a remote user similar to:
> >
> > psql -o output dbname < functionupdate.sql
> >
> > Is there any way to save any ERROR and NOTICE messages to a file?
> >
> > The -o option doesn't capture this information.
>
> You have to use command shell redirects.
>
> For example, I commonly do in bash
> psql -o output dbname < functionupdate.sql >out.dump
> ... which sends all the command responses to a file, allowing me to read
only
> the errors on the screen.
>
> See a guide to your shell for more creative redirection.
>
> -- 
> 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])
>


---(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: [SQL] Capturing pgsql ERRORS/NOTICES to file

[Wei Weng]

George:

Have you tried psql {whatever operations} 2> error_output ?

(for Bash)

Thanks

Wei


On Fri, 26 Sep 2003, George Weaver wrote:

> Hi Josh,
> 
> Thanks for the reply.
> 
> What I am trying to achieve is to have errors go to a file, rather than show
> up on the screen.
> 
> Is this possible?
> 
> George
> 
> - Original Message - 
> From: "Josh Berkus" <[EMAIL PROTECTED]>
> To: "George Weaver" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> Sent: Friday, September 26, 2003 1:33 PM
> Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file
> 
> 
> > George,
> >
> > > I am in the process of creating a batch file that will update some
> > > functions in a database for a remote user similar to:
> > >
> > > psql -o output dbname < functionupdate.sql
> > >
> > > Is there any way to save any ERROR and NOTICE messages to a file?
> > >
> > > The -o option doesn't capture this information.
> >
> > You have to use command shell redirects.
> >
> > For example, I commonly do in bash
> > psql -o output dbname < functionupdate.sql >out.dump
> > ... which sends all the command responses to a file, allowing me to read
> only
> > the errors on the screen.
> >
> > See a guide to your shell for more creative redirection.
> >
> > -- 
> > 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])
> >
> 
> 
> ---(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
> 

---(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: [SQL] Capturing pgsql ERRORS/NOTICES to file

[George Weaver]

Hi Wei,

I hadn't tried that, and it did the trick!

Thank you!

George

- Original Message - 
From: "Wei Weng" <[EMAIL PROTECTED]>
To: "George Weaver" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, September 26, 2003 2:16 PM
Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file


> George:
>
> Have you tried psql {whatever operations} 2> error_output ?
>
> (for Bash)
>
> Thanks
>
> Wei
>
>
> On Fri, 26 Sep 2003, George Weaver wrote:
>
> > Hi Josh,
> >
> > Thanks for the reply.
> >
> > What I am trying to achieve is to have errors go to a file, rather than
show
> > up on the screen.
> >
> > Is this possible?
> >
> > George
> >
> > - Original Message - 
> > From: "Josh Berkus" <[EMAIL PROTECTED]>
> > To: "George Weaver" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
> > Sent: Friday, September 26, 2003 1:33 PM
> > Subject: Re: [SQL] Capturing pgsql ERRORS/NOTICES to file
> >
> >
> > > George,
> > >
> > > > I am in the process of creating a batch file that will update some
> > > > functions in a database for a remote user similar to:
> > > >
> > > > psql -o output dbname < functionupdate.sql
> > > >
> > > > Is there any way to save any ERROR and NOTICE messages to a file?
> > > >
> > > > The -o option doesn't capture this information.
> > >
> > > You have to use command shell redirects.
> > >
> > > For example, I commonly do in bash
> > > psql -o output dbname < functionupdate.sql >out.dump
> > > ... which sends all the command responses to a file, allowing me to
read
> > only
> > > the errors on the screen.
> > >
> > > See a guide to your shell for more creative redirection.
> > >
> > > -- 
> > > 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]
g)
> > >
> >
> >
> > ---(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
> >
>


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] does postgresql execute unions in parallel?

[Gaetano Mendola]

teknokrat wrote:

If I have several selects joined with unions does postgresql execute the 
concurrently or not?
nope.



Regards
Gaetan Mendola
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] does postgresql execute unions in parallel?

[Christopher Browne]

[EMAIL PROTECTED] (Gaetano Mendola) writes:
> teknokrat wrote:
>> If I have several selects joined with unions does postgresql
>> execute the concurrently or not?
>
> nope.

I was talking with Jan about that very idea yesterday; this would seem
to be the place where PostgreSQL might take some (possibly even nearly
magical :-)) benefit from threading.

The usual way that people expect to use threading is for each
connection to have a thread.

If, instead, every _join_ had a thread, that would allow producers of
data to look for their data quasi-independently, passing result sets
upwards towards the return set to whatever thread was waiting to
consume the data.  

This would allow one complex query to take over a whole horde of
processors :-).

The "magic" part would be if the system decided, "The SEQ SCAN on the
table I'm looking at is a big one; let's split it into 4 chunks, doing
a virtual UNION ALL, and thereby filter bits of it in parallel on 4
CPUs."  That would provide many of the benefits Informix claimed from
"fragmentation" without having to fragment the table :-).
-- 
If this was helpful,  rate me
http://www3.sympatico.ca/cbbrowne/lsf.html
Rules of the  Evil Overlord #187. "I will not  hold lavish banquets in
the middle of  a famine. The good PR among the  guests doesn't make up
for the bad PR among the masses."  

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Removing simliar elements from a set

[Dan Langille]

Hi folks,

I'm trying to remove items from a set which are similar to items in
another set.

This is the set (MASTER) from which I wish to remove items:

 /ports/Mk/bsd.python.mk
 /ports/lang/python-doc-html/distinfo
 /ports/lang/python/Makefile
 /ports/lang/python/distinfo
 /ports/lang/python/files/patch-Modules-Setup.dist

These are the items (MATCHES) which are the prefixes which must be
removed:

 /ports/lang/python-doc-html
 /ports/lang/python

In this case, the answer would be:

 /ports/Mk/bsd.python.mk

In short, we remove all items from MASTER which are under the directories
specified in MATCHES.

My first attempt, which works only if MATCHES contains one item:

SELECT *
  FROM MASTER JOIN MATCHES
ON NOT (MASTER.pathname ~ ('^' || MATCHES.pathname || '/.+'));

However, if there is more than one row in MATCHES, this will not work.

Clues please?
-- 
Dan Langille - http://www.langille.org/

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Removing simliar elements from a set

[Josh Berkus]

Dan, 

> I'm trying to remove items from a set which are similar to items in
> another set.


> In short, we remove all items from MASTER which are under the directories
> specified in MATCHES.

from your example, you are trying to remove all directories which do *not* 
match.   What do you want, exactly?

-- 
-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] [HACKERS] plpgsql doesn't coerce boolean expressions to boolean

[Bruce Momjian]

Where are we on this --- we all decided on #4.  Does this just require
an announcment in the release notes.

(I need to complete the release notes soon.)

---

Tom Lane wrote:
> Following up this gripe
> http://archives.postgresql.org/pgsql-sql/2003-09/msg00044.php
> I've realized that plpgsql just assumes that the test expression
> of an IF, WHILE, or EXIT statement is a boolean expression.  It
> doesn't take any measures to ensure this is the case or convert
> the value if it's not the case.  This seems pretty bogus to me.
> 
> However ... with the code as it stands, for pass-by-reference datatypes
> any nonnull value will appear TRUE, while for pass-by-value datatypes
> any nonzero value will appear TRUE.  I fear that people may actually be
> depending on these behaviors, particularly the latter one which is
> pretty reasonable if you're accustomed to C.  So while I'd like to throw
> an error if the argument isn't boolean, I'm afraid of breaking people's
> function definitions.
> 
> Here are some possible responses, roughly in order of difficulty
> to implement:
> 
> 1. Leave well enough alone (and perhaps document the behavior).
> 
> 2. Throw an error if the expression doesn't return boolean.
> 
> 3. Try to convert nonbooleans to boolean using plpgsql's usual method
>for cross-type coercion, ie run the type's output proc to get a
>string and feed it to bool's input proc.  (This seems unlikely to
>avoid throwing an error in very many cases, but it'd be the most
>consistent with other parts of plpgsql.)
> 
> 4. Use the parser's coerce_to_boolean procedure, so that nonbooleans
>will be accepted in exactly the same cases where they'd be accepted
>in a boolean-requiring SQL construct (such as CASE).  (By default,
>none are, so this isn't really different from #2.  But people could
>create casts to boolean to override this behavior in a controlled
>fashion.)
> 
> Any opinions about what to do?
> 
>   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])
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] [HACKERS] plpgsql doesn't coerce boolean expressions to boolean

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Where are we on this --- we all decided on #4.  Does this just require
> an announcment in the release notes.

I haven't done anything about it --- been busy with other stuff, and I
wasn't sure we'd agreed to change it for 7.4 anyway.  I'm willing to
make the code change though.

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend