PostgreSQL: Notes about Quotes and Identifiers

Leave a reply

Last night I was playing around with schemas and found some oddities with quotes. In version 8.0+, you can use ‘$$’ symbols to replace quotes. This gives the programmer some flexibility in terms of having to escape quotes within the text body. For example: 

CREATE OR REPLACE FUNCTION NoticeNoEscaping() RETURNS VOID AS
$$
  BEGIN
	 Raise Notice "See, no escaping required";
  END;
$$
LANGUAGE 'plpgsql';

When creating tables, I noticed that defining a column name with quotes and without quotes results in two different column names.

Create Table QuotedColumn
(
	“Id” Serial,
	Primary Key(“Id”)
);

The following would throw an error because of missing quotes.

Create Table QuotedColumn
(
	“Id” Serial,
	Primary Key(Id)
);

I suggest dropping the quotes all together. To correct the error above, you would type:

Create Table UnQuotedColumn
(
	Id Serial,
	Primary Key(Id)
);

I also encountered a similar problem while implementing a schema. For this example, a table is being added to the schema, ‘test_schema’.

Create Schema “test_schema”;

Create Table “test_schema”.Customers
(
	Id Serial,
	Primary Key(Id)
);

When creating schemas using PgAdmin, quotes are automatically insert around the schema name. This results in having to use quotes to define the schema prefix each time a table within the schema is referenced. Try the following instead.

Create Schema test_schema;

Set search_path To test_schema;

Create Table Customers
(
	Id Serial,
	Primary Key(Id)
);

 In the above snippet, the schema,  ‘test_schema’, can now be referenced without quotes. Not only that, but I also set the schema for all code to automatically reference ‘test_schema’ by using the command ‘Set search_path To <schema name>;’.

If you would like more information on the topics discussed, please consult the following links:

Leave a Reply