This tag specifies that the question is independent of any particular database and the sql should work in any rdbms.
Questions tagged [database-agnostic]
100 questions
0
votes
1 answer
How to avoid concurrency issues when using a database as a communication bus?
Some people tell me that we should avoid multiple apps reading/writing to the same database, because of concurrency issues. They were insistent to the point where they convinced me that a solid architecture only allows a single application to access…
Jader Dias
- 88,211
- 155
- 421
- 625
0
votes
3 answers
Why does this query only return results with non-empty child tables?
This is a simplified version of a query we are running where we need to find all rows in the main parent table where the child rows match. The query below returns no results when one of the child tables is empty.
The main table has two child…
Blair Zajac
- 4,555
- 3
- 25
- 36
0
votes
1 answer
Comparing various date/time types
Is it defined how a date should be compared to a timestamp with a timezone or a timestamp without a timezone? For example, something like:
SELECT
DATE '2014-01-01' = TIMESTAMP WITH TIME ZONE '2014-01-01',
DATE '2014-01-01' = TIMESTAMP…
David542
- 104,438
- 178
- 489
- 842
0
votes
0 answers
Database agnostic views with liquibase
I am facing a problem related to the creation of views across multiple RDBMS.
I started from a db in SQLite with a view like this:
create view V001 (viewfield1, viewfield2) as
select field1, strftime(''%Y-%m-%d %H:%M:%S'', field2) from T001;
being…
Tomaso Tosolini
- 233
- 2
- 9
0
votes
0 answers
Databases that support the ::type cast syntax
I'm having a hard time searching to find which databases support the non-standard CAST operator that postgres defines which allows for doing something like:
SELECT '01-OCT-2015'::DATE
I know it's supported in Postgres, but was having a hard time…
David542
- 104,438
- 178
- 489
- 842
0
votes
1 answer
Database Application - Store or compute on-the-fly?
I have a table of purchase list with fields: ItemName, Quantity, UnitPrice, Amount. Note that Amount is equal to Quantity * UnitPrice.
My simple problem is, should I STORE the amount or COMPUTE it when retrieving the data? To what should I concern…
kazinix
- 28,987
- 33
- 107
- 157
0
votes
5 answers
Query to simulate a transaction log
Let's say I have the following table with data:
sales:
id
timestamp
product
price
1
2014-01-01 01:02:03
phone
14.99
2
2014-01-01 03:02:03
car
1200.00
And then we have transactions stored in a separate…
David542
- 104,438
- 178
- 489
- 842
0
votes
0 answers
Database agnostic query to fetch specific n number of records
I am looking for ways to run SELECT * FROM TABLE query across different databases.
To name a few : postgres, teradata, mysql, bigquery, redshift.
I want to find a way to parallelize this query so I can spawn multiple threads to read the data.
Each…
Dark Angel
- 107
- 9
0
votes
1 answer
Is there a more database agnostic way to write this default scope in Rails 3?
I have the following default scope defined in one of my models
default_scope order("IF(format = #{FORMATS[:wide]}, 1, 0) DESC, created_at DESC, name ASC")
It worked fine on my dev machine where I'm running MySQL, but borked when deployed to…
Chris Bloom
- 3,526
- 1
- 33
- 47
0
votes
1 answer
ActiveRecord aggregate function: is there a database-agnostic 'EXTRACT(WEEK from date)'?
i have a method that works just fine (with rails 3 and PostgreSQL) ; i just wonder if it's possible to make it database-agnostic :
FarmGatePrice.average :price, :group => 'EXTRACT(WEEK FROM date)'
As i understand it, methods to extract a week ISO…
m_x
- 12,357
- 7
- 46
- 60
0
votes
1 answer
The Name-Value-Pair Model For configuration-only data
I read that the Name-Value-Pair Model in database design is an anti-pattern. Essentially you have a table with two columns. One column is called 'name' and the other column is called 'value'. Let's say you are managing AWS configuration for…
Daniel Viglione
- 8,014
- 9
- 67
- 101
0
votes
5 answers
Why is this kind of foreign keys possible?
Why does the SQL Standard accept this? Which are the benefits?
If have those tables:
create table prova_a (a number, b number);
alter table prova_a add primary key (a,b);
create table prova_b (a number, b number);
alter table prova_b add foreign key…
FerranB
- 35,683
- 18
- 66
- 85
0
votes
1 answer
Use of LIKE in PostgreSQL with brackets
I try to be so specific as possible.
Currently I use MS SQL Server 2012.
A simplified table PlanMission contain these rows
|---------------------|---------------------|
| Bold_Id | MCurrentStates …
Roland Bengtsson
- 5,058
- 9
- 58
- 99
0
votes
2 answers
Many to many self reference without id numbered
I want to make a relationship between two Tags entities, but I don't like the typical way it is handled in the RDBMS databases.
Like here: https://stackoverflow.com/a/35784048/1624397
INSERT INTO RECOMMENDED_BOOKS (Book_id1, Book_id2) VALUES (1,…
forsberg
- 1,681
- 1
- 21
- 27
0
votes
4 answers
Is it crazy to bypass database case sensitivity issues by storing original string case AND lower case?
I'm implementing a database where several tables have string data as candidate keys (eg: username) and will be correspondingly indexed. For these fields I want:
Case insensitivity when someone queries the table on those keys
The initially written…
Russ
- 10,835
- 12
- 42
- 57