Обсуждение: 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:

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