Hello
you can use a own function
CREATE OR REPLACE FUNCTION clean_some(text)
RETURNS text AS $$
SELECT replace(replace($1, ' ',''),'-','')
$$ LANGUAGE sql;
then you can do query with where clause
WHERE clean_some(colum) = clean_some('userinput');
you can enhance it with functional index
2010/10/13 Andrus kobrule...@hot.ee:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example
Andrus wrote:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example searching for code 12344 should
r...@iol.ie wrote:
...
In fairness, it *is* flagged in the release note - it's the first item
under data types in the list of incompatibilities.
Quote:
bytea output now appears in hex format by default (Peter Eisentraut)
The server parameter bytea_output can be used to select the
On 13 October 2010 19:04, Carlos Mennens carlos.menn...@gmail.com wrote:
OK so I have read the docs and Google to try and find a way to add a
new column to an existing table. My problem is I need this new column
to be created 3rd rather than just dumping this new column to the end
of my
Carlos Mennens wrote:
OK so I have read the docs and Google to try and find a way to add a
new column to an existing table. My problem is I need this new column
to be created 3rd rather than just dumping this new column to the end
of my table. I can't find anywhere how I can insert my new
On 13 October 2010 19:06, Carlos Mennens carlos.menn...@gmail.com wrote:
On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens
carlos.menn...@gmail.com wrote:
OK so I have read the docs and Google to try and find a way to add a
new column to an existing table. My problem is I need this new column
Hello !
I would like to write a PL/PGSQL trigger that will store changes (Table
name / Column name / primary key id / OLD value / NEW value) in several
tables.
As I said, this trigger must be usable for every table (it assumes that
theses table will have only one primary key).
In order to
On 14/10/10 01:45, Andrus wrote:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example
Hi, Merlin. You wrote:
In other words, something like this:
create or replace function test() returns setof foo as
$$
declare
r refcursor;
f foo;
i int;
begin
open r for select * from foo;
for i in 1..10
loop
fetch 1 from r into f;
exit when not found;
On 14/10/10 00:59, Evan D. Hoffman wrote:
Thanks, Brian Jaime. Regarding Slony, would that allow for
migration to a new version as well - i.e. moving from 8.2 on the old
machine to 8.4 on the new machine via Slony with minimal downtime?
The Slony method is one I hadn't considered. Since
Carlos Mennens, 13.10.2010 20:06:
OK so I have read the docs and Google to try and find a way to add a
new column to an existing table. My problem is I need this new column
to be created 3rd rather than just dumping this new column to the end
of my table. I can't find anywhere how I can insert
Dear Carlos,
In old version of postgresql attnum colmn of pg_catalog.pg_attribute
system catalog
could be modified to get desired results. I am not sure if it should
be done anymore.
Rajesh Kumar Mallah.
On Wed, Oct 13, 2010 at 2:06 PM, Carlos Mennens
carlos.menn...@gmail.com wrote:
On Wed,
Dear Andrus,
Quick Dirty Soln:
SELECT * from table where regexp_replace( col , '[-\\s+]' , '' ,
'g') ilike '%search_term%' ;
note above sql will not use any index if you have to search 1s of
rows use alternate
approaches.
regds
Rajesh Kumar Mallah.
2010/10/13 Andrus
Dear Carlos,
application code should not depend on column positions.
the requirement is not good.
regds
rajesh kumar mallah.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 13/10/2010 22:03, ljb wrote:
r...@iol.ie wrote:
...
In fairness, it *is* flagged in the release note - it's the first item
under data types in the list of incompatibilities.
Quote:
bytea output now appears in hex format by default (Peter Eisentraut)
The server parameter
Dear Griper!,
fortunately someone showed a easy 'fix'
ALTER DATABASE foo SET bytea_output='escape' ;
Regds
Rajesh Kumar Mallah.
On Wed, Oct 13, 2010 at 5:03 PM, ljb ljb9...@pobox.com wrote:
r...@iol.ie wrote:
...
In fairness, it *is* flagged in the release note - it's the first item
under
On 14/10/10 00:18, Joshua D. Drake wrote:
On Wed, 2010-10-13 at 11:40 -0400, Geoffrey Myers wrote:
On 10/13/2010 11:30 AM, zhong ming wu wrote:
On Wed, Oct 13, 2010 at 11:17 AM, Geoffrey Myers
li...@serioustechnology.com mailto:li...@serioustechnology.com wrote:
Excuse the ignorance, but I
Hello
2010/10/14 Bruno Baguette bruno.bague...@gmail.com:
Hello !
I would like to write a PL/PGSQL trigger that will store changes (Table
name / Column name / primary key id / OLD value / NEW value) in several
tables.
As I said, this trigger must be usable for every table (it assumes that
On 10/13/2010 07:45 PM, Andrus wrote:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
There are many options
I think it's incorrect to expect a query to return column in any specific order
if you do something like select * from You may see columns returned in
the order you created them, but I don't believe it's guaranteed. If you want a
specific order, then select col1, col3, col5, col2,
Hello,
For example searching for code 12344 should return
12 3-44 as matching item.
Andrus.
This will do?
postgres=# select name from myt;
name
13-333-333
12 3-44
33 33 333
12345
(4 rows)
postgres=# select * from myt where translate(translate(name,'-',''),'
','')
And the dynamic linker will take any version of libpq.so that has the same
major number, meaning that you
cannot assume that it's not 8.4 just because you built against 9.0.
[...]
Not to mention when you're on windows (and I dunno if any other
platforms are like *that*), where they're
On 13/10/2010 19:06, Carlos Mennens wrote:
On Wed, Oct 13, 2010 at 2:04 PM, Carlos Mennens
carlos.menn...@gmail.com wrote:
OK so I have read the docs and Google to try and find a way to add a
new column to an existing table. My problem is I need this new column
to be created 3rd rather than
2010/10/13 Andrus kobrule...@hot.ee:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example
On 13/10/2010 19:04, Carlos Mennens wrote:
OK so I have read the docs and Google to try and find a way to add a
new column to an existing table. My problem is I need this new column
to be created 3rd rather than just dumping this new column to the end
of my table. I can't find anywhere how I
Or create view with your desired order on this table.
Regards,
Vijay
- Original Message -
From: Thomas Kellerer spam_ea...@gmx.net
Date: Thursday, October 14, 2010 3:09 pm
Subject: Re: [GENERAL] Adding a New Column Specifically In a Table
To: pgsql-general@postgresql.org
Carlos Mennens,
On 13 October 2010 23:19, Raymond O'Donnell r...@iol.ie wrote:
On 13/10/2010 19:04, Carlos Mennens wrote:
OK so I have read the docs and Google to try and find a way to add a
new column to an existing table. My problem is I need this new column
to be created 3rd rather than just dumping this
select regexp_replace(myval, E'(\\D)', '', 'g') from foo;
for added speed, you might consider this:
CREATE INDEX ON foo((regexp_replace(myval, E'(\\D)', '', 'g'))::bigint);
which is also going to protect you against inserts where value doesn't
contain any digits.
and added benefit of index:
2010/10/13 Andrus kobrule...@hot.ee:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example
On 14/10/2010 11:32, Thom Brown wrote:
..and indexes, triggers, rules, views and every other dependency.
It's a potentially tricky solution to something which shouldn't really
be a problem.
Indeed - as others have said, depending on a specific column ordering in
the database is asking for
Hi Andrus,
2010/10/13 Andrus kobrule...@hot.ee
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For
Hey Massa,
Again, I suggest PQlibVersion() or PQversion() instead of PQlibpqVersion().
Why this pq redundancy in name? Waste of bytes.
2010/10/14 Massa, Harald Armin c...@ghum.de
And the dynamic linker will take any version of libpq.so that has the same
major number, meaning that you
On 14 October 2010 12:07, Raymond O'Donnell r...@iol.ie wrote:
Indeed - as others have said, depending on a specific column ordering in the
database is asking for trouble.
Yes, it certainly is (in fact, 1NF says that there should be no order
to the columns), but it still annoys me that I cannot
Dmitriy,
Again, I suggest PQlibVersion() or PQversion() instead of PQlibpqVersion().
Why this pq redundancy in name? Waste of bytes.
the first PQ qualifies the name-space those are PostgresQl functions.
the library is called libpq, and we are explicitly asking for the version of
libpq; not
2010/10/14 Massa, Harald Armin c...@ghum.de
Dmitriy,
Again, I suggest PQlibVersion() or PQversion() instead of
PQlibpqVersion().
Why this pq redundancy in name? Waste of bytes.
the first PQ qualifies the name-space those are PostgresQl functions.
the library is called libpq, and we are
On 14 October 2010 09:51, Rajesh Kumar Mallah mallah.raj...@gmail.com wrote:
Dear Carlos,
In old version of postgresql attnum colmn of pg_catalog.pg_attribute
system catalog
could be modified to get desired results. I am not sure if it should
be done anymore.
That will only make the column
If I do:
SELECT St_Buffer('010120E864F8FFF8FF', 50);
the PostgreSQL process leaves with:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the
On Thu, Oct 14, 2010 at 12:31 AM, Reuven M. Lerner reu...@lerner.co.il wrote:
Hi, Merlin. You wrote:
In other words, something like this:
create or replace function test() returns setof foo as
$$
declare
r refcursor;
f foo;
i int;
begin
open r for select * from foo;
for
Hi, Merlin. Thanks for the clarification and explanation; it was
quite helpful. I'll give it a shot!
Reuven
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi,
I am using Slony-I for database replication.
When the master machine is failed, the slave machine is promoted to
master. The script used for this is as below,
cluster name = sql_cluster;
node 1 admin conninfo = 'dbname=postgres host=10.1.20.55 port=5432
user=postgres';
node 2
On 10/14/2010 04:32 AM, Thom Brown wrote:
On 13 October 2010 23:19, Raymond O'Donnell r...@iol.ie wrote:
On 13/10/2010 19:04, Carlos Mennens wrote:
OK so I have read the docs and Google to try and find a way to add a
new column to an existing table. My problem is I need this new column
to
Hi Gurus,
How to remove enter or new line from varchar field, I believed be chr(10)
and
chr(13).
Regards Paulo
Think of it this way...
A person has many properties... age, nationality, eye_color, weight, etc...
Does it maks sense to put these properties in a particular order ?
Neither does a relational DB require them to be in any order. The fact that
select * consistently shows them in one particular
On Thu, Oct 14, 2010 at 5:32 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
2010/10/14 Bruno Baguette bruno.bague...@gmail.com:
Hello !
I would like to write a PL/PGSQL trigger that will store changes (Table
name / Column name / primary key id / OLD value / NEW value) in several
On 14 October 2010 14:59, paulo matadr saddon...@yahoo.com.br wrote:
Hi Gurus,
How to remove enter or new line from varchar field, I believed be chr(10)
and chr(13).
You can use regexp_replace. For example:
regexp_replace(column, '\r|\n', '', 'g')
Bear in mind, the above example would
On Wed, Oct 13, 2010 at 12:59 PM, Evan D. Hoffman
evandhoff...@gmail.com wrote:
The Slony method is one I hadn't considered. Since our database is so
large, even a direct file copy would require some downtime (since we'd
If you do go the slony route, you may want to do the replication
Devrim G?ND?Z wrote:
-- Start of PGP signed section.
On Fri, 2010-09-24 at 11:35 -0700, prof_cleverson wrote:
PGCluster installed but when connecting to the Load Balancer gives the
error:
Error connect to server:server closed the connection unexpectedly
This probably means the
On Thu, 2010-10-14 at 12:52 -0400, Bruce Momjian wrote:
PGCluster is no longer developer AFAIK, so you'd better consider
testing
Postgres-XC:
http://postgres-xc.sourceforge.net/
I don't think Postgres-XC is ready for production use either. :-(
That's why I wrote testing ;)
--
Hi,
I use postgreSQL.8.4.2 and don't have postGIS installed.
I have a geometric type polygon and I know that it is possible to determine
if a point it is inside or not of this polygon, using anything like that:
select
On 10/14/10 12:00 PM, Josi Perez (3T Systems) wrote:
Hi,
I use postgreSQL.8.4.2 and don't have postGIS installed.
I have a geometric type polygon and I know that it is possible to
determine if a point it is inside or not of this polygon, using
anything like that:
select
On Thu, Oct 14, 2010 at 1:39 AM, Vishnu S. vishn...@nestgroup.net wrote:
drop node(id = 1, event node = 2);
once you've dropped the node, you need to re-initialize it from
scratch. just follow the slony procedures for adding a new node to
your replication cluster after you dropdb the database
when I use the psql to send a sql, how to get current sql execution time?
\timing
On 10/14/2010 04:47 PM, sunpeng wrote:
when I use the psql to send a sql, how to get current sql execution time?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Oct 14, 2010 at 3:47 PM, sunpeng blueva...@gmail.com wrote:
when I use the psql to send a sql, how to get current sql execution time?
At the psql prompt:
# \timing
Timing is on.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
On 10/14/2010 03:47 PM, sunpeng wrote:
when I use the psql to send a sql, how to get current sql execution time?
http://www.postgresql.org/docs/9.0/interactive/app-psql.html
\timing [ on | off ]
Without parameter, toggles a display of how long each SQL statement
takes, in milliseconds.
On 10/14/10 3:47 PM, sunpeng wrote:
when I use the psql to send a sql, how to get current sql execution time?
\timing [{on|off}]
either toggles or sets on and off. when timing is on, it displays
query time.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make
We have a table A:
CREATE TABLE A(
uid integer,
groupid integer
)
Now we use this subsql to get each group's count:
SELECT count(*) as count
FROM A
GROUP BY groupid
ORDER BY groupid
Then we try to find the group pair with following conditions:
SELECT c.groupid as groupid1,d.groupid as
On 10/14/2010 05:34 PM, sunpeng wrote:
We have a table A:
CREATE TABLE A(
uid integer,
groupid integer
)
Now we use this subsql to get each group's count:
SELECT count(*) as count
FROM A
GROUP BY groupid
ORDER BY groupid
Then we try to find the group pair with following
Actually I've simplied my original sql to the previous version, since it's
simple yet reveals the same problem.
My original sql is to get two instersected cluster(as same concept as group
) and its commonarea:
SELECT a.clusterid AS clusterida,
I guess I should mention that im basically searching for a way to
recusively coalesce the title. So I want to search the second table
and
table_one (id,title)
1 | new one
table_two (id,title)
2 | new two
table_three (id,title)
1 | one
2 | two
3 | three
Id like an sql statement that returns...
Is it possible to get the results of this snip of a function without
using a function? All tables include an id and title column.
tables := ARRAY[table_one,table_two,table_three]::VARCHAR;
CREATE TEMP TABLE final_results (id INTEGER, title VARCHAR, r_types
VARCHAR[]);
FOR t IN
Found a solution for what I need. Please let me know if you know of
something better/faster. -Nick
CREATE AGGREGATE array_accum (anyelement) (
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
SELECT id, title, array_accum(t) AS ts FROM (
SELECT 'table_one' AS t, id, title
On 15/10/2010 6:47 AM, sunpeng wrote:
when I use the psql to send a sql, how to get current sql execution time?
In addition to \timing as others have mentioned, you can use:
EXPLAIN ANALYZE [your-sql-here];
to get the server to tell you how it executed your SQL. If you do this,
the server
Sure:
select
t3.id,
coalesce
(
t1.title,
t2.title,
t3.title
),
coalesce
(
case
when t1.title is not null
then 'table_one,'
else null
end,
case
when t2.title is not null
then 'table_two,'
65 matches
Mail list logo
