Thread: Insert using a subselect?
Does PostgreSQL supports doing an insert with a subselect as it's value? Something like: insert into hearn_dates (date) values ((select distinct merge_date from hearn)); The error I am getting is: ERROR: More than one tuple returned by a subselect used as an expression.
On Mon, 10 Sep 2001, Francisco Reyes wrote: > Does PostgreSQL supports doing an insert with a subselect as it's value? > Something like: > > insert into hearn_dates (date) > values ((select distinct merge_date from hearn)); > > The error I am getting is: > ERROR: More than one tuple returned by a subselect used as an expression. > Presumably you slect distinct is returing more than one value/tuple. Run it by hand and see what it returns. Jason
On Tue, 11 Sep 2001, Jason Tan wrote: > > On Mon, 10 Sep 2001, Francisco Reyes wrote: > > > Does PostgreSQL supports doing an insert with a subselect as it's value? > > Something like: > > insert into hearn_dates (date) > > values ((select distinct merge_date from hearn)); > > The error I am getting is: > > ERROR: More than one tuple returned by a subselect used as an expression. > > Presumably you slect distinct is returing more than one value/tuple. > > Run it by hand and see what it returns. drf=# select distinct merge_date from hearn; merge_date ------------ 2000-11-17 2001-07-10 2001-09-07 (3 rows) 3 rows on 1 column.
You need to make it return just one row. It doenst knwo which value to use in the insert. Jason On Tue, 11 Sep 2001, Francisco Reyes wrote: > On Tue, 11 Sep 2001, Jason Tan wrote: > > > > > On Mon, 10 Sep 2001, Francisco Reyes wrote: > > > > > Does PostgreSQL supports doing an insert with a subselect as it's value? > > > Something like: > > > insert into hearn_dates (date) > > > values ((select distinct merge_date from hearn)); > > > The error I am getting is: > > > ERROR: More than one tuple returned by a subselect used as an expression. > > > > Presumably you slect distinct is returing more than one value/tuple. > > > > Run it by hand and see what it returns. > > drf=# select distinct merge_date from hearn; > merge_date > ------------ > 2000-11-17 > 2001-07-10 > 2001-09-07 > (3 rows) > > 3 rows on 1 column. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- ------------------------------------------------------------------------------ Jason Tan jason@rebel.net.au "Democracy is two wolves and a lamb voting on what to have for lunch. Liberty is a well-armed lamb contesting the vote." ~Benjamin Franklin, 1759 ------------------------------------------------------------------------------
On Tue, 11 Sep 2001, Jason Tan wrote: > > > You need to make it return just one row. > It doenst knwo which value to use in the insert. > Jason so it is not possible to populate an insert from a subselect if there is more than one row/column?
----- Original Message ----- From: "Francisco Reyes" <lists@natserv.com> Subject: Re: [NOVICE] Insert using a subselect? > > so it is not possible to populate an insert from a subselect if there is > more than one row/column? Of course, it is. But you should just either use he keyword "values" or use a select. The right statement is: insert into hearn_dates (date) select distinct merge_date from hearn; For further details have a look at the iDoc's http://www.postgresql.org/idocs/index.php?sql-insert.html Andre
On Thu, 13 Sep 2001, Francisco Reyes wrote:
> On Tue, 11 Sep 2001, Jason Tan wrote:
>
> >
> >
> > You need to make it return just one row.
> > It doenst knwo which value to use in the insert.
> > Jason
>
> so it is not possible to populate an insert from a subselect if there is
> more than one row/column?
>
I dont think so.
I _think_ that an insert can only ever insert a single row.
I _think_ you ahve to do a separate inssert for each row to be inserted.
But i ma not a postgres experet or an expert on sql and its sematnics,
just auser.
My thoughts above are from my won experience, not any absolute knowledge.
--
------------------------------------------------------------------------------
Jason Tan jason@rebel.net.au
"Democracy is two wolves and a lamb voting on what to have for lunch.
Liberty is a well-armed lamb contesting the vote."
~Benjamin Franklin, 1759
------------------------------------------------------------------------------
yes you can. i do it. it works. rjsjr > -----Original Message----- > From: pgsql-novice-owner@postgresql.org > [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Jason Tan > Sent: Thursday, September 13, 2001 9:39 PM > To: yFrancisco Reyes > Cc: Pgsql Novice > Subject: Re: [NOVICE] Insert using a subselect? > > > On Thu, 13 Sep 2001, Francisco Reyes wrote: > > > On Tue, 11 Sep 2001, Jason Tan wrote: > > > > > > > > > > > You need to make it return just one row. > > > It doenst knwo which value to use in the insert. > > > Jason > > > > so it is not possible to populate an insert from a > subselect if there is > > more than one row/column? > > > > I dont think so. > I _think_ that an insert can only ever insert a single row. > > I _think_ you ahve to do a separate inssert for each row to > be inserted. > > But i ma not a postgres experet or an expert on sql and its > sematnics, > just auser. > My thoughts above are from my won experience, not any > absolute knowledge. > > > -- > ------------------------------------------------------------ > ------------------ > Jason Tan > jason@rebel.net.au > "Democracy is two wolves and a lamb voting on what to > have for lunch. > Liberty is a well-armed lamb contesting the vote." > ~Benjamin Franklin, 1759 > ------------------------------------------------------------ > ------------------ > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >