Most Popular
1500 questions
53
votes
6 answers
How to update 10 million+ rows in MySQL single table as Fast as possible?
Using MySQL 5.6 with InnoDB storage engine for most of the tables. InnoDB buffer pool size is 15 GB and Innodb DB + indexes are around 10 GB. Server has 32GB RAM and is running Cent OS 7 x64.
I have one big table which contains around 10 millions +…
user16108
52
votes
6 answers
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
I am using pg_dump / pg_restore to backup and restore a PostgreSQL database, but am getting some error messages (and a non-zero exit status) from pg_restore. I tried a super simple base case (outlined below) but still got these errors:
pg_restore:…
KSletmoe
- 677
- 1
- 6
- 7
52
votes
2 answers
Refresh materalized view incrementally in PostgreSQL
Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?
Consider this table & materialized view:
CREATE TABLE graph (
xaxis integer NOT NULL,
value integer NOT…
user4150760
- 1,129
- 3
- 14
- 20
52
votes
3 answers
How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?
I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
leeand00
- 1,722
- 6
- 20
- 36
52
votes
5 answers
When should you denormalize?
I think we are all familiar with database normalization.
My question is: What are some guidelines that you use when you want to denormalize the tables?
Richard
- 1
- 8
- 42
- 62
52
votes
6 answers
Can I see Historical Queries run on a SQL Server database?
Someone was running a query on our SQL Server database remotely and their system crashed.
They have no backup of that query and want to see what was run on the server.
Is it possible to find this query in a log or in a history somewhere?
user87094
- 623
- 1
- 5
- 4
52
votes
3 answers
How do I know what indexes to create for a table?
Is there a way I can figure out the best way to know which indexes to create for a table?
Nick Ginanto
- 1,009
- 3
- 10
- 10
52
votes
4 answers
Using column alias in a WHERE clause doesn't work
Given a table users with two fields: id and email.
select id, email as electronic_mail
from (
select id, email
from users
) t
where electronic_mail = ''
Postgres complains that:
ERROR: column "electronic_mail" does not…
Victor
- 657
- 1
- 7
- 11
52
votes
3 answers
How to wrap long lines when SELECTing SQL text columns?
I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length.
From:
SELECT * FROM test;
test_id | …
jkj
- 856
- 1
- 6
- 8
52
votes
8 answers
Calculating disk space usage per MySQL DB
I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate.
What is the quickest…
GoldenNewby
- 664
- 1
- 6
- 8
51
votes
2 answers
Replace multiple columns with single JSON column
I am running PostgreSQL 9.3.4. I have a table with 3 columns:
id
name
addr
1
n1
ad1
2
n2
ad2
I need to move the data to a new table with a JSON column like:
id
data
1
{"name": "n1", "addr": "ad1"}
2
{"name": "n2", "addr":…
AliBZ
- 1,827
- 5
- 17
- 27
51
votes
3 answers
How to make MySQL table name case insensitive in Ubuntu?
I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default.
Now, I want to make MySQL work as case insensitive in Ubuntu.
Is it possible? If yes, how can I…
java baba
51
votes
5 answers
How to design a database for storing a sorted list?
I am looking to store a sorted list inside a database. I want to perform the following operations efficiently.
Insert(x) - Insert record x into the table
Delete(x) - Delete record x from the table
Before(x,n) - Return the 'n' records preceding the…
chitti
- 611
- 1
- 5
- 6
51
votes
7 answers
Why does Postgres generate an already used PK value?
I'm using Django, and every once in a while I get this error:
IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey"
DETAIL: Key (id)=(1) already exists.
My Postgres database does in fact have a myapp_mymodel object…
orokusaki
- 1,209
- 2
- 12
- 21
51
votes
8 answers
SQL Server Agent Jobs and Availability Groups
I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server Agent is not really integrated with this great…
nojetlag
- 2,927
- 9
- 34
- 42