Re: [GENERAL] best way to manage indexes

On Wed, Dec 23, 2009 at 3:10 PM, Jamie Kahgee wrote: > what would be considered "best practice" for my situation? > I have a table member, with column name that I want to put an index on, > because it is searched quiet frequently. When I create my sql search string, > the name will consist only of

[GENERAL] best way to manage indexes

what would be considered "best practice" for my situation? I have a table *member*, with column *name *that I want to put an index on, because it is searched quiet frequently. When I create my sql search string, the name will consist only of alpha-numeric characters and be compared against lowerca

Re: [GENERAL] WARNING: nonstandard use of escape in a string literal

On Wed, Dec 23, 2009 at 3:52 PM, Bill Moran wrote: > In response to "Patrick M. Rutkowski" : > >> No, that doesn't sound right. >> >> I'm not trying to insert a literal '\s' or anything (whatever the heck >> that might mean). The sequence '\s' is to be interpreted by the ~ >> regular expression op

Re: [GENERAL] How to add month.year column validation

On Wed, Dec 23, 2009 at 12:02 PM, Andy Shellam wrote: > Andrus, > >> >> -- add bad data >> INSERT INTO foo VALUES (''); >> INSERT INTO foo VALUES ('02.x'); >> INSERT INTO foo VALUES ('02.1970'); >> INSERT INTO foo VALUES ('02.2101'); >> INSERT INTO foo VALUES (NULL); >> -- add good data >> INSERT

Re: [GENERAL] WARNING: nonstandard use of escape in a string literal

John R Pierce writes: > Bill Moran wrote: >> You need to spend some quality time with the documentation. Seriously, >> the issue _is_ confusing, but the docs explain it all, if you take the >> time to read all of it. > and, naturally, this gets even more complicated and confusing when the > str

Re: [GENERAL] WARNING: nonstandard use of escape in a string literal

Bill Moran wrote: You need to spend some quality time with the documentation. Seriously, the issue _is_ confusing, but the docs explain it all, if you take the time to read all of it. To directly answer your question, \s is not a recognized escape sequence, so PG passes it unchanged. However,

Re: [GENERAL] WARNING: nonstandard use of escape in a string literal

In response to "Patrick M. Rutkowski" : > No, that doesn't sound right. > > I'm not trying to insert a literal '\s' or anything (whatever the heck > that might mean). The sequence '\s' is to be interpreted by the ~ > regular expression operator, isn't it? I would imagine that I would > want the s

Re: [GENERAL] postgres: writer process,what does this process actually do?

On 23/12/2009 02:56, Thomas wrote: > And could you give me some info about postgres internals? Such as > ebooks or online articles. > There's quite a bit in the manual: http://www.postgresql.org/docs/8.4/interactive/internals.html Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie

Re: [GENERAL] WARNING: nonstandard use of escape in a string literal

No, that doesn't sound right. I'm not trying to insert a literal '\s' or anything (whatever the heck that might mean). The sequence '\s' is to be interpreted by the ~ regular expression operator, isn't it? I would imagine that I would want the sequence of BACKSLASH + LETTER_S to go through to the

Re: [GENERAL] WARNING: nonstandard use of escape in a string literal

In response to "Patrick M. Rutkowski" : > I just ran something like: > = > UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' > = > > > I got the following warnings/hints as a result: > ==

[GENERAL] 12/08 SFPUG meeting, "Operator Exclusion Constraints," video now available

The video archive for the 12/08 SFPUG meeting, "Operator Exclusion Constraints," is now available: http://thebuild.com/blog/2009/12/23/sfpug-operator-exclusion-constraints/ -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql

[GENERAL] WARNING: nonstandard use of escape in a string literal

I just ran something like: = UPDATE my_table SET colname = NULL WHERE colname ~ '^\s*$' = I got the following warnings/hints as a result: = WARNING: nonstandard use

Re: [GENERAL] How to get a list of tables that have a particular column value?

Rajan, Pavithra wrote: Hello - I would like to know if there is a way to find all the table names in a data base that have a particular* column value* eg:"volt" .ie given a column value (not column name) how to I find which tables and their column names have them .Thank you. Do you wan

Re: [GENERAL] How to add month.year column validation

Andrus, > > -- add bad data > INSERT INTO foo VALUES (''); > INSERT INTO foo VALUES ('02.x'); > INSERT INTO foo VALUES ('02.1970'); > INSERT INTO foo VALUES ('02.2101'); > INSERT INTO foo VALUES (NULL); > -- add good data > INSERT INTO foo VALUES ('12.2009'); > > delete from foo where tmkuu is n

Re: [GENERAL] Simple function

Thanks a lot, that worked great. Saved me a lot of time trying to figure it out too. Christine It works for me: postgres=# CREATE OR REPLACE FUNCTION countertest(integer) RETURNS bigint AS $$ SELECT COUNT(*) FROM test WHERE nbr = $1; $$ LANGUAGE SQL; CREATE FUNCTION postgres=# select countert

Re: [GENERAL] Extended Query, flush or sync ?

On 22/12/2009, at 18:15, Tom Lane wrote: > Raimon Fernandez writes: >> But the portal isn't destroyed after a sync ? > > Not directly by a Sync, no. > >> I'm getting a "Portal 'myPortal' doesn't exist "when sending the next >> Execute ... > > End of transaction would destroy portals --- are

[GENERAL] How to get a list of tables that have a particular column value?

Hello - I would like to know if there is a way to find all the table names in a data base that have a particular column value eg:"volt" .ie given a column value (not column name) how to I find which tables and their column names have them .Thank you. Pavithra Rajan

Re: [GENERAL] Simple function

On Wed, Dec 23, 2009 at 9:03 AM, Christine Penner wrote: > Hi, > > I'm trying to create a simple function but having a bit of trouble. This is > what I want to do. > > I want to pass a key as a parameter (BKEY integer) > the code in the function should be > count(*) from F_BUILDINGS where B_PRIMAR

Re: [GENERAL] Simple function

- "Christine Penner" wrote: > Hi, > > I'm trying to create a simple function but having a bit of trouble. > This is what I want to do. > > I want to pass a key as a parameter (BKEY integer) > the code in the function should be > count(*) from F_BUILDINGS where B_PRIMARY_SEQ=BKEY > > I

[GENERAL] Simple function

Hi, I'm trying to create a simple function but having a bit of trouble. This is what I want to do. I want to pass a key as a parameter (BKEY integer) the code in the function should be count(*) from F_BUILDINGS where B_PRIMARY_SEQ=BKEY I want to return the count from the select statement. Ch

Re: [GENERAL] reindex

Sim Zacks writes: > I have an aggregate table which is constantly being overwritten. Every > 10 minutes or so, the table is erased and populated with new data, most > of which is the same. > Basically a materialized view. > I have been going through some queries that use this table and noticed >

Re: [GENERAL] Drop/ Alter index if exist

thanks 2009/12/22 Filip Rembiałkowski > > > 2009/12/22 Pau Marc Munoz Torres > > Hi every body >> > > >> >> there is some way to delete or rename an index only if this index exists? >> >> something like >> >> "alter index index rename to pepe if exists" >> >> > for drop - yes: > DROP INDEX IF

Re: [GENERAL] Archive command seem to be working.

Thanks Andrew, I was on vacation and the /var/lib of root did fill up. The on call person was notified and the fix was to copy all of the logs back into /data/pgsql/backup/wal_arch. Some 6500 files and the copy failed because it couldn't transverse the directory without an error. I mo

Re: [GENERAL] reindex

use truncate instead of delete ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Return Next and Return Query

2009/12/23 Wappler, Robert : > Hello, > > I’m not quite sure, what’s the difference between RETURN NEXT and RETURN > QUERY. > > > > From the documentation (Sec. 38.6) RETURN NEXT returns a table and in my > understanding with each execution a different table. it is wrong. > > RETURN QUERY appends

[GENERAL] Return Next and Return Query

Hello, I'm not quite sure, what's the difference between RETURN NEXT and RETURN QUERY. >From the documentation (Sec. 38.6) RETURN NEXT returns a table and in my understanding with each execution a different table. RETURN QUERY appends a result set of a query to the function's result set, i.e.

Re: [GENERAL] Not finding RPMs for 8.4.2!

2009/12/23 Devrim GÜNDÜZ : > On Wed, 2009-12-23 at 18:27 +0800, Phoenix Kiula wrote: >> What;s the YUM command to upgrade PG? Do I need to stop PG server? Can >> I be sure that my config and other settings will not be overwritten? > > If you are upgrading from 8.X.Y to 8.X.Z, you don't need to back

Re: [GENERAL] Not finding RPMs for 8.4.2!

On Wed, 2009-12-23 at 18:27 +0800, Phoenix Kiula wrote: > What;s the YUM command to upgrade PG? Do I need to stop PG server? Can > I be sure that my config and other settings will not be overwritten? If you are upgrading from 8.X.Y to 8.X.Z, you don't need to backup anything. If you are performing

Re: [GENERAL] Not finding RPMs for 8.4.2!

2009/12/23 Devrim GÜNDÜZ : > On Tue, 2009-12-22 at 18:34 -0500, Francisco Reyes wrote: >> > Which os/distro are you looking for? I hope it is not Fedora 11 - >> > x86_64. >> >> CentOS x86_64 > > As written before: > > http://yum.pgsqlrpms.org/8.4/redhat/rhel-5-x86_64/repoview/ > Thanks. Just what

Re: [GENERAL] How to add month.year column validation

Dann, CREATE DOMAIN Nasty_Month_year AS CHAR(7) CHECK ( SUBSTR(VALUE, 1, 2) IN ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12') AND SUBSTR(VALUE, 3, 1) = '.' AND SUBSTR(VALUE, 4, 4)::int BETWEEN 1980 and 2110 ); CREATE TABLE foo (bar Nasty_Month_year ); Thank you. This

[GENERAL] reindex

I have an aggregate table which is constantly being overwritten. Every 10 minutes or so, the table is erased and populated with new data, most of which is the same. Basically a materialized view. I have been going through some queries that use this table and noticed that the explain looked diffe

Re: [GENERAL] PostgreSQL and character set change

Hi, Thanks. For anyone else who may be looking for more information, please see details on how to do it here http://docs.moodle.org/en/UTF-8_PostgreSQL#PostgreSQL_UTF8_Migration_How-to Regards, Jayadevan From: "Albe Laurenz" To: "Jayadevan M *EXTERN*" , Date: 12/23/2009 01:12 P