Postgres Pro
Downloads
Home   >   mailing lists

Thread: sequence... my nightmare :-(

sequence... my nightmare :-(

From
"Alain Roger"
Date:
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

Re: sequence... my nightmare :-(

From
Glyn Astill
Date:
>
> 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.

Re: sequence... my nightmare :-(

From
"Alain Roger"
Date:
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

Re: sequence... my nightmare :-(

From
Martin Gainty
Date:
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. Learn Now

Re: sequence... my nightmare :-(

From
"Scott Marlowe"
Date:
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)