Обсуждение: pg newbie stumped on sequences!
Hi. I'm from a MSSQL/mySQL background and am just poking about in PG.
I'm using phpPgAdmin 2.3 and PostgreSQL 7.1.3.
I'm a bit stumped why I'm getting an error when trying to create new
records.
I tried the following:
CREATE TABLE "test" (
"ID" SERIAL,
"Subject" varchar (255) ,
"Body" varchar (2000) ,
"DTS" timestamp NOT NULL,
"Archive" int2 NOT NULL );
and then:
INSERT INTO "test" ("ID", "Subject", "Body", "DTS", "Archive") VALUES
(NEXTVAL('""test_ID_seq""'::text), 'test', 'this is a test', '2003-05-20
16:30:42', '0')
and i got:
PostgreSQL said: ERROR: pg_aclcheck: class ""test_ID_seq"" not found
Your query:
INSERT INTO "test" ("ID", "Subject", "Body", "DTS", "Archive") VALUES
(NEXTVAL('""test_ID_seq""'::text), 'test', 'this is a test', '2003-05-20
16:30:42', '0')
This leads me to believe i have a sequence missing, but:
test_ID_seq 1 1 2147483647 1 1 1 f f
it's there!
It's been suggested that ('""test_ID_seq""'::text) might be the issue,
irregular quoting? Odd, as this is what phpPgAdmin spewed out.
any ideas?
.b(affled)
--
ben joyce
Since sequences are inserted by default in serial columns, just change
your insert to this:
INSERT INTO "test" ("Subject", "Body", "DTS", "Archive") VALUES
('test', 'this is a test', '2003-05-20 16:30:42', '0')
It looks like phppgadmin is trying to quote something that is already
quoted. If the insert was without the doubled double quotes, it would
also work.
But it's easier to just let the database do it.
Oh, and you should upgrade to 7.3.x, there are some known issues with 7.1
and before. The minimum version of postgresql you should be running would
likely be 7.2.4. 7.3.2 the best choice right now though, in my not so
humble opinion.
I.e. let the database insert it for you.
On Tue, 20 May 2003, Ben Joyce wrote:
> Hi. I'm from a MSSQL/mySQL background and am just poking about in PG.
> I'm using phpPgAdmin 2.3 and PostgreSQL 7.1.3.
>
> I'm a bit stumped why I'm getting an error when trying to create new
> records.
>
> I tried the following:
>
> CREATE TABLE "test" (
> "ID" SERIAL,
> "Subject" varchar (255) ,
> "Body" varchar (2000) ,
> "DTS" timestamp NOT NULL,
> "Archive" int2 NOT NULL );
>
> and then:
>
> INSERT INTO "test" ("ID", "Subject", "Body", "DTS", "Archive") VALUES
> (NEXTVAL('""test_ID_seq""'::text), 'test', 'this is a test', '2003-05-20
> 16:30:42', '0')
>
> and i got:
>
> PostgreSQL said: ERROR: pg_aclcheck: class ""test_ID_seq"" not found
> Your query:
> INSERT INTO "test" ("ID", "Subject", "Body", "DTS", "Archive") VALUES
> (NEXTVAL('""test_ID_seq""'::text), 'test', 'this is a test', '2003-05-20
> 16:30:42', '0')
>
> This leads me to believe i have a sequence missing, but:
>
> test_ID_seq 1 1 2147483647 1 1 1 f f
>
> it's there!
>
> It's been suggested that ('""test_ID_seq""'::text) might be the issue,
> irregular quoting? Odd, as this is what phpPgAdmin spewed out.
>
> any ideas?
>
> .b(affled)
>
> --
> ben joyce
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
"Ben Joyce" <ben.joyce-pgsql@babelfish.co.uk> writes:
> Hi. I'm from a MSSQL/mySQL background and am just poking about in PG.
> I'm using phpPgAdmin 2.3 and PostgreSQL 7.1.3.
>
> INSERT INTO "test" ("ID", "Subject", "Body", "DTS", "Archive") VALUES
> (NEXTVAL('""test_ID_seq""'::text), 'test', 'this is a test', '2003-05-20
> 16:30:42', '0')
This is wrong--the "nextval" call should look like:
NEXTVAL('"test_ID_seq"')
The '::text' cast is optional but shouldn't hurt anything.
Looks like phpPgAdmin is inserting too many quotes.
FYI: the double-quotes-within-single-quotes thing is to tell NEXTVAL
not to fold the sequence name to lower case (which it would do if you
left out the double quotes). Yes, it's a little odd. ;)
'
Hmm, thanks for the suggestion Scott (et al)
I think it was a mix of errors, one being an old Sequqnce left over from
an earlier test with a similar name. Dropping, creating, and then using
the INSERT described below appears to work.
Thanks ppl, will consider upgrading my PG and phpPgAdmin software.
Ta.
.ben
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: 20 May 2003 17:26
> To: Ben Joyce
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] pg newbie stumped on sequences!
>
>
> Since sequences are inserted by default in serial columns,
> just change
> your insert to this:
>
> INSERT INTO "test" ("Subject", "Body", "DTS", "Archive")
> VALUES ('test', 'this is a test', '2003-05-20 16:30:42', '0')
>
> It looks like phppgadmin is trying to quote something that is already
> quoted. If the insert was without the doubled double quotes,
> it would
> also work.
>
> But it's easier to just let the database do it.
>
> Oh, and you should upgrade to 7.3.x, there are some known
> issues with 7.1
> and before. The minimum version of postgresql you should be
> running would
> likely be 7.2.4. 7.3.2 the best choice right now though, in
> my not so
> humble opinion.
>
> I.e. let the database insert it for you.
> On Tue, 20 May 2003, Ben Joyce wrote:
>
> > Hi. I'm from a MSSQL/mySQL background and am just poking
> about in PG.
> > I'm using phpPgAdmin 2.3 and PostgreSQL 7.1.3.
> >
> > I'm a bit stumped why I'm getting an error when trying to
> create new
> > records.
> >
> > I tried the following:
> >
> > CREATE TABLE "test" (
> > "ID" SERIAL,
> > "Subject" varchar (255) ,
> > "Body" varchar (2000) ,
> > "DTS" timestamp NOT NULL,
> > "Archive" int2 NOT NULL );
> >
> > and then:
> >
> > INSERT INTO "test" ("ID", "Subject", "Body", "DTS",
> "Archive") VALUES
> > (NEXTVAL('""test_ID_seq""'::text), 'test', 'this is a test',
> > '2003-05-20 16:30:42', '0')
> >
> > and i got:
> >
> > PostgreSQL said: ERROR: pg_aclcheck: class ""test_ID_seq"" not found
> > Your query:
> > INSERT INTO "test" ("ID", "Subject", "Body", "DTS",
> "Archive") VALUES
> > (NEXTVAL('""test_ID_seq""'::text), 'test', 'this is a
> test', '2003-05-20
> > 16:30:42', '0')
> >
> > This leads me to believe i have a sequence missing, but:
> >
> > test_ID_seq 1 1 2147483647 1 1 1 f f
> >
> > it's there!
> >
> > It's been suggested that ('""test_ID_seq""'::text) might be
> the issue,
> > irregular quoting? Odd, as this is what phpPgAdmin spewed out.
> >
> > any ideas?
> >
> > .b(affled)
> >
> > --
> > ben joyce
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> >
>
"Ben Joyce" <ben.joyce-pgsql@babelfish.co.uk> writes:
> It's been suggested that ('""test_ID_seq""'::text) might be the issue,
> irregular quoting? Odd, as this is what phpPgAdmin spewed out.
Should be nextval('"test_ID_seq"') ... the ::text is unnecessary, though
harmless. If phpPgAdmin doubled the double quotes then it's wrong.
7.1.3 is a bit long in the tooth as far as Postgres versions go, and it
may be that you are overdue for a phpPgAdmin update too ...
regards, tom lane