0

I am not so into PostgreSQL and pgAdmin 4 and I have the following doubt.

Following a screenshot of what I can see in my pgAdmin4:

enter image description here

As you can see it is performing this very simple query:

SELECT * FROM public."Example"
ORDER BY id ASC

The thing that I am not understanding is what is this public name in front of the Example table name. What is it?

I was trying to perform a query in this way but it is not working:

SELECT * FROM Example
ORDER BY id ASC

It give me a syntax error. I often used MySql and in MySql it is working.

I tried to replace the query in this way:

SELECT * FROM "Example"
ORDER BY id ASC

and so it is working. So it means that in PosgreSQL database the "" around the table name are mandatory?

AndreaNobili
  • 40,955
  • 107
  • 324
  • 596
  • 2
    Does this answer your question? [Omitting the double quote to do query on PostgreSQL](https://stackoverflow.com/questions/6331504/omitting-the-double-quote-to-do-query-on-postgresql) – Eyeslandic Nov 03 '21 at 19:15
  • 2
    `public` is the schema that the table is in. It is a good habit to schema qualify table names as there can be tables of the same name in different schema. See [search_path](https://www.postgresql.org/docs/14/ddl-schemas.html#DDL-SCHEMAS-PATH) for more. As to double quoting see [Identifiers](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) in the docs it will explain everything. Short version, if don't want to double quote use all lower case names. – Adrian Klaver Nov 03 '21 at 19:43
  • `public` is the schema name. –  Nov 03 '21 at 19:44
  • Sounds like you accidentally defined the `example` table with a capitalised name – Bergi Nov 03 '21 at 19:51
  • @Bergi So is it better use lowercase name for table? (as standard) – AndreaNobili Nov 03 '21 at 22:23
  • 2
    It is less work if you lower case identifiers(table, function,view, etc names). If less work is better then yes it is better. – Adrian Klaver Nov 03 '21 at 22:30

2 Answers2

1

The thing that I am not understanding is what is this public name in front of the Example table name. What is it?

As said in postgres documentation: "By default tables (and other objects) are automatically put into a schema named "public". Every new database contains such a schema."

So it means that in PosgreSQL database the "" around the table name are mandatory?

Not really but you need to use it if you are using reserved keywords (such as "user","name"and other)or if your table's name contains uppercase(it's your case) letters. Anyways, in this case if you can it's better change your table's name.

Ansol
  • 95
  • 1
  • 11
  • So is it better use lowercase name for table? (as standard) – AndreaNobili Nov 03 '21 at 22:23
  • 1
    Yes, you should follow the convention and use lowcase and underscore for your table's name. If you are interested and wanna know more about Postgres naming conventions you can check [here](https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS) – Ansol Nov 04 '21 at 08:41
0

You should change your table name to all alphabet in lowercase then try again with

select * from example
INTz_
  • 41
  • 2