Postgres Syntax requiring extra ' ' and "" around things

Question

So I am reading tutorial online and I am having a weird issue with my database when doing normal queries. So I see the following does not work:

select * from DBS
ERROR:  relation "dbs" does not exist

but This works:

select * from "DBS"

When I do this it fails:

select name from "DBS" 
ERROR:  column "name" does not exist

but this works but doesnt actually return the correct information (it just has name for every row:

select 'name' from "DBS"

name
name
name

Is there some setting on Postgres causing this to happen? Postgres 9.4.5 (On RDS).

select 'NAME' from "DBS";
 ?column?
----------
 NAME
 NAME
 NAME
(3 rows)

When I look at select * from "DBS";

    NAME
----------
    default
    matt
    matt2

What is the result of `select "name" from "DBS"` – innomatics Apr 11 '16 at 23:44 — innomatics, Apr 11 '16 at 23:44

score 4 · Accepted Answer · edited May 23 '17 at 12:07

4

You need to specify the quotes around the table identifier, because the table uses capital letters and was created using quotes.

postgres has some distinctive behavior re: quoting

select 'name' from "DBS"

You are simply selecting the string literal 'name' once for each row in your table.

edited May 23 '17 at 12:07

Community

1
1

answered Apr 11 '16 at 23:43

innomatics

370
1
10

score 2 · Answer 2 · answered Apr 12 '16 at 00:45

Postgres is a little unique in that it folds everything to lowercase unless you use " quotes. I suspect you created your database using pgadmin. If you create a database in pgadmin using UPPER case it will create it with UPPER case and then you will be required to use " around the name to access it. Candidly I'd rename the db to lower case and get rid of the quotes, your life will be a lot easier.

Postgres Syntax requiring extra ' ' and "" around things

2 Answers2