Below is an example of my pricing_supports table with more than 3 millions rows. There some rows having same values in the first_origin_id, delivery_id fields. If there are records with same first_origin_id, delivery_id but different source, I only want to select those records where source = 0 ID code first_origin_id delivery_id source 1 A 10 20 0 2 B 10 20 1 3 C 11 21 1 4 D 12 22 0 5 E 12 22 1 I would like result like that: ID code first_origin_id delivery_id source 1 A 10 20 0 3 C 11 21 1 4 D 12 22 0 How can I do for good performance?

Does anyone know the file name or path where I can change the lower_case_tables_names value on the latest version of MySQL Windows 11?

I have a Mariab-database using collation utf8bm4_general_ci and I am migrating to Postgresql. Some tables are foreign keyed using case-insensitive strings (e-mails sourced from different systems). When I use pgloader to migrate, I get an error message stating that some keys are not present. I can update the source table to be able to do the migration, but the error will present itself after a while if I do the switch. What can I do to fix this other than rewriting and debugging all systems to make sure e-mails are lowercased?

I would like a column in a table inside a PostgreSQL database (I am using version 9.6). I know of the UTF8_UNICODE_CI collation on MySQL, so I tried: CREATE TABLE thing ( id BIGINT PRIMARY KEY ,name VARCHAR(120) NOT NULL COLLATE "UTF8_UNICODE_CI" ); but I get: > ERROR: collation "UTF8_UNICODE_CI" for encoding "UTF8" does not exist Reading around, I found that the pg_collation table displays collations, which showed: =# SELECT * from pg_collation; collname | collnamespace | collowner | collencoding | collcollate | collctype ----------+---------------+-----------+--------------+-------------+----------- default | 11 | 10 | -1 | | C | 11 | 10 | -1 | C | C POSIX | 11 | 10 | -1 | POSIX | POSIX (3 rows) So it's clearly not there... Is there any way to add it, or obtain the behavior I want? (I want a search WHERE name LIKE '%lala%' to match 'lalá', 'LÂLÄ', etc.)

In PostgreSQL 9.4, with following schema: CREATE TABLE people ( id INTEGER PRIMARY KEY, name TEXT, junk CHAR(1000) ); INSERT INTO people(id, name) SELECT generate_series(1,100000), md5(random()::text); CREATE INDEX ON people (name text_pattern_ops); if I search by name, the index is used: test=# explain analyze select id, name from people where name like 'a%'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on people (cost=248.59..1160.92 rows=6061 width=37) (actual time=2.412..8.340 rows=6271 loops=1) Filter: (name ~~ 'a%'::text) Heap Blocks: exact=834 -> Bitmap Index Scan on people_name_idx (cost=0.00..247.08 rows=6266 width=0) (actual time=2.123..2.123 rows=6271 loops=1) Index Cond: ((name ~>=~ 'a'::text) AND (name ~ Otherwise, it behaves almost exactly like text. How can I tell PostgreSQL to use the index?

We are migrating from OracleDB to PostgreSQL and all the tables name in database are lower case while the script from application is mixed with double quotes. For example the code in application like this:

SELECT * FROM "USER_ID";

Can it be change to:

SELECT * FROM "user_id";
or
SELECT * FROM USER_ID;

Is there anyway to lower case or remove double quotes for every tables name on query?

Section 5.6 of the SQL-92 standard contains rules 10...13 per which unquoted identifiers should be upper-cased, so foo becomes FOO but "foo" remains foo. These rules are respected by Oracle , IBM DB2, Snowflake , and ksqlDB but not by Postgres , MySQL or SQLite, for example. The question is, why? In my understanding, the optional quoting of identifiers in a language with a lot of keywords makes sense. Consistent case sensitivity or insensitivity of identifiers would also make sense. But making it dependent on the identifier being quoted doesn't look rational. What am I missing?

I have a postgres database and a case insensitive collation. The collation was created by CREATE COLLATION IF NOT EXISTS case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false). (I also have another collation the removes accents, so ILIKE probably can't be used) In my table I have a JSONB column (here called "forms"). This is a jsonb array containing multiple keys. I now want to find the rows that have values with a specific string- case insensitively. For example, the query SELECT * FROM etymologies WHERE forms @> '[{"form": "bears"}]' ; returns 3 entries. I now want to get the same 3 entries when I search for SELECT * FROM etymologies WHERE forms @> '[{"form": "Bears"}]'. What would be the best way to achieve this? I tried SELECT * FROM etymologies WHERE forms @> '[{"form": "Bears"}]' collate case_insensitive ;, but this returns no results. (I also tried creating an index, but was super unsure about the syntax: CREATE INDEX forms_form_case_insensitive_index ON etymologies USING GIN ((forms->'form' collate case_insensitive)); did something (it ran as slow as a usual index creation), but I am not sure what exactly.)

postgresql 12.3 in a container with Alpine Linux 3.10 I would like to convert an existing DB to use case-insensitive collation. Is it possible? I mean, without dropping it and recreating from scratch. Also, instead of setting this for the whole db, can I add this collation to a field in an existing table, or to a table?

After reading documentation I thought I should ask about the performance issues with the case insensitive behavior I am looking for. For context, I am working on a migration project from SQL Server to PostgreSQL, and I need to make sure that case insensitivity is achieved for all data entries in the database. Will this become a issue for the database migration? From my understandings, there will be performance issues when all tables are using nondeterministic collations. > While nondeterministic collations give a more “correct” behavior, especially when considering the full power of Unicode and its many special cases, they also have some drawbacks. Foremost, their use leads to a performance penalty. Note, in particular, that B-tree cannot use deduplication with indexes that use a nondeterministic collation. Also, certain operations are not possible with nondeterministic collations, such as pattern matching operations. Therefore, they should be used only in cases where they are specifically wanted.

Creating table in MemSQL and creating composite primary key. Lets create simple table with following structure: Table User Data Name userid Primary Key (userid) And assume application support case sensitive userid. So that following user id considered to be different: abc ABC Insert following data: 1) JOHN ABC b) ALEX abc Insert fails in MemSQL as it consider 'ABC' same as 'abc'. Please help me to understand if case sensitive primary key possible in MemSQL table or not.

I have a function (which I found here years ago) that uses STUFF / PATINDEX to strip out non-alphanumeric characters from a string. When running on a Case Insensitive collation, it works fine. Recently I needed to use it on a Case Sensitive collation DB and found some odd behaviors. If the **%pattern%** for PATINDEX is just specifying lower case (ex: **%[^a-z0-9_-]%** ), then all upper case "Z"s are removed when the Collation is **Latin1_General_100_CS_AS**. If the Collation is **SQL_Latin1_General_CP1_CS_AS**, then upper case "A"s are removed. Is this a bug or did I miss something?

USE TestCollation
GO
PRINT '---------SourceString---------'
PRINT 'ABCDEFGHIJKLMNO_Z_A_PQRSTUVWXYZ-abcdefghijklmnopqrstuvwxyz'
GO
ALTER DATABASE TestCollation COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
PRINT '---------SQL_Latin1_General_CP1_CI_AS---------'
GO

DECLARE @ExternalId VARCHAR(255) =
     'ABCDEFGHIJKLMNO_Z_A_PQRSTUVWXYZ-abcdefghijklmnopqrstuvwxyz'
DECLARE @return VARCHAR(255)
SET @return = @ExternalId
DECLARE @KeepValues AS VARCHAR(50)
SET @KeepValues = '%[^a-z0-9_-]%'
WHILE PATINDEX ( @KeepValues, @return ) > 0
BEGIN
    SET @return = STUFF ( @return, PATINDEX ( @KeepValues, @return ), 1, '' )
END
PRINT @return

go

ALTER DATABASE TestCollation COLLATE Latin1_General_100_CS_AS;
GO
PRINT '---------Latin1_General_100_CS_AS---------'
GO
DECLARE @ExternalId VARCHAR(255) =
     'ABCDEFGHIJKLMNO_Z_A_PQRSTUVWXYZ-abcdefghijklmnopqrstuvwxyz'
DECLARE @return VARCHAR(255)
SET @return = @ExternalId
DECLARE @KeepValues AS VARCHAR(50)
SET @KeepValues = '%[^a-z0-9_-]%'
WHILE PATINDEX ( @KeepValues, @return ) > 0
BEGIN
    SET @return = STUFF ( @return, PATINDEX ( @KeepValues, @return ), 1, '' )
END
PRINT @return

ALTER DATABASE TestCollation COLLATE SQL_Latin1_General_CP1_CS_AS;
GO
PRINT '---------SQL_Latin1_General_CP1_CS_AS---------'
GO
DECLARE @ExternalId VARCHAR(255) =
     'ABCDEFGHIJKLMNO_Z_A_PQRSTUVWXYZ-abcdefghijklmnopqrstuvwxyz'
DECLARE @return VARCHAR(255)
SET @return = @ExternalId
DECLARE @KeepValues AS VARCHAR(50)
SET @KeepValues = '%[^a-z0-9_-]%'
WHILE PATINDEX ( @KeepValues, @return ) > 0
BEGIN
    SET @return = STUFF ( @return, PATINDEX ( @KeepValues, @return ), 1, '' )
END
PRINT @return

The below SQL query runs in 2s: SELECT * FROM fb_name INNER JOIN fb_kg ON mid = subject_mid WHERE LOWER(alias) = LOWER('Polaski') AND fb_kg.relation = 'location/location/containedby' The below SQL query runs in 2ms: SELECT * FROM fb_name INNER JOIN fb_kg ON mid = subject_mid WHERE alias = 'Polaski' AND fb_kg.relation = 'location/location/containedby' The difference between the two queries is the inclusion of the LOWER function. There has been an index created for both queries: CREATE INDEX fb_name_lower_alias ON fb_name (lower(alias)); CREATE INDEX fb_name_mid_lower_alias_index ON fb_name (mid, lower(alias)); CREATE INDEX fb_name_alias ON fb_name (alias); CREATE INDEX fb_name_mid_alias_index ON fb_name (mid, alias); EXPLAIN for LOWER: "Gather (cost=247515.73..249133.22 rows=5708 width=70)" " Workers Planned: 2" " -> Merge Join (cost=246515.73..247562.42 rows=2378 width=70)" " Merge Cond: ((fb_kg.subject_mid)::text = (fb_name.mid)::text)" " -> Sort (cost=205352.30..205794.73 rows=176972 width=44)" " Sort Key: fb_kg.subject_mid" " -> Parallel Bitmap Heap Scan on fb_kg (cost=14116.24..184480.39 rows=176972 width=44)" " Recheck Cond: ((relation)::text = 'location/location/containedby'::text)" " -> Bitmap Index Scan on relation_index (cost=0.00..14010.06 rows=424733 width=0)" " Index Cond: ((relation)::text = 'location/location/containedby'::text)" " -> Sort (cost=41163.43..41232.45 rows=27611 width=26)" " Sort Key: fb_name.mid" " -> Bitmap Heap Scan on fb_name (cost=750.54..39126.71 rows=27611 width=26)" " Recheck Cond: (lower((alias)::text) = 'polaski'::text)" " -> Bitmap Index Scan on fb_name_lower_alias_varchar_pattern_ops (cost=0.00..743.64 rows=27611 width=0)" " Index Cond: (lower((alias)::text) = 'polaski'::text)" Other EXPLAIN: "Nested Loop (cost=11.45..13502.52 rows=2 width=70)" " -> Bitmap Heap Scan on fb_name (cost=4.64..48.23 rows=11 width=26)" " Recheck Cond: ((alias)::text = 'Polaski'::text)" " -> Bitmap Index Scan on fb_name_alias_index (cost=0.00..4.64 rows=11 width=0)" " Index Cond: ((alias)::text = 'Polaski'::text)" " -> Bitmap Heap Scan on fb_kg (cost=6.81..1223.04 rows=8 width=44)" " Recheck Cond: ((subject_mid)::text = (fb_name.mid)::text)" " Filter: ((relation)::text = 'location/location/containedby'::text)" " -> Bitmap Index Scan on subject_mid_index (cost=0.00..6.81 rows=316 width=0)" " Index Cond: ((subject_mid)::text = (fb_name.mid)::text)" CREATE TABLE definitions CREATE TABLE fb_kg (object_mid varchar NOT NULL, relation varchar NOT NULL, subject_mid varchar NOT NULL, PRIMARY KEY(object_mid, relation, subject_mid)); CREATE TABLE fb_name (mid varchar NOT NULL, alias varchar NOT NULL, PRIMARY KEY(mid, alias)); How come with the same indexes the LOWER function is about 60x slower?

I am looking for a charset/collation that would make it so when I do a > SELECT * FROM table_name WHERE username = "Warrior" It only returns me the rows where username = "Warrior", "warrior" or "WARRIOR", and not "WÂRRÎOR" "Wârrîor" etc. I found a partial solution, by changing the Charset to "utf8mb4" and the Collation to "utf8mb4_bin", now it seems accent sensitive, it differentiates "Wârrîor" from "Warrior", but it's also case sensitive, so "Warrior" is different than "WARRIOR" which is not what I want. I tried a different collations but I couldn't get one to do what exactly what I want. Any ideas ? Below is a screenshot of the different Collations available to me in the "utf8mb4" Charset :

This problem may be unique to our server, but I can't tell from the symptoms where the issue may lie. My SQL Server version is Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor) The Server Level Collation: SQL_Latin1_General_CP1_CI_AS; The Database Maintenance Collation: SQL_Latin1_General_CP1_CI_AS I have a field (searchtitle) in a table (fsItems) that has a full-text index turned ON. For the record with primary key (IDItem) 704629, the content of this full-text indexed field is "TEST AFA 0 TEST". Surprisingly, the following query returns no results: SELECT * FROM fsItems WHERE CONTAINS(searchtitle,'AFA') AND IDItem = 704629 However, if I change the content to be "TEST afa 0 TEST" or "TEST AFA O TEST" (capital "O" instead of zero) the query returns the record. (It also returns the record if I change the content to "TEST AFB 0 TEST" and change the CONTAINS argument to AFB.) At first I thought maybe AFA was some kind of stop word (I checked, it isn't), but that wouldn't explain why changing zero to upper-case "O" returns the proper result. Any idea what is going on here? In other words, the query doesn't seem to be able to find "AFA" unless it is "afa" or unless I make some seemingly unimportant changes to the text itself, like change a number to a letter, or change the spelling of AFA to AFB. Why would that be? Thanks for any suggestions

Is there a way to force all text data to uppercase without recurring to writing a function for every table or doing it client side?

I get an error but I don´t understand why. Does this make sense in SQL? I need the column ValidNaming to be filled with False/True if the customer (or field CommonLastName) has an invalid name. Why this is not working?

Select
CommonLastName,
CASE COMMONLASTNAME
     WHEN ((CommonLastName like 'Connect%')
     OR (CommonLastName = 'Unknown', '', 'Null')) THEN 'False'
     ELSE 'True'
END ValidNaming,
FROM [DA1]

:( Thanks for any help

I am trying to make a case-insensitive version of json_extract_path_text(), using the citext module. I would like this to be a simple wrapper around the built-in function, the only difference being that it accepts citext as the first parameter instead of json. I want this to be a straight pass-through to the native implementation, just with a type conversion beforehand. Here's what I have so far: create extension citext; create or replace function json_extract_path_text ( string citext, variadic params text[]) RETURNS text IMMUTABLE AS $$ BEGIN SELECT json_extract_path_text(string::json, params); END; $$ LANGUAGE 'plpgsql'; This doesn't work properly, however, because of the type mis-match: > ERROR: function json_extract_path_text(json, text[]) does not exist > LINE 1: SELECT json_extract_path_text(string::json, params) > ^ > HINT: No function matches the given name and argument types. You might need to add explicit type casts. > QUERY: SELECT json_extract_path_text(string::json, params) > CONTEXT: PL/pgSQL function json_extract_path_text(citext,text[]) line 3 at SQL statement I've tried hacking around a solution using dynamic string construction and EXECUTE, but this is a real hassle and I feel like there must be a cleaner way to pass VARIADIC params through to the inner function. I can't see any obvious way to do so, however. How can I accomplish that?

There seems to be something special about 'tR' in Vietnamese collation. Appreicate if anyone who knows about it can explain in simple terms. This issue was discovered during the installation of our product on a "Vietnamese" collated SQL Server. One of the tables in the schema has 'tR' in it's name but a stored procedure is referencing the table in all lower case 'tr'. And this reference fails. I guess this situation is analogous to '阝' matching 'ss' in other collations. Here is a reproduction:

select  case when 'tr' = 'tR' COLLATE SQL_Latin1_General_CP1_CI_AS   then 'match' else 'no match' end 
select  case when 'tr' = 'tR' COLLATE Vietnamese_CI_AI   then 'match' else 'no match' end 
select  case when 'tr' = 'TR' COLLATE Vietnamese_CI_AI   then 'match' else 'no match' end

Results:

-----
match


--------
no match


-----
match

The second T-SQL produces the mismatch. Other combinations of 't' and 'R' do not.

I want enable lowercase table names so i have added to etc/mysql/my.cnf also etc/mysql/mariadb.cnf

lower_case_table_name = 1

restart the service sudo service mysql restart I can acces service from phpmyadmin, but I can't access it from cli

$ mysql -u root -p
mysql: unknown variable 'lower_case_table_names=1'

how to enabled lowercase table name correctly? environment - OS: ubuntu 20.04 - Mariadb: 10.5.9-MariaDB-1:10.5.9+maria~focal **updated:** my.cnf

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/ 

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock
 lower_case_table_names = 1

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

mariadb.cnf

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/ 

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock
 lower_case_table_names = 1

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/