一些列使用双引号，一些列在postgres中没有双引号

Question

Does any body know why some columns in postgres is shown as double quotes and some columns without any quotes in pgadmin III.

I am able to insert data for the columns (with quotes) only if I give the column name in double quotes.

I am having problem when I try to insert the data to that table having both the columns (with & without double quotes) from PHP using PDO

Answer 1

There are two reasons why you need double quotes around column names in PostgreSQL.

1. If a reserved word is used as a column name
2. PostgreSQL is case sensitive and all objects are regarded as lowercase unless double quotes are provided. Double quotes tells PostgreSQL to use the case given.

If you create a table

CREATE TABLE tt1 (
    id integer,
    "Order" integer
);

The above statement tell PostgreSQL that the "Order" column must be saved in that case.

The following INSERT statements will not work :-

INSERT INTO tt1 (id, Order) VALUES (1, 1)
INSERT INTO tt1 (id, "order") VALUES (2, 2)
INSERT INTO tt1 (id, "OrDer") VALUES (3, 3)

You will have to get the case correct :-

INSERT INTO tt1 (id, "Order") VALUES (1, 1)
INSERT INTO tt1 (id, "Order") VALUES (2, 2)
INSERT INTO tt1 (id, "Order") VALUES (3, 3)