All Questions
45,138
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
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 ...
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
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
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
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
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
494
votes
34
answers
1.1m
views
How to select the nth row in a SQL database table?
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native ...
- 20.5k
492
votes
14
answers
620k
views
How to log PostgreSQL queries?
How to enable logging of all SQL executed by PostgreSQL 8.3?
Edited (more info)
I changed these lines :
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
...
- 12.6k
478
votes
17
answers
797k
views
Declare a variable in a PostgreSQL query
How do I declare a variable for use in a PostgreSQL 8.3 query?
In MS SQL Server I can do this:
DECLARE @myvar INT;
SET @myvar = 5/
SELECT * FROM somewhere WHERE something = @myvar;
How do I do ...
- 60.6k
463
votes
26
answers
321k
views
How can I see the raw SQL queries Django is running?
Is there a way to show the SQL that Django is running while performing a query?
- 78.5k
438
votes
37
answers
956k
views
Error Code: 2013. Lost connection to MySQL server during query
I got the Error Code: 2013. Lost connection to MySQL server during query error when I tried to add an index to a table using MySQL Workbench.
I also noticed that it appears whenever I run long query.
...
- 11k
426
votes
21
answers
1.2m
views
Error 1046 No database selected, how to resolve?
Error
SQL query:
--
-- Database: `work`
--
-- --------------------------------------------------------
--
-- Table structure for table `administrators`
--
CREATE TABLE IF NOT EXISTS `administrators` ...
- 4,277
426
votes
11
answers
336k
views
Best database field type for a URL
I need to store a url in a MySQL table. What's the best practice for defining a field that will hold a URL with an undetermined length?
- 7,936
413
votes
5
answers
202k
views
What's the difference between CharField and TextField in Django?
The documentation says that CharField() should be used for smaller strings and TextField() should be used for larger strings.
Okay, but where is the line drawn between "small" and "...
- 4,637
412
votes
7
answers
338k
views
What datatype to use when storing latitude and longitude data in SQL databases? [duplicate]
When storing latitude or longitude data in an ANSI SQL compliant database, what datatype would be most appropriate? Should float be used, or decimal, or ...?
I'm aware that Oracle, MySql, and SQL ...
- 41.3k
402
votes
10
answers
898k
views
How to replace a string in a SQL Server Table Column
I have a table (SQL Sever) which references paths (UNC or otherwise), but now the path is going to change.
In the path column, I have many records and I need to change just a portion of the path, ...
- 7,238
390
votes
33
answers
593k
views
What's the difference between TRUNCATE and DELETE in SQL
What's the difference between TRUNCATE and DELETE in SQL?
If your answer is platform specific, please indicate that.
- 51.9k
386
votes
10
answers
1.1m
views
Altering column size in SQL Server
How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)
- 7,299
383
votes
7
answers
168k
views
T-SQL Cast versus Convert
What is the general guidance on when you should use CAST versus CONVERT? Is there any performance issues related to choosing one versus the other? Is one closer to ANSI-SQL?
- 70.5k
365
votes
11
answers
531k
views
What's the fastest way to do a bulk insert into Postgres? [closed]
I need to programmatically insert tens of millions of records into a Postgres database. Presently, I'm executing thousands of insert statements in a single query.
Is there a better way to do this, ...
- 25.1k
355
votes
7
answers
370k
views
Can table columns with a Foreign Key be NULL?
I have a table which has several ID columns to other tables.
I want a foreign key to force integrity only if I put data in there. If I do an update at a later time to populate that column, then it ...
- 3,746
341
votes
11
answers
549k
views
Is there any boolean type in Oracle databases?
Is there any Boolean type in Oracle databases, similar to the BIT datatype in Ms SQL Server?
- 3,411
330
votes
14
answers
582k
views
What is the difference between JOIN and UNION?
What is the difference between JOIN and UNION? Can I have an example?
- 61.6k
329
votes
6
answers
661k
views
How to compare dates in datetime fields in Postgresql?
I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows).
I have a column in my table say update_date with type timestamp without timezone. Client can search ...
- 3,309
327
votes
13
answers
1.2m
views
How do I list all the columns in a table?
For the various popular database systems, how do you list all the columns in a table?
- 12.2k
326
votes
7
answers
273k
views
What is the difference between single and double quotes in SQL?
What is the difference between single quotes and double quotes in SQL?
- 5,179
326
votes
10
answers
255k
views
Storing JSON in database vs. having a new column for each key
I am implementing the following model for storing user related data in my table - I have 2 columns - uid (primary key) and a meta column which stores other data about the user in JSON format.
uid ...
- 8,734
325
votes
11
answers
533k
views
Best way to check for "empty or null value"
What is best way to check if value is null or empty string in Postgres sql statements?
Value can be long expression so it is preferable that it is written only once in check.
Currently I'm using:
...
- 27k
324
votes
16
answers
262k
views
What's faster, SELECT DISTINCT or GROUP BY in MySQL?
If I have a table
CREATE TABLE users (
id int(10) unsigned NOT NULL auto_increment,
name varchar(255) NOT NULL,
profession varchar(255) NOT NULL,
employer varchar(255) NOT NULL,
PRIMARY KEY ...
- 25.1k
312
votes
4
answers
180k
views
What does ON [PRIMARY] mean?
I'm creating an SQL setup script and I'm using someone else's script as an example. Here's an example of the script:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[...
- 3,910
310
votes
4
answers
398k
views
How do I interpret precision and scale of a number in a database?
I have the following column specified in a database: decimal(5,2)
How does one interpret this?
According to the properties on the column as viewed in SQL Server Management studio I can see that it ...
- 28.4k
307
votes
14
answers
485k
views
How to randomly select rows in SQL?
I am using MSSQL Server 2005. In my db, I have a table "customerNames" which has two columns "Id" and "Name" and approx. 1,000 results.
I am creating a functionality where I have to pick 5 customers ...
- 27k
306
votes
25
answers
239k
views
Why do you create a View in a database?
When and Why does some one decide that they need to create a View in their database? Why not just run a normal stored procedure or select?
- 15.2k
305
votes
16
answers
112k
views
Why is SELECT * considered harmful?
Why is SELECT * bad practice? Wouldn't it mean less code to change if you added a new column you wanted?
I understand that SELECT COUNT(*) is a performance problem on some DBs, but what if you ...
- 22.5k
302
votes
15
answers
650k
views
Difference between primary key and unique key
I'm using a MySQL database.
In which situations should I create a unique key or a primary key?
- 3,079
300
votes
20
answers
829k
views
SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
I need to write a query on SQL server to get the list of columns in a particular table, its associated data types (with length) and if they are not null. And I have managed to do this much.
But now ...
- 6,864
300
votes
3
answers
383k
views
Correct use of transactions in SQL Server
I have 2 commands and need both of them executed correctly or none of them executed. So I think I need a transaction, but I don't know how to use it correctly.
What's the problem with the following ...
- 13.4k
298
votes
8
answers
184k
views
Max length for client ip address [duplicate]
Possible Duplicate:
Maximum length of the textual representation of an IPv6 address?
What would you recommend as the maximum size for a database column storing client ip addresses? I have it set ...
- 7,104
293
votes
26
answers
422k
views
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
Why does add a foreign key to the tblDomare table result in this error?
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK__tblDomare__PersN__5F7E2DAC". The conflict ...
- 2,949
290
votes
10
answers
614k
views
Find rows that have the same value on a column in MySQL
In a [member] table, some rows have the same value for the email column.
login_id | email
---------|---------------------
john | [email protected]
peter | [email protected]
johnny | ...
- 8,627
289
votes
4
answers
253k
views
Count the occurrences of DISTINCT values
I am trying to find a MySQL query that will find DISTINCT values in a particular field, count the number of occurrences of that value and then order the results by the count.
example db
id ...
- 4,361