Most Popular
1500 questions
19
votes
2 answers
What does [FROM x, y] mean in Postgres?
I'm just getting started with Postgres. Reading this document I came across this query:
SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT…
andrerpena
- 885
- 2
- 9
- 12
19
votes
1 answer
MySQL: will a transaction lock the row?
I haven't tried using MySQL transaction before, I just want to clarify something.
If two users execute a query at the very exact time, how MySQL would handle this?
e.g. the users is trying to update a record.
user1: update table set column = column…
zer09
- 453
- 1
- 4
- 10
19
votes
3 answers
High Disk I/O from sql server or is High disk I/O slowing sql server?
I've been arguing with a DBA and a couple hardware guys about performance issues on our SQL server. Normally everything is fine, however over the past few weeks we have been having huge lag spikes in sql server. Its clear that SQL Server is…
Edgey
- 191
- 1
- 1
- 4
19
votes
3 answers
What does NVL stand for?
What does NVL stand for? I'm talking about the Oracle and Informix (perhaps some others too) function used to filter out non NULL values from query results (similar to COALESCE in other databases).
newenglander
- 1,025
- 5
- 12
- 23
19
votes
6 answers
What can be the downside of always having a single integer column as primary key?
Within one Web application I am working on, all database operations are abstracted using some generic repositories defined over Entity Framework ORM.
However, in order to have a simple design for the generic repositories, all involved tables must…
Alexei
- 1,251
- 1
- 13
- 33
19
votes
1 answer
Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?
There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for instance, I can do this..
WITH w AS (
SELECT *
…
Evan Carroll
- 59,330
- 43
- 219
- 447
19
votes
2 answers
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-array_agg() looked like. For review, modern aggregation…
Evan Carroll
- 59,330
- 43
- 219
- 447
19
votes
4 answers
How do I generate a pivoted CROSS JOIN where the resulting table definition is unknown?
Given two tables with an undefined row count with a name and value, how would I display a pivoted CROSS JOIN of a function over their values.
CREATE TEMP TABLE foo AS
SELECT x::text AS name, x::int
FROM generate_series(1,10) AS t(x);
CREATE TEMP…
Evan Carroll
- 59,330
- 43
- 219
- 447
19
votes
3 answers
Is innodb_file_per_table advisable?
We have an application where just one of the table will be growing into million of lines but the rest will just below a million. So what is the advice should we go with innodb_file_per_table or leave just as one .ibd? I read some articles say do…
newbie14
- 961
- 3
- 17
- 25
19
votes
3 answers
Wrapping query in IF EXISTS makes it very slow
I have the below query :
select databasename
from somedb.dbo.bigtable l where databasename ='someval' and source <>'kt'
and not exists(select 1 from dbo.smalltable c where c.source=l.source)
The above query completes in three seconds.
If the…
TheGameiswar
- 2,919
- 4
- 26
- 48
19
votes
7 answers
Is polling the only way for updating app's data from a database?
An application needs to have data as more freshly updated from a database as possible. In such a case, is there any other way for getting the data, besides of a timer based requesting (polling) the database?
I work with a MS SQL Server 2008 (and…
rem
- 1,525
- 3
- 20
- 22
19
votes
4 answers
Differences between "Unique Key" and "Primary Key"
What are the major differences between Unique Key and Primary Key in MySQL?
newuser
- 661
- 1
- 9
- 20
19
votes
3 answers
How can I tell what recovery model my SQL database has?
Is there a SQL command I can run to determine the recovery model of my database? I want to know if its full recovery or not.
kacalapy
- 2,018
- 2
- 24
- 36
19
votes
2 answers
How do I determine why a MongoDB document is failing validation?
How do I determine why a MongoDB document insert is failing validation? All I get back is a writeError that says "Document failed validation", which isn't very helpful.
(This happens often, and I'd like to understand how to properly debug these,…
Jonathan Wheeler
- 293
- 1
- 2
- 6
19
votes
1 answer
psql 9.5: gen_random_uuid() not working
SELECT gen_random_uuid()
produces output
ERROR: function gen_random_uuid() does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
I ran CREATE EXTENSION pgcrypto; on…
d9k
- 293
- 1
- 2
- 6