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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-----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)