>
> You could filter IP out with a regular expression:
>
> select hostname as hosts_not_ip
> from table
> where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$'
>
Oops, i missed something. Its:
select hostname as hosts_not_ip
from table
where hostname !~ '^[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9
ERE clause?
>
You could filter IP out with a regular expression:
select hostname as hosts_not_ip
from table
where hostname !~ '^[0-9]\.[0-9]\.[0-9]\.[0-9]$'
Cheers,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
ect * from voipdb where prefix <= string order by prefix
> desc limit 1;
>
> Regards,
>
> -- Raju
Hum, I wonder if some kind of best-matching query is what you are looking
for:
SELECT *
FROM voipdb
WHERE prefix IN (
SELECT substr(string, 1, i)
FROM gene
> -Mensaje original-
> From: Mark Stosberg
>
> Hello,
>
> I use PostgreSQL and the "cube" type to perform geo-spatial
> zipcode proximity searches. I'm wondering about the best
> practices also supporting a geo-spatial distance search based
> on a city name rather than zipcode.
>
> -Mensaje original-
> De: Alvaro Herrera [mailto:alvhe...@commandprompt.com]
> Enviado el: Lunes, 29 de Diciembre de 2008 19:39
> Para: Fernando Hevia
> CC: 'George Pavlov'; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] Object create date
>
> Fernando
lpful.
>
> 2. you can also make some inference about the relative timing
> of object creation based on the OIDs (query
> pg_catalog.pg_proc rather than information_schema.routines
> for proc OIDs).
>
I am not sure this would be helpful since different databases are involved
(same produ
> -Mensaje original-
> De: pgsql-sql-ow...@postgresql.org
> [mailto:pgsql-sql-ow...@postgresql.org] En nombre de Scott Marlowe
>
> On Mon, Dec 29, 2008 at 11:23 AM, Fernando Hevia
> wrote:
> > Hi list,
> >
> > I'm having a hard time trying
bject when it was created. Is
this information available on 8.3? Where should I look?
Thanks,
Fernando
the grouping by the #
> (ie, group by cat, then subsort on num, and select the "last"
> one of the group).
>
I wonder if this suites you:
SELECT sub.cat, t.id, sub.Num
FROM my_table t, ( SELECT cat, MAX(num) as Num FROM my_table GROUP_BY cat
) sub
WHERE t.cat = sub.cat AND t.Num = sub.Num
ORDER BY t.cat;
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Hi Bart,
If you are using psql then you can get rid of those messages by entering on
the psql prompt:
\set verbose TERSE
If you are using pgAdmin, sadly there is currently no way for this
application to filter context messages.
Regards,
Fernando
_
De: [EMAIL PROTECTED] [mailto
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Aarni Ruuhimäki
> Enviado el: Viernes, 10 de Octubre de 2008 07:56
> Para: pgsql-sql@postgresql.org
> Asunto: [SQL] SELECT multiple MAX(id)s ?
>
> Hello list,
>
> table diary_entry
>
> entry_id SERI
table a
where date = (select max(b.date) from table b where b.key = a.key) q;
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
tion.
Ref: http://www.postgresql.org/docs/current/static/datatype-numeric.html
The explicit "very slow" assertion scared me quite enough so to avoid
numeric types where possible.
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
thing, like:
>
> (tbltransaksi.hargapokok * tbltransaksi.keluar)::numeric(2,0) AS modal
>
Consider that if you are NOT going to use the decimals you should really use
integer or bigint datatypes. The numeric type compute much slower than
integer datatypes.
Regards,
Fernando
--
Sent via pgsql-
You seem to be missing a ';' in this line:
v_from := c_from ;
v_where := p_where<--- missing ; here
v_stmt := c_select || v_from || v_where;
Regards,
Fernando
De: [EMAIL PROTECTED]
[mailto:[EMAIL
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Gary Chambers
> Enviado el: Lunes, 01 de Septiembre de 2008 11:31
> Para: D'Arcy J.M. Cain
> CC: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] MAY I HAVE YOUR ASSISTANCE
>
> >> I hav
s.
> Anyone have an example complete the how load and read each position of a
cursor?
> I wait your answer
> Thanks...guys
Hi Xavier. This is a very simple example of a function with an implicit
cursor.
Regards,
Fernando.
CREATE OR REPLACE FUNCTION f_cursor()
RETURNS void
> De: Richard Broersma [mailto:[EMAIL PROTECTED]
> Enviado el: Martes, 22 de Julio de 2008 17:19
>
> > 2008-07-22 15:52:37 ART|postgres| LOG: duration: 38.154 ms
>
> :o) You might be encountering a bit of parallax. This shows
> both 38 and 36 *milliseconds*.
>
That's embarrasing... I misto
pletes its execution within one second. Nevertheless in the LOG
duration entry it shows a statement duration of over 36 secs.
¿What is going on? ¿Where come those 36 seconds from?
Regards,
Fernando
--- Postgres Log extract ---
2008-07-22 15:52:37 ART|postgres| LOG: statement: select f_tasador();
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Tom Lane
> Enviado el: Martes, 01 de Julio de 2008 19:24
> Para: Fernando Hevia
> CC: 'Richard Broersma'; pgsql-sql@postgresql.org
> Asunto: Re: [SQL] column default dep
quot; condition is satisfied. The DO INSTEAD part runs
an insert were minutes is NOT null so the rule should be ignored.
Where is the recursion then? I am on postgres 8.2.9.
Thanks for your hindsight Richard. I Will look into the trigger solution.
Still, I'd like to understand this recursion err
rrect? Is there another (better/simpler) way to achieve this?
Regards,
Fernando
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
> > -Mensaje original-
> > De: Scott Marlowe [mailto:[EMAIL PROTECTED] Enviado el:
> > Miércoles, 18 de Junio de 2008 17:47
> > Para: Fernando Hevia
>
> > >
> > > For complex calculations I have obtained better performance using
> >
> -Mensaje original-
> De: Scott Marlowe [mailto:[EMAIL PROTECTED]
> Enviado el: Miércoles, 18 de Junio de 2008 17:47
> Para: Fernando Hevia
> >
> > For complex calculations I have obtained better performance using
> > nested queries. For example:
from ta) subquery1
where c = 2;
This nesting is probably overhead in such a simple case as this, but in more
complex ones and specially with volatile functions it will provide an
improvement.
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
4 and I would like to convert the 14 weeks to 3
> months (some lose of accuracy expected).
> Expected tests may be:
> 14 weeks yields 3 months.
> 1 weeks yields 0 months.
>
If accuracy isnt a issue probably floor() could suite you:
months=select floor(weeks/4);
Regards,
Fernando.
> -Mensaje original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] En nombre de Stephan Szabo
> Enviado el: Viernes, 25 de Abril de 2008 17:46
> Para: Emi Lu
> CC: pgsql-sql@postgresql.org
> Asunto: Re: [SQL] trim(both) problem?
>
> On Fri, 25 Apr 2008, Emi Lu wrote:
>
> > Hi,
>
> -Mensaje original-
> De: Scott Marlowe [mailto:[EMAIL PROTECTED]
>
>
> Note that if you are storing your time stamp as timestamptz,
> you can use the "at time zone 'xyz'" construct to create an
> index, and as long as you retrieve them with the same
> construct you'll get to use
not understand this. Are you saying timestamps WITH timezone are NOT
indexable or you mean that you cant build a partial index on a
timestamp-with-time-zone returning function?
Regards,
Fernando.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscripti
ET num = num + 1 WHERE num = v_num;
END LOOP;
INSERT INTO temp VALUES (p_num, p_name);
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
To run it:
sistema=# select insert_value(4, 'NOT');
insert_value
--
(1 row)
sistema=# select * from temp order
> Tom Lane [mailto:[EMAIL PROTECTED] wrote:
>
> "Fernando Hevia" <[EMAIL PROTECTED]> writes:
> > With 8.2.x the ARST abbreviation was recognized after including the
> > following line in /usr/share/postgresql/8.2/timezonesets/Default
>
> > AR
00:00 | t
America/Argentina/San_Juan | ARST | -02:00:00 | t
(11 rows)
Thanks for all contributions.
Regards,
Fernando.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
tom lane
Thanks Scott and Tom for your help on this.
After copying the updated tz file to /usr/share/postgresql/timezone Postgres
got aware of the time change.
Regarding the ARST recognition, I'm still on 8.1.9. :(
An upgrade seems urgent now.
Regards,
Fernando.
--
-
2008-01-02 01:13:16.426-03
(1 row)
I'm lost here. ARST isn't new. It has been used in former years.
Any help would be greatly appreciated.
Regards,
Fernando
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
2007 so there shouldn't appear an empty
> knitting-column. --> classes.is_availlable
> 3) b) Or it is availlable but no one has chosen it in 2007. -->
> attends_to.in_year
>
Yes, you could. Read about different JOINS and WHERE clauses. ;)
Regards,
Fernando.
---(end of broadcast)---
TIP 6: explain analyze is your friend
essiontime > 0) AND ((calledstationid)::text !~ '^99[89]#'::text))
-> Bitmap Index Scan on idx_stopvoip_callingid2
(cost=0.00..29.29 rows=2939 width=0)
Index Cond: ((callingstationid)::text =
'2941605118'::text)
Thanks,
Fernando.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
onnectcause)::text =
'10'::text) AND (acctsessiontime > 0) AND ((calledstationid)::text !~
'^99[89]#'::text))"
" -> Bitmap Index Scan on idx_stopvoip_callingid2
(cost=0.00..29.29 rows=2939 width=0)"
"Index Cond: ((callingstationid)::text =
'2941605118'::text)"
Thanks,
Fernando.
if(c!=FILTER) putchar(c);
c=fgetc(fp);
}
exit (0);
}
/*--*/
compile as:
$ gcc -o test test.c
Execute as:
$ test test.txt
this is field1, and this is field2
Could this be of help?
Regards,
Fernando.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ld like to just remove single or double quotes you should do:
sed "s/[\'\"]//g" file_with_quotes.txt > file_without_quotes.txt
Say you want to replace quotes with a space, then:
sed "s/[\'\"]/ /g" file_with_quotes.txt > file_without_quotes.
elp.
>
Hi Chuck,
Do you need those characters in your table? If not I think you will be
better off preprocessing the data before running copy.
Replacing those " for ' or directly removing them is quite simple if you are
working in Unix, actually it should be quite s
put. Thanks!
BTW, this explanation should go into the manual.
Regards,
Fernando.
---(end of broadcast)---
TIP 6: explain analyze is your friend
luate the function but I'm not sure what that means as it *is*
executing it every time.
My goal of course is that the function gets executed only once per row.
I'm using 8.2.4
Thanks for your hindsight.
Regards,
Fernando.
uggestions using JOIN.
Cheers,
Fernando.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Try this:
Select *
from view v1
where duration = (select max(duration) from view v2 where v2.phone_number =
v1.phone_number)
You could get more than one call listed for the same number if many calls
match max(duration) for that number.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[E
De: hubert depesz lubaczewski [mailto:[EMAIL PROTECTED]
>>On Fri, Aug 10, 2007 at 04:40:34PM -0300, Fernando Hevia wrote:
>> Found your query is shorter and clearer, problem is I couldn't have it
use
>> an index. Thought it was a locale issue but adding a 2nd index with
ing the explain plan. It has ~31k rows.
Any hindsight will be greatly appreciated.
Regards,
Fernando.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
En nombre de hubert depesz lubaczewski
Enviado el: Viernes, 10 de Agosto de 2007 05:00
Para: Kiran
CC: pgsql-sql@postgresql.o
eated,'Mon-dd-') AS "Report Date",
(((EXTRACT (MONTH FROM pi.incident_date ))::integer - 1) / 3) + 1 AS
Quarter
...
Regards,
Fernando.
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
En nombre de Norm Garand
Enviado el: Martes, 17 de Julio de 2
>>"Fernando Hevia" <[EMAIL PROTECTED]> 2007-06-26 16:25 >>>
>>How do I refer a specific field of the returned row from outside the
>>function? How should I write the query in order to show only fields 1 and
3, for example?
>In case you would like
a specific field of the returned row from outside the
function? How should I write the query in order to show only fields 1 and 3,
for example?
It's sad to bother with this syntax questions, but I've had a hard time
finding code examples online.
Regards,
Fernando.
--
>On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote:
>> when <> then return variable;
>> when <> then <> ;
>> when <> then <> ;
>
>Check out the FOUND variable in the documentation for the first two,
>and the "trap
return v_register.prefix;
end;
v_len := v_len - 1;
END LOOP;
raise 'Not found';
END;
$body$
LANGUAGE 'plpgsql' VOLATILE ;
ERROR: unrecognized exception condition "no_data"
SQL state: 42704
Context: compile of PL/pgSQL function "test" near li
> > I have a column with the following values (example below)
> >
> > 5673
> > 4731
> > 4462
> > 5422
> > 756
> > 3060
> >
> > I want the column to display the numbers as follows:
> >
> > 56.73
> > 47.31
> > 44.62
> > 54.22
> > 7.56
> > 30.60
> >
> > I have been playing around with string functions
I see. Thanks for the tip.
Regards,
Fernando.
-Mensaje original-
De: Tom Lane [mailto:[EMAIL PROTECTED]
Enviado el: Miércoles, 20 de Junio de 2007 19:37
Para: Fernando Hevia
CC: 'PostgreSQL SQL List'
Asunto: Re: [SQL] Constraint exclusion
"Fernando Hevia" <[E
ne) AND (setuptime <= '2007-06-01
00:00:00-03'::timestamp with time zone))
(28 rows)
The plan shows that it scans the indexes for all partitions when it should
only scan indexes for partitions 4 and 5. Is my assumption correct? If it
is, could someone point me out what I am doing wrong? I can't figure out why
it doesn't work.
I think the caveats mentioned in the manual about constraint exclusion have
been taken into account here but I might have missed something.
Regards,
Fernando.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
>> B) SELECT * FROM some_table WHERE to_char(some_timestamp,
>> 'MMDD') >
>> to_char((now() - interval '1 day'), 'MMDD');
>
>I'd never use to_char to compare dates. The built-in comparison
>operators work just fine.
>
Why not? I'm curious if has anything to do with performance or just
at kind of maintenance, and for my case 12
partitions are quite enough.
Any suggestions how to achieve this otherwise?
Regards,
Fernando.
quiero darme de baja de la lista.mandenme el vinculo para hacerlo.
Hola a todo... necesito ejecutar una consulta Insert que realice en pgsql para ver si inserta correctamente en la tabla asociada, pero con execute me da un erroralguien me puede decir como lo hago
yo trabajo con el editor postgresql manager pro..
Gracias...
Ahh otra cosa: en otra cons
Subject: Migracion de SQL Server 2000 a PostgreSQL
hola a todos, tengo un problema hace unos dias y necesito resolverlo cuanto antes. Necesito migrar la Base de Datos de un Portal Corporativo que desarrolle en SQL a Postgresql, pero no encuentro manera de hacerlo, trate de hacerlo por el export de
Thank you, I found out after I posted the message.
I did read the docs but must have looked somewhere else and I search
the lists for hex only and it did not bring the answer back. It
wasn't until I search for int4 to hex that I was able to find the
answer.
Thank you again.
Fernando
On 6
I want to be able to change an int4 from a sequence and store it as
varchar in the database as a hex number.
Is this possible?
Thank you,
Fernando
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Hi,
I am using Postgresql version 7.2.2
I made a small function...
CREATE FUNCTION ejem1(varchar) RETURNS SETOF to varchar as'
SELECT names from mi_tabla WHERE city = $1; '
language ' SQL ';
ejem1
Sergio
Carlos
Fernando
When wanting to obtain
#include
#include
char *fernando(char *texto)
{
char *resultp = palloc(strlen(texto)+5);
*resultp = *texto;
strcat(resultp," mais");
return resultp;
}
gcc -shared fernando.c -o fernando.so
CREATE FUNCTION fernando (bpchar) RETURNS bpchar
AS &
Table 1
create table person (
doc text primary key,
etc
city text );
9 rows
create table sales (
doc text,
etc .
);
30 rows
select p.city,coun
Hey everybody !!!
I am new on this list !!!
I have a little problem .
I try this on my system: (Postgres 6.5.2, Linux)
"select n_lote from pedidos except select rp.n_lote from relpedidos rp,
relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having
sum(rp.cantidad)=sum(rf.cantida
Hey everybody !!!
I am new on this list !!!
I have a little problem .
I try this on my system:
"select n_lote from pedidos except select rp.n_lote from relpedidos rp,
relfacturas rf where rp.n_lote=rf.n_lote group by rp.n_lote having
sum(rp.cantidad)=sum(rf.cantidad)"
I get this result:
66 matches
Mail list logo
