Which db manager for a 100Go Table?

I realize a 2G / 3G / 4G data retrieval project as part of my studies. I have to store this data, and to make queries on it. My table : [freq {float}, dbm {float}, timestamp {init}] I receive about 15GB per day of data, from 100000 to 200000 entries ...
more »

2017-06-18 14:06 (1) Answers

Simple approach for storing time series data

I have about 1 billion events daily. I need to store these events in the database for last 30 days, so it's about 30 billion rows. Let's say it is athletes database, each row has only 4 column (athlete name, athlete's discipline, athlete rank, date)...
more »

2017-06-18 13:06 (1) Answers

Issues with DB connection in Python

We have an application that does queries to our DB. We're using sqlalchemy, and the create_engine method. To execute an sql query we use : with engine.begin() as connection: ...... The issue we're facing is that when no calls has been made to o...
more »

2017-06-11 15:06 (0) Answers

SQL: dealing with NULL values (union)

Given this two, similar, queries: SELECT EXTRACT(YEAR FROM to_date) AS year_expired FROM salaries GROUP BY year_expired ORDER BY year_expired and ( SELECT EXTRACT(YEAR FROM to_date) AS year_expired FROM sal...
more »

2017-06-09 20:06 (1) Answers

postgres users can login with any or no password

So I set up a user called 'paperwork' with a database of the same name postgres=# create role paperwork; postgres=# create database paperwork; postgres=# grant all privileges on database paperwork to paperwork; postgres=# ALTER ROLE paperwork WITH L...
more »

2017-06-09 00:06 (1) Answers

Database design for kids chore schema

I'm wondering about best practise to keep a database as tidy as possible. The database is postgresql accessed by express.js/node. It is for a kids chores app that I'm working on and it has the following schema: CHILDREN id name points ...
more »

2017-06-07 22:06 (1) Answers

SQL Error when trying to alter column

I add trying to add a method to be the defualt value of an existing SQL Table Column: ALTER TABLE Category ALTER COLUMN category_course CONSTRAINT cat_other_course DEFAULT otherCourse(); However I keep getting this error: ERROR: syntax err...
more »

2017-06-05 12:06 (1) Answers

postgres transform 2 columns as json with array

I have next resultset from Postgres data base : actually I never work with postgres json and I naven't found any same examples id | another_id -------------- 1 | 10 1 | 11 3 | 20 3 | 21 3 | 22 and I need to transform it as a json string: {"i...
more »

2017-06-01 11:06 (2) Answers

Postgres ERROR: row is too big

psql (9.2.7) Type "help" for help. postgres=# \c dtb_dpx You are now connected to database "dtb_dpx" as user "postgres". dtb_dpx=# vacuum full; ERROR: row is too big: size 4674072, maximum size 8160 dtb_dpx=# What can be done ? Any idea how to sol...
more »

2017-05-29 17:05 (0) Answers

Handle connection breakages in rails

I have a module written in ruby which connects to a postgres table and then applies some logic and code. Below is a sample code: module SampleModuleHelper def self.traverse_database ProductTable.where(:column => value).find_each do |produ...
more »

2017-05-26 11:05 (1) Answers

Real time python app that works on a database

I am using postgreSQL with python and the SQL database is such that rows are added regularly. At present, the python program does not know if new data has been added (I used psycopg2 to read rows. But it reads till the end of rows and stops). How can...
more »

2017-05-24 06:05 (2) Answers

postgresql - query to build up json

Running: PostgreSQL 9.6.2 I have data stored in a table that is in the form of a key/value pair. The "key" is actually the path of a json object, each one being a property. So for example if the key was "cogs","props1","value", then the json objec...
more »

2017-05-23 19:05 (1) Answers