Most Popular
1500 questions
36
votes
1 answer
Optimal way to ignore duplicate inserts?
Background
This problem relates to ignoring duplicate inserts using PostgreSQL 9.2 or greater. The reason I ask is because of this code:
-- Ignores duplicates.
INSERT INTO
db_table (tbl_column_1, tbl_column_2)
VALUES (
SELECT
…
Dave Jarvis
- 833
- 1
- 10
- 25
36
votes
4 answers
Moving postgresql data to different drive
I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my PostgreSQL data to the different drive. I am still in…
codecool
- 1,983
- 2
- 16
- 21
36
votes
4 answers
Still wrong to start the name of a user stored procedure with sp_?
One of my co-workers named a stored procedure in our SQL Server 2008 R2 database sp_something. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this online article that explains why it is wrong, so I…
user5147
36
votes
5 answers
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci
I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:
Error: Unknown collation utf8mb4_0900_ai_ci
Then I went…
Frosty
- 361
- 1
- 3
- 3
36
votes
3 answers
What does a RED X on a database user mean?
I created two new AD groups and added them as Users of a database, but their icons show with a red X.
What does this mean?
user18718
36
votes
3 answers
Why does SQL Server use a better execution plan when I inline the variable?
I have a SQL query that I am trying to optimize:
DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT
Id,
MIN(SomeTimestamp),
MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @Id
AND SomeBit = 1
GROUP BY Id
MyTable has two…
Rainbolt
- 800
- 1
- 9
- 18
36
votes
3 answers
Why does SQL Server return some rows while still executing the query, and sometimes not?
There are queries where when we hit "execute", it shows some rows and it keeps growing, but the query is not over yet. Yet sometimes, it waits until the end of the query.
Why does this happen? Is there a way to control this?
Racer SQL
- 7,386
- 14
- 69
- 128
36
votes
2 answers
Constraint - one boolean row is true, all other rows false
I have a column: standard BOOLEAN NOT NULL
I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with plpgsql, but this seems like a sledgehammer. I…
theGtknerd
- 461
- 1
- 4
- 6
36
votes
2 answers
Why are Denali sequences supposed to perform better than identity columns?
In his answer to Which is better: identity columns or generated unique id values? mrdenny says:
When SQL Denali comes out it will support sequences which will be more efficient than identity, but you can't create something more efficient…
bernd_k
- 12,211
- 23
- 75
- 111
36
votes
1 answer
MySQL : Why are there "test" entries in mysql.db?
Recently, I posted an answer to a question about mysql.db.
Then, I got to thinking I should ask everyone this question:
I have noticed for years that upon installation of MySQL 5.0+, mysql.db is populated with two entries that allow test databases…
RolandoMySQLDBA
- 182,700
- 33
- 317
- 520
36
votes
2 answers
'CONCAT' is not a recognized built-in function name
A client reported that they were running on SQL Server 2012, and we delivered some test queries for testing prior to a final delivery, however:
'CONCAT' is not a recognized built-in function name.
I understand that CONCAT() is a new built-in…
beeks
- 1,251
- 1
- 8
- 15
36
votes
5 answers
How should I design a relationship table for friendship?
If A is a friend of B, then should I store both values AB and BA, or one is enough? What are the advantages and disadvantages of both methods.
Here is my observation:
If I keep both then I have to update both when receive a request from a friend. …
roxrook
- 485
- 1
- 4
- 7
36
votes
2 answers
mysql Lock wait timeout exceeded; try restarting transaction
we are running java application, running for ages, back end is MySQL, recently updated to MySQL 5.6. Everything was running fine, past week started getting this error:
Lock wait timeout exceeded; try restarting transaction
which looks like never…
JAVAC
- 471
- 1
- 4
- 6
35
votes
1 answer
MySQL Count rows from another table for each record in table
SELECT
student.StudentID,
student.`Name`,
COUNT(attendance.AttendanceID) AS Total
FROM
student
LEFT JOIN attendance ON student.StudentID = attendance.StudentID
I am trying to count the last row but instead it counts all the results and…
Ali Shaikh
- 479
- 1
- 6
- 11
35
votes
4 answers
Should dbo schema be avoided?
When it comes to the dbo schema:
Is it a best practice to avoid using the dbo schema when creating database objects?
Why should the dbo schema be avoided or should it?
Which database user should own the dbo schema?
jrara
- 5,333
- 20
- 56
- 65