Monday, November 30, 2015

Creating PostgreSQL tables, views, columns, etc with case insensitivity

They key thing when defining postgreSQL tables, views, columns, etc with case insensitivity is to not put quotes around the names.  If you do, they will always be case sensitive with the need to put quotes around them to access them.  There is also an exception to this rule -  defining identifiers with lower case and quotes - it acts the same way as defining them without quotes.  This is because the default behavior for postgreSQL is to force identifiers to lowercase.  Below are examples for clarity.

This was verified on psql (PostgreSQL) 9.0.4.

Identifiers with quotes - the wrong way to do it:

CREATE TABLE "MYTABLE"
(
  "ID" numeric(9,0) NOT NULL,
  "DESCRIPTION" character varying(4000),
  CONSTRAINT "ID_PRIMARY_KEY" PRIMARY KEY ("ID")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "MYTABLE" OWNER TO cherryshoe;

sql statement errors because of case sensitivity and quotes:
1
cherryshoe=> select * from mytable;
ERROR:  relation "mytable" does not exist
LINE 1: select * from mytable;

2
cherryshoe=> select * from MYTABLE;
ERROR:  relation "mytable" does not exist
LINE 1: select * from MYTABLE;

3
cherryshoe=> INSERT INTO mytable (id, description) VALUES (0, 'description');
ERROR:  relation "mytable" does not exist
LINE 1: INSERT INTO mytable (id, description) VALUES (0, 'descrip...

4
cherryshoe=> INSERT INTO MYTABLE (ID, DESCRIPTION) VALUES (1, 'description1');
ERROR:  relation "mytable" does not exist
LINE 1: INSERT INTO MYTABLE (ID, DESCRIPTION) VALUES (1, 'descrip...

5
cherryshoe=> INSERT INTO myTable (Id, Description) VALUES (2, 'description2');
ERROR:  relation "mytable" does not exist
LINE 1: INSERT INTO myTable (Id, Description) VALUES (2, 'descriptio...

sql statements work because case sensitivity and quotes:
6
cherryshoe=> select * from "MYTABLE";
 ID | DESCRIPTION
----+-------------
(0 rows)

7
cherryshoe=> INSERT INTO "MYTABLE" ("ID", "DESCRIPTION") VALUES (3, 'description3');
INSERT 0 1

Identifiers without quotes - three different ways to do it the right way:
a All Uppercase
CREATE TABLE MYTABLE
(
  ID numeric(9,0) NOT NULL,
  DESCRIPTION character varying(4000),
  CONSTRAINT ID_PRIMARY_KEY PRIMARY KEY (ID)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE MYTABLE OWNER TO cherryshoe;

b All Lowercase
CREATE TABLE mytable
(
  id numeric(9,0) NOT NULL,
  description character varying(4000),
  CONSTRAINT id_primary_key PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE mytable OWNER TO cherryshoe;

c Camelcase
CREATE TABLE myTable
(
  Id numeric(9,0) NOT NULL,
  Description character varying(4000),
  CONSTRAINT Id_primary_key PRIMARY KEY (Id)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE myTable OWNER TO cherryshoe;

All the sql statements that didn't work before, now work (The ones that didn't use case sensitivity and no quotes):
1
cherryshoe=> select * from mytable;
 id | description
----+-------------
(0 rows)

2
cherryshoe=> select * from MYTABLE;
 id | description
----+-------------
(0 rows)

3
cherryshoe=> select * from myTable;
 id | description
----+-------------

(0 rows)

4
cherryshoe=> INSERT INTO mytable (id, description) VALUES (0, 'description');
INSERT 0 1

5
cherryshoe=> INSERT INTO MYTABLE (ID, DESCRIPTION) VALUES (1, 'description1');
INSERT 0 1

6
cherryshoe=> INSERT INTO myTable (Id, Description) VALUES (2, 'description2');
INSERT 0 1

All the sql statements that worked before, now don't work (The ones that used case sensitivity and quotes):
7
cherryshoe=> select * from "MYTABLE";
ERROR:  relation "MYTABLE" does not exist
LINE 1: select * from "MYTABLE";
                      ^
8
cherryshoe=> INSERT INTO "MYTABLE" ("ID", "DESCRIPTION") VALUES (3, 'description3');
ERROR:  relation "MYTABLE" does not exist
LINE 1: INSERT INTO "MYTABLE" ("ID", "DESCRIPTION") VALUES (3, 'desc...

Here is the example for the exception to this rule -  defining identifiers with lower case and quotes - it acts the same way as defining them without quotes.  This is because the default behavior for postgreSQL is to force identifiers to lowercase.  

CREATE TABLE "mytable"
(
  "id" numeric(9,0) NOT NULL,
  "description" character varying(4000),
  CONSTRAINT "id_primary_key" PRIMARY KEY ("id")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "mytable" OWNER TO cherryshoe;

Then all the same sql statements that did/did not work act the same way as defining a table with no quotes.

Takeaway - 
You probably want to choose all uppercase, all lowercase, or camelcase based on your project standards.  Or even easier is to pick all lowercase, so it fits with the postgreSQL default behavior.

No comments:

Post a Comment

I appreciate your time in leaving a comment!