Most Popular
1500 questions
30
votes
4 answers
How to find out who deleted some data SQL Server
My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters).
I thought this could be found from the transaction log (providing it hadn't…
Matt Wilko
- 402
- 1
- 4
- 7
30
votes
4 answers
Retrieving all PK and FK
I have a big database that I need to extract all primary keys and foreign keys from each table.
I have pgAdmin III.
Is there a way to do this automatically and not go over each table manually?
Nick Ginanto
- 929
- 3
- 9
- 10
30
votes
1 answer
How can I convert from Double Precision to Bigint with PostgreSQL?
I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that?
I have tried with to_bigint(myvalue) but that function didn't exist.
Jonas
- 32,975
- 27
- 61
- 64
30
votes
1 answer
ERROR: database "dbname" does not exist
According to the documentation, so long as I'm not connected to a database, I can either delete a database in the console using:
DROP DATABASE dbname;
Or I can drop it using the wrapper tool dropdb.
Both give me an error saying the database doesn't…
Sephethus
- 415
- 1
- 4
- 5
30
votes
10 answers
What is an effective way of labeling columns in a database?
I used to label columns in my databases like this:
user_id
user_name
user_password_hash
To avoid conflicts when joining two tables, but then I learnt some more on how to alias tables, and I stopped doing this.
What is an effective way of labeling…
Thomas O
- 501
- 5
- 8
30
votes
6 answers
Alter table on live production databases
How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?
I know the correct way is to backup everything schedule downtime and do then do…
NeuronQ
- 403
- 1
- 4
- 6
30
votes
3 answers
Why does ANSI SQL define SUM(no rows) as NULL?
The ANSI SQL standard defines (chapter 6.5, set function specification) the following behaviour for aggregate functions on empty result sets:
COUNT(...) = 0
AVG(...) = NULL
MIN(...) = NULL
MAX(...) = NULL
SUM(...) = NULL
Returning NULL for AVG, MIN…
Heinzi
- 3,145
- 2
- 29
- 42
30
votes
1 answer
Change existing datadir path
I want to change the datadir path in the my.ini file.
The existing datadir path is C:/ProgramData/MySQL/MySQL Server 5.1/Data/
The size of my C: drive is 30 GB and the MySQL data folder is occupying 25 GB. So I want to change the datadir to…
naveen
- 301
- 1
- 3
- 3
30
votes
4 answers
How can I get a unique array in PostgreSQL?
The following
SELECT ARRAY[a,b,c,d]
FROM ( VALUES
('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);
Returns {foo,bar,foo,baz} of type text[]. I would like to get {foo,bar,baz} of type text[] with one of the duplicate foo elements removed? Does…
Evan Carroll
- 63,051
- 46
- 242
- 479
30
votes
1 answer
Why is a scan faster than seek for this predicate?
I was able to reproduce a query performance issue that I would describe as unexpected. I'm looking for an answer that's focused on internals.
On my machine, the following query does a clustered index scan and takes about 6.8 seconds of CPU…
Joe Obbish
- 32,165
- 4
- 71
- 151
30
votes
1 answer
PostgreSQL two different ways to generate a UUID: gen_random_uuid vs uuid_generate_v4?
What is the difference between the following two functions
gen_random_uuid() provided by pgcrypto extension
uuid_generate_v4() provided by uuid-ossp extension
Are they both the same behind the scenes? Any performance impacts on using one?
user148246
30
votes
7 answers
How do I Create Tablespace in Oracle 11g?
I am not too savvy in Oracle, and I have been trying to create a TABLESPACE, which keep on giving me an error message of invalid file name. Below is the syntax that I have used:
SQL> create Tablespace HRMT
2 datafile
3 size
4 ;
size
*
ERROR at line…
user74139
30
votes
3 answers
How to set Postgresql database to see date as "MDY" permanently
How can I set my database to see the 'date' as "MDY" without running:
SET datestyle = "ISO, MDY";
every-time I'm trying to access it?
I'm using Postgresql version 9.1, Ubuntu 12.04.
My system locale, at the time of the database installation was set…
Timka
- 415
- 1
- 4
- 5
30
votes
5 answers
How do I switch off SCHEMABINDING for a view without recreating it?
How do I switch off SCHEMABINDING for a view without recreating it?
garik
- 6,722
- 10
- 43
- 56
30
votes
10 answers
How do I access Postgres when I get an error about "/var/run/postgresql/.s.PGSQL.5432"?
I am running Ubuntu 16. I have installed Postgresql. Postgresql used to work, but then I rebooted.
nmap commands show port 5432 is open. Postgres seems to be working correctly:
service postgresql status
postgresql.service - PostgreSQL RDBMS …
Victor
- 403
- 1
- 4
- 5