pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect into
43+ messages / 18 participants
[nested] [flat]
* Select into
@ 2001-12-06 03:01 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 3 replies; 43+ messages in thread
From: Christopher Kings-Lynne @ 2001-12-06 03:01 UTC (permalink / raw)
To: pgsql-sql
Hi All,
Is it possible to insert into a table based on a select from another table?
The docs for SELECT INTO and like seem to imply you can only create a table
based on a select, not append to an existing table.
How do you do this?
Chris
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2001-12-06 04:10 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 0 replies; 43+ messages in thread
From: Bruce Momjian @ 2001-12-06 04:10 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-sql
> Hi All,
>
> Is it possible to insert into a table based on a select from another table?
>
> The docs for SELECT INTO and like seem to imply you can only create a table
> based on a select, not append to an existing table.
Sure, do INSERT INTO ... SELECT.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2001-12-06 04:18 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 0 replies; 43+ messages in thread
From: Stephan Szabo @ 2001-12-06 04:18 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-sql
On Thu, 6 Dec 2001, Christopher Kings-Lynne wrote:
> Hi All,
>
> Is it possible to insert into a table based on a select from another table?
>
> The docs for SELECT INTO and like seem to imply you can only create a table
> based on a select, not append to an existing table.
>
> How do you do this?
I think you can use
INSERT INTO <table>(<cols>) SELECT ...
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2001-12-06 21:43 Jean-Michel Chabanne <jeanmichel.chabanne@free.fr>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2 siblings, 0 replies; 43+ messages in thread
From: Jean-Michel Chabanne @ 2001-12-06 21:43 UTC (permalink / raw)
To: pgsql-sql
Christopher Kings-Lynne wrote:
> Hi All,
>
> Is it possible to insert into a table based on a select from another
> table?
>
> The docs for SELECT INTO and like seem to imply you can only create a
> table based on a select, not append to an existing table.
>
> How do you do this?
>
> Chris
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
You have to use another syntaxe form :
INSERT INTO target_table (COLx,COLy....) select COLa,COLB... from
source-table [where ...]
--
Jean-Michel Chabanne
77450 MONTRY (FRANCE)
48" 54' N - 2" 49' E
Powered by Linux
^ permalink raw reply [nested|flat] 43+ messages in thread
* Select into
@ 2004-01-12 01:09 beyaRecords - The home Urban music <uzo@beya-records.com>
0 siblings, 1 reply; 43+ messages in thread
From: beyaRecords - The home Urban music @ 2004-01-12 01:09 UTC (permalink / raw)
To: pgsql-sql
Hi,
I want to copy a sequence of rows from one table into another table. I
have tried select into but this will only work if the table you are
copying to does not already exist. How do I copy to a table that does
already exist?
regards
Uzo
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2004-01-12 09:45 Richard Huxton <dev@archonet.com>
parent: beyaRecords - The home Urban music <uzo@beya-records.com>
0 siblings, 0 replies; 43+ messages in thread
From: Richard Huxton @ 2004-01-12 09:45 UTC (permalink / raw)
To: beyaRecords - The home Urban music <uzo@beya-records.com>; pgsql-sql
On Monday 12 January 2004 01:09, beyaRecords - The home Urban music wrote:
> Hi,
> I want to copy a sequence of rows from one table into another table. I
> have tried select into but this will only work if the table you are
> copying to does not already exist. How do I copy to a table that does
> already exist?
Something like:
INSERT INTO table_a (a,b,c)
SELECT d,e,f FROM table_b WHERE f=1;
You could replace any of the columns in the SELECT by an expression if you
wanted to.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 43+ messages in thread
* select into
@ 2006-11-22 08:28 Mulham freshcode <mulhamcode@yahoo.com>
0 siblings, 3 replies; 43+ messages in thread
From: Mulham freshcode @ 2006-11-22 08:28 UTC (permalink / raw)
To: pgsql-sql
Hi guys,
Am new to sql scripting so this might be a stupid question. Am getting an error while trying to do the following
SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
where svc_data_rec is defined as record and svc_tbl_name is a varchar that holds the name of a table and sub_id is another varchar. the error message is
ERROR: syntax error at or near "$1" at character 17
QUERY: SELECT * from $1 where 'uid' = $2
CONTEXT: PL/pgSQL function "foo" line 132 at select into variables
LINE 1: SELECT * from $1 where 'uid' = $2
where am i going wrong? it seems that the variables are not being substituted with their values correctly.
Thank you,
---------------------------------
Sponsored Link
Mortgage rates near 39yr lows. $420,000 Mortgage for $1,399/mo - Calculate new house payment
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-22 15:47 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Mulham freshcode <mulhamcode@yahoo.com>
2 siblings, 1 reply; 43+ messages in thread
From: A. Kretschmer @ 2006-11-22 15:47 UTC (permalink / raw)
To: pgsql-sql
am Wed, dem 22.11.2006, um 0:28:15 -0800 mailte Mulham freshcode folgendes:
> Hi guys,
>
> Am new to sql scripting so this might be a stupid question. Am getting an
> error while trying to do the following
>
> SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
>
> where svc_data_rec is defined as record and svc_tbl_name is a varchar that
> holds the name of a table and sub_id is another varchar. the error message is
You should rewrite your plpgsql-function. You can't handle with
string-vars in this way, you must create a string with your complete sql
and EXECUTE this string.
Read
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-22 15:55 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Mulham freshcode <mulhamcode@yahoo.com>
2 siblings, 1 reply; 43+ messages in thread
From: Richard Broersma Jr @ 2006-11-22 15:55 UTC (permalink / raw)
To: Mulham freshcode <mulhamcode@yahoo.com>; pgsql-sql
> Hi guys,
>
> Am new to sql scripting so this might be a stupid question. Am getting an error while trying
> to do the following
>
> SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
>
> where svc_data_rec is defined as record and svc_tbl_name is a varchar that holds the name of a
> table and sub_id is another varchar. the error message is
>
> ERROR: syntax error at or near "$1" at character 17
> QUERY: SELECT * from $1 where 'uid' = $2
> CONTEXT: PL/pgSQL function "foo" line 132 at select into variables
> LINE 1: SELECT * from $1 where 'uid' = $2
>
> where am i going wrong? it seems that the variables are not being substituted with their
> values correctly.
Notice:
http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html
and particularly this example:
http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html#AEN54014
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-22 16:07 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 43+ messages in thread
From: Andreas Kretschmer @ 2006-11-22 16:07 UTC (permalink / raw)
To: pgsql-sql
Richard Broersma Jr <rabroersma@yahoo.com> schrieb:
> > Hi guys,
> >
> > Am new to sql scripting so this might be a stupid question. Am getting an error while trying
> > to do the following
> >
> > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
> >
> > where svc_data_rec is defined as record and svc_tbl_name is a varchar that holds the name of a
> > table and sub_id is another varchar. the error message is
> >
> > ERROR: syntax error at or near "$1" at character 17
> > QUERY: SELECT * from $1 where 'uid' = $2
> > CONTEXT: PL/pgSQL function "foo" line 132 at select into variables
> > LINE 1: SELECT * from $1 where 'uid' = $2
> >
> > where am i going wrong? it seems that the variables are not being substituted with their
> > values correctly.
>
> Notice:
> http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html
> and particularly this example:
> http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html#AEN54014
> Regards,
Richard, the plain SQL "INSERT INTO <table>" is an other thing as the
original problem...
He is in a PL/pgsql - function and he should rewrite this to use EXECUTE
... INTO
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-22 16:14 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mulham freshcode <mulhamcode@yahoo.com>
2 siblings, 0 replies; 43+ messages in thread
From: Tom Lane @ 2006-11-22 16:14 UTC (permalink / raw)
To: Mulham freshcode <mulhamcode@yahoo.com>; +Cc: pgsql-sql
Mulham freshcode <mulhamcode@yahoo.com> writes:
> Am new to sql scripting so this might be a stupid question. Am getting an error while trying to do the following
> SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
The error message shows that this is getting rewritten into
> QUERY: SELECT * from $1 where 'uid' = $2
so the problem is that you are using svc_tbl_name as a plpgsql variable,
and plpgsql is not bright enough to realize that it shouldn't substitute
the variable value at this particular spot in the query. You need to
change the variable name to something that won't conflict. In general,
don't use plpgsql variables that are named the same as any SQL tables or
columns you need to mention in the function.
regards, tom lane
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-22 17:26 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 43+ messages in thread
From: Richard Broersma Jr @ 2006-11-22 17:26 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-sql
--- Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> Richard, the plain SQL "INSERT INTO <table>" is an other thing as the
> original problem...
>
> He is in a PL/pgsql - function and he should rewrite this to use EXECUTE
> ... INTO
>
I see, I am not even a novice yet when it comes PL/pgsql. Thanks for the clarification.
Regards,
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-23 17:19 Mulham freshcode <mulhamcode@yahoo.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 43+ messages in thread
From: Mulham freshcode @ 2006-11-23 17:19 UTC (permalink / raw)
To: A. Kretschmer <andreas.kretschmer@schollglas.com>; pgsql-sql
Hi,
Thanks very much for all the suggestions. Like Andreas said i have to use EXECUTE to do this. That was my guess too but i was putting the INTO cluase into the string before executing it. This is a step forward. The problem now is getting the into to work with a record. Am using a RECORD variable after declaring it in the INTO clause. pg is complaining
ERROR: record "svc_data_rec" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function "foo" line 130 at execute statement
I read in the docs the following, "The INTO clause specifies where the results of a SELECT command should be assigned. If a row or variable list is provided, it must exactly match the structure of the results produced by the SELECT (when a record variable is used, it will configure itself to match the result's structure automatically)." That i take to mean that the above is ok. But it ain't.
Thanks again for your guys help...
"A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: am Wed, dem 22.11.2006, um 0:28:15 -0800 mailte Mulham freshcode folgendes:
> Hi guys,
>
> Am new to sql scripting so this might be a stupid question. Am getting an
> error while trying to do the following
>
> SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
>
> where svc_data_rec is defined as record and svc_tbl_name is a varchar that
> holds the name of a table and sub_id is another varchar. the error message is
You should rewrite your plpgsql-function. You can't handle with
string-vars in this way, you must create a string with your complete sql
and EXECUTE this string.
Read
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
---------------------------------
Access over 1 million songs - Yahoo! Music Unlimited.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-23 18:35 Adrian Klaver <aklaver@comcast.net>
parent: Mulham freshcode <mulhamcode@yahoo.com>
0 siblings, 1 reply; 43+ messages in thread
From: Adrian Klaver @ 2006-11-23 18:35 UTC (permalink / raw)
To: pgsql-sql; +Cc: Mulham freshcode <mulhamcode@yahoo.com>; A. Kretschmer <andreas.kretschmer@schollglas.com>
On Thursday 23 November 2006 09:19 am, Mulham freshcode wrote:
> Hi,
> Thanks very much for all the suggestions. Like Andreas said i have to
> use EXECUTE to do this. That was my guess too but i was putting the INTO
> cluase into the string before executing it. This is a step forward. The
> problem now is getting the into to work with a record. Am using a RECORD
> variable after declaring it in the INTO clause. pg is complaining
>
> ERROR: record "svc_data_rec" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is
> indeterminate. CONTEXT: PL/pgSQL function "foo" line 130 at execute
> statement
>
> I read in the docs the following, "The INTO clause specifies where the
> results of a SELECT command should be assigned. If a row or variable list
> is provided, it must exactly match the structure of the results produced by
> the SELECT (when a record variable is used, it will configure itself to
> match the result's structure automatically)." That i take to mean that the
> above is ok. But it ain't.
>
> Thanks again for your guys help...
>
> "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: am Wed, dem
22.11.2006, um 0:28:15 -0800 mailte Mulham freshcode folgendes:
> > Hi guys,
> >
> > Am new to sql scripting so this might be a stupid question. Am getting
> > an error while trying to do the following
> >
> > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
> >
> > where svc_data_rec is defined as record and svc_tbl_name is a varchar
> > that holds the name of a table and sub_id is another varchar. the error
> > message is
>
> You should rewrite your plpgsql-function. You can't handle with
> string-vars in this way, you must create a string with your complete sql
> and EXECUTE this string.
>
> Read
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPG
>SQL-STATEMENTS-EXECUTING-DYN
>
>
> Andreas
My guess is that the error message is correct, the svc_data_rec has not had
any values assigned to it. In other words the EXECUTE statement is not
working the way you think it is. Could you post the EXECUTE string?
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-24 04:21 Mulham freshcode <mulhamcode@yahoo.com>
parent: Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 43+ messages in thread
From: Mulham freshcode @ 2006-11-24 04:21 UTC (permalink / raw)
To: aklaver@comcast.net; pgsql-sql; +Cc: Mulham freshcode <mulhamcode@yahoo.com>; A. Kretschmer <andreas.kretschmer@schollglas.com>
Hello Adrian,
Here is my execute string,
sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || sub_id ;
execute sql_str1 into svc_data_rec ;
svc_data_rec is a RECORD, which is supposed to be dynamic. If I need to define the structure of the record then there will be no point in using it since I need something to change according the structure the table, which i don't know in advance...
Thank you,
Mustafa...
Adrian Klaver <aklaver@comcast.net> wrote: On Thursday 23 November 2006 09:19 am, Mulham freshcode wrote:
> Hi,
> Thanks very much for all the suggestions. Like Andreas said i have to
> use EXECUTE to do this. That was my guess too but i was putting the INTO
> cluase into the string before executing it. This is a step forward. The
> problem now is getting the into to work with a record. Am using a RECORD
> variable after declaring it in the INTO clause. pg is complaining
>
> ERROR: record "svc_data_rec" is not assigned yet
> DETAIL: The tuple structure of a not-yet-assigned record is
> indeterminate. CONTEXT: PL/pgSQL function "foo" line 130 at execute
> statement
>
> I read in the docs the following, "The INTO clause specifies where the
> results of a SELECT command should be assigned. If a row or variable list
> is provided, it must exactly match the structure of the results produced by
> the SELECT (when a record variable is used, it will configure itself to
> match the result's structure automatically)." That i take to mean that the
> above is ok. But it ain't.
>
> Thanks again for your guys help...
>
> "A. Kretschmer" wrote: am Wed, dem
22.11.2006, um 0:28:15 -0800 mailte Mulham freshcode folgendes:
> > Hi guys,
> >
> > Am new to sql scripting so this might be a stupid question. Am getting
> > an error while trying to do the following
> >
> > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
> >
> > where svc_data_rec is defined as record and svc_tbl_name is a varchar
> > that holds the name of a table and sub_id is another varchar. the error
> > message is
>
> You should rewrite your plpgsql-function. You can't handle with
> string-vars in this way, you must create a string with your complete sql
> and EXECUTE this string.
>
> Read
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPG
>SQL-STATEMENTS-EXECUTING-DYN
>
>
> Andreas
My guess is that the error message is correct, the svc_data_rec has not had
any values assigned to it. In other words the EXECUTE statement is not
working the way you think it is. Could you post the EXECUTE string?
--
Adrian Klaver
aklaver@comcast.net
---------------------------------
Want to start your own business? Learn how on Yahoo! Small Business.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-24 04:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mulham freshcode <mulhamcode@yahoo.com>
0 siblings, 2 replies; 43+ messages in thread
From: Tom Lane @ 2006-11-24 04:45 UTC (permalink / raw)
To: Mulham freshcode <mulhamcode@yahoo.com>; +Cc: aklaver@comcast.net, pgsql-sql@postgresql.org, "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Mulham freshcode <mulhamcode@yahoo.com> writes:
> execute sql_str1 into svc_data_rec ;
> svc_data_rec is a RECORD, which is supposed to be dynamic.
This should work --- in PG 8.1 or later. In older versions you'd have
to fool around with a FOR ... IN EXECUTE ... loop.
regards, tom lane
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-24 06:54 Mulham freshcode <mulhamcode@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 43+ messages in thread
From: Mulham freshcode @ 2006-11-24 06:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Hi Tom,
Thanks for the help. Am using version 8.0 and it seems like RECORD is not that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP and it does the trick. But am still finding it hard to move forward with this. I have the name of table field in a varchar variable that i got from information_schema.columns and I have the records variable that stores the contains the data from that table. Usually I'd do something like data_rec.col_name to extract the data from the record but now I don't know the name per se. how can i say something like data_rec[col_name] where col_name is a variable that has the actual column name. I found no examples in the docs that explain this. Can it be done in version 8.0.1?
I find variable substitution kind of confusing. I mean why is there no way of saying explicitly replace this variable with its content before executing the statement?
Sorry for the long question,
and thanks again for the help
Mustafa...
Tom Lane <tgl@sss.pgh.pa.us> wrote: Mulham freshcode writes:
> execute sql_str1 into svc_data_rec ;
> svc_data_rec is a RECORD, which is supposed to be dynamic.
This should work --- in PG 8.1 or later. In older versions you'd have
to fool around with a FOR ... IN EXECUTE ... loop.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------------
Everyone is raving about the all-new Yahoo! Mail beta.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-24 14:45 Adrian Klaver <aklaver@comcast.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 43+ messages in thread
From: Adrian Klaver @ 2006-11-24 14:45 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Mulham freshcode <mulhamcode@yahoo.com>; A. Kretschmer <andreas.kretschmer@schollglas.com>
On Thursday 23 November 2006 08:45 pm, Tom Lane wrote:
> Mulham freshcode <mulhamcode@yahoo.com> writes:
> > execute sql_str1 into svc_data_rec ;
> >
> > svc_data_rec is a RECORD, which is supposed to be dynamic.
>
> This should work --- in PG 8.1 or later. In older versions you'd have
> to fool around with a FOR ... IN EXECUTE ... loop.
>
> regards, tom lane
The documentation for pl/pgsql in 8.1 and higher says different.
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-...
" SELECT INTO is not currently supported within EXECUTE."
The change is reflected in the HISTORY text though. How is the best way to get
a change made to the documentation?
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-24 14:55 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Adrian Klaver <aklaver@comcast.net>
1 sibling, 1 reply; 43+ messages in thread
From: Andreas Kretschmer @ 2006-11-24 14:55 UTC (permalink / raw)
To: pgsql-sql
Adrian Klaver <aklaver@comcast.net> schrieb:
> On Thursday 23 November 2006 08:45 pm, Tom Lane wrote:
> > Mulham freshcode <mulhamcode@yahoo.com> writes:
> > > execute sql_str1 into svc_data_rec ;
> > >
> > > svc_data_rec is a RECORD, which is supposed to be dynamic.
> >
> > This should work --- in PG 8.1 or later. In older versions you'd have
> > to fool around with a FOR ... IN EXECUTE ... loop.
> >
> > regards, tom lane
> The documentation for pl/pgsql in 8.1 and higher says different.
>
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-...
> " SELECT INTO is not currently supported within EXECUTE."
Thats right. The solution with 8.1 or later is:
EXECUTE .... INTO var, and _not_ SELECT INTO var.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-24 15:26 Adrian Klaver <aklaver@comcast.net>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 43+ messages in thread
From: Adrian Klaver @ 2006-11-24 15:26 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andreas Kretschmer <akretschmer@spamfence.net>
On Friday 24 November 2006 06:55 am, Andreas Kretschmer wrote:
> Adrian Klaver <aklaver@comcast.net> schrieb:
> > On Thursday 23 November 2006 08:45 pm, Tom Lane wrote:
> > > Mulham freshcode <mulhamcode@yahoo.com> writes:
> > > > execute sql_str1 into svc_data_rec ;
> > > >
> > > > svc_data_rec is a RECORD, which is supposed to be dynamic.
> > >
> > > This should work --- in PG 8.1 or later. In older versions you'd have
> > > to fool around with a FOR ... IN EXECUTE ... loop.
> > >
> > > regards, tom lane
> >
> > The documentation for pl/pgsql in 8.1 and higher says different.
> >
> > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PL
> >PGSQL-STATEMENTS-EXECUTING-DYN " SELECT INTO is not currently supported
> > within EXECUTE."
>
> Thats right. The solution with 8.1 or later is:
>
> EXECUTE .... INTO var, and _not_ SELECT INTO var.
>
>
> Andreas
Thanks for the information. Somewhere along the line I missed the distinction.
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-24 15:30 Adrian Klaver <aklaver@comcast.net>
parent: Mulham freshcode <mulhamcode@yahoo.com>
0 siblings, 1 reply; 43+ messages in thread
From: Adrian Klaver @ 2006-11-24 15:30 UTC (permalink / raw)
To: pgsql-sql; +Cc: Mulham freshcode <mulhamcode@yahoo.com>; Tom Lane <tgl@sss.pgh.pa.us>
On Thursday 23 November 2006 10:54 pm, Mulham freshcode wrote:
> Hi Tom,
>
> Thanks for the help. Am using version 8.0 and it seems like RECORD is not
> that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP and it
> does the trick. But am still finding it hard to move forward with this. I
> have the name of table field in a varchar variable that i got from
> information_schema.columns and I have the records variable that stores the
> contains the data from that table. Usually I'd do something like
> data_rec.col_name to extract the data from the record but now I don't know
> the name per se. how can i say something like data_rec[col_name] where
> col_name is a variable that has the actual column name. I found no examples
> in the docs that explain this. Can it be done in version 8.0.1?
>
> I find variable substitution kind of confusing. I mean why is there no way
> of saying explicitly replace this variable with its content before
> executing the statement?
>
> Sorry for the long question,
> and thanks again for the help
>
> Mustafa...
>
> Tom Lane <tgl@sss.pgh.pa.us> wrote: Mulham freshcode writes:
> > execute sql_str1 into svc_data_rec ;
> >
> > svc_data_rec is a RECORD, which is supposed to be dynamic.
>
> This should work --- in PG 8.1 or later. In older versions you'd have
> to fool around with a FOR ... IN EXECUTE ... loop.
>
> regards, tom lane
>
I am trying to sort this out. Are you trying to find the data for a single
field from each table, or for some set of fields?. If you are looking for
data from a single field couldn't you dispense with the RECORD variable and
just build a query of the form SELECT col_name FROM tbl_name. If you want to
go through a set of fields then it would involve some nested loops.
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-24 16:02 Tom Lane <tgl@sss.pgh.pa.us>
parent: Adrian Klaver <aklaver@comcast.net>
1 sibling, 0 replies; 43+ messages in thread
From: Tom Lane @ 2006-11-24 16:02 UTC (permalink / raw)
To: aklaver@comcast.net; +Cc: pgsql-sql@postgresql.org, Mulham freshcode <mulhamcode@yahoo.com>; A. Kretschmer <andreas.kretschmer@schollglas.com>
Adrian Klaver <aklaver@comcast.net> writes:
> On Thursday 23 November 2006 08:45 pm, Tom Lane wrote:
>> This should work --- in PG 8.1 or later.
> The documentation for pl/pgsql in 8.1 and higher says different.
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-...
> " SELECT INTO is not currently supported within EXECUTE."
That says you can't do
EXECUTE 'select ... into foo ...';
which is not the same thing as
EXECUTE 'select ...' into foo;
regards, tom lane
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-25 04:17 Mulham freshcode <mulhamcode@yahoo.com>
parent: Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 43+ messages in thread
From: Mulham freshcode @ 2006-11-25 04:17 UTC (permalink / raw)
To: aklaver@comcast.net; pgsql-sql
Hi Adrian,
I have number of similar tables that have different number of fields (similar in functionality). An in my stored procedure am trying to select a row from one of these tables (that i don't know in advance, hence the use of record) and return the data in the form of a table that has column_name:value pairs. where column name is that from the original table. I have no problem finding the column names but I don't know how to say data[column_name] to get the corresponding value. Is there a way to do it in pgsql?
here is my code so far
sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || sub_id ;
for svc_data_rec in execute sql_str1 loop
end loop;
-- get service_user table's column names
for col_name in select column_name
from information_schema.columns
where table_name~svc_tbl_name loop
raise notice 'Column name:%', col_name.column_name;
raise notice 'Value: %', svc_data_rec[col_name.column_name];
end loop;
Thank you,
Mustafa ...
Adrian Klaver <aklaver@comcast.net> wrote: On Thursday 23 November 2006 10:54 pm, Mulham freshcode wrote:
> Hi Tom,
>
> Thanks for the help. Am using version 8.0 and it seems like RECORD is not
> that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP and it
> does the trick. But am still finding it hard to move forward with this. I
> have the name of table field in a varchar variable that i got from
> information_schema.columns and I have the records variable that stores the
> contains the data from that table. Usually I'd do something like
> data_rec.col_name to extract the data from the record but now I don't know
> the name per se. how can i say something like data_rec[col_name] where
> col_name is a variable that has the actual column name. I found no examples
> in the docs that explain this. Can it be done in version 8.0.1?
>
> I find variable substitution kind of confusing. I mean why is there no way
> of saying explicitly replace this variable with its content before
> executing the statement?
>
> Sorry for the long question,
> and thanks again for the help
>
> Mustafa...
>
> Tom Lane wrote: Mulham freshcode writes:
> > execute sql_str1 into svc_data_rec ;
> >
> > svc_data_rec is a RECORD, which is supposed to be dynamic.
>
> This should work --- in PG 8.1 or later. In older versions you'd have
> to fool around with a FOR ... IN EXECUTE ... loop.
>
> regards, tom lane
>
I am trying to sort this out. Are you trying to find the data for a single
field from each table, or for some set of fields?. If you are looking for
data from a single field couldn't you dispense with the RECORD variable and
just build a query of the form SELECT col_name FROM tbl_name. If you want to
go through a set of fields then it would involve some nested loops.
--
Adrian Klaver
aklaver@comcast.net
---------------------------------
Access over 1 million songs - Yahoo! Music Unlimited.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-26 22:45 Adrian Klaver <aklaver@comcast.net>
parent: Mulham freshcode <mulhamcode@yahoo.com>
0 siblings, 1 reply; 43+ messages in thread
From: Adrian Klaver @ 2006-11-26 22:45 UTC (permalink / raw)
To: pgsql-sql; +Cc: Mulham freshcode <mulhamcode@yahoo.com>
On Friday 24 November 2006 08:17 pm, Mulham freshcode wrote:
> Hi Adrian,
>
> I have number of similar tables that have different number of fields
> (similar in functionality). An in my stored procedure am trying to select a
> row from one of these tables (that i don't know in advance, hence the use
> of record) and return the data in the form of a table that has
> column_name:value pairs. where column name is that from the original table.
> I have no problem finding the column names but I don't know how to say
> data[column_name] to get the corresponding value. Is there a way to do it
> in pgsql?
>
> here is my code so far
>
> sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' ||
> sub_id ;
>
> for svc_data_rec in execute sql_str1 loop
> end loop;
>
> -- get service_user table's column names
> for col_name in select column_name
> from information_schema.columns
> where table_name~svc_tbl_name loop
>
> raise notice 'Column name:%', col_name.column_name;
> raise notice 'Value: %', svc_data_rec[col_name.column_name];
>
> end loop;
>
> Thank you,
>
> Mustafa ...
>
I am afraid I can't make it work either.
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-27 02:23 Adrian Klaver <aklaver@comcast.net>
parent: Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 43+ messages in thread
From: Adrian Klaver @ 2006-11-27 02:23 UTC (permalink / raw)
To: pgsql-sql; +Cc: Mulham freshcode <mulhamcode@yahoo.com>
On Sunday 26 November 2006 02:45 pm, Adrian Klaver wrote:
>
> I am afraid I can't make it work either.
I could not make it work with pl/pgsql, but I did manage to come up with a
solution using pl/pythonu.
The function is as follows-
CREATE OR REPLACE FUNCTION dat_col_py(text) RETURNS text AS
$Body$
tbl_name=args[0]
cols=plpy.prepare("select column_name from information_schema.columns where\
table_name=$1",["text"])
clean=plpy.prepare("delete from dat_col where table_name=$1",["text"])
clean_tbl=plpy.execute(clean,[tbl_name])
ins=plpy.prepare("insert into dat_col values($1,$2,$3),["text","text","text"])
data_rs=plpy.execute('select * from '+tbl_name)
cols_rs=plpy.execute(cols,[tbl_name])
for i in range(len(data_rs)):
for j in range(len(cols_rs)):
plpy.execute(ins,(tbl_name,cols_rs[j]['column_name'],
data_rs[i][cols_rs[j]['column_name'] ]))
$Body$
LANGUAGE plpythonu;
For this to work I created a table dat_col(table_name text,column_name
text,column_data text). The function deletes old data from the table before
it is run, based on table name. Just run as dat_col_py("table name"). This
assumes you have pl/pythonu installed.
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-28 02:31 Mulham freshcode <mulhamcode@yahoo.com>
parent: Adrian Klaver <aklaver@comcast.net>
0 siblings, 1 reply; 43+ messages in thread
From: Mulham freshcode @ 2006-11-28 02:31 UTC (permalink / raw)
To: aklaver@comcast.net; pgsql-sql; +Cc: Mulham freshcode <mulhamcode@yahoo.com>
Hi Adrian,
Thanks very much for your help...it is a pity you can't do this in plpgsql coz i have almost every thing else I need, and I hate to use yet another language. What does it take to add this mechanism to the language? Is any one planning to add it? It can come it handy I bet.
Thanks again,
Mustafa...
Adrian Klaver <aklaver@comcast.net> wrote: On Sunday 26 November 2006 02:45 pm, Adrian Klaver wrote:
>
> I am afraid I can't make it work either.
I could not make it work with pl/pgsql, but I did manage to come up with a
solution using pl/pythonu.
The function is as follows-
CREATE OR REPLACE FUNCTION dat_col_py(text) RETURNS text AS
$Body$
tbl_name=args[0]
cols=plpy.prepare("select column_name from information_schema.columns where\
table_name=$1",["text"])
clean=plpy.prepare("delete from dat_col where table_name=$1",["text"])
clean_tbl=plpy.execute(clean,[tbl_name])
ins=plpy.prepare("insert into dat_col values($1,$2,$3),["text","text","text"])
data_rs=plpy.execute('select * from '+tbl_name)
cols_rs=plpy.execute(cols,[tbl_name])
for i in range(len(data_rs)):
for j in range(len(cols_rs)):
plpy.execute(ins,(tbl_name,cols_rs[j]['column_name'],
data_rs[i][cols_rs[j]['column_name'] ]))
$Body$
LANGUAGE plpythonu;
For this to work I created a table dat_col(table_name text,column_name
text,column_data text). The function deletes old data from the table before
it is run, based on table name. Just run as dat_col_py("table name"). This
assumes you have pl/pythonu installed.
--
Adrian Klaver
aklaver@comcast.net
---------------------------(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
---------------------------------
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: select into
@ 2006-11-28 03:00 Adrian Klaver <aklaver@comcast.net>
parent: Mulham freshcode <mulhamcode@yahoo.com>
0 siblings, 0 replies; 43+ messages in thread
From: Adrian Klaver @ 2006-11-28 03:00 UTC (permalink / raw)
To: pgsql-sql; +Cc: Mulham freshcode <mulhamcode@yahoo.com>
On Monday 27 November 2006 06:31 pm, Mulham freshcode wrote:
> Hi Adrian,
>
> Thanks very much for your help...it is a pity you can't do this in plpgsql
> coz i have almost every thing else I need, and I hate to use yet another
> language. What does it take to add this mechanism to the language? Is any
> one planning to add it? It can come it handy I bet.
>
> Thanks again,
>
> Mustafa...
>
It may not be a shortcoming of the language, but a shortcoming in how well I
understand it. I haven't given up hope I can make it work in plpgsql. The
problem was I was going in circles.
--
Adrian Klaver
aklaver@comcast.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Select into
@ 2008-03-20 09:57 Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
0 siblings, 1 reply; 43+ messages in thread
From: Gavin 'Beau' Baumanis @ 2008-03-20 09:57 UTC (permalink / raw)
To: pgsql-sql
Hi Everyone,
I have asked our DBA at work and h is not too sure either... so I
thought it best to on the list.
Basically, what I am after is a way to copy the contents of one record
into another.
Something like select into; but where the destination record already
exists, as opposed to creating a new record.
Thanks in advance for anything you might come up with.
Warmest regards,
Gavin Baumanis
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 10:49 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
0 siblings, 1 reply; 43+ messages in thread
From: A. Kretschmer @ 2008-03-20 10:49 UTC (permalink / raw)
To: pgsql-sql
am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis folgendes:
> Hi Everyone,
>
> I have asked our DBA at work and h is not too sure either... so I
> thought it best to on the list.
>
> Basically, what I am after is a way to copy the contents of one record
> into another.
> Something like select into; but where the destination record already
> exists, as opposed to creating a new record.
insert into <table> select from <table> where ...
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 10:58 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 43+ messages in thread
From: Gurjeet Singh @ 2008-03-20 10:58 UTC (permalink / raw)
To: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; +Cc: pgsql-sql@postgresql.org, "A. Kretschmer" <andreas.kretschmer@schollglas.com>
On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer <
andreas.kretschmer@schollglas.com> wrote:
> am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis
> folgendes:
> > Hi Everyone,
> >
> > I have asked our DBA at work and h is not too sure either... so I
> > thought it best to on the list.
> >
> > Basically, what I am after is a way to copy the contents of one record
> > into another.
> > Something like select into; but where the destination record already
> > exists, as opposed to creating a new record.
>
> insert into <table> select from <table> where ...
>
>
He specifically asked for
<snip>
where the destination record already
exists, as opposed to creating a new record.
</snip>
I think an UPDATE with joins would be helpful. Though, it may become
lengthy if the tables have too many columns.
Can you post your exact requirement?
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 11:09 Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
parent: Gurjeet Singh <singh.gurjeet@gmail.com>
0 siblings, 4 replies; 43+ messages in thread
From: Gavin 'Beau' Baumanis @ 2008-03-20 11:09 UTC (permalink / raw)
To: Gurjeet Singh <singh.gurjeet@gmail.com>; +Cc: pgsql-sql@postgresql.org, "A. Kretschmer" <andreas.kretschmer@schollglas.com>
HI Gurjeet,
You're right.
But what information do you need to know?
The copy is inside the same table, so I don't understand why it (the
required query ) would require any joins.
Ie. I want to copy the contents of a row (but for the id column - of
course) into a record in the same table.
I am happy enough to give you a table schema, if that's required...
but I just don't see why it would be needed - but of course am happy
to be told something new!
Thanks again.
Gavin Baumanis
On 20/03/2008, at 9:58 PM, Gurjeet Singh wrote:
> On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer <andreas.kretschmer@schollglas.com
> > wrote:
> am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau'
> Baumanis folgendes:
> > Hi Everyone,
> >
> > I have asked our DBA at work and h is not too sure either... so I
> > thought it best to on the list.
> >
> > Basically, what I am after is a way to copy the contents of one
> record
> > into another.
> > Something like select into; but where the destination record already
> > exists, as opposed to creating a new record.
>
> insert into <table> select from <table> where ...
>
>
> He specifically asked for
>
> <snip>
>
> where the destination record already
> exists, as opposed to creating a new record.
>
> </snip>
>
>
> I think an UPDATE with joins would be helpful. Though, it may
> become lengthy if the tables have too many columns.
>
> Can you post your exact requirement?
>
> --
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB http://www.enterprisedb.com
>
> 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
> 18° 32' 57.25"N, 73° 56' 25.42"E - Pune
> 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
>
> http://gurjeet.frihost.net
>
> Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 11:39 Greg Sabino Mullane <greg@turnstep.com>
parent: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
3 siblings, 0 replies; 43+ messages in thread
From: Greg Sabino Mullane @ 2008-03-20 11:39 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
> Ie. I want to copy the contents of a row (but for the id
> column - of course) into a record in the same table.
BEGIN;
CREATE TEMP TABLE tempfoo AS SELECT * FROM foo WHERE id = 123;
UPDATE tempfoo SET id = 456;
DELETE FROM foo WHERE id = 456;
INSERT INTO foo SELECT * FROM tempfoo;
COMMIT;
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200803200737
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkfiTIYACgkQvJuQZxSWSsiCMwCdESkEe8Hc5xHhJ2B3qX3V7EqX
Z2IAoMy65D2OhdUpYVtfEq182PhfsEfZ
=fx5V
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 12:03 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
3 siblings, 0 replies; 43+ messages in thread
From: Gurjeet Singh @ 2008-03-20 12:03 UTC (permalink / raw)
To: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; +Cc: pgsql-sql@postgresql.org, "A. Kretschmer" <andreas.kretschmer@schollglas.com>
On Thu, Mar 20, 2008 at 4:39 PM, Gavin 'Beau' Baumanis <
gavinb@eclinic.com.au> wrote:
> HI Gurjeet,
> You're right.
>
> But what information do you need to know?
>
> The copy is inside the same table, so I don't understand why it (the
> required query ) would require any joins.
>
> Ie. I want to copy the contents of a row (but for the id column - of
> course) into a record in the same table.
>
> I am happy enough to give you a table schema, if that's required... but I
> just don't see why it would be needed - but of course am happy to be told
> something new!
>
Even a small example of what you wanted would have worked.
Anyway, lets see if I can got your problem.
There are two records in your table emp:
id | name | salary
-------------------------
21 | scott | 2000
31 | greg | 3000
So you want to copy all the data from 'scott' row on to 'greg' row, but keep
the id (id obviously being your unique identifier).
UPDATE emp
SET (salary, name) = ( (select salary from emp where id = 21 ),
(select name from emp where id = 21) )
where id = 31;
HTH,
Best regards,
> Thanks again
>
> Gavin Baumanis
>
>
>
> On 20/03/2008, at 9:58 PM, Gurjeet Singh wrote:
>
> On Thu, Mar 20, 2008 at 4:19 PM, A. Kretschmer <
> andreas.kretschmer@schollglas.com> wrote:
>
> > am Thu, dem 20.03.2008, um 20:57:53 +1100 mailte Gavin 'Beau' Baumanis
> > folgendes:
> > > Hi Everyone,
> > >
> > > I have asked our DBA at work and h is not too sure either... so I
> > > thought it best to on the list.
> > >
> > > Basically, what I am after is a way to copy the contents of one record
> > > into another.
> > > Something like select into; but where the destination record already
> > > exists, as opposed to creating a new record.
> >
> > insert into <table> select from <table> where ...
> >
> >
> He specifically asked for
>
> <snip>
>
> where the destination record already
> exists, as opposed to creating a new record.
>
> </snip>
>
>
> I think an UPDATE with joins would be helpful. Though, it may become
> lengthy if the tables have too many columns.
>
> Can you post your exact requirement?
>
> --
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB http://www.enterprisedb.com
>
> 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
> 18° 32' 57.25"N, 73° 56' 25.42"E - Pune
> 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
>
> http://gurjeet.frihost.net
>
> Mail sent from my BlackLaptop device
>
>
>
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 12:05 Craig Ringer <craig@postnewspapers.com.au>
parent: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
3 siblings, 2 replies; 43+ messages in thread
From: Craig Ringer @ 2008-03-20 12:05 UTC (permalink / raw)
To: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; +Cc: Gurjeet Singh <singh.gurjeet@gmail.com>; pgsql-sql@postgresql.org, "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Gavin 'Beau' Baumanis wrote:
>
> The copy is inside the same table, so I don't understand why it (the
> required query ) would require any joins.
Maybe you should use FROM clause in the update that references a
row-valued subquery?
craig=# create table x ( id serial, val integer );
NOTICE: CREATE TABLE will create implicit sequence "x_id_seq" for
serial column "x.id"
CREATE TABLE
craig=# insert into x ( val ) values ( 4 ) , ( 6 ) ;
INSERT 0 2
craig=# select * from x;
id | val
----+-----
1 | 4
2 | 6
(2 rows)
craig=# update x set val = foundrow.val from ( select val from x where
id = 2 ) as foundrow where id = 1 ;
UPDATE 1
craig=# select * from x;
id | val
----+-----
2 | 6
1 | 6
(2 rows)
craig=# insert into x ( val ) select generate_series(0,10000);
INSERT 0 10001
craig=# explain update x set val = foundrow.val from ( select val from x
where id = 4123 ) as foundrow where id = 5912 ;
QUERY PLAN
-------------------------------------------------------------------------
Nested Loop (cost=0.00..16.55 rows=1 width=14)
-> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=10)
Index Cond: (id = 5912)
-> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=4)
Index Cond: (public.x.id = 4123)
(5 rows)
Will that do the job?
--
Craig Ringer
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 12:10 Joe <dev@freedomcircle.net>
parent: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
3 siblings, 2 replies; 43+ messages in thread
From: Joe @ 2008-03-20 12:10 UTC (permalink / raw)
To: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; +Cc: pgsql-sql
Gavin 'Beau' Baumanis wrote:
>
> The copy is inside the same table, so I don't understand why it (the
> required query ) would require any joins.
>
> Ie. I want to copy the contents of a row (but for the id column - of
> course) into a record in the same table.
I think what you want is something like this:
Given (col1 being the id or PK):
col1 | col2 | col3
------+------+---------------
1 | 123 | first record
2 | 456 | second record
3 | 789 | third record
then
update t1 set col2 = t1copy.col2, col3 = t1copy.col3
from t1 as t1copy
where t1.col1 = 1 and t1copy.col1 = 3;
will result in:
col1 | col2 | col3
------+------+---------------
1 | 789 | third record
2 | 456 | second record
3 | 789 | third record
So, it is a join ... of a table with a virtual copy of itself.
Joe
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 12:14 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 0 replies; 43+ messages in thread
From: Gurjeet Singh @ 2008-03-20 12:14 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; pgsql-sql@postgresql.org, "A. Kretschmer" <andreas.kretschmer@schollglas.com>
On Thu, Mar 20, 2008 at 5:35 PM, Craig Ringer <craig@postnewspapers.com.au>
wrote:
>
> craig=# explain update x set val = foundrow.val from ( select val from x
> where id = 4123 ) as foundrow where id = 5912 ;
>
Thats nifty.
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 12:20 Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
parent: Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 1 reply; 43+ messages in thread
From: Gavin 'Beau' Baumanis @ 2008-03-20 12:20 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: Gurjeet Singh <singh.gurjeet@gmail.com>; pgsql-sql@postgresql.org, "A. Kretschmer" <andreas.kretschmer@schollglas.com>
Hi Everyone,
I want to thank everyone for their help / suggestions...
I really appreciate it.
Though I think I have found a winner.
> craig=# update x set val = foundrow.val from ( select val from x
> where id = 2 ) as foundrow where id = 1 ;
> UPDATE 1
>
Very elegant, very clean...
Very nice!
Thanks!!!!
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 12:49 Craig Ringer <craig@postnewspapers.com.au>
parent: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
0 siblings, 0 replies; 43+ messages in thread
From: Craig Ringer @ 2008-03-20 12:49 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
>
>
>> craig=# update x set val = foundrow.val from ( select val from x
>> where id = 2 ) as foundrow where id = 1 ;
>> UPDATE 1
>>
>
Thinking about it, it'd actually be better written as:
UPDATE x SET val = foundrow.val FROM ( SELECT val FROM x AS x2 WHERE
x2.id = 2 ) AS foundrow WHERE id = 1;
... because it's nicer to use a table alias for x within the subquery
and elimate any ambiguity for the reader about which "id" you're
referring to. After all, it's also valid to reference the "id "field of
the "x" outside the subquery within it, like in the following valid but
rather nonsensical query:
UPDATE x SET val = (SELECT id+1) WHERE id = 1;
Using the table alias will not change the query plan at all, it just
makes the reference to "id" within the subquery unambiguous to the reader.
Sorry for the repeat post.
--
Craig Ringer
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 13:13 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: Joe <dev@freedomcircle.net>
1 sibling, 2 replies; 43+ messages in thread
From: Gurjeet Singh @ 2008-03-20 13:13 UTC (permalink / raw)
To: Joe <dev@freedomcircle.net>; +Cc: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; pgsql-sql
On Thu, Mar 20, 2008 at 5:40 PM, Joe <dev@freedomcircle.net> wrote:
> Gavin 'Beau' Baumanis wrote:
> >
> > The copy is inside the same table, so I don't understand why it (the
> > required query ) would require any joins.
> >
> > Ie. I want to copy the contents of a row (but for the id column - of
> > course) into a record in the same table.
>
> I think what you want is something like this:
>
> Given (col1 being the id or PK):
>
> col1 | col2 | col3
> ------+------+---------------
> 1 | 123 | first record
> 2 | 456 | second record
> 3 | 789 | third record
>
> then
>
> update t1 set col2 = t1copy.col2, col3 = t1copy.col3
> from t1 as t1copy
> where t1.col1 = 1 and t1copy.col1 = 3;
>
> will result in:
>
> col1 | col2 | col3
> ------+------+---------------
> 1 | 789 | third record
> 2 | 456 | second record
> 3 | 789 | third record
>
> So, it is a join ... of a table with a virtual copy of itself.
>
Except that it doesn't work... Did you try to execute that query; I am
assuming not.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 13:38 Joe <dev@freedomcircle.net>
parent: Gurjeet Singh <singh.gurjeet@gmail.com>
1 sibling, 1 reply; 43+ messages in thread
From: Joe @ 2008-03-20 13:38 UTC (permalink / raw)
To: Gurjeet Singh <singh.gurjeet@gmail.com>; +Cc: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; pgsql-sql
Gurjeet Singh wrote:
> Except that it doesn't work... Did you try to execute that query; I am
> assuming not.
Of course I did, do you think I create results by editing them into my
email?
The script:
delete from t1;
insert into t1 values (1, 123, 'first record');
insert into t1 values (2, 456, 'second record');
insert into t1 values (3, 789, 'third record');
select * from t1;
update t1 set col2 = t1copy.col2, col3 = t1copy.col3
from t1 as t1copy
where t1.col1 = 1 and t1copy.col1 = 3;
select * from t1;
select version();
The output:
DELETE 3
INSERT 0 1
INSERT 0 1
INSERT 0 1
col1 | col2 | col3
------+------+---------------
1 | 123 | first record
2 | 456 | second record
3 | 789 | third record
(3 rows)
UPDATE 1
col1 | col2 | col3
------+------+---------------
2 | 456 | second record
3 | 789 | third record
1 | 789 | third record
(3 rows)
version
------------------------------------------------------------------------
PostgreSQL 8.1.9 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.3
(1 row)
And BTW, I also tried your UPDATE SET (salary, name) but that only
works on PG 8.2 and above. I don't see why my query would fail in
subsequent releases.
Joe
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 13:44 Craig Ringer <craig@postnewspapers.com.au>
parent: Gurjeet Singh <singh.gurjeet@gmail.com>
1 sibling, 0 replies; 43+ messages in thread
From: Craig Ringer @ 2008-03-20 13:44 UTC (permalink / raw)
To: Gurjeet Singh <singh.gurjeet@gmail.com>; +Cc: Joe <dev@freedomcircle.net>; Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; pgsql-sql
Gurjeet Singh wrote:
> Except that it doesn't work... Did you try to execute that query; I am
> assuming not.
>
It does, or at least a query written to work the same way works fine for
me. Not only that, but at least in the presence of a unique index the
query planner optimises it to the same query plan as the one I proposed.
From my earlier test data:
craig=# update x set val = x2.val from x as x2 where x.id = 1000 and
x2.id = 1024;
UPDATE 1
craig=# select * from x where id in (1000,1024);
id | val
------+------
1024 | 1021
1000 | 1021
(2 rows)
craig=# explain update x set val = x2.val from x as x2 where x.id = 1000
and x2.id = 1024;
QUERY PLAN
---------------------------------------------------------------------------
Nested Loop (cost=0.00..16.55 rows=1 width=14)
-> Index Scan using x_id_idx on x (cost=0.00..8.27 rows=1 width=10)
Index Cond: (id = 1000)
-> Index Scan using x_id_idx on x x2 (cost=0.00..8.27 rows=1 width=4)
Index Cond: (x2.id = 1024)
(5 rows)
The above query actually executes slightly faster, presumably because
the query planner has to do less work to reach the same point than it
does with the subquery-based one I proposed. You should probably use
this one instead of the subquery one.
--
Craig Ringer
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 14:30 Gurjeet Singh <singh.gurjeet@gmail.com>
parent: Joe <dev@freedomcircle.net>
0 siblings, 0 replies; 43+ messages in thread
From: Gurjeet Singh @ 2008-03-20 14:30 UTC (permalink / raw)
To: Joe <dev@freedomcircle.net>; +Cc: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; pgsql-sql
On Thu, Mar 20, 2008 at 7:08 PM, Joe <dev@freedomcircle.net> wrote:
> Gurjeet Singh wrote:
> > Except that it doesn't work... Did you try to execute that query; I am
> > assuming not.
> Of course I did,
My bad... I did not run your query either, and based my assumption on my
previous attempt that had thrown an error. Here's something similar to what
had failed in my previous attemt:
update t1 *as e1* set e1.col2 = t1copy.col2, e1.col3 = t1copy.col3
from t1 as t1copy
where e1.col1 = 1 and t1copy.col1 = 3;
As you can see, I had aliased the main table t1, and hence the error.
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad *
18° 32' 57.25"N, 73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 43+ messages in thread
* Re: Select into
@ 2008-03-20 15:05 Erik Jones <erik@myemma.com>
parent: Joe <dev@freedomcircle.net>
1 sibling, 0 replies; 43+ messages in thread
From: Erik Jones @ 2008-03-20 15:05 UTC (permalink / raw)
To: Joe <dev@freedomcircle.net>; +Cc: Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>; pgsql-sql
On Mar 20, 2008, at 7:10 AM, Joe wrote:
> Gavin 'Beau' Baumanis wrote:
>>
>> The copy is inside the same table, so I don't understand why it
>> (the required query ) would require any joins.
>>
>> Ie. I want to copy the contents of a row (but for the id column -
>> of course) into a record in the same table.
>
> I think what you want is something like this:
>
> Given (col1 being the id or PK):
>
> col1 | col2 | col3
> ------+------+---------------
> 1 | 123 | first record
> 2 | 456 | second record
> 3 | 789 | third record
>
> then
>
> update t1 set col2 = t1copy.col2, col3 = t1copy.col3
> from t1 as t1copy
> where t1.col1 = 1 and t1copy.col1 = 3;
>
> will result in:
>
> col1 | col2 | col3
> ------+------+---------------
> 1 | 789 | third record
> 2 | 456 | second record
> 3 | 789 | third record
>
> So, it is a join ... of a table with a virtual copy of itself.
Note that in 8.2.x and above you can write that as:
update t1
set (col2, col3) = (t1copy.col2, t1copy.col3)
from t1 as t1copy
where t1.col =1 and t1copy.col1=3;
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
^ permalink raw reply [nested|flat] 43+ messages in thread
end of thread, other threads:[~2008-03-20 15:05 UTC | newest]
Thread overview: 43+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-06 03:01 Select into Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2001-12-06 04:10 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-12-06 04:18 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-06 21:43 ` Jean-Michel Chabanne <jeanmichel.chabanne@free.fr>
2004-01-12 01:09 Select into beyaRecords - The home Urban music <uzo@beya-records.com>
2004-01-12 09:45 ` Richard Huxton <dev@archonet.com>
2006-11-22 08:28 select into Mulham freshcode <mulhamcode@yahoo.com>
2006-11-22 15:47 ` Re: select into A. Kretschmer <andreas.kretschmer@schollglas.com>
2006-11-23 17:19 ` Re: select into Mulham freshcode <mulhamcode@yahoo.com>
2006-11-23 18:35 ` Re: select into Adrian Klaver <aklaver@comcast.net>
2006-11-24 04:21 ` Re: select into Mulham freshcode <mulhamcode@yahoo.com>
2006-11-24 04:45 ` Re: select into Tom Lane <tgl@sss.pgh.pa.us>
2006-11-24 06:54 ` Re: select into Mulham freshcode <mulhamcode@yahoo.com>
2006-11-24 15:30 ` Re: select into Adrian Klaver <aklaver@comcast.net>
2006-11-25 04:17 ` Re: select into Mulham freshcode <mulhamcode@yahoo.com>
2006-11-26 22:45 ` Re: select into Adrian Klaver <aklaver@comcast.net>
2006-11-27 02:23 ` Re: select into Adrian Klaver <aklaver@comcast.net>
2006-11-28 02:31 ` Re: select into Mulham freshcode <mulhamcode@yahoo.com>
2006-11-28 03:00 ` Re: select into Adrian Klaver <aklaver@comcast.net>
2006-11-24 14:45 ` Re: select into Adrian Klaver <aklaver@comcast.net>
2006-11-24 14:55 ` Re: select into Andreas Kretschmer <akretschmer@spamfence.net>
2006-11-24 15:26 ` Re: select into Adrian Klaver <aklaver@comcast.net>
2006-11-24 16:02 ` Re: select into Tom Lane <tgl@sss.pgh.pa.us>
2006-11-22 15:55 ` Re: select into Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-22 16:07 ` Re: select into Andreas Kretschmer <akretschmer@spamfence.net>
2006-11-22 17:26 ` Re: select into Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-22 16:14 ` Re: select into Tom Lane <tgl@sss.pgh.pa.us>
2008-03-20 09:57 Select into Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
2008-03-20 10:49 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-03-20 10:58 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2008-03-20 11:09 ` Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
2008-03-20 11:39 ` Greg Sabino Mullane <greg@turnstep.com>
2008-03-20 12:03 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2008-03-20 12:05 ` Craig Ringer <craig@postnewspapers.com.au>
2008-03-20 12:14 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2008-03-20 12:20 ` Gavin 'Beau' Baumanis <gavinb@eclinic.com.au>
2008-03-20 12:49 ` Craig Ringer <craig@postnewspapers.com.au>
2008-03-20 12:10 ` Joe <dev@freedomcircle.net>
2008-03-20 13:13 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2008-03-20 13:38 ` Joe <dev@freedomcircle.net>
2008-03-20 14:30 ` Gurjeet Singh <singh.gurjeet@gmail.com>
2008-03-20 13:44 ` Craig Ringer <craig@postnewspapers.com.au>
2008-03-20 15:05 ` Erik Jones <erik@myemma.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox