PostgreSQL No Longer Handles Mixed Case Sequences
Hello,
I have just updated to PostgreSQL 7.2.2. I am utilising tables with
mixed case table and field names. I can now no longer insert data into
these tables as shown by the following psql interchange:
sharetrack=> \d "T_Joint"
Table "T_Joint"
Column | Type | Modifiers
---------+-----------------------+--------------------------------------------------
id | integer | not null default nextval('T_Joint_id_seq'::text)
epic | character(4) |
code | character(1) | not null
number | integer | not null
price | double precision | not null
costs | double precision | not null
condate | date | not null
effdate | date | not null
comment | character varying(40) |
Unique keys: T_Joint_id_key
Triggers: RI_ConstraintTrigger_17019
sharetrack=> \ds
List of relations
Name | Type | Owner
-----------------+----------+-------
T_Joint_id_seq | sequence | brian
T_Tester_id_seq | sequence | brian
splits_id_seq | sequence | brian
(3 rows)
sharetrack=> INSERT INTO "T_Tester" (epic,code,number,price,costs,condate,effdate,comment) VALUES ('37RL','B','1','36750','800','1984-12-01','1984-12-01','');
ERROR: pg_aclcheck: class "t_tester_id_seq" not found
sharetrack=>
So although the sequence reference name is stored in mixed case in the
table, it gets converted to lower case when processing.
Please can you advise if there is a fix available or any circumvention
apart from redefining all affected tables.
Thanks,
Brian
On Wed, 2002-11-13 at 07:51, Brian Harris wrote:
Hello,
I have just updated to PostgreSQL 7.2.2. I am utilising tables with
mixed case table and field names. I can now no longer insert data into
these tables as shown by the following psql interchange:sharetrack=> \d "T_Joint"
Table "T_Joint"Column | Type | Modifiers
---------+-----------------------+--------------------------------------------------
id | integer | not null default nextval('T_Joint_id_seq'::text)
Name | Type | Owner
-----------------+----------+-------
T_Joint_id_seq | sequence | brian
Please can you advise if there is a fix available or any circumvention
apart from redefining all affected tables.
You need to either:
ALTER TABLE RENAME "T_Tester_id_seq" TO t_tester_id_seq;
Or
ALTER TABLE "T_Joint"
ALTER COLUMN id
SET DEFAULT nextval('"T_Tester_id_seq"'::text);
The first converts the case of the sequence name to lower, the second
has nextval use the uppercase version.
--
Rod Taylor <rbt@rbt.ca>
Brian Harris <brian@harris-piper.freeserve.co.uk> writes:
id | integer | not null default nextval('T_Joint_id_seq'::text)
This default is wrong --- it should be
nextval('"T_Joint_id_seq"'::text)
Note the quotes.
regards, tom lane
Tom,
Thanks for the response.
The tables were created from the output from the previous release
pg_dump program, which omitted the double quotes in the DEFAULT
nextval() clause. I have tested the new version of pg_dump which
includes them if the sequence name contain mixed case characters.
Regards,
Brian
Tom Lane wrote:
Show quoted text
Brian Harris <brian@harris-piper.freeserve.co.uk> writes:
id | integer | not null default nextval('T_Joint_id_seq'::text)
This default is wrong --- it should be
nextval('"T_Joint_id_seq"'::text)
Note the quotes.regards, tom lane