The following will return an input out of error as the acos() function
cannot be -1 <= x <= 1.
SELECT * FROM
(SELECT *, (3959 * acos(cos(radians(37.7438640)) *
cos(radians(37.7438640)) * cos(radians(-97.4631299) -
radians(-97.4631299)) + sin(radians(37.7438640)) *
s
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,'
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 wo
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 F
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 ARRAY_LOWER(tables,1
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...
1
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,
b.clu
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 follo
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 groupi
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 c
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 Thu, Oct 14, 2010 at 3:47 PM, sunpeng 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 subscription:
http://www.postg
\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
when I use the psql to send a sql, how to get current sql execution time?
On Thu, Oct 14, 2010 at 1:39 AM, Vishnu S. 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 from that
machine.
a
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
polygon('(-23.5672,-4
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
polygon('(-23.5672,-46.6510),(-23.5670,-46.65095),(-23.5672,-46.6508),(-23.5672,
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"
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 mea
On Wed, Oct 13, 2010 at 12:59 PM, Evan D. Hoffman
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
incrementally. That is, in
On 14 October 2010 14:59, paulo matadr 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 mean that if the
On Thu, Oct 14, 2010 at 5:32 AM, Pavel Stehule wrote:
> Hello
>
> 2010/10/14 Bruno Baguette :
>> 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
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 particu
Hi Gurus,
How to remove "enter" or new line from varchar field, I believed be chr(10)
and
chr(13).
Regards Paulo
On 10/14/2010 04:32 AM, Thom Brown wrote:
> On 13 October 2010 23:19, Raymond O'Donnell 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
>>>
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 adm
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
On Thu, Oct 14, 2010 at 12:31 AM, Reuven M. Lerner 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;
>>
>
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 se
On 14 October 2010 09:51, Rajesh Kumar Mallah 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 names swap round,
2010/10/14 Massa, Harald Armin
> 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
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
li
On 14 October 2010 12:07, Raymond O'Donnell 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 re-order
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
> And the dynamic linker will take any version of libpq.so that has the same
>> major number, meaning that you
>> cannot assu
Hi Andrus,
2010/10/13 Andrus
> 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 se
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 tro
2010/10/13 Andrus :
> 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
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:
gj=
On 13 October 2010 23:19, Raymond O'Donnell 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 ne
Or create view with your desired order on this table.
Regards,
Vijay
- Original Message -
From: Thomas Kellerer
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, 13.10.2010 20:06:
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 can
2010/10/13 Andrus :
> 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
On 13/10/2010 19:06, Carlos Mennens wrote:
On Wed, Oct 13, 2010 at 2:04 PM, 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 colu
>
> 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 the
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,'-',''),'
'
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, .
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
Hello
2010/10/14 Bruno Baguette :
> 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 wil
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
>>> mailto:li...@serioustechnology.com>> wrote:
>>> > Excuse the ignorance, but I see the follow
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 wrote:
> r...@iol.ie wrote:
>>...
>> In fairness, it *is* flagged in the release note - it's the first item
>> under "data type
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 bytea_
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
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 :
> CHAR(20) c
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
wrote:
> On Wed, Oct 13, 2010 at 2:04 PM, C
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 m
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. Si
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;
retur
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 exampl
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 that,
On 13 October 2010 19:06, Carlos Mennens wrote:
> On Wed, Oct 13, 2010 at 2:04 PM, 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 dumpin
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 column
On 13 October 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 anywhe
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
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 retur
2010/10/13 Andrus :
> 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
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
CRET
66 matches
Mail list logo
