Re: [SQL] Copying a row within table

[Aarni Ruuhimäki]

On Wednesday 15 March 2006 03:11, John DeSoi wrote:
> On Mar 14, 2006, at 2:19 AM, Aarni Ruuhimäki wrote:
> > testing=# INSERT INTO foo (foo_1, foo_2, foo_3 ...) (SELECT foo_1,
> > foo_2,
> > foo_3 ... FROM message_table WHERE foo_id = 10);
> > INSERT 717286 1
> > testing=#
> >
> > Is there a fast way to copy all but not the PK column to a new row
> > within the
> > same table so that the new foo_id gets its value from the sequence ?
>
> Here is an example using a plpgsql function:
>
> create or replace function test_duplicate (p_id integer)
> returns integer as $$
> declare
>  tt test%rowtype;
> begin
>  select into tt * from test where id = p_id;
>  tt.id := nextval(pg_get_serial_sequence('test', 'id'));
>  insert into test values (tt.*);
>  return tt.id;
> end;
> $$ language plpgsql;
>
>
>
>
>
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org

Hi,

Thank you guys, I'll try these.

Reason I need this is I have a largish product table which stores slightly 
different types of products and for some types it is extremely handy to just 
clone an existing product and then change the name and other few details 
rather than go the time consuming way of creating a new almost identical 
product from scratch filling in all the required values and calling all their 
check routines in the application. Different types do not use all or the same 
colums, some are left null or empty and some have different boolean values in 
one or more columns.

Best regards and thanks again,

Aarni

--
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core** linux system
--

---(end of broadcast)---
TIP 1: 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] group by function, make SQL cleaner?

[Bryce Nesbitt]

I've got a working query:

stage=# SELECT date_trunc('day',endtime),count(*)
FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
GROUP BY  date_trunc('day',endtime)
ORDER BY date_trunc('day',endtime);

 date_trunc  | count
-+---
 2006-02-01 00:00:00 |   253
 2006-02-02 00:00:00 |   245
 2006-02-03 00:00:00 |   231
 2006-02-04 00:00:00 |   313
 2006-02-05 00:00:00 |   285
 2006-02-06 00:00:00 |   194
 2006-02-07 00:00:00 |   229
 2006-02-08 00:00:00 |   239
 2006-02-09 00:00:00 |   250
 2006-02-10 00:00:00 |   245
 2006-02-11 00:00:00 |   275

Is there a way to eliminate the ugly repeated use of
date_trunc('day',endtime)?


---(end of broadcast)---
TIP 1: 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] group by function, make SQL cleaner?

[Tom Lane]

Bryce Nesbitt <[EMAIL PROTECTED]> writes:
> SELECT date_trunc('day',endtime),count(*)
> FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
> GROUP BY  date_trunc('day',endtime)
> ORDER BY date_trunc('day',endtime);

> Is there a way to eliminate the ugly repeated use of
> date_trunc('day',endtime)?

In this particular case you could say

... GROUP BY 1 ORDER BY 1;

"ORDER BY n" as a reference to the n'th SELECT output column is in the
SQL92 spec.  (IIRC they removed it in SQL99, but we still support it,
and I think most other DBMSes do too.)  "GROUP BY n" is *not* in any
version of the spec but we allow it anyway.  I'm not sure how common
that notation is.

This does not work in any context except repeating a SELECT result
expression in GROUP BY or ORDER BY.

regards, tom lane

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