Most Popular
1500 questions
25
votes
1 answer
How to preserve the original order of elements in an unnested array?
Given the string:
'I think that PostgreSQL is nifty'
I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details and would like to join an unnested array of that string…
swasheck
- 10,665
- 4
- 47
- 88
25
votes
3 answers
Which collation should I choose for a muiti-language website?
Does a collation have any influence over a query speed? Does the size of a table change depending of the collation?
If I want to build a website that must support all possible languages (lets take for e.g. Google) which would be the recommended…
BrunoLM
- 3,473
- 7
- 27
- 22
25
votes
2 answers
How do I shrink the innodb file ibdata1 without dumping all databases?
InnoDB stores all tables in one big file ibdata1.
After dropping a big table, the file is keeping its size no matter how big the table was.
How can I shrink that file without having to dump and re-import the whole database (which has several…
rubo77
- 816
- 2
- 13
- 24
25
votes
1 answer
Try catch equivalent in Postgres
Do we have a try catch equivalent in Postgres? I have written some user defined functions that are called by trigger. I (don't) want to ignore errors so that flow does not get interrupted.
FastTurtle
- 353
- 1
- 3
- 7
25
votes
4 answers
How to get notices to the output stream?
I have debugging messages in functions. Those message are raised like
RAISE NOTICE 'Value of id : %', id;
I set my log file with \o messages.txt
Then I do what I need to do with \i process.sql
And when the execution is terminated, \o.
The problem is…
Luc M
- 589
- 1
- 7
- 13
25
votes
1 answer
SQL Server 2017 crashes when backing up because filepath is wrong
I was trying to restore my database and SQL Server kept crashing. I would get a message in SSMS that said there was a network transport error (the connection dropped bc the crash). I checked the logs and found nothing more than SQL Server closed…
Rick
- 415
- 1
- 4
- 7
25
votes
5 answers
Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
In mysqld.log I have thousands of lines like:
Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade
So, according to virtually every post on the matter (and error msg itself):
mysql_upgrade -u…
Roy Hinkley
- 449
- 2
- 8
- 15
25
votes
3 answers
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect each record, executing a regular expression or…
Nick
- 353
- 1
- 3
- 5
25
votes
3 answers
Why filtered index on IS NULL value is not used?
Assume we have a table definition like this:
CREATE TABLE MyTab (
ID INT IDENTITY(1,1) CONSTRAINT PK_MyTab_ID PRIMARY KEY
,GroupByColumn NVARCHAR(10) NOT NULL
,WhereColumn DATETIME NULL
)
And a filtered non-clustered index like…
jericzech
- 945
- 8
- 20
25
votes
1 answer
Multiple on conflict targets
I have two unique indexes on columns a and b. I need something like this:
insert into my_table (a, b) values (1, 2), (1, 2)
on conflict (a) do update set c = 'a_violation'
on conflict (b) do update set c = 'b_violation'
So generally I want to make…
user606521
- 1,365
- 6
- 21
- 28
25
votes
2 answers
What is the data type of the ‘ctid’ system column in Postgres?
The Postgres system columns are documented in Chapter 5. Data Definition > 5.4. System Columns.
That page mentions that oid values “are 32-bit quantities”. And that page says the same about transaction identifiers. So I will assume that means oid,…
Basil Bourque
- 10,806
- 20
- 59
- 92
25
votes
1 answer
Reporting Service and Application Role
First poster, long time lurker here.
What is the best way to activate application role in a report ?
I've tried different things and so far the only method that works is to embed the call to the application role like so :-
EXEC sp_setapprole…
YS.
- 351
- 2
- 4
25
votes
2 answers
How badly do SQL Compilations impact the performance of SQL Server?
I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so.
I whipped out SQL Profiler and looked for SP:Compile or SQL:Compile events. …
AngryHacker
- 1,931
- 5
- 20
- 33
25
votes
3 answers
What are the arguments in favor of using ELT process over ETL?
I realized that my company uses an ELT (extract-load-transform) process instead of using an ETL (extract-transform-load) process.
What are the differences in the two approaches and in which situations would one be "better" than the other? It would…
HelloWorld1
- 797
- 2
- 8
- 16
25
votes
1 answer
Ignore accents in 'where' clause
In our database we have multiple entries with grave accents, carets or carons (aka hačeks). These accents are collectively also known as diacritical marks.
Now our users want to find entries including these diacritical marks when they search for…
lumo
- 445
- 2
- 6
- 13