Mark Fenbers | 1 Dec 15:23 2005
Picon

APPEND INTO?

I want to SELECT INTO mytable WHERE (criteria are met), except that I 
want to APPEND into an existing table the rows that are selected, 
instead of creating a new table (which SELECT INTO will do).  How can 
this be done?

(Is this what the "FOR UPDATE OF tablename" clause is for?)

Mark

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

Tom Lane | 1 Dec 16:32 2005
Picon

Re: APPEND INTO?

Mark Fenbers <Mark.Fenbers <at> noaa.gov> writes:
> I want to SELECT INTO mytable WHERE (criteria are met), except that I 
> want to APPEND into an existing table the rows that are selected, 
> instead of creating a new table (which SELECT INTO will do).  How can 
> this be done?

INSERT INTO foo SELECT whatever

			regards, tom lane

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

               http://archives.postgresql.org

Jaime Casanova | 1 Dec 16:32 2005
Picon

Re: APPEND INTO?

On 12/1/05, Mark Fenbers <Mark.Fenbers <at> noaa.gov> wrote:
> I want to SELECT INTO mytable WHERE (criteria are met), except that I
> want to APPEND into an existing table the rows that are selected,
> instead of creating a new table (which SELECT INTO will do).  How can
> this be done?
>
> (Is this what the "FOR UPDATE OF tablename" clause is for?)
>
> Mark
>

INSERT INTO table SELECT ....

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

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

Emil Rachovsky | 2 Dec 10:04 2005
Picon

Sybase Connection_Property('number') equivalent in PostGre ?


  I am trying to find out the PostGre equivalent to
the Sybase function Connection_Property (which returns
the connection id, given the parameter 'number') ,but
without success so far. Can anyone tell me how to
retrieve the connection id in PostGre?
 Thanks in advance,
Emil

		
__________________________________ 
Start your day with Yahoo! - Make it your home page! 
http://www.yahoo.com/r/hs

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

Achilleus Mantzios | 2 Dec 11:36 2005

Re: Sybase Connection_Property('number') equivalent in PostGre

O Emil Rachovsky έγραψε στις Dec 2, 2005 :

> 
>   I am trying to find out the PostGre equivalent to
> the Sybase function Connection_Property (which returns
> the connection id, given the parameter 'number') ,but
> without success so far. Can anyone tell me how to
> retrieve the connection id in PostGre?
>  Thanks in advance,
> Emil

I think pg_backend_pid() and pg_stat_get_backend_pid(integeger)
are your best bets.

> 
> 
> 		
> __________________________________ 
> Start your day with Yahoo! - Make it your home page! 
> http://www.yahoo.com/r/hs
> 
> ---------------------------(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
> 

--

-- 
-Achilleus

(Continue reading)

Mark Fenbers | 2 Dec 18:15 2005
Picon

Just 1 in a series...

I currently have a working SQL that SELECTs all records whose 
'river_stage' column exceeds the 'flood_stage' column.  (Very simple -- 
no applause needed.)  Typically, if I get one record, I get a 
consecutive series of them since rivers rise and fall in a continuous 
fashion, and usually respond lethargically when this much water is in 
the rivers.  This time-series of river stages all have (another column 
called) 'event_id' set to the same integer value, so long as the river 
has not fallen below flood stage (which will trigger the event_ID to be 
incremented). 

However, I only want the first occurrence of a such a series (where the 
event_id is the same), what SQL syntax should I use to do this? 

I tried playing with different combinations using DISTINCT, GROUP BY, 
and LIMIT 1, but I have had no success getting the results I'm looking 
for, thus far.  So I figured I might get farther faster by asking the 
group.  I must be misunderstanding the "GROUP BY" clause because I get 
an error essentially stating that I need to list every column in the 
SELECT list in the GROUP BY list (which makes it ineffective)...

My knots are tangled.  Can someone please send advice regarding this issue?

Mark

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly

(Continue reading)

Tom Lane | 2 Dec 18:20 2005
Picon

Re: Just 1 in a series...

Mark Fenbers <Mark.Fenbers <at> noaa.gov> writes:
> ... However, I only want the first occurrence of a such a series (where the 
> event_id is the same), what SQL syntax should I use to do this? 

You might find the "DISTINCT ON" syntax does just what you want --- see
the "weather report" example on the SELECT reference page.  It's not
standard SQL though.

			regards, tom lane

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

Mark Fenbers | 2 Dec 18:44 2005
Picon

Re: Just 1 in a series...

>
>
>You might find the "DISTINCT ON" syntax does just what you want --- see
>the "weather report" example on the SELECT reference page.  It's not
>standard SQL though.
>
This works!  Thanks! 

What would have to be done if I needed a standard SQL solution?
Mark

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

               http://archives.postgresql.org

george young | 2 Dec 19:00 2005
Picon

rename idx's with table; avoid confusing idx names?

[PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1]
After tearing out some hair over the following sequence of events:

[a few weeks ago]
   alter table foo rename to old_foo;
   create table foo(<somewhat different schema>);
   insert into foo select blahblahblah from old_foo;

[today]
   cluster foo_pkey on foo;
   ERROR:  "foo_pkey" is not an index for table "foo"
   What?????  Why does \d say the primary key idx is foo_pkey1 ????

[light dawns]
   Aha! "alter table rename to" did not rename the table's indexes!

I put together a plpgsql function to rename a table and it's indexes
correspondingly[see below].  I would like to know:

  Is there a more robust/portable/clear way to do this?
  Is this a bad idea for some subtle reason?
  Is there any way to get a less cumbersome interface than "select rename_table_and_indexes('foo','old_foo')?
  Does this look useful enough for me to package more formally? 

-- George Young

vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
CREATE or REPLACE FUNCTION rename_table_and_indexes(old_name text, new_name text) returns void AS $$
declare
   prefix_len integer;
(Continue reading)

Tom Lane | 2 Dec 23:33 2005
Picon

Re: rename idx's with table; avoid confusing idx names?

george young <gry <at> ll.mit.edu> writes:
> I put together a plpgsql function to rename a table and it's indexes
> correspondingly[see below].  I would like to know:

>   Is there a more robust/portable/clear way to do this?
>   Is this a bad idea for some subtle reason?

It won't work if the table and column names are so long as to require
truncation to form an index name.  Also there are some corner cases
in which you'll collide with existing index names.  (The underlying
backend index-name creation logic goes to some effort to generate
nonconflicting index names, but this code isn't doing any such thing.)

			regards, tom lane

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


Gmane