Why PostgreSQL does not like UPPERCASE table names?
put table name into double quotes if you want postgres to preserve case for relation names.
Quoting an identifier also makes it case-sensitive, whereas unquoted
names are always folded to lower case. For example, the identifiers
FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo"
and "FOO" are different from these three and each other. (The folding
of unquoted names to lower case in PostgreSQL is incompatible with the
SQL standard, which says that unquoted names should be folded to upper
case. Thus, foo should be equivalent to "FOO" not "foo" according to
the standard. If you want to write portable applications you are
advised to always quote a particular name or never quote it.)
from docs (emphasis mine)
example with quoting:
t=# create table "UC_TNAME" (i int);
CREATE TABLE
t=# \dt+ UC
t=# \dt+ "UC_TNAME"
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+---------+-------------
public | UC_TNAME | table | postgres | 0 bytes |
(1 row)
example without quoting:
t=# create table UC_TNAME (i int);
CREATE TABLE
t=# \dt+ UC_TNAME
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+---------+-------------
public | uc_tname | table | postgres | 0 bytes |
(1 row)
So if you created table with quotes, you should not skip quotes querying it. But if you skipped quotes creating object, the name was folded to lowercase and so will be with uppercase name in query - this way you "won't notice" it.
Select uppercase table name on postgreSQL is not working
PostgresSQL column and table names are case insensitive, unless you surround them with quotes (like you do, "SELECT * FROM \"" + name + "\" ;"
).
See this answer: https://stackoverflow.com/a/21798517/1453822
Are PostgreSQL column names case-sensitive?
Identifiers (including column names) that are not double-quoted are folded to lowercase in PostgreSQL. Column names that were created with double-quotes and thereby retained uppercase letters (and/or other syntax violations) have to be double-quoted for the rest of their life:
"first_Name"
Values (string literals / constants) are enclosed in single quotes:
'xyz'
So, yes, PostgreSQL column names are case-sensitive (when double-quoted):
SELECT * FROM persons WHERE "first_Name" = 'xyz';
Read the manual on identifiers here.
My standing advice is to use legal, lower-case names exclusively so double-quoting is not needed.
Psycopg2 doesn't like table names that start with a lower case letter
Read "Identifiers and Key Words" from the manual, especially the part about "quoted identifiers".
Postgres query with uppercase letters in Column name
Single quotes are for string constants, not for identifiers. And each part of a multi-part identifier needs to be quoted separately:
select "id", "firstName", "fromGB"
from "User"
join "gBInfo" on "User".id = "gBInfo"."userId";
The condition 'User.id' = 'gBInfo.userId'
simply compares the string value User.id
with the string value gBInfo.userId
which are obviously never equal.
Spark doesn't respect the case sensitivity of table
In Postgres, when you don't double quote object identifiers (like table name), they are treated as case insensitive. So this TextLogs
actually equals to textlogs
.
In order to have case sensitive object identifier, you need to double quote it. In your case, that would be "TextLogs"
, so in your code you should just add escaped double quotes to table name:
val opts = Map(
"url" -> "jdbc:postgresql://localhost:5433/sparkdb",
"dbtable" -> "\"TextLogs\"",
"user" -> "admin",
"password" -> "mypassword"
)
val df = spark
.read
.format("jdbc")
.options(opts)
.load
Related Topics
How to Get Only Digits from String in MySQL
How to Import CSV Data into a Table Without Knowing the Columns of the Csv
Er_Access_Denied_Error: Access Denied for User ''@'Localhost' (Using Password: No)
Select Only Rows With Max Date
Could Not Load File or Assembly Error in .Net Standard 2.0 Class Library
Find All Parent Records Where All Child Records Have a Given Value (But Not Just Some Child Records)
Sql Select Everything After a Certain Character
Addition of Total Hours in SQL Server
Mysql - Get All Records That Have More Than 1 Record for the Same Id
Sql Get Parent Where Children Have Specific Values
Oracle Sql, Concatenate Multiple Columns + Add Text
Sql Server: Check If Variable Is Null and Then Assign Statement for Where Clause
Extracting Data Between Two Delimiters in SQL Server
How to Execute a Stored Procedure Once for Each Row Returned by Query
How to Calculate Age (In Years) Based on Date of Birth and Getdate()
How Select Max(Salary) of Employee Each Department With Employee_Id and Emp_Name
Select Every Employee That Has a Higher Salary Than the Average of His Department