Re: [SQL] Is there any function to test for numeric ips?

> > 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

Re: [SQL] Is there any function to test for numeric ips?

> -Mensaje original- > De: de Oliveiros C, > > Dear All, > > I have a table with host names and some happen to be numeric IPs. > > I would like to be able to filter out the later. > > Is there any function pre-defined in the system that can test > a particular text type value to

Re: [SQL] Substring

> > > > Given that tablename is "voipdb"; I wonder if OP really > wants to write > > a query that finds the row where argument to function > matches the most > > number of leading characters in "prefix". > > > > If voipdb table contains: ab, abc, def, defg; then calling > function > > wit

Re: [SQL] Best practices for geo-spatial city name searches?

> -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. >

Re: [SQL] Object create date

> -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

Re: [SQL] Object create date

Thanks Pavlov for your response. > -Mensaje original- > De: George Pavlov [mailto:gpav...@mynewplace.com] > wrote: > > Hi list, > > > > I'm having a hard time trying to find out if the latest > patches have > > been applied to my application (uses lots of pgplsql functions). > > Does P

Re: [SQL] Object create date

> -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

[SQL] Object create date

Hi list, I'm having a hard time trying to find out if the latest patches have been applied to my application (uses lots of pgplsql functions). Does Postgres store creation date and/or modification date for tables, functions and other objects? It would help me a lot if I could query each object

Re: [SQL] Subsorting GROUP BY data

> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Johnson, > Michael L. > Enviado el: Lunes, 10 de Noviembre de 2008 12:57 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] Subsorting GROUP BY data > > Given the following table: > > ID | Cat | Num

Re: [SQL] Display message to user

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:[E

Re: [SQL] SELECT multiple MAX(id)s ?

> -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

Re: [SQL] For each key, find row with highest value of other field

> Raj Mathur wrote: > > I have some data of the form: > > Key | Date | Value > A | 2008-05-01 | foo* > A | 2008-04-01 | bar > A | 2008-03-01 | foo* > B | 2008-03-04 | baz > B | 2008-02-04 | bar > C | 2008-06-03 | foo* > C | 2008-04-04 | baz > C | 2008-03-04 | ba

Re: [SQL] Pls Hlp: SQL Problem

> -Mensaje original- > De: Scott Marlowe [mailto:[EMAIL PROTECTED] > > > > 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. > > Note that if you're just c

Re: [SQL] Pls Hlp: SQL Problem

> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Scott Marlowe > > On Fri, Sep 12, 2008 at 12:50 PM, Hengky Lie > <[EMAIL PROTECTED]> wrote: > > Dear Richard, > > > > Put parens around the whole thing, like: > > (tbltransaksi.hargapokok * tbltran

Re: [SQL] Syntax help please

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 PROTECTED] En nombr

Re: [SQL] MAY I HAVE YOUR ASSISTANCE

> -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

Re: [SQL] Cursor

> De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] ] En nombre de Xavier Bermeo > Enviado el: Sábado, 16 de Agosto de 2008 14:54 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] Cursor > > Hi, guys... > I have problems with cursosrs. > Anyone have an example co

Re: [SQL] Strange query duration

> 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

[SQL] Strange query duration

Hi list, I just enabled log duration in a 8.3.1 database and got puzzling information. I have a daemon shell-script run every 10 seconds the following: psql -c "select f_tasador();" The 'f_tasador' procedure is quite fast. As per log output I can see the procedure completes its execution w

Re: [SQL] column default dependant on another columns value

> -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

Re: [SQL] column default dependant on another columns value

> -Mensaje original- > De: Richard Broersma [mailto:[EMAIL PROTECTED] > > It is possible to do this with a trigger or a rule. A > trigger would be more robust. > > > Is this correct? Is there another (better/simpler) way to > achieve this? > > Well I might work, but it is a bad prac

[SQL] column default dependant on another columns value

Hi list, Given a table with columns seconds and minutes, how can I have minutes be computed automatically at the insert statement? I tried: ALTER TABLE table1 ALTER COLUMN minutes SET default (seconds/60); Postgres' answer was: ERROR: cannot use column references in default expression So

Re: [SQL] using calculated column in where-clause

> > -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 > >

Re: [SQL] using calculated column in where-clause

> -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:

Re: [SQL] using calculated column in where-clause

> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Patrick > Scharrenberg > Enviado el: Martes, 17 de Junio de 2008 17:46 > Para: pgsql-sql@postgresql.org > Asunto: [SQL] using calculated column in where-clause > > Hi! > > I'd like to do some calculat

Re: [SQL] Weeks elapsed to Months elapsed conversion

> > Hi all, > I have a simple question (tried googling but found no > answers). How do I convert weeks elapsed into months elapsed? > I have data that contains duration in weeks (without any > other date values such as year and so on) for example a week > value of 14 and I would like to convert

Re: [SQL] trim(both) problem?

> -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, >

Re: [SQL] First day of month, last day of month

> -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

Re: [SQL] First day of month, last day of month

> -Mensaje original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] En nombre de Scott Marlowe > > Then you can just use date_trunc on the values in the > database. Plus if you're using timestamp WITHOUT timezone, > you can index on it. > Did not understand this. Are you sayin

Re: [SQL] Update PK Violation

> Franklin Haut wrote: > > Hi all, > > i have a problem with one update sentence sql. > > example to produce: > > create table temp (num integer primary key, name varchar(20)); > > insert into temp values (1, 'THE'); > insert into temp values (2, 'BOOK'); > insert into temp values (3, 'IS');

Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?

> 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

Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?

> Tom Lane [mailto:[EMAIL PROTECTED] wrote: > > Since the OP has apparently already managed to get updated tzdata files > installed on his system, he could just copy them into > /usr/share/postgresql/timezone --- anything using zic should be a > compatible file format. > > The lack-of-ARST-on

Re: [SQL] Argentinian timezone change at the last moment. How to change pgsql tz db?

> Tom Lane wrote: > > "Scott Marlowe" <[EMAIL PROTECTED]> writes: > > That doesn't get me what I need. It lets me change the alias of > > timezones, but not the start and stop of daylight savings time. I > > think for that I'd have to edit / replace the files in > > postgresql-8.2.x/src/timezo

[SQL] PG is in different timezone than the OS

Hi all, I am not sure if this is the correct list to post this issue. Please let me know if there is a more suitable one. Argentina's government has recently decreted a timezone change for the summer (daylight's savings) where local time zone changes from GMT-3 to GMT-2. The Argentinean Summer Ti

Re: [SQL] design of tables for sparse data

> --- Andreas Wrote: --- > ... > > MY QUESTIONS: Your questions have a strong "home-work" look. > > 1) How would I SELECT a report that looks like the first version of > the pupil table out of the 3 table design? > There must be a nontrivial SELECT statement that combines all 3 tables. > E.g

Re: [SQL] Perfomance benefit using Min() against order by & limit 1?

Oops. Previous message went in HMTL. Sorry for that. Text-only version follows. --- Hi guys. Is there any difference between these two queries regarding performance? Table stopvoip has several million records. I suspect using the aggregate function would be best, but benchmarking doesn’t seem to

[SQL] Perfomance benefit using Min() against order by & limit 1?

Hi guys. Is there any difference between these two queries regarding performance? Table stopvoip has several million records. I suspect using the aggregate function would be best, but benchmarking doesn't seem to confirm it. Both queries take around 150 - 175 ms once data has been cached. Any hi

Re: [SQL] request for help with COPY syntax

> On October 25, 2007 10:57:49 am you wrote: > > > > If all you just want to do is strip out the ^M, you can run dos2unix on > > it, assuming that you are running a *nix distro. > > Well, I guess I could strip the ^M but I'm still left with a $ in the > middle > of a field which in the same as th

Re: [SQL] request for help with COPY syntax

> De: Chuck D. > > I'm not sure if they are needed because I've never seen a double quote in > a > place name before. I don't believe they are errors though because there > are > more records that contain them. As well, some records have single and > double > quotes allowed within a record and

Re: [SQL] request for help with COPY syntax

> -Mensaje original- > De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > En nombre de Chuck D. > > Anyone known how I can rewrite the COPY command to allow those " or ' > within > the data? After a couple days I wasn't able to find any examples to help. > Hi Chuck, Do you need those c

Re: [SQL] Function Volatility

Tom Lane writes: > The IMMUTABLE marker is a promise from you to the system that it is safe > to optimize away multiple calls to the function. It is not a promise > from the system to you that the system will expend unlimited amounts of > energy to detect duplicate calls. Nicely put. Thanks! BTW

[SQL] Function Volatility

Hi guys, I am not sure if I am understanding volatility. My issue is better explained with a quick example. The function below expresses call durations in minutes and it is immutable. CREATE OR REPLACE FUNCTION dur2min(secs INTEGER) RETURNS INTEGER AS $$ BEGIN RAISE NOTICE 'BEEN HERE!';

Re: [SQL] Problem with phone list.

--- Michael Glaesemann wrote: > SELECT DISTINCT ON (phone_number) > phone_number, call_duration, id > FROM calls > ORDER BY phone_number > , call_duration DESC; Wasn't acquainted with "DISTINCT ON (column)". I found it to be many times faster than other suggestions using JOIN. Cheers,

Re: [SQL] Problem with phone list.

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

Re: [SQL] Best Fit SQL query statement

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

Re: [SQL] Best Fit SQL query statement

Hi Depesz, I was curious about your solution for Best Fit since I had mine working in a function with a loop: ... FOR v_len IN REVERSE v_max..v_min LOOP v_prefix := substring(v_destino, 1, v_len); SELECT * INTO v_result FROM numeracion WHERE prefijo = v_prefix; IF FOUND

Re: [SQL] Inserting an IF statement in the middle of a SELECT in pl/pgSQL code

You should try 'Coalesce' function. By the way, your "incident_date-to-quarter" expression could be shortened: SELECT DISTINCT(pi.serial_number) AS "Incident ID", to_char(pi.incident_date,'Mon-dd-') AS "Incident date", to_char(pi.date_created,'Mon-dd-') AS "Report Date", (((EXTRACT (M

Re: [SQL] NO DATA FOUND Exception

>>"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

Re: [SQL] NO DATA FOUND Exception

On Jun 25, 2007, at 17:05, Michael Glaesemann wrote: >[Please create a new message to post about a new topic, rather than >replying to and changing the subject of a previous message. This will >allow mail clients which understand the References: header to >properly thread replies.] Wasn't

Re: [SQL] NO DATA FOUND Exception

>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

[SQL] NO DATA FOUND Exception

Hi. Im taking my first steps with plpgsql. I want my function to react to the result of a query in the following way: begin select column into variable from table where condition; exception when <> then return variable; when <> then <> ; when <> then <> ; end ; Is something like this pos

Re: [SQL] simple SQL question

> > 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

Re: [SQL] Constraint exclusion

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

[SQL] Constraint exclusion

It seems constraint exclusion is not working with my partitioned tables and the rules I wrote. This is my partition deployment: -- Master table CREATE TABLE table_master ( setuptime timestamp with time zone NOT NULL, ... }; -- Partitions CREATE TABLE table_p01 INHERITS (table_mas

Re: [SQL] subtract a day from the NOW function

>> 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

[SQL] check_constraint and Extract not working?

Just to be sure I am getting this right: I have a big table I want to partition: create table big_table ( row_date timestamp with time zone, row_data character varying(80) }; A nice solution would be to spread its rows in one of 12 child tables according to which month the date f