Re: [GENERAL] Error in insert statement
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in insert statement
> From: bricklen [mailto:brick...@gmail.com]
> Sent: Thursday, July 16, 2009 4:05 PM
> To: Relyea, Mike
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Error in insert statement
>
> Try dropping the word "VALUES".
>
> eg.
> INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID",
> "TestTypeID","ZoneID", "PaperID", "AttributeID", "Spec")
> SELECT "CartridgeTypeID", "ColorID", "TestTypeID","ZoneID",
> "PaperID", "AttributeID","Spec" from "tblTempSpecs";
>
I knew there had to be some bone-headed mistake I was making. That
worked of course. Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in insert statement
Try dropping the word "VALUES".
eg.
INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID",
"TestTypeID","ZoneID", "PaperID", "AttributeID", "Spec")
SELECT "CartridgeTypeID", "ColorID", "TestTypeID","ZoneID", "PaperID",
"AttributeID","Spec" from "tblTempSpecs";
On Thu, Jul 16, 2009 at 12:40 PM, Relyea, Mike wrote:
> 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.
>
> I'm trying to execute
>
> DROP TABLE IF EXISTS "tblTempSpecs";
> CREATE TEMP TABLE "tblTempSpecs" AS SELECT "tblSpecs"."CartridgeTypeID",
> "tblSpecs"."ColorID", "tblTestTypes"."TestTypeID", "tblZones"."ZoneID",
> "tblSpecs"."PaperID",
> "tblSpecs"."AttributeID", "tblSpecs"."Spec"
> FROM "tblSpecs", "tblZones", "tblTestTypes"
> WHERE "tblSpecs"."ZoneID" IS NULL AND "tblTestTypes"."TestTypeID" IN
> (SELECT DISTINCT "TestTypeID" FROM "tblCartridgePQSetIntervals" WHERE
> "CartridgeTypeID" = 74);
>
> INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID", "TestTypeID",
> "ZoneID", "PaperID", "AttributeID", "Spec") VALUES SELECT * FROM
> "tblTempSpecs";
>
> To insert data into
>
> CREATE TABLE "tblSpecs"
> (
> "SpecID" integer NOT NULL DEFAULT
> nextval('"sequence_SpecID"'::regclass),
> "CartridgeTypeID" integer NOT NULL,
> "ColorID" integer NOT NULL,
> "TestTypeID" integer,
> "ZoneID" integer,
> "PaperID" integer,
> "PrintCopyID" integer,
> "AttributeID" integer NOT NULL,
> "Spec" numeric NOT NULL,
> CONSTRAINT "tblSpecs_pkey" PRIMARY KEY ("SpecID"),
> CONSTRAINT "tblSpecs_AttributeID_fkey" FOREIGN KEY ("AttributeID")
> REFERENCES "tblAttributes" ("AttributeID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_CartridgeTypeID_fkey" FOREIGN KEY
> ("CartridgeTypeID")
> REFERENCES "tblCartridgeTypes" ("CartridgeTypeID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_ColorID_fkey" FOREIGN KEY ("ColorID")
> REFERENCES "tblColors" ("ColorID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_PaperID_fkey" FOREIGN KEY ("PaperID")
> REFERENCES "tblPaperTypes" ("PaperID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_TestTypeID_fkey" FOREIGN KEY ("TestTypeID")
> REFERENCES "tblTestTypes" ("TestTypeID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_ZoneID_fkey" FOREIGN KEY ("ZoneID")
> REFERENCES "tblZones" ("ZoneID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (
> OIDS=FALSE
> );
>
> What am I doing wrong?
>
> Mike
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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";
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.
Btw: you should create your tables without using double quotes, thus you can get
rid of them when doing normal DML.
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.
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error in insert statement
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.
I'm trying to execute
DROP TABLE IF EXISTS "tblTempSpecs";
CREATE TEMP TABLE "tblTempSpecs" AS SELECT "tblSpecs"."CartridgeTypeID",
"tblSpecs"."ColorID", "tblTestTypes"."TestTypeID", "tblZones"."ZoneID",
"tblSpecs"."PaperID",
"tblSpecs"."AttributeID", "tblSpecs"."Spec"
FROM "tblSpecs", "tblZones", "tblTestTypes"
WHERE "tblSpecs"."ZoneID" IS NULL AND "tblTestTypes"."TestTypeID" IN
(SELECT DISTINCT "TestTypeID" FROM "tblCartridgePQSetIntervals" WHERE
"CartridgeTypeID" = 74);
INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID", "TestTypeID",
"ZoneID", "PaperID", "AttributeID", "Spec") VALUES SELECT * FROM
"tblTempSpecs";
To insert data into
CREATE TABLE "tblSpecs"
(
"SpecID" integer NOT NULL DEFAULT
nextval('"sequence_SpecID"'::regclass),
"CartridgeTypeID" integer NOT NULL,
"ColorID" integer NOT NULL,
"TestTypeID" integer,
"ZoneID" integer,
"PaperID" integer,
"PrintCopyID" integer,
"AttributeID" integer NOT NULL,
"Spec" numeric NOT NULL,
CONSTRAINT "tblSpecs_pkey" PRIMARY KEY ("SpecID"),
CONSTRAINT "tblSpecs_AttributeID_fkey" FOREIGN KEY ("AttributeID")
REFERENCES "tblAttributes" ("AttributeID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_CartridgeTypeID_fkey" FOREIGN KEY
("CartridgeTypeID")
REFERENCES "tblCartridgeTypes" ("CartridgeTypeID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_ColorID_fkey" FOREIGN KEY ("ColorID")
REFERENCES "tblColors" ("ColorID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_PaperID_fkey" FOREIGN KEY ("PaperID")
REFERENCES "tblPaperTypes" ("PaperID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_TestTypeID_fkey" FOREIGN KEY ("TestTypeID")
REFERENCES "tblTestTypes" ("TestTypeID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_ZoneID_fkey" FOREIGN KEY ("ZoneID")
REFERENCES "tblZones" ("ZoneID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
What am I doing wrong?
Mike
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
