Postgres and RTx extension

RT Developers
1

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

I recently written a extention for RT and I have a problem. How should I
create the schema for Postgresql ? It seems that pgsql does not care
about sensitivity in names and I end up in situations where I have a
table named “Customers” and RT failes to insert a record saying that:

[Tue Jan 8 19:01:23 2008] [warning]: DBD::Pg::st execute failed: ERROR:
relation “customers” does not exist
(/usr/share/perl5/DBIx/SearchBuilder/Handle.pm:505)
[Tue Jan 8 19:01:23 2008] [warning]: RT::Handle=HASH(0x91bfc6c)
couldn’t execute the query ‘INSERT INTO Customers (Details, Created,
Creator, Email, Name) VALUES (?, ?, ?, ?, ?)’ at
/usr/share/perl5/DBIx/SearchBuilder/Handle.pm line 518

The create statement for Pg looks like this:

CREATE TABLE “Customers” (
id integer DEFAULT nextval(‘seq_customers_idx’::regclass) NOT NULL,
“Name” character varying(128) NOT NULL,
“Email” character varying(128) NOT NULL,
“Details” text,
“Creator” integer NOT NULL,
“Created” timestamp without time zone
);

What is wrong here ?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHg8xAjGXbUSvc3AsRAjPgAJ4i1J8uTtAxNYyIJ7Wr0wfyry7wVwCdHU9N
wYZlDjc4bxGMQKAmfdPcj2k=
=0T+K
-----END PGP SIGNATURE-----

2

What is wrong here ?

Notice carefully the quotes around the table name “Customers” in the
create statement, and how they’re missing on your insert. Then
research the significance of those quotes in the Pg documentation.

3

I recently written a extention for RT and I have a problem. How should I
create the schema for Postgresql ? It seems that pgsql does not care
about sensitivity in names and I end up in situations where I have a
table named “Customers” and RT failes to insert a record saying that:

No, it cares about sensitivity if you tell it to care. This part is in
conformance with the SQL standard (although Postgres gets it backwards by
folding to lower case instead of upper for unquoted identifiers).

What you want to do is perform quoting consistently: either always
double-quote identifiers, or never do. I believe that RT has some sections
where it always quotes, so you should, too. Therefore, you want

CREATE TABLE “Customers”. . .

and also

INSERT INTO “Customers”. . .

rather than

CREATE TABLE Customers. . .
INSERT INTO Customers. . .

(These latter two are the same as typing customers, without the capital.)

A

4

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Vivek Khera wrote:

What is wrong here ?

Notice carefully the quotes around the table name “Customers” in the
create statement, and how they’re missing on your insert. Then research
the significance of those quotes in the Pg documentation.
Thanks a lot for the pointer. All is ok now.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFHhGoijGXbUSvc3AsRAkEeAJ4mCHCGKLbm5uAfm+kjJdTj4jQkVgCbBYGA
WXsGxL+4VAjQHjRp2a7bEOE=
=amJn
-----END PGP SIGNATURE-----