Most Popular
1500 questions
63
votes
1 answer
How to get a working and complete PostgreSQL DB backup and test
I was hoping I could get a clear answer on how to ensure taking a full Postgres backup just like you would get with MS SQL Server, and then take care of orphaned users.
From what I've read, and it could be wrong, finding a good PostgreSQL blog has…
Ali Razeghi - AWS
- 7,566
- 1
- 26
- 38
63
votes
6 answers
What are different ways to replace ISNULL() in a WHERE clause that uses only literal values?
What this isn't about:
This is not a question about catch-all queries that accept user input or use variables.
This is strictly about queries where ISNULL() is used in the WHERE clause to replace NULL values with a canary value for comparison to a…
Erik Reasonable Rates Darling
- 45,549
- 14
- 145
- 532
63
votes
4 answers
Find IDs from a list that don't exist in a table
Say I have the following schema and data:
create table images(
id int not null
);
insert into images values(1), (2), (3), (4), (6), (8);
I want to perform a query like:
select id from images where id not exists in(4, 5, 6);
But this doesn't…
Patrick D'appollonio
- 735
- 1
- 5
- 7
63
votes
4 answers
What is lock escalation?
I was asked this question at an interview and had no answer. Can anyone here explain?
Kilhoffer
63
votes
6 answers
Date range rolling sum using window functions
I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need:
SELECT
TH.ProductID,
TH.TransactionDate,
TH.ActualCost,
…
Paul White
- 94,921
- 30
- 437
- 687
62
votes
7 answers
.bak file not visible in any directory in SSMS
I have a .bak file created today by someone else, manually created through SSMS 2008 R2. I'm trying to manually restore the database, unfortunately the file isn't appearing when I go to browse it.
I can script the restore process, but I've seen this…
Sean Long
- 2,256
- 5
- 23
- 32
62
votes
2 answers
PostgreSQL EXCLUDE USING error: Data type integer has no default operator class
In PostgreSQL 9.2.3 I am trying to create this simplified table:
CREATE TABLE test (
user_id INTEGER,
startend TSTZRANGE,
EXCLUDE USING gist (user_id WITH =, startend WITH &&)
);
But I get this error:
ERROR: data type integer has no…
Ian Timothy
- 905
- 1
- 7
- 10
62
votes
4 answers
What is the difference between select count(*) and select count(any_non_null_column)?
I seem to remember that (on Oracle) there is a difference between uttering select count(*) from any_table and select count(any_non_null_column) from any_table.
What are the differences between these two statements, if any?
Martin
- 2,420
- 4
- 26
- 35
61
votes
6 answers
Performance implications of MySQL VARCHAR sizes
Is there a performance difference in MySQL between varchar sizes? For example, varchar(25) and varchar(64000). If not, is there a reason not to declare all varchars with the max size just to ensure you don't run out of room?
BenV
- 4,923
- 7
- 40
- 38
61
votes
5 answers
Why does ALTER COLUMN to NOT NULL cause massive log file growth?
I have a table with 64m rows taking 4.3 GB on disk for its data.
Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text.
I added a a NULLABLE column with data-type Datetimeoffset(0).
I then UPDATED this column…
PapillonUK
- 713
- 1
- 5
- 5
61
votes
2 answers
Are WHERE clauses applied in the order they are written?
I'm trying to optimize a query which looks into a big table (37 millions rows) and have a question about what order the operations are executed in a query.
select 1
from workdays day
where day.date_day >= '2014-10-01'
and day.date_day <=…
Jorge Vega Sánchez
- 993
- 3
- 15
- 22
60
votes
7 answers
Select columns inside json_agg
I have a query like:
SELECT a.id, a.name, json_agg(b.*) as "item"
FROM a
JOIN b ON b.item_id = a.id
GROUP BY a.id, a.name;
How can I select the columns in b so I don't have b.item_id in the JSON object?
I have read about ROW, but it returns a…
Yanick Rochon
- 1,651
- 4
- 20
- 28
60
votes
6 answers
Getting last modification date of a PostgreSQL database table
I'm trying to get when my table was modified by checking its file modification date as it is described in this answer. But the result is not always correct. The file modification date updates in several minute after I update my table. Is it correct…
hank
- 919
- 1
- 8
- 12
60
votes
6 answers
Why use WHERE 1 or WHERE 1=1?
Usually, if conditions are not required in our query statements, we don't use a WHERE clause. But I've seen a WHERE 1 clause being used in many places, even where other conditions are not present.
Why is this done?
Are there specific benefits to…
ursitesion
- 2,061
- 8
- 32
- 45
60
votes
3 answers
Performance difference for COALESCE versus ISNULL?
I've seen a lot of people use the COALESCE function in place of ISNULL. From internet searches, I've found that COALESCE is ANSI standard, so there is an advantage that we know what to expect when using it. However, ISNULL seems easier to read…
Richard
- 1
- 8
- 42
- 62