APPEND INTO?

Mark Fenbers <Mark.Fenbers <at> noaa.gov>
2005-12-01 14:23:06 GMT

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

Re: APPEND INTO?

Tom Lane <tgl <at> sss.pgh.pa.us>
2005-12-01 15:32:08 GMT

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

Re: APPEND INTO?

Jaime Casanova <systemguards <at> gmail.com>
2005-12-01 15:32:02 GMT

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

Sybase Connection_Property('number') equivalent in PostGre ?

Emil Rachovsky <zsevgymko <at> yahoo.com>
2005-12-02 09:04:19 GMT

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

Just 1 in a series...

Mark Fenbers <Mark.Fenbers <at> noaa.gov>
2005-12-02 17:15:29 GMT

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

Re: Just 1 in a series...

Tom Lane <tgl <at> sss.pgh.pa.us>
2005-12-02 17:20:30 GMT

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

Re: Just 1 in a series...

Mark Fenbers <Mark.Fenbers <at> noaa.gov>
2005-12-02 17:44:27 GMT

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

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

george young <gry <at> ll.mit.edu>
2005-12-02 18:00:37 GMT

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

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

Tom Lane <tgl <at> sss.pgh.pa.us>
2005-12-02 22:33:44 GMT

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