Anonymous 1199
I have a question about this fiddle:
<>https://dbfiddle.uk?rdbms=postgres_12&fiddle=c32d1bdd2ac2df3ca59132e095f51f06
I cannot see why the records from the json data set will not be added to the table SAMPLE. It is a bit baffling to me right now. Even when I manually add an INSERT INTO ... command the data still does not show up after running SELECT * .....
Top Answer
PeterVandivier
`SAMPLE` is a different table from `"SAMPLE"`. PostgreSQL lower-cases unquoted table names to conform (its interpretation of) to the SQL standard.
<>https://dbfiddle.uk/?rdbms=postgres_12&fiddle=0ebc1c1ccccb94c0a89c23c186b10640&hide=79
You can read at length my own troubles with this behaviour on [this thread][1]. The TL;DR: of it though is:
1. The SQL Standard requires identifiers to match regardless of casing
2. Different platforms have different interpretations of this. If I recall correctly...
- SQL Server: `MyTable` = `mytable`, by default you cannot create both
- Oracle: all unquoted identifiers get **UPPERCASED**
- MySQL & PostgreSQL: all unquoted identifiers get **lowercased**
3. If you _always_ quote-wrap your identifier, every platform will respect your given casing. If not, you have to be sensitive to each platform's quirks
The most direct citation from the (2011) SQL standard is (I believe) found in **IWD 9075-2:201?(E), 5.4 Names and identifiers** under **Syntax Rules**
> 2) An <SQL language identifier> is equivalent to an <SQL language identifier> in which every letter that is
a lower-case letter is replaced by the corresponding upper-case letter or letters...
Sadly, I cannot find a freely available complete PDF of the 2011 standard at this time, but you can spelunk further yourself a the following links if the spirit moves you
* https://modern-sql.com/standard
* https://standards.iso.org/ittf/PubliclyAvailableStandards/
* https://jakewheat.github.io/sql-overview/sql-2011-foundation-grammar.html#_5_4_names_and_identifiers
[1]: https://chat.stackexchange.com/transcript/message/52858026#52858026