Most Popular
1500 questions
20
votes
2 answers
Difference between star schema and data cube?
I am involved in a new project where I have to create a data cube from the existing relational database system. I understood that the existing system is not properly designed, and I am not sure where to start.
My question are:
What is difference…
Rathish Kumar B
- 2,304
- 5
- 24
- 36
20
votes
7 answers
Efficiently select beginning and end of multiple contiguous ranges in Postgresql query
I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there are gaps.
This query generates an example…
Stew
- 483
- 2
- 4
- 9
20
votes
4 answers
PostgreSQL 9.1 Hot Backup Error: the database system is starting up
I have been working on a hot backup for Postgres 9.1 for awhile and have run into a consistent issue. After restarting Postgres on the slave server the pgstartup log file and the daily log file under the pg_log directory reads with no errors.…
Jen
20
votes
1 answer
Querying non-ASCII rows from Postgres
Does [:ascii:] class work in Postgres at all? It is not listed in their help, however I see examples in the web which utilize it.
I have a UTF-8 database, where collation and c_type are en_US.UTF-8, and Postgres version is 9.6.2.
When I search for…
Suncatcher
- 367
- 2
- 4
- 12
20
votes
4 answers
PostgreSQL: Generated Columns
Does PostgreSQL support generated columns? Also know as virtual columns. I am not talking about IDENTITY columns.
I can’t find any information on this remarkable feature but I know that it is available on SQL Server, and in the latest versions of…
Manngo
- 2,819
- 8
- 34
- 56
20
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,055
- 5
- 13
- 23
20
votes
1 answer
Nonclustered index storage on clustered columnstore
In SQL Server, a non-unique nonclustered index on a rowstore table incorporates the base object's bookmark (RID or clustering key) at all levels of the nonclustered index structure. The bookmark is stored as part of the nonclustered index key at all…
Paul White
- 83,961
- 28
- 402
- 634
20
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
- 63,051
- 46
- 242
- 479
20
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
- 969
- 3
- 18
- 25
20
votes
2 answers
Why is this join cardinality estimate so large?
I am experiencing what I think is an impossibly high cardinality estimate for the following query:
SELECT dm.PRIMARY_ID
FROM
(
SELECT COALESCE(d1.JOIN_ID, d2.JOIN_ID, d3.JOIN_ID) PRIMARY_ID
FROM X_DRIVING_TABLE dt
LEFT OUTER JOIN…
Joe Obbish
- 32,165
- 4
- 71
- 151
20
votes
4 answers
Why can't I use variables in T-SQL like I imagine I can?
Forgive me, I am a developer who has moved over to the world of SQL. I thought I could improve some SQL by adding variables but it did not function like I expected. Can someone tell me why this does not work? I don't want a work around, I want to…
gareth
- 369
- 3
- 8
20
votes
4 answers
Differences between "Unique Key" and "Primary Key"
What are the major differences between Unique Key and Primary Key in MySQL?
newuser
- 681
- 1
- 9
- 20
20
votes
1 answer
Use Where Clause With Merge
In the below syntax with the When Matched is it possible to only update if the value in the update table differs from the value in the employee table? Something similar to my below DDL - but of course this throws an error and does not work.
What…
SmallFries BigGuys
- 479
- 5
- 10
- 22
20
votes
3 answers
Comparing Columns that can contain NULLS - is there a more elegant way?
I know that you can't compare a value to NULL and expect a result without adding something like in the following code...
SELECT
*
FROM
A INNER JOIN
B ON A.ID = B.ID
WHERE
A.STRING <> B.STRING OR (A.STRING IS NULL AND B.STRING IS…
GWR
- 2,807
- 9
- 33
- 42
20
votes
2 answers
Why is AT TIME ZONE nondeterministic?
SQL Server 2016's AT TIME ZONE appears to be nondeterministic. However, I haven't been able to find documentation officially stating this or giving a rationale as to the reasoning behind it.
Why is AT TIME ZONE nondeterministic?
Example Showing…
Ben Gribaudo
- 659
- 4
- 13