Calculations between 2 records of 2 tables need to extend to more then 1300 fields. How and which database can achieve this?

Tag: database,postgresql,database-design

For one of our projects we are using PostgreSQL and access two tables with complex SQL calculations due to performance advantages.

Each table, lets call them TableA and TableB, have 1301 fields of type double precision (field number restriction by PostgreSQL!) The field names for TableA are: id, A1, A2, ..., A1300 and for TableB: id, B2, ..., B1300.

We are using many dynamically changing formulas which can directly access the two tables with a simple id join to do calculations over the numeric data in the records.

An example formula would be: A99 * ((A1 * B3) / (B5 * B88))

So the program can simply take the formula and directly use it in its SQL like:

select A99 * ((A1 * B3) / (B5 * B88)) as ResultValue
from TableA A
join TableB B on A.id = B.id

This worked fast in one DB request to 2 indexed tables. But over time, the system has grown, and all the available fields are used up.

Now, the requirement is to have nearly no restriction in the amount of used fields, to accommodate much longer and more complex calculations in one SQL select over 2 tables. I must admit I don't know much about many other new database systems and their capabilities for such a problem. Are there databases which would support such a concept or another way to have those calculations done in the database? The old way was very flexible for the time being, but is there a more efficient way to implement the same logic? Reading out the data dynamically in to the program, parsing it and calculating with it will probably take much longer. I also think just using another database which allows defining tables with more then 1300 fields would be a short term solution, until we need more fields.

Best How To :

As an example to my comment above, using an array rather than discrete fields for each value:

create table tablea (
  id int not null,
  a numeric[]
);    

create table tableb (
  id int not null,
  b numeric[]
);

insert into tablea
select 2, array (select generate_series(1,10000));

insert into tableb
select 2, array (select generate_series(1,10000));


select
  A[99] * ((A[1] * B[3]) / (B[5] * B[88])) 
from
  tablea
  join tableb on
    tablea.id = tableb.id
where
  tablea.id = 2;

ElasticSearch asynchronous post

database,post,asynchronous,elasticsearch,get

To ensure data is available, you can make a refresh request to corresponding index before GET/SEARCH: http://localhost:9200/your_index/_refresh Or refresh all indexes: http://localhost:9200/_refresh ...

How to get second row in PostgreSQL?

sql,postgresql

This query uses WITH construction that works similar to sub-queries. Investigate this query with EXPLAIN before use in production because it may be slow on big tables: WITH orders AS ( SELECT email , first_value(dt_cr) OVER wnd1 AS min_date , nth_value(dt_cr, 2) OVER wnd1 AS second_date FROM orders WINDOW wnd1...

IBM Cognos _days_between function not working

mysql,database,date,cognos

The Cognos _days_between function works with dates, not with datetimes. Some databases, like Oracle, store all dates with a timestamp. On a query directly to the datasource, try using the database's functions to get this data instead. When possible, this is always preferable as it pushes work to the database,...

In simple RESTful design, does PATCH imply mapping to CRUD's (ORM's) “update” and PUT to “destroy”+“create” (to replace a resource)?

database,rest,http,orm,crud

Well, Both the actions actually means update, where PUT is full update and PATCH is partial update. In case of PUT you already know the identifier of the resource and the resource already exists, so it is not a create and delete action per se. Infact, you can make do...

Desktop Database with Server without installation

java,database,server,desktop,h2

Like Jayan told me in a comment to my question embedded mode does accept a location to save the data.

creating stored procedure in mysql calculate profit from product table

mysql,sql,database,stored-procedures

You forgot the () after the procedure name. Also, you should set a delimiter: DELIMITER // CREATE PROCEDURE sp_profit() BEGIN SET @v1:= (select sum( cost_price * current_stock) from product); SET @v2:= (select sum( selling_price * current_stock) from product); SELECT (@v2 - @v1); END; // ...

PostgreSQL conditional statement

sql,postgresql,if-statement

Try this: with c as (select count(*) cnt from table1) select table2.* from table2, c where c.cnt < 1 union all select table3.* from table3, c where c.cnt >= 1 ...

Order by count not sorting the records correctly?

php,mysql,database

Try this SELECT count(receiver_id) as total_receiver FROM gr_group_memberships INNER JOIN gr_group on gr_group_memberships.group_id = gr_group.id GROUP BY gr_group_memberships.receiver_id ORDER BY gr_group_memberships.receiver_id DESC I think it will worked what you want...

Postgresql Update JDBC

java,postgresql,jdbc

You shouldn't build SQL by putting your variables directly via string concatenation. What happens here is that with 11, your SQL becomes: set last=11 Which is valid SQL (using 11 as a integer literal), while with xx it becomes: set last=xx There are no quotes, so the SQL means you're...

How do I access website databases? [closed]

database

In barely any case would you get direct access to another company's database, even if you are affiliated. The most common way would be an API (which might be public or licensed): https://en.wikipedia.org/wiki/Application_programming_interface If the data is publicly available on a website, some do content parsing although this is not...

ODBC ISAM_EOF without any reason

c#,database,odbc,cobol

It seems to be Windows UAC reliant. As our application run in compatibility mode, UAC visualization is active and causing may some problems. The reason for this is, that the COBOL databse is a file based database, and the client where are coding for uses these files in ODBC DSN...

How to order SQL query result on condition?

sql,postgresql,order,condition

Use CASE expression in ORDER BY clause: SELECT category FROM ( SELECT DISTINCT category FROM merchant ) t ORDER BY CASE WHEN category = 'General' THEN 0 ELSE 1 END, category ASC CASE guarantees that rows with General will be sorted first. The second argument orders the rest of the...

In PostgreSQL is it possible to join between table and function?

sql,postgresql

Your table uses a carid value to retrieve the corresponding part_ids from function PartsPerCar() which returns a set of rows, a so-called table function. In sub-section 5 (keep on reading) we see that Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions...

echo both users

php,mysql,sql,database,loops

Why don't you just do it in one single query? Just replace the necessary table and column name, and variables/values/parameters to be bind in your query: $query = mysqli_query($conn, "SELECT first_name, last_name, description, role FROM `wp_usermeta` WHERE `first_name` = '$first_name' OR `last_name` = '$last_name' OR `description` = '$description' OR `role`...

Database object with different data

sql,asp.net,asp.net-mvc,database,entity-framework-6

Ideally what you want is a many-to-many relationship between your Shop and Product entities: public class Shop { public int ShopId {get; set;} public virtual ICollection<ShopProduct> ShopProducts {get; set;} } public class Product { public int ProductId {get; set;} public string Name {get; set;} public virtual ICollection<ShopProduct> ShopProducts {get; set;}...

Why am getting this error?: Unknown column 'firstname' in 'field list'

php,database,mysqli

$query = "INSERT INTO `myDatabaseForAll`.`users` (`id`, `firstname`, `lastname`, `username`, `password`) VALUES (NULL, $firstname, $lastname,$username,$password)"; you need single quote around text feilds in sql queries change above query to $query = "INSERT INTO `myDatabaseForAll`.`users` (`id`, `firstname`, `lastname`, `username`, `password`) VALUES (NULL, '$firstname', '$lastname','$username','$password')"; ...

Postgres SQL constraint a character type

postgresql,constraints

Use a check constraint: CREATE TABLE my_table ( id character varying(255) NOT NULL, uid character varying(255) NOT NULL, my_text text NOT NULL, is_enabled boolean NOT NULL, constraint check_allowed check (my_text in ('A', 'B', 'C')) ); More details in the manual: http://www.postgresql.org/docs/current/static/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS...

Complex SQL with Multiple Joins

mysql,database,join

What I think you should take note of here is that each post has a department and a postType. If you take a step back, you can select all posts that belong to a certain department and a certain postType like this: SELECT p.* FROM posts p JOIN department d...

Avoid calling COUNT twice in CASE expression (PostgreSQL)

sql,postgresql

This expression: CASE COUNT(measurement.id) > 1 THEN to_char(COUNT(measurement.id),' 999') ELSE '' is not slow because COUNT() is called twice. The hard work of aggregating the data is the part where the key values are brought together. The individual aggregation functions are generally not particularly expensive (there are exceptions such as...

If I export my database with phpmyadmin will it lock my tables or take my database down?

mysql,database,phpmyadmin

The answer is no, tables won't be locked, database won't be down. But, if your database is large and it takes long time to backup it, you can sometimes expect performance degradation(slow SQL queries from your application).

PostgreSQL: trigger to call function with parameters

postgresql

A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger. You can use the arguments passed to the trigger function via TG_ARGV, e.g. TG_TABLE_NAME - the name of the table that caused the trigger invocation....

need help specifying potentially reserved words as strings in postgres query

postgresql

For string literals, you should you single quote instead of double quote: UPDATE rv_template_fields SET view = 'display_type_1' WHERE rv_template_fields.view = 'display_type_2' Double quotes are for quoting identifiers of fields and relations, like, for instance view, so that you could write also: UPDATE rv_template_fields SET "view" = 'display_type_1' WHERE "view"...

Combining two select statements

sql,database,select,where

Try to this var chgAssociationQuery1 = ((from a in sostenuto.PROBLEMS join b in sostenuto.S_ASSOCIATION on a.SERVICEREQNO equals b.FROMSERVICEREQNO join c in sostenuto.Changes on b.TOSERVICEREQNO equals c.SERVICEREQNO where b.FROMSERVICEID == 101001110 && b.TOSERVICEID == 101001109 && a.NAME.Contains(name) select new { ProblemReqNo = a.SERVICEREQNO, ProblemId = a.SERVICEREQID, ChangeReqNo = c.SERVICEREQNO, ChangeId =...

MySQL: Select several rows based on several keys on a given column

mysql,sql,database

If you are looking to find the records matching with both the criteria here is a way of doing it select `item_id` FROM `item_meta` where ( `meta_key` = 'category' and `meta_value` = 'Bungalow' ) or ( `meta_key` = 'location' AND `meta_value` = 'Lagos' ) group by `item_id` having count(*)=2 ...

Creating a generic / abstract “DBContext” Class for shared functionality among different DBs

c#,database,generics,inheritance,abstract-class

The key here is to step back and think about the problem from another angle. You are duplicating lots of code because you are creating instances of the database and command classes within the method. So inject them instead: public class SomeDBClass { static DataTable exec_DT(DBConnection conn, DBCommand cmd) {...

PostgreSQL can't find column

java,postgresql

Just in case the issue it related to upper and lower case in the column name: it's possible to put the column name in double quotes: PreparedStatement ps = conn.prepareStatement("SELECT * FROM produits where \"NOM_PRODUIT\" like ?"); This way the name is case sensitive....

Retrieve updated rows in AFTER UPDATE trigger Postgresql

postgresql,triggers,plpgsql

You can create a temporary table (so that it will visible only in the session). In the row level trigger you insert the rows into the temporary table, in the statement level trigger you select (and delete) from the temporary table.

Improving work with SQL DataTime

sql,sql-server,database,tsql

You can do it like this: SELECT IIF(DAY(@A) >= 25, DATEADD(d, 25 - DAY(@A), @A), DATEADD(d, 25, EOMONTH(@A, -2))) Here's a sample fiddle as well: sqlfiddle Note: EOMONTH requires SQL Sever 2012 or above - it returns the End-Of-Month date given a start date and a month offset....

Save a hex-string to PostgreSQL column character varying

postgresql,hex

If you are sure that there are never more than 10 elements, you can simply cast your hex string to text: INSERT INTO my_table (hex_text) VALUES (<some hex data>::text); Or use a bytea column instead?...

What type of database is the best for storing array or object like data [on hold]

database,node.js,sockets

Redis would probably be fastest, especially if you don't need a durability guarantee - most of the game can be played out using Redis' in-memory datastore, which is probably gonna be faster than writing to any disk in the world. Perhaps periodically, you can write the "entire game" to disk....

How to create a SELECT query FROM “TABLE1 AND TABLE2”

sql,postgresql,select,join

UNION ALL SELECT field1, field2, field3 FROM table1 WHERE condition UNION ALL SELECT field1, field2, field3 FROM table2 WHERE condition; Or to simplify your WHERE condition SELECT * FROM ( SELECT field1, field2, field3 FROM table1 UNION ALL SELECT field1, field2, field3 FROM table2 ) WHERE condition; ...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures

The only way to do this is to manually run a count(*) on all of your tables filtering on the particular date field. The reason for this is because one table might have a column "CreatedDate" that you need to check if it's >30 days old, while another might have...

Pull information from SQL database and getting login errors

php,sql,database

change $username = "'rylshiel_order"; to $username = "rylshiel_order"; and you should be through. You are passing on an extra single quote here. ...

postgresql complex group by in query

sql,postgresql

SELECT itemid, deadlineneeded, sum(quantity) AS total_quantity FROM <your table> WHERE (deadlineneeded - delievrydate)::int >= 1 GROUP BY 1, 2 ORDER BY 1, 2; This uses a "delievrydate" (looks like a typo to me) that is at least 1 day before the "deadlineneeded" date, as your sample data is suggesting....

Prepared statements: Using unnamed and unnumbered question mark style positional placeholders

postgresql

Prepared statements are used to speed up the repeated execution of the same query with different arguments. If your aim is to insert many rows at once it is better to execute regular insert query, which will be faster than the prepared insert. However, if you insisted on this solution,...

How to check what constraint has been violated?

java,sql,postgresql,exception

something like below catch (ConstraintViolationException conEx) { if (conEx.getConstraintName().contains("xyz_fK")) { //TODO Project Entity is violating it's constrain } LOGGER.Info( "My log message", conEx.getConstraintName()); LOGGER.ERROR( "My log message", conEx); ...

Difference between dba_SEGMENTS and dba_data_files

mysql,sql,database,oracle11g,oracle-sqldeveloper

Oracle uses "logical" und "physical" structures to store the data. For this case: The extents of a segment can be stored in different datafiles, so just summing up can work but must not work see here: http://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT301 Plus: Oracle has a "High Water Mark" so even if your segment size...

Error while trying to insert data using plpgsql

postgresql,timestamp,plpgsql

CURRENT_TIME is a reserved word (and a special function), you cannot use it as variable name. You don't need a variable here to begin with: CREATE OR REPLACE FUNCTION test_func(OUT pid bigint) AS $func$ BEGIN INSERT INTO "TEST"(created) VALUES (now()) RETURNING id INTO pid; END $func$ LANGUAGE plpgsql; now() is...

Displaying MySQL results in a single table

php,mysql,database

Try getting all the columns out first, then add it to the sql query, no need to loop the database query. $mark = $_POST['mark']; if (isset($_POST['mark']) && is_array($_POST['mark'])) { echo "<table border='1'>"; echo "<tr>"; for ($i = 0; $i < count($mark); $i++) { echo "<th>" . $mark[$i] . "</th>"; }...

Does Maria DB support ANSI-89 join syntax

sql,database,join,syntax,mariadb

Short answer - yes, both options are supported.

Foreign key in C#

c#,sql,sql-server,database

You want create relationship in two table Refer this link http://www.c-sharpcorner.com/Blogs/5608/create-a-relationship-between-two-dataset-tables.aspx...

SQLite: Individual tables per user or one table for them all?

database,sqlite

In an object oriented language, would you make a class for every user? Or would you have an instance of a class for each user? Having one table per user is a really bad design. You can't search messages based on any field that isn't the username. With your current...

Redirect if ActiveRecord::RecordNotUnique error exists

ruby-on-rails,postgresql,activerecord,error-handling

Just the way you catch every other error begin Transaction.create!(:status => params[:st], :transaction_id => params[:tx], :purchased_at => Time.now) rescue ActiveRecord::RecordNotUnique redirect_to root_path end ...

Syntax error while creating table in PostgreSQL 8.1

postgresql

PostgreSQL 8.1 only supports INCLUDING DEFAULTS. You'll either have to upgrade to at least 8.3 or create the indices manually....

How to use existing SQLite database in swift?

ios,database,xcode,sqlite,swift

First add libsqlite3.dylib to your Xcode project (in project settings/Build Phases/Link Binary with Libraries), then use something like fmdb, it makes dealing with SQLite a lot easier. It's written in Objective-C but can be used in a Swift project, too. Then you could write a DatabaseManager class, for example... import...

Id in database using qt

database,qt,sqlite

The method you're looking for is QSqlQuery::lastInsertId(). To quote the documentation: Returns the object ID of the most recent inserted row if the database supports it. An invalid QVariant will be returned if the query did not insert any value or if the database does not report the id back....

ER diagram for booking database

database,database-design

typically, you would store the password as some sort of encrypted hash. It is best if this is one-way, so it cannot be decrypted. When authenticating, you check that you can generate the same hash from the provided password; not decypt what is stored. Your hash should also be "salted"...

Return integer value of age(date) function in Postgres

postgresql

Here is how you get the number of days comparing two dates: SQL> select extract(day from now()-'2015-02-21'::timestamptz); date_part ----------- 122 (1 row) ...

Translation of interval

postgresql,datetime,translation,intervals,postgresql-8.4

I think no. sorry. day, month etc are field in type interval. it will be in English. like you don't expect use "vybrac" instead of select :) But you can have locale in time values, yes. td=# set lc_time TO pl_PL; SET td=# SELECT to_char(to_timestamp (4::text, 'MM'), 'TMmon'); to_char ---------...

Is there a better way to write this query involving a self select?

sql,postgresql,join,aggregate-functions

Query The query is not as simple as it looks at first. The shortest query string does not necessarily yield best performance. This should be as fast as it gets, being as short as possible for that: SELECT p.username, COALESCE(w.ct, 0) AS won, COALESCE(l.ct, 0) AS lost FROM ( SELECT...