Thread: Ok, what am I doing wrong here?

Ok, what am I doing wrong here?

From

Karl Denninger

Date:

17 February 2004, 15:03:18

I want to insert values from one table into another, and add some "default"
values (that are not defaults on the table different reasons - that is, this
is maintenance function and in normal operation there would be "real" values
there - and null is valid)

So, I want to do, for example, the following:

insert into table (id, time, type) values (select id, now(), '1' from secondtable);

Postgres's command line pukes on this, complaining that "select" is invalid 
inside the values part of the definition.

SQL's language specification says otherwise, as does "\h insert" from the
command line.

The query stand-alone returns a table with values that are valid for the 
table I wish to insert into.

Where's my brain-fade on this?

--
-- 
Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist
http://www.denninger.net    Tired of spam at your company?  LOOK HERE!
http://childrens-justice.org    Working for family and children's rights
http://diversunion.org        LOG IN AND GET YOUR TANK STICKERS TODAY!
http://scubaforum.org        Come talk about DIVING!

Re: Ok, what am I doing wrong here?

From

"Marc G. Fournier"

Date:

17 February 2004, 15:25:13

On Tue, 17 Feb 2004, Karl Denninger wrote:

> I want to insert values from one table into another, and add some "default"
> values (that are not defaults on the table different reasons - that is, this
> is maintenance function and in normal operation there would be "real" values
> there - and null is valid)
>
> So, I want to do, for example, the following:
>
> insert into table (id, time, type) values (select id, now(), '1' from secondtable);
>
> Postgres's command line pukes on this, complaining that "select" is invalid
> inside the values part of the definition.
>
> SQL's language specification says otherwise, as does "\h insert" from the
> command line.
>
> The query stand-alone returns a table with values that are valid for the
> table I wish to insert into.
>
> Where's my brain-fade on this?

INSERT INTO table (id, time, type) SELECT id, now(), '1' FROM secondtable;

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Re: Ok, what am I doing wrong here?

From

Stephan Szabo

Date:

17 February 2004, 15:30:51

sszabo@bigpanda.com

On Tue, 17 Feb 2004, Karl Denninger wrote:

> I want to insert values from one table into another, and add some "default"
> values (that are not defaults on the table different reasons - that is, this
> is maintenance function and in normal operation there would be "real" values
> there - and null is valid)
>
> So, I want to do, for example, the following:
>
> insert into table (id, time, type) values (select id, now(), '1' from secondtable);
>
> Postgres's command line pukes on this, complaining that "select" is invalid
> inside the values part of the definition.
>
> SQL's language specification says otherwise, as does "\h insert" from the
> command line.

I think what you want is
insert into table (id, time, type) select id, now(), '1' from secondtable;

The choices allowed right now are default values, something that is
basically a row constructor with values [*] or a query.

[*] - The full spec allows a list of row constructors but we don't
currently.

Re: Ok, what am I doing wrong here?

From

Tomasz Myrta

Date:

17 February 2004, 15:32:01

Dnia 2004-02-17 17:02, Użytkownik Karl Denninger napisał:
> I want to insert values from one table into another, and add some "default"
> values (that are not defaults on the table different reasons - that is, this
> is maintenance function and in normal operation there would be "real" values
> there - and null is valid)
> 
> So, I want to do, for example, the following:
> 
> insert into table (id, time, type) values (select id, now(), '1' from secondtable);

Documentation says:
INSERT INTO table [ ( column [, ...] ) ]    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | 
SELECT query }

insert into table (id, time, type)
select id, now(), '1' from secondtable;

Regards,
Tomasz Myrta

Re: Ok, what am I doing wrong here?

From

Karl Denninger

Date:

17 February 2004, 15:58:10

Thanks...

--
-- 
Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist
http://www.denninger.net    Tired of spam at your company?  LOOK HERE!
http://childrens-justice.org    Working for family and children's rights
http://diversunion.org        LOG IN AND GET YOUR TANK STICKERS TODAY!
http://scubaforum.org        Come talk about DIVING!

On Tue, Feb 17, 2004 at 08:29:06AM -0800, Stephan Szabo wrote:
> sszabo@bigpanda.com
> 
> On Tue, 17 Feb 2004, Karl Denninger wrote:
> 
> > I want to insert values from one table into another, and add some "default"
> > values (that are not defaults on the table different reasons - that is, this
> > is maintenance function and in normal operation there would be "real" values
> > there - and null is valid)
> >
> > So, I want to do, for example, the following:
> >
> > insert into table (id, time, type) values (select id, now(), '1' from secondtable);
> >
> > Postgres's command line pukes on this, complaining that "select" is invalid
> > inside the values part of the definition.
> >
> > SQL's language specification says otherwise, as does "\h insert" from the
> > command line.
> 
> I think what you want is
> insert into table (id, time, type) select id, now(), '1' from secondtable;
> 
> The choices allowed right now are default values, something that is
> basically a row constructor with values [*] or a query.
> 
> [*] - The full spec allows a list of row constructors but we don't
> currently.
> 
> 
> 
> %SPAMBLOCK-SYS: Matched [@postgresql.org], message ok