Re: Error in insert statement

> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
> Thomas Kellerer
> Sent: Thursday, July 16, 2009 4:05 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Error in insert statement
>
> Relyea, Mike wrote on 16.07.2009 21:40:
> > I need help understanding what I'm doing wrong with an
> insert statement.
> > I'm running 8.3.7 on Windows and the DB is complaining about the
> > select statement in my insert statement.
>
> When using a SELECT for an INSERT the values part is not
> needed in fact its incorrect syntax.
>
> You need to run:
>
> INSERT INTO "tblSpecs"
> ("CartridgeTypeID", "ColorID", "TestTypeID", "ZoneID",
> "PaperID", "AttributeID", "Spec") SELECT * FROM "tblTempSpecs";

Got it.  Thanks.

> But: using a "SELECT *" here is calling for trouble. You are
> relying on an implicit an not guaranteed order and number of columns.
> Listing all the needed columns in the SELECT statement is
> much more robust.

Point taken.  Since this is just to load the table initially to do my
development work no harm done.  If it were a production environment I'll
make sure to list all of the columns specifically.

>
> Btw: you should create your tables without using double
> quotes, thus you can get rid of them when doing normal DML.

I would if I could.  I'm interfacing with a database I did not create.
Instead of having some needing quotes and some not, I find it easier to
just quote everything.

> Out of curiosity: why do you prefix the table with "tbl"?
> Don't you know it's a table? Sounds like a strange naming
> scheme to me.

It's an old habit that I picked up from MS Access.  There were times
that when going through some of the wizards in Access it wasn't clear if
the choice you were selecting was a table or a query.  So I learned
there to prefix all of my tables with "tbl" and all of my queries with
"qry".  Old habits die hard.

Mike