Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
123
votes
2 answers

Difference between now() and current_timestamp

In PostgreSQL, I use the now() and current_timestamp function and I see no difference: # SELECT now(), current_timestamp; now | now …
JohnMerlino
  • 1,939
  • 5
  • 20
  • 21
122
votes
11 answers

When to use NULL and when to use an empty string?

I'm interested mainly in MySQL and PostgreSQL, but you could answer the following in general: Is there a logical scenario in which it would be useful to distinguish an empty string from NULL? What would be the physical storage implications for…
Maniero
  • 2,758
  • 6
  • 28
  • 29
122
votes
3 answers

Is it a bad practice to always create a transaction?

Is it a bad practice to always create a transaction? For example, it is a good practice to create a transaction for nothing but one simple SELECT? What is the cost of creating a transaction when it is not really necessary? Even if you are using an…
elranu
  • 1,323
  • 2
  • 9
  • 7
121
votes
5 answers

Best database and table design for billions of rows of data

I am writing an application that needs to store and analyze large amounts of electrical and temperature data. Basically I need to store large amounts of hourly electricity usage measurements for the past several years and for many years to come for…
Gecata
  • 1,313
  • 3
  • 9
  • 5
119
votes
4 answers

Why are numbers tables "invaluable"?

Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table: USE Model GO CREATE TABLE Numbers ( Number INT NOT NULL, CONSTRAINT PK_Numbers PRIMARY KEY…
Jeff Atwood
  • 2,374
  • 2
  • 21
  • 15
118
votes
11 answers

How to run psql on Mac OS X?

I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available. I get this message: psql -bash: psql: command not found Do I have to…
Jonas
  • 33,945
  • 27
  • 62
  • 64
117
votes
5 answers

How to safely change MySQL innodb variable 'innodb_log_file_size'?

So I'm fairly new to tuning InnoDB. I'm slowly changing tables (where necessary) from MyIsam to InnoDB. I've got about 100MB in innodb, so I increased the innodb_buffer_pool_size variable to 128MB: mysql> show variables like…
Derek Downey
  • 23,568
  • 11
  • 79
  • 104
116
votes
7 answers

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign keys referencing the main schema and not the other…
jd.
  • 1,262
  • 2
  • 9
  • 6
114
votes
3 answers

What is the search_path for a given database and user?

I can see the current search_path with: show search_path ; And I can set the search_path for the current session with: set search_path = "$user", public, postgis; As well, I can permanently set the search_path for a given database with: alter…
user664833
  • 1,969
  • 3
  • 21
  • 19
114
votes
6 answers

SQL Server Management Studio 18 won't open (only splash screen pops up)

I just installed SSMS 18 GA on a computer with only VS2019 installed, and when I try to open SSMS the splash screen will come up, but then the process exits. Running ssms with the -log parameter reveals an error message: CreateInstance failed for…
Mitch
  • 2,688
  • 2
  • 19
  • 24
112
votes
3 answers

ALTER TABLE - Rename a column

This is driving me nuts! I want to rename a column from read-more to read_more in my blog table I tried all this: ALTER TABLE blog RENAME COLUMN read-more to read_more; ALTER TABLE blog CHANGE COLUMN 'read-more' 'read_more' VARCHAR(255) NOT…
Bojan
  • 1,123
  • 2
  • 7
  • 4
111
votes
3 answers

What is the default order of records for a SELECT statement in MySQL?

Suppose you have the following table and data: create table t ( k int, v int, index k(k) ) engine=memory; insert into t (k, v) values (10, 1), (10, 2), (10, 3); When issuing select * from t where k = 10 with no order…
daisy
  • 1,338
  • 3
  • 11
  • 17
111
votes
5 answers

Storing vs calculating aggregate values

Are there any guidelines or rules of thumb to determine when to store aggregate values and when to calculate them on the fly? For example, suppose I have widgets which users can rate (see schema below). Each time I display a widget I could…
BenV
  • 4,923
  • 7
  • 40
  • 38
111
votes
6 answers

Safest way to perform mysqldump on a live system with active reads and writes?

I'm not sure if this is true but I remember reading if you run the following command in linux mysqldump -u username -p database_name > backup_db.sql while reads and writes are being made to a database then the dump may contain errors. Are there…
user784637
  • 1,245
  • 2
  • 9
  • 7
110
votes
2 answers

Connect to SQL Server with Windows Authentication in a different domain

I am trying to connect to a remote SQL Server on a VPN in a different domain. When I enter the Server name on the SQL Server and choose Additional Connection Parameters to add some extra stuff needed by my school: Integrated Security=SSPI; User…
stergosz
  • 1,225
  • 2
  • 10
  • 7
1 2 3
…
99 100