Re: [SQL] Odd query behavior

[silly sad]

On 03/12/10 18:41, Dan McFadyen wrote:

Hello,

I've come across an odd situation. I've had access to a database where a
the following happens:

" SELECT * FROM table WHERE name LIKE 'abc%' " returns 2 rows...

but...

" SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name
LIKE 'abc%') " returns 0 rows...


I am sorry if it was already spoken.

SELECT name FROM table WHERE name LIKE 'abc%'

EXPLAIN SELECT name FROM table WHERE name LIKE 'abc%'

EXPLAIN SELECT * FROM table WHERE name IN
(SELECT name FROM table WHERE name LIKE 'abc%')



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Odd query behavior

[Tom Lane]

"Dan McFadyen"  writes:
> You're right, the second one does use an index, one that is used to
> enforce a unique constraint on the column. I wasn't able to turn it off
> as the database is currently in use, and disabling a unique constraint
> probably isn't a good idea.

> Sorry for not mentioning the PG version, it's 8.3.

> Now, as for the LC_CTYPE, I didn't even know about such a thing till you
> mentioned it, and I found the lovely disclaimers in the postgresql docs
> about the consequences of it not being c or posix.

> It's Finnish Finland 1252. If that's the probable cause, I'm guessing
> the only option is to export, re-initdb, and import.

Oh, this is Windows huh?  In principle, UTF8 encoding should work in all
locales for Windows --- at least, the code thinks so, which is why it
let you create that combination to start with.  I'm not very familiar
with the possible pitfalls though.  If you don't actually have any use
for Finnish-specific sorting or case-folding, a re-initdb in C locale
would probably be a good idea.

In the meantime it seems there might be a bug here.  Can you give more
information about the contents of the column involved, in particular
what non-7-bit-ASCII characters it contains?

regards, tom lane

PS: please cc the list on replies.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Odd query behavior

[Dan McFadyen]

Bah... yes Windows.

For being a developer I certainly miss all the details that make a good
bug report/question. Also, sorry about the CC, don't post to lists often
and I forget.

Now, the interesting part is for all I can tell, there are no special
characters in the field. Unless the latest version of Pgadmin (1.10.1 or
1.10.2) hides this from the UI, either that or it's some character that
renders into a similar glyph as ASCII.

Is there any way to force hex/binary in a SQL statement? 


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: Monday, March 15, 2010 11:04 AM
To: Dan McFadyen
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Odd query behavior 

"Dan McFadyen"  writes:
> You're right, the second one does use an index, one that is used to
> enforce a unique constraint on the column. I wasn't able to turn it
off
> as the database is currently in use, and disabling a unique constraint
> probably isn't a good idea.

> Sorry for not mentioning the PG version, it's 8.3.

> Now, as for the LC_CTYPE, I didn't even know about such a thing till
you
> mentioned it, and I found the lovely disclaimers in the postgresql
docs
> about the consequences of it not being c or posix.

> It's Finnish Finland 1252. If that's the probable cause, I'm guessing
> the only option is to export, re-initdb, and import.

Oh, this is Windows huh?  In principle, UTF8 encoding should work in all
locales for Windows --- at least, the code thinks so, which is why it
let you create that combination to start with.  I'm not very familiar
with the possible pitfalls though.  If you don't actually have any use
for Finnish-specific sorting or case-folding, a re-initdb in C locale
would probably be a good idea.

In the meantime it seems there might be a bug here.  Can you give more
information about the contents of the column involved, in particular
what non-7-bit-ASCII characters it contains?

regards, tom lane

PS: please cc the list on replies.

The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. 
Statements and opinions expressed in this e-mail may not represent those of the 
company. Any review, retransmission, dissemination or other use of, or taking 
of any action in reliance upon, this information by persons or entities other 
than the intended recipient is prohibited. If you received this in error, 
please contact the sender immediately and delete the material from any 
computer.  Please see our legal details at http://www.cryptocard.com
CRYPTOCard Inc. is registered in the province of Ontario, Canada with Business 
number 80531 6478.  CRYPTOCard Europe is limited liability company registered 
in England and Wales (with registered number 05728808 and VAT number 869 3979 
41); its registered office is Aztec Centre, Aztec West, Almondsbury, Bristol, 
UK, BS32 4TD



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Odd query behavior

[Tom Lane]

"Dan McFadyen"  writes:
> Now, the interesting part is for all I can tell, there are no special
> characters in the field. Unless the latest version of Pgadmin (1.10.1 or
> 1.10.2) hides this from the UI, either that or it's some character that
> renders into a similar glyph as ASCII.

> Is there any way to force hex/binary in a SQL statement? 

Huh.  Try pg_dump'ing the table and then searching the file for any
bytes with high bit set.

It's possible that Finnish locale sorts some of the plain-ASCII
characters differently than C locale would, but I don't know of
a reason for that to break anything.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Please delete my email

[Eduardo Palafox]

Hi!, I don't want to receive more emails from postgresql.

Please remove my email from your delivery list.

Thanks


  
_
Prefiero un día sin coche que sin Messenger
www.vivirmessenger.com

[SQL] Remove my e-mail

[Daniel Guedes]

Hi!, I don't want to receive more emails from postgresql.

Please remove my email from your delivery list.

Thanks

-- 
Daniel Guedes

Re: [SQL] Please delete my email

[Adrian Klaver]

On 03/15/2010 10:18 AM, Eduardo Palafox wrote:


Hi!, I don't want to receive more emails from postgresql.

Please remove my email from your delivery list.

Thanks



_
Prefiero un día sin coche que sin Messenger
www.vivirmessenger.com


To unsubscribe go here:
http://www.postgresql.org/mailpref/pgsql-sql

Thanks,
--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] I, nead to capture the IP number from the PC how is running the script ...

[John Dizaro]

I, nead to capture the IP number from the PC how is running the script
"update TABLE1 set campo1 = 123 where ...; "
Can someone help me please?


-- 
John Evan Dizaro -
Fone: (41) -0303
Fone: (41) 9243-3240
Rua: Alferes Poli Curitiba - PR - Brasil

Re: [SQL] Help : insert a bytea data into new table

[dennis]

here is example

table name is "mail":
column|   type
-
sender|char
subject   |char
content   |bytea


I want copy some record into new table 'mail_new'.

sql:
create table mail_new as select * from mail sender='dennis'

result has an error:
operator does not exist: text || bytea


But if my sql statement has no column "content"
the sql works.
sql:
create table mail_new as select sender,subject from mail sender='dennis'


Ben Morrow 提到:

Quoth dennis :

I need to copy some data to new table.
But I encounter some error message.
the table structure
Table A:
  c1  char
  c2  bytea

Table B:
  c1  char
  c2  bytea


My sql command:
insert into B as select * from a where c1=xxx


'AS' isn't valid there. What is xxx? Is it a field you haven't shown us,
or is it a quoted string?


error:
operator does not exist: text || bytea


That command (with 'xxx' quoted and the AS removed) doesn't give that
error with those table definitions, so you will need to show us your
actual query.

Ben




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Odd query behavior

[Dan McFadyen]

Hello,

 

I've come across an odd situation. I've had access to a database where a
the following happens:

 

" SELECT * FROM table WHERE name LIKE 'abc%' "returns 2 rows...

 

but...

 

" SELECT * FROM table WHERE name IN (SELECT name FROM table WHERE name
LIKE 'abc%') "  returns 0 rows...

 

I've also tried doing a join on the column to another table that has
what appears to be the same data, and the join comes out with zero rows.
Weirder yet, I took the hash of both of the values and it came out
identical.

 

Now, I know the first thing you'll ask is if you can get a copy of the
data to re-produce it, and the problem is, I got a  copy of the data in
question, and loaded into another server and it works fine. Both queries
return 2 rows.

 

I do know that server it was running on was not an english server, but
don't know the exact language. The database uses a UTF8 encoding though,
so I don't know what that would make any difference.

 

Any ideas about things I should be looking at?

 

Thanks

 

Dan


The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. 
Statements and opinions expressed in this e-mail may not represent those of the 
company. Any review, retransmission, dissemination or other use of, or taking 
of any action in reliance upon, this information by persons or entities other 
than the intended recipient is prohibited. If you received this in error, 
please contact the sender immediately and delete the material from any 
computer.  Please see our legal details at http://www.cryptocard.com
CRYPTOCard Inc. is registered in the province of Ontario, Canada with Business 
number 80531 6478.  CRYPTOCard Europe is limited liability company registered 
in England and Wales (with registered number 05728808 and VAT number 869 3979 
41); its registered office is Aztec Centre, Aztec West, Almondsbury, Bristol, 
UK, BS32 4TD

[SQL] list of all months

[query]

Hi,

I want to display data for all days in a month even if no data exists for that 
month. Some of the days in a month might not have any data at all. With normal 
query, we can display days only if data exists.But I want to display rows for 
all days in a month with blank data for non-existing day in database.

How can this be achieved ?
 

Re: [SQL] Help : insert a bytea data into new table

[dennis]

Hi Ben

here is my function , it's for fix missing chunk problem.
It has same problem ,please take look


thank for you help

-table--


db=# \d usersessiontable;
Table "public.usersessiontable"
  Column   |  Type  | Modifiers
---++---
 serverid  | character varying(100) |
 sessionid | character varying(50)  |
 data  | bytea  |
Indexes:
"usersessiontable_idx" btree (sessionid)
db=#

db=# \d usersessiontable_test;
Table "public.usersessiontable"
  Column   |  Type  | Modifiers
---++---
 serverid  | character varying(100) |
 sessionid | character varying(50)  |
 data  | bytea  |

--function


CREATE OR REPLACE FUNCTION check_missing_chunk_table_usersessiontable()
  RETURNS integer AS
$BODY$
declare
begin
   records = 0;
   OPEN curs1  FOR EXECUTE 'SELECT * FROM usersessiontable ORDER BY 
sessionid';

   loop
FETCH curs1 INTO rowvar;
IF  NOT FOUND THEN
EXIT;
END IF;
begin
a_sql = 'insert into 
usersessiontable_test(sessionid,serverid,data) 
values('''||rowvar.sessionid||''','''||rowvar.serverid||''',E'''||rowvar.data||''')';<<--my 
problem

execute a_sql;
exception
when others then
raise notice '/* NUM:%, DETAILS:% */', SQLSTATE, SQLERRM;
raise notice  'select * from % order by % limit 1 
offset %',v_old_table,v_old_order_by,records;

end;
records=records+1;
   end loop;
   return records;
end;$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION check_missing_chunk_table_usersessiontable() OWNER TO 
postgres;


--run function ---
select check_missing_chunk_table_usersessiontable();

result:

NOTICE:  /* NUM:42883, DETAILS:operator does not exist: text || bytea */ 
 
NOTICE:  select * from usersessiontable order by sessionid limit 1 offset 1
 check_missing_chunk_table_usersessiontable

  1
(1 row)


Ben Morrow 提到:

Quoth dennis :

here is example

table name is "mail":
column|   type
-
sender|char
subject   |char


I presume you mean 'varchar'?


content   |bytea


I want copy some record into new table 'mail_new'.

sql:
create table mail_new as select * from mail sender='dennis'


You omitted the WHERE. It's very hard to see what's actually going on
when you keep mis-typing the commands you used.


result has an error:
operator does not exist: text || bytea


But if my sql statement has no column "content"
the sql works.
sql:
create table mail_new as select sender,subject from mail sender='dennis'


No, it still doesn't give that error for me. Show us something you've
*actually* *tried*.

Ben




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Clarification With Money data type

[Navanethan Muthusamy]

Hi,

I am using Postgresql 8.4, Can you tell me How Can I use Money data type?

I want to store the money and retrieve. Please give me idea to work on that.

I am using Java with Postgresql, I have tried java.math.BigDecimal with
Money, but its giving error. (Its asking me to do the casing)

Thanks in advance.

Regards,
Navanethan

Re: [SQL] Help : insert a bytea data into new table

[dennis]

Postgres : 8.1.4

Dennis


Ben Morrow wrote:

Quoth dennis:

Dear Ben

 thanks for you anwser.
I try to add function quote_literal on my sql statement .

but it raise other error message (quote_literal not support bytea format):
   function quote_literal(bytea) does not exist


Which Postgres version are you using?

Ben





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Help : insert a bytea data into new table

[dennis]

Dear Ben

   thanks for you anwser.
I try to add function quote_literal on my sql statement .

but it raise other error message (quote_literal not support bytea format):
 function quote_literal(bytea) does not exist





Ben Morrow 提到:

Quoth dennis :

Hi Ben

here is my function , it's for fix missing chunk problem.
It has same problem ,please take look


thank for you help

-table--


db=# \d usersessiontable;
 Table "public.usersessiontable"
   Column   |  Type  | Modifiers
---++---
  serverid  | character varying(100) |
  sessionid | character varying(50)  |
  data  | bytea  |
Indexes:
 "usersessiontable_idx" btree (sessionid)
db=#

db=# \d usersessiontable_test;
 Table "public.usersessiontable"
   Column   |  Type  | Modifiers
---++---
  serverid  | character varying(100) |
  sessionid | character varying(50)  |
  data  | bytea  |

--function


CREATE OR REPLACE FUNCTION check_missing_chunk_table_usersessiontable()
   RETURNS integer AS
$BODY$
declare
begin
records = 0;
OPEN curs1  FOR EXECUTE 'SELECT * FROM usersessiontable ORDER BY 
sessionid';

loop
 FETCH curs1 INTO rowvar;
 IF  NOT FOUND THEN
 EXIT;
 END IF;
 begin
 a_sql = 'insert into 
usersessiontable_test(sessionid,serverid,data) 
values('''||rowvar.sessionid||''','''||rowvar.serverid||''',E'''||rowvar.data||''')';<<--my 


You are trying to concatenate ''',E''' (of type text) and rowvar.data
(of type bytea). This is where the error is coming from. (This actually
works in 8.4, so I presume you're using an earlier version?) In any
case, this is not a safe way to interpolate into an SQL string: you need
the quote_literal function.

a_sql = 'insert into usersessiontable (sessionid, serverid, data) '
|| 'values (' || quote_literal(rowvar.sessionid) || ', '
|| quote_literal(rowvar.serverid) || ', '
|| quote_literal(rowvar.data) || ')';

(Is there a function which will do %-interpolation the way RAISE does?
It would be much clearer in cases like this.)

Ben




--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Remove my e-mail

[Adrian Klaver]

On 03/15/2010 10:24 AM, Daniel Guedes wrote:

Hi!, I don't want to receive more emails from postgresql.

Please remove my email from your delivery list.

Thanks



To unsubscribe go here:
http://www.postgresql.org/mailpref/pgsql-sql

Thanks,
--
Adrian Klaver
adrian.kla...@gmail.com

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] I, nead to capture the IP number from the PC how is running the script ...

[Ing. Marcos Ortiz Valmaseda]

John Dizaro escribió:

I, nead to capture the IP number from the PC how is running the script
"update TABLE1 set campo1 = 123 where ...; "
Can someone help me please?


--
John Evan Dizaro -
Fone: (41) -0303
Fone: (41) 9243-3240
Rua: Alferes Poli Curitiba - PR - Brasil

inet_client_addr(): inet address of the client
inet_client_port(): client''s port number for this connection

Regards

--
 
-- Ing. Marcos Luís Ortíz Valmaseda   --

-- Twitter: http://twitter.com/@marcosluis2186--
-- FreeBSD Fan/User   --
-- http://www.freebsd.org/es  --
-- Linux User # 418229--
-- Database Architect/Administrator   --
-- PostgreSQL RDBMS   --
-- http://www.postgresql.org  --
-- http://planetpostgresql.org--
-- http://www.postgresql-es.org   --

-- Data WareHouse -- Business Intelligence Apprentice --
-- http://www.tdwi.org--
 
-- Ruby on Rails Fan/Developer--

-- http://rubyonrails.org --


Comunidad Técnica Cubana de PostgreSQL
http://postgresql.uci.cu
http://personas.grm.uci.cu/+marcos

Centro de Tecnologías de Gestión de Datos (DATEC) 
Contacto: 
   Correo: centa...@uci.cu
   Telf: +53 07-837-3737
 +53 07-837-3714
Universidad de las Ciencias Informáticas
http://www.uci.cu 





--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] I, nead to capture the IP number from the PC how is running the script ...

[Gerardo Herzig]

John Dizaro wrote:
> I, nead to capture the IP number from the PC how is running the script
> "update TABLE1 set campo1 = 123 where ...; "
> Can someone help me please?
> 
> 
the pg_stat_activity view has a column named client_addr and a
current_query column.

That should help.

Gerardo

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] list of all months

[Garrett Murphy]

I recently ran into the same issue and I resolved it by generating a table of 
nothing but months for the last 5 years:

select  TO_CHAR((current_date - interval '1 month' * a),'-MM') AS mm 
FROM generate_series(1,60,1) AS s(a)

"2010-02"

"2010-01"

"2009-12"

"2009-11"

"2009-10"

…

 

Then I did a join on this generated series:

 

SELECT months.mm

,COUNT(foo_key) 

from  (

select TO_CHAR((current_date - interval '1 month' * 
a),'-MM') as mm 

from generate_series(1,60,1) AS s(a)

) months 

LEFT OUTER JOIN foo

ON 
months.mm=to_char(foo_date_created,'-MM')

GROUP BY months.mm

 

I’m sure you can adapt this to your needs.

 

Garrett Murphy

 

 

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of query
Sent: Monday, March 08, 2010 5:25 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] list of all months

 

Hi,

I want to display data for all days in a month even if no data exists for that 
month. Some of the days in a month might not have any data at all. With normal 
query, we can display days only if data exists.But I want to display rows for 
all days in a month with blank data for non-existing day in database.

How can this be achieved ?

 

 

Re: [SQL] list of all months

[Petru Ghita]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
 
One approach could be:

You build a table with month information over which you are willing to
show data from another table.

Then you just cross join your data table and the data in your month table.

Here is some code I use for generating the table that holds all the
month I care about.


DROP TABLE  IF EXISTS tmp_lookup_months;
CREATE TABLE tmp_lookup_months
(month_year_str varchar(7) NOT NULL,
 first_day_of_month DATE NOT NULL,
 month INTEGER NOT NULL,
 year INTEGER NOT NULL,
 PRIMARY KEY (first_day_of_month, year),
 
 CONSTRAINT valid_date
CHECK (
   (EXTRACT (YEAR FROM first_day_of_month)::integer = year) AND
   (EXTRACT (MONTH FROM first_day_of_month)::integer = month) AND
   (EXTRACT (MONTH FROM first_day_of_month) > 0) AND
   (EXTRACT (MONTH FROM first_day_of_month) < 13) AND  
   (EXTRACT (DAY FROM first_day_of_month) = 01) AND
   (month_year_str) like (CASE WHEN month <= 9
then
  cast (year::text ||'-0'|| month::text  as char(7))   
  
else
 cast (year::text||'-'||  month::text  as char(7))  
   end)
   )   
);


INSERT INTO  tmp_lookup_months
select month_year_str, first_day_of_month, month, year from   
(
select month, year,
   CASE WHEN month <= 9
then
  cast (year::text ||'-0'|| month::text  as char(7))   
  
else
  cast (year::text||'-'||  month::text  as char(7))
  
   end as month_year_str,  
   cast (year::text||'-'||month||'-1' as date) as
first_day_of_month   
from
generate_series(1990, 2090) as year cross join
generate_series(1,12) as month
order by year, month
) as t1;


What is nice about this approach is that you can easily change the
granularity of the time over which you are willing to show the info so
you can create a second table with a trimester list for example.


Then say you have your data in a table called mydata.


select

...
from mydata

CROSS JOIN tmp_lookup_months as ym where(
ym.year >= $1 and  ym.year <= $2
   and my_intersection_function(start_date, end_date, ym.month, ym.year)>0
)
order by ...

So I'm assuming here that in the mydata table you have at least 4 columns:
id, start_date, end_date,  some_data.
some_data field probably only makes sense over the start_date to
end_date interval.
So in your select query you'll most likely need an aggregate function.


If you could provided a more complete description of what you are
trying to achive I might be able to further help.

Petru Ghita

On 08/03/2010 13:25, query wrote:
> Hi,
>
> I want to display data for all days in a month even if no data
> exists for that month. Some of the days in a month might not have
> any data at all. With normal query, we can display days only if
> data exists.But I want to display rows for all days in a month with
> blank data for non-existing day in database.
>
> How can this be achieved ?
>
> 
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
 
iEYEARECAAYFAkueqZsACgkQt6IL6XzynQT+rgCguhFx6qzH3sgiti3O5zaqVQYS
ra4Anjz1C8hS5YC6jRVD9coV6j1AxpPv
=OoAd
-END PGP SIGNATURE-

Re: [SQL] list of all months

[Dawid Kuroczko]

On Mon, Mar 8, 2010 at 13:25, query  wrote:
>
> Hi,
>
> I want to display data for all days in a month even if no data exists for 
> that month. Some of the days in a month might not have any data at all. With 
> normal query, we can display days only if data exists.But I want to display 
> rows for all days in a month with blank data for non-existing day in database.
>
> How can this be achieved ?

Say, you have a table like:

CREATE TABLE some_data (
   date date NOT NULL,
   some_value int
);

Now, You would like to print values
from March 2010, even if there is no
entry for some days in such a table.

We need to have a list of all the days
in March.  We can do it with a query:

SELECT date '2010-03-01' + n AS date
  FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n);

Sweet, we have dates, we just need a LEFT JOIN now:

SELECT date,
   coalesce(value, 0) AS value
   FROM some_data
   RIGHT JOIN (
 SELECT date '2010-03-01' + n AS date
   FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n)
 ) AS dates USING (date);

If you are running fairy recent PostgreSQL
it could be written even nicer:

WITH dates AS (
  SELECT date '2010-03-01' + n AS date
   FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n)
)
SELECT date,
   coalesce(value, 0) AS value
  FROM dates
 LEFT JOIN some_data USING (date);


Two remarks:
 - it is fairy easy to create generate_series(date, date) function.
   Give it a try - its fun! :)
 - coalesce function will provide 0 in places where there is no
   data row, or value is NULL.

Best regards,
Dawid

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] installing uuid generators

[Rob Sargent]

Stop me if you've heard this one before :)

Given that pg_config --libdir yields "/usr/lib64"
to where/what would you expect

  "AS '$libdir/uuid-ossp', 'uuid_generate_v5'"

to resolve?

The loader script,
~/tools/postgresql-8.4.2/contrib/uuid-ossp/uuid-ossp.sql,  generates
"tools/postgresql-8.4.2/contrib/uuid-ossp/uuid-ossp.sql:9: ERROR:  could
not access file "$libdir/uuid-ossp": No such file or directory"

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] list of all months

[silly sad]

It looks like a procedural problem.
I would solve it in plpgsql.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] I, nead to capture the IP number from the PC how is running the script ...

[silly sad]

On 03/15/10 22:18, John Dizaro wrote:

I, nead to capture the IP number from the PC how is running the script


first of all you must tell us how your client is connected to your database?
there are a lot of different variants, and the most common of them is 
not PG related at all while the connection is made via http.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql