sequence... my nightmare :-(
Hi,
i have a schema called : test_survey
i have within this schema this table:
CREATE TABLE test_survey.accounts
(
"ID" bigint NOT NULL DEFAULT
nextval('test_survey."accounts_ID_seq"'::regclass),
"login" character varying(300) NOT NULL,
pwd character varying(100) NOT NULL,
creation_date timestamp without time zone NOT NULL,
CONSTRAINT accounts_pkey PRIMARY KEY ("ID")
)
WITH (OIDS=FALSE);
ALTER TABLE test_survey.accounts OWNER TO mysurvey;
and this sequence:
CREATE SEQUENCE test_survey."accounts_ID_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
ALTER TABLE test_survey."accounts_ID_seq" OWNER TO mysurvey;
when i write the following query i get the error :ERROR: relation
"accounts_id_seq" does not exist
SET search_path = test_survey;
insert into accounts values
(nextval('accounts_ID_seq'),'test@test.com','ab4ef51934f2d3f02f1a','11/19/2007
15:46:09');
why ?
--
Alain
------------------------------------
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
when i write the following query i get the error :ERROR:
relation
"accounts_id_seq" does not exist
SET search_path = test_survey;
insert into accounts values
(nextval('accounts_ID_seq'),'test@test.com','ab4ef51934f2d3f02f1a','11/19/2007
15:46:09');why ?
It's lowercasing the relation name, note "accounts_id_seq" not "accounts_ID_seq". Try double quoting it.
if i double-quote it, postgre tells me that the column accounts_id_seq does
not exist.
On Sat, Sep 27, 2008 at 5:59 PM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
when i write the following query i get the error :ERROR:
relation
"accounts_id_seq" does not exist
SET search_path = test_survey;
insert into accounts values
(nextval('accounts_ID_seq'),'test@test.com','ab4ef51934f2d3f02f1a','11/19/2007
15:46:09');
why ?
It's lowercasing the relation name, note "accounts_id_seq" not
"accounts_ID_seq". Try double quoting it.
--
Alain
------------------------------------
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
you'll need to reference the column with the correct camel_case as
accounts_ID_seq != accounts_id_seq
hint: next time you create a table keep the table and column names lowercase
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.
Date: Sat, 27 Sep 2008 18:21:26 +0200
From: raf.news@gmail.com
To: glynastill@yahoo.co.uk
Subject: Re: [GENERAL] sequence... my nightmare :-(
CC: pgsql-general@postgresql.org
if i double-quote it, postgre tells me that the column accounts_id_seq does not exist.
On Sat, Sep 27, 2008 at 5:59 PM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
when i write the following query i get the error :ERROR:
relation
"accounts_id_seq" does not exist
SET search_path = test_survey;
insert into accounts values
(nextval('accounts_ID_seq'),'test@test.com','ab4ef51934f2d3f02f1a','11/19/2007
15:46:09');
why ?
It's lowercasing the relation name, note "accounts_id_seq" not "accounts_ID_seq". Try double quoting it.
--
Alain
------------------------------------
Windows XP SP3
PostgreSQL 8.2.4 / MS SQL server 2005
Apache 2.2.4
PHP 5.2.4
C# 2005-2008
_________________________________________________________________
Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie.
http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entry?ocid=TXT_TAGLM_WL_domore_092008
On Sat, Sep 27, 2008 at 10:21 AM, Alain Roger <raf.news@gmail.com> wrote:
if i double-quote it, postgre tells me that the column accounts_id_seq does
not exist.
You almost got it. You need to doublequote to tell nextval with
capitalization correctly, then single quote that so the query planner
doesn't say "oh look! An identifier!
create sequence "Abc";
CREATE SEQUENCE
select nextval('Abc');
ERROR: relation "abc" does not exist
select nextval('"Abc"');
nextval
---------
1
(1 row)