[SQL] unsubscribe

2003-10-27 Thread Antony Gubert 


_
Making your life easy! That is Citibank Suvidha. 
http://server1.msn.co.in/msnleads/citi_cards_sept03/CitiSuvidha.asp Get your 
account now!

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] extend INSERT by 'INSERT INTO table FETCH ... FROM cursor' syntax

2003-10-27 Thread Christoph Haller 
> 
> Please CC me, I am not subscribed.
> 
> An imaginary SQL statement
> INSERT INTO table FETCH ... FROM cursor;
> looks almost the same as currently available
> INSERT INTO table SELECT ...;
> 
> I tried it because I needed to insert a row in a table
> after I DELETEd a set of rows, something like this:
> 
> BEGIN;
> DECLARE total CURSOR
>  FOR SELECT=20
>   SUBSTR(datetime,1,7)||'-01 00:00:00' as month,
>   client,
>   SUM(money)
>  FROM stat
>  WHERE SUBSTR(datetime,1,7)=3D'2003-10'
>  GROUP BY month,client;
> DELETE FROM stat WHERE SUBSTR(datetime,1,7)=3D'2003-10';
> INSERT INTO stat FETCH ALL FROM total;
> COMMIT;
> 
> but it does not work, chokes on FETCH ALL.
> 
> I want to sum up all the money by month, delete all the rows
> (possibly thousands of them) and insert one row per client
> with monthly totals.
> 
> Obviously I cannot swap order of INSERT and DELETE here.
> 
> I hesitate to post this to [EMAIL PROTECTED],
> do I have to? ;)
> 
> --=20
> Alexander Vlasenko
> 
Using a temporary table to buffer the result comes to mind. 
Regards, Christoph 


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Query planner: current_* vs. explicit date

2003-10-27 Thread Tom Lane 
Chris Gamache <[EMAIL PROTECTED]> writes:
> By giving it a definitive range I was able to coax query planner to use the
> index:

> SELECT id FROM trans_table WHERE trans_date >=  (SELECT
> current_date::timestamptz) AND trans_date < (SELECT current_timestamp);

> BTW, This didn't work:

> SELECT id FROM trans_table WHERE trans_date >= current_date::timestamptz AND
> trans_date < current_timestamp;

[ scratches head... ]  AFAICS the latter should "work" too.  Doesn't
EXPLAIN show the same estimated row count for both versions?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[SQL] URGENT!!! changing Column size

2003-10-27 Thread mohan 
Hi can we change the size of a column in postgres. I have a table named
institution and column name is name varchar2(25), i want to change it to
varchar2(50). Please let me know.

--Mohan






---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] URGENT!!! changing Column size

2003-10-27 Thread Tomasz Myrta 
Dnia 2003-10-27 18:10, Użytkownik [EMAIL PROTECTED] napisał:
Hi can we change the size of a column in postgres. I have a table named
institution and column name is name varchar2(25), i want to change it to
varchar2(50). Please let me know.
alter table institution add column tmp varchar2(50);
update institution set tmp=name;
alter table institution drop column name;
alter table institution rename tmp to name;
(or something like this)

Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] URGENT!!! changing Column size

2003-10-27 Thread Gary Stainburn 
On Monday 27 Oct 2003 5:10 pm, [EMAIL PROTECTED] wrote:
> Hi can we change the size of a column in postgres. I have a table named
> institution and column name is name varchar2(25), i want to change it to
> varchar2(50). Please let me know.
>
> --Mohan

try 

alter table institution add column newname varchar2(50);
update institution set newname = name;
alter table institution drop column namel;
alter table institution rename column newname to name;


>
>
>
>
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

-- 
Gary Stainburn
 
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] extend INSERT by 'INSERT INTO table FETCH ... FROM cursor' syntax

2003-10-27 Thread Josh Berkus 
Alexander,

> > BEGIN;
> > DECLARE total CURSOR
> >  FOR SELECT=20
> >   SUBSTR(datetime,1,7)||'-01 00:00:00' as month,
> >   client,
> >   SUM(money)
> >  FROM stat
> >  WHERE SUBSTR(datetime,1,7)=3D'2003-10'
> >  GROUP BY month,client;
> > DELETE FROM stat WHERE SUBSTR(datetime,1,7)=3D'2003-10';
> > INSERT INTO stat FETCH ALL FROM total;
> > COMMIT;
> > 
> > but it does not work, chokes on FETCH ALL.

Well, there's two problems with your program:

1) INSERT INTO  FETCH ALL is not currently implemented.  You would need to 
use a loop, and insert one row at a time by value.

2) You can't insert the rows you've just deleted from the base tables.  In 
your example, the TOTAL cursor would be empty.   I think that what you really 
want is a temp table.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] URGENT!!! changing Column size

2003-10-27 Thread Tomasz Myrta 
Dnia 2003-10-27 19:33, Użytkownik [EMAIL PROTECTED] napisał:
I've seen these sets of steps suggested in response to other such
inquires, but doesn't this break views on the associated table, or may
just not work because if a view dependency exists?
It would be the second case (it won't work at all). You can use 
"cascade" when dropping column. After this you need to recreate views 
dropped together with a column.

Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] Error with DROP column

2003-10-27 Thread mohan 
Hi All i am trying to DROP the column but i keep getting this error.

 ALTER TABLE institution DROP COLUMN name CASCADE;
ERROR:  ALTER TABLE / DROP COLUMN is not implemented

please help me in solving this issue.







---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Error with DROP column

2003-10-27 Thread Devrim GUNDUZ 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


Hi,

On Mon, 27 Oct 2003 [EMAIL PROTECTED] wrote:

> Hi All i am trying to DROP the column but i keep getting this error.
> 
>  ALTER TABLE institution DROP COLUMN name CASCADE;
> ERROR:  ALTER TABLE / DROP COLUMN is not implemented

I think you are using an old version of PostgreSQL. AFAIR this 
functionality was implemented on 7.3. Here is mine:

***
[EMAIL PROTECTED]:test=# SELECT version();
 version
- 
-
 PostgreSQL 7.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 
20030222 (Red Hat Linux 3.2.2-5)
(1 row)

[EMAIL PROTECTED]:test=# ALTER TABLE institution drop column name;
ALTER TABLE

***

Maybe you should upgrade your PostgreSQL.

Regards,
- -- 
Devrim GUNDUZ
[EMAIL PROTECTED]   [EMAIL PROTECTED] 
http://www.tdmsoft.com
http://www.gunduz.org
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE/nXbYtl86P3SPfQ4RAqScAKDsSSSG7KYiM/TnBRguaG5hRF6MIACgwbfu
ZdGWzsBbtu486c0BreFFlmA=
=hFr5
-END PGP SIGNATURE-


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] Error with DROP column

2003-10-27 Thread Tomasz Myrta 
Dnia 2003-10-27 20:35, Użytkownik [EMAIL PROTECTED] napisał:
Hi All i am trying to DROP the column but i keep getting this error.

 ALTER TABLE institution DROP COLUMN name CASCADE;
ERROR:  ALTER TABLE / DROP COLUMN is not implemented
please help me in solving this issue.
Looks like Postgresql older than 7.x (7.3?)

You can't drop column this way. In older versions you can do this only by:
create table temp (columns as you wish to have)
insert into temp select * from old_table
drop old_table
alter table temp rename to old_table
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] extend INSERT by 'INSERT INTO table FETCH ... FROM cursor' syntax

2003-10-27 Thread Alexander Vlasenko 
On Monday 27 October 2003 21:35, Josh Berkus wrote:
> Alexander,
>
> > > BEGIN;
> > > DECLARE total CURSOR
> > >  FOR SELECT=20
> > >   SUBSTR(datetime,1,7)||'-01 00:00:00' as month,
> > >   client,
> > >   SUM(money)
> > >  FROM stat
> > >  WHERE SUBSTR(datetime,1,7)=3D'2003-10'
> > >  GROUP BY month,client;
> > > DELETE FROM stat WHERE SUBSTR(datetime,1,7)=3D'2003-10';
> > > INSERT INTO stat FETCH ALL FROM total;
> > > COMMIT;

[ BTW: quoted-printable is evil ;) ]

> > >
> > > but it does not work, chokes on FETCH ALL.
>
> Well, there's two problems with your program:
>
> 1) INSERT INTO  FETCH ALL is not currently implemented.  You would need
> to use a loop, and insert one row at a time by value.

Exactly. I was saying that if implemented it may be useful.
My example is certainly doable without it but it quickly gets ugly
since I can't use this nifty trick.

> 2) You can't insert the rows you've just deleted from the base tables.  In
> your example, the TOTAL cursor would be empty.   I think that what you
> really want is a temp table.

Why do you think it would be empty? It is not. I tried this:

BEGIN;
DECLARE total CURSOR
 FOR SELECT
  SUBSTR(datetime,1,7)||'-01 00:00:00' as month,
  client,
  SUM(money)
 FROM stat
 WHERE SUBSTR(datetime,1,7)='2003-10'
 GROUP BY month,client;
DELETE FROM stat WHERE SUBSTR(datetime,1,7)='2003-10';
FETCH ALL FROM total;   <===
COMMIT;

and it does work as expected. FETCH spews out already deleted rows.
There is no problem with it.
-- 
Alexander Vlasenko

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] URGENT!!! changing Column size

2003-10-27 Thread CoL 
Hi,

[EMAIL PROTECTED] wrote, On 10/27/2003 6:10 PM:

Hi can we change the size of a column in postgres. I have a table named
institution and column name is name varchar2(25), i want to change it to
varchar2(50). Please let me know.
1 solution:

begin;
create temporary table temp as select * from mytable;
drop table mytable;
create table mytable (name varchar(50));
insert into mytable select CAST(name AS varchar(50)) from temp;
drop table temp;
commit;
C.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[SQL] connectby

2003-10-27 Thread sector119 
hi

I have menu table:
 id  | integer | not null default
 nextval('public.menu_id_seq'::text)
 parent_id   | integer |
 description | text|

 I do select:
 test=> SELECT * FROM connectby('menu','id','parent_id','2',0,'~')
 t(id integer, parent_id integer, level int, branch text);

id | parent_id | level |   branch
 +---+---+-
   2 |   | 0 | 2
   4 | 2 | 1 | 2~4
   7 | 4 | 2 | 2~4~7
  10 | 7 | 3 | 2~4~7~10
  16 |10 | 4 | 2~4~7~10~16
   9 | 4 | 2 | 2~4~9

How am I able to select description file from menu table,
not only id, parent_id, level, branch fields?
  
-- 
WBR, sector119

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[SQL] connectby

2003-10-27 Thread Sergei Levchenko 
hi

I have menu table:
 id  | integer | not null default 
nextval('public.menu_id_seq'::text)
 parent_id   | integer |
 description | text|

I do select:
test=> SELECT * FROM connectby('menu','id','parent_id','2',0,'~') t(id 
integer, parent_id integer, level int, branch text);
 id | parent_id | level |   branch
+---+---+-
  2 |   | 0 | 2
  4 | 2 | 1 | 2~4
  7 | 4 | 2 | 2~4~7
 10 | 7 | 3 | 2~4~7~10
 16 |10 | 4 | 2~4~7~10~16
  9 | 4 | 2 | 2~4~9

How am I able to select description file from menu table, not only id, 
parent_id, level, branch fields?

--
WBR, sector119
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] connectby

2003-10-27 Thread George Essig 
> hi
> 
> I have menu table:
>   id  | integer | not null default 
> nextval('public.menu_id_seq'::text)
>   parent_id   | integer |
>   description | text|
> 
> I do select:
> test=> SELECT * FROM connectby('menu','id','parent_id','2',0,'~') t(id 
> integer, parent_id integer, level int, branch text);
>   id | parent_id | level |   branch
> +---+---+-
>2 |   | 0 | 2
>4 | 2 | 1 | 2~4
>7 | 4 | 2 | 2~4~7
>   10 | 7 | 3 | 2~4~7~10
>   16 |10 | 4 | 2~4~7~10~16
>9 | 4 | 2 | 2~4~9
> 
> How am I able to select description file from menu table, not only id, 
> parent_id, level, branch fields?
> 
> -- 
> WBR, sector119

Try a join with the original table:

SELECT t.*, description FROM connectby('menu','id','parent_id','2',0,'~') 
AS t(id integer, parent_id integer, level int, branch text), menu 
WHERE t.id = menu.id

George Essig

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]