Questions tagged [database]
A database is an organized collection of data. It is the collection of schemas, tables, queries, reports, views, and other objects. The data are typically organized to model aspects of reality in a way that supports processes requiring information. Use this tag if you have questions about designing a database. If it is about a particular database management system, (e.g., MySQL), please use that tag instead.
195,229
questions
2883
votes
7
answers
1.1m
views
How does database indexing work? [closed]
Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level?
For information on queries to index a field, check out How ...
- 83.6k
2768
votes
57
answers
5.1m
views
How to import an SQL file using the command line in MySQL?
I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line.
I have a Windows Server 2008 R2 installation. I placed the .sql file on the C ...
- 39.7k
2476
votes
29
answers
2.8m
views
How to show tables in PostgreSQL?
What's the equivalent to show tables (from MySQL) in PostgreSQL?
- 267k
2265
votes
19
answers
725k
views
What is the "N+1 selects problem" in ORM (Object-Relational Mapping)?
The "N+1 selects problem" is generally stated as a problem in Object-Relational mapping (ORM) discussions, and I understand that it has something to do with having to make a lot of database queries ...
- 25.1k
2181
votes
25
answers
1.5m
views
PostgreSQL "DESCRIBE TABLE"
How do you perform the equivalent of Oracle's DESCRIBE TABLE in PostgreSQL with psql command?
- 23.2k
1841
votes
27
answers
3.4m
views
Insert into ... values ( SELECT ... FROM ... )
I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the ...
- 41.9k
1598
votes
8
answers
338k
views
What are the options for storing hierarchical data in a relational database?
Good Overviews
Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the ...
1350
votes
24
answers
1.1m
views
Which version of PostgreSQL am I running?
I'm in a corporate environment (running Debian Linux) and didn't install it myself. I access the databases using Navicat or phpPgAdmin (if that helps). I also don't have shell access to the server ...
- 39k
1151
votes
46
answers
1.6m
views
How do I rename a MySQL database (change schema name)?
How do I quickly rename a MySQL database (change its schema name)?
Usually I just dump a database and re-import it with a new name. This is not an option for very big databases. Apparently RENAME {...
1011
votes
25
answers
1.7m
views
Reset identity seed after deleting records in SQL Server
I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each ...
- 18.5k
981
votes
13
answers
618k
views
Optimistic vs. Pessimistic locking
I understand the differences between optimistic and pessimistic locking. Now, could someone explain to me when I would use either one in general?
And does the answer to this question change depending ...
- 195k
919
votes
23
answers
719k
views
Database, Table and Column Naming Conventions? [closed]
Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:
Should table names be plural?
Should column ...
- 75k
892
votes
25
answers
305k
views
MyISAM versus InnoDB [closed]
I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads ...
- 9,251
885
votes
15
answers
518k
views
What's the difference between identifying and non-identifying relationships?
I haven't been able to fully grasp the differences. Can you describe both concepts and use real world examples?
- 9,512
865
votes
5
answers
435k
views
Multiple Indexes vs Multi-Column Indexes
What is the difference between creating one index across multiple columns versus creating multiple indexes, one per column?
Are there reasons why one should be used over the other?
For example:
Create ...
- 75k
814
votes
11
answers
833k
views
How to get size of mysql database?
How to get size of a mysql database?
Suppose the target database is called "v3".
- 8,445
814
votes
10
answers
470k
views
Difference between scaling horizontally and vertically for databases [closed]
I have come across many NoSQL databases and SQL databases. There are varying parameters to measure the strength and weaknesses of these databases and scalability is one of them. What is the difference ...
- 27.8k
764
votes
6
answers
316k
views
MongoDB vs. Cassandra [closed]
I am evaluating what might be the best migration option.
Currently, I am on a sharded MySQL (horizontal partition), with most of my data stored in JSON blobs. I do not have any complex SQL queries (...
- 27.8k
763
votes
8
answers
1.6m
views
'IF' in 'SELECT' statement - choose output value based on column values
SELECT id, amount FROM report
I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?
- 14k
746
votes
10
answers
920k
views
Create new user in MySQL and give it full access to one database
I want to create a new user in MySQL and give it full access only to one database, say dbTest that I create with a command like create database dbTest;. What would be the MySQL commands to do that?
- 7,477
738
votes
12
answers
426k
views
Postgres: upgrade a user to be a superuser?
In postgres, how do I change an existing user to be a superuser? I don't want to delete the existing user, for various reasons.
# alter user myuser ...?
- 10.7k
738
votes
22
answers
930k
views
Solutions for INSERT OR UPDATE on SQL Server
Assume a table structure of MyTable(KEY, datafield1, datafield2...).
Often I want to either update an existing record, or insert a new record if it doesn't exist.
Essentially:
IF (key exists)
run ...
- 30k
731
votes
24
answers
514k
views
How to drop columns using Rails migration
What's the syntax for dropping a database table column through a Rails migration?
- 9,668
726
votes
10
answers
239k
views
How can I avoid concurrency problems when using SQLite on Android?
What would be considered the best practices when executing queries on an SQLite database within an Android app?
Is it safe to run inserts, deletes and select queries from an AsyncTask's ...
- 42.8k
724
votes
6
answers
438k
views
Difference between rake db:migrate db:reset and db:schema:load
The difference between rake db:migrate and rake db:reset is pretty clear to me. The thing which I don't understand is how rake db:schema:load is different from the former two.
Just to be sure that I ...
- 14.7k
699
votes
22
answers
1.2m
views
Import SQL dump into PostgreSQL database
We are switching hosts and the old one provided a SQL dump of the PostgreSQL database of our site.
Now, I'm trying to set this up on a local WAMP server to test this.
The only problem is that I don'...
- 8,452
684
votes
11
answers
896k
views
How do you query for "is not null" in Mongo?
I would like to execute a following query:
db.mycollection.find(HAS IMAGE URL)
What should be the correct syntax?
- 266k
678
votes
12
answers
459k
views
How to skip certain database tables with mysqldump?
Is there a way to restrict certain tables from the mysqldump command?
For example, I'd use the following syntax to dump only table1 and table2:
mysqldump -u username -p database table1 table2 > ...
670
votes
16
answers
553k
views
Export schema without data
I'm using a MySql database with a Java program, now I want to give the program to somebody else.
How to export the MySQL database structure without the data in it, just the structure?
- 9,615
669
votes
19
answers
1.1m
views
How do I restore a dump file from mysqldump?
I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine.
I tried using MySQL Administrator, but I got the following error:
The selected file was ...
- 56.6k
667
votes
26
answers
514k
views
How to insert multiple rows in SQLite?
In MySQL you can insert multiple rows like this:
INSERT INTO 'tablename' ('column1', 'column2') VALUES
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2'),
('data1', 'data2');
...
- 233k
658
votes
11
answers
290k
views
Difference between partition key, composite key and clustering key in Cassandra?
I have been reading articles around the net to understand the differences between the following key types. But it just seems hard for me to grasp. Examples will definitely help make understanding ...
- 30.8k
645
votes
66
answers
605k
views
Postgres could not connect to server
After I did brew update and brew upgrade, my postgres got some problem. I tried to uninstall postgres and install it again, but it didn't work as well.
This is the error message. (I also got this ...
- 6,613
643
votes
13
answers
523k
views
Copying PostgreSQL database to another server
I'm looking to copy a production PostgreSQL database to a development server. What's the quickest, easiest way to go about doing this?
- 13.3k
619
votes
29
answers
1.0m
views
Elasticsearch query to return all records
I have a small database in Elasticsearch and for testing purposes would like to pull all records back. I am attempting to use a URL of the form...
http://localhost:9200/foo/_search?pretty=true&q=...
- 30.7k
617
votes
58
answers
141k
views
Do you use source control for your database items? [closed]
I feel that my shop has a hole because we don't have a solid process in place for versioning our database schema changes. We do a lot of backups so we're more or less covered, but it's bad practice to ...
602
votes
25
answers
806k
views
How to get a list of column names on Sqlite3 database?
I want to migrate my iPhone app to a new database version. Since I don't have some version saved, I need to check if certain column names exist.
This Stackoverflow entry suggests doing the select
...
- 6,561
595
votes
12
answers
297k
views
How do you rename a MongoDB database?
There's a typo in my MongoDB database name and I'm looking to rename the database.
I can copy and delete like so...
db.copyDatabase('old_name', 'new_name');
use old_name
db.dropDatabase();
Is there ...
- 8,964
581
votes
10
answers
625k
views
How to shrink/purge ibdata1 file in MySQL
I am using MySQL in localhost as a "query tool" for performing statistics in R, that is, everytime I run a R script, I create a new database (A), create a new table (B), import the data into B, submit ...
- 24.3k
566
votes
40
answers
128k
views
Database development mistakes made by application developers [closed]
What are common database development mistakes made by application developers?
559
votes
16
answers
206k
views
Room - Schema export directory is not provided to the annotation processor so we cannot export the schema
I am using Android Database Component Room
I've configured everything, but when I compile, Android Studio gives me this warning:
Schema export directory is not provided to the annotation processor ...
- 12.7k
558
votes
25
answers
809k
views
Kill a postgresql session/connection
How can I kill all my postgresql connections?
I'm trying a rake db:drop but I get:
ERROR: database "database_name" is being accessed by other users
DETAIL: There are 1 other session(s) using the ...
- 17.9k
553
votes
34
answers
1.5m
views
Restore a postgres backup file using the command line?
Locally, I use pgadmin3. On the remote server, however, I have no such luxury.
I've already created the backup of the database and copied it over, but is there a way to restore a backup from the ...
- 10.3k
553
votes
6
answers
451k
views
MySQL: @variable vs. variable. What's the difference?
In another question I posted someone told me that there is a difference between:
@variable
and:
variable
in MySQL. He also mentioned how MSSQL has batch scope and MySQL has session scope. Can ...
- 36.7k
552
votes
29
answers
2.0m
views
MySQL Error: : 'Access denied for user 'root'@'localhost'
Consider:
./mysqladmin -u root -p** '_redacted_'
Output (including typing the password):
Enter password:
mysqladmin: connect to server at 'localhost' failed error:
'Access denied for user 'root'@'...
- 5,761
542
votes
25
answers
967k
views
How should I resolve --secure-file-priv in MySQL?
I am learning MySQL and tried using a LOAD DATA clause. When I used it as below:
LOAD DATA INFILE "text.txt" INTO table mytable;
I got the following error:
The MySQL server is running with the --...
- 9,226
540
votes
30
answers
821k
views
How can I stop redis-server?
I apparently have a redis-server instance running because when I try to start a new server by entering redis-server, I'm greeted with the following:
Opening port: bind: Address already in use
I can'...
- 18.7k
538
votes
8
answers
650k
views
Maximum length for MySQL type text
I'm creating a form for sending private messages and want to set the maxlength value of a textarea appropriate to the max length of a text field in my MySQL database table. How many characters can a ...
- 22.3k
537
votes
8
answers
429k
views
Best way to store password in database [closed]
I am working on a project that has to have authentication (username and password)
It also connects to a database, so I figured I would store the username and password there. However, it seems like ...
- 11.6k
514
votes
20
answers
615k
views
Show Procedures/Functions MySQL Command Line
How can I see the list of the stored procedures or stored functions in mysql command line like SHOW TABLES; or SHOW DATABASES; commands.
- 15.3k