Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact version of Postgres when asking questions. Questions concerning administration or advanced features are best ...
1
vote
0answers
17 views
Exact same query and database. In production more than 100x slower
I am running PG 9.6.1 both in production (Linux) and dev (Mac)
I am testing an identical query in both development and production. In development I've tested on multiple snapshots of the production ...
0
votes
1answer
14 views
Need to count rows during a date period then count rows based on what that date period is using other conditions
I am trying to calculate subscription churn. I have a table that looks like the following:
subid | startdate | enddate
000001 | 9/26/2016 | 10/26/2016
000002 | 11/4/2015 | 12/4/2016
...
0
votes
0answers
7 views
Rails 5: (Object doesn't support #inspect) when serializing text column
I'm building a rails 5 api only app, working with a postgres database created from a rails 4 app.
In rails 4, I could easily include serialize :column_name on a model to turn a text column into a ...
0
votes
0answers
19 views
Generate n-row sets of possible combinations in SQL
I don't really think this can be done for n-rows, but would be happy to find out how Here is the problem presented as an example:
Consider this set of 2 columns t and f, with 5 rows:
CREATE TABLE ...
0
votes
0answers
11 views
Workflow for R as intermediate step in production environment? [on hold]
We are a small nonprofit trying to define our production workflow for data analysis tools. Our workflow is:
Do data-warehousey stuff in our Postgres data warehouse, which is hosted on Heroku.
Use R ...
0
votes
1answer
8 views
How do I change this query to return an association instead of an array in Rails and Postgres?
Suppose I have this query:
t.pool_tournament_matches
.where(status: "unstarted")
.joins("INNER JOIN pool_tournament_match_users ON pool_tournament_match_users.pool_tournament_match_id = ...
0
votes
0answers
19 views
raw query with primary key
My model
class Despacho (models.Model):
bus=models.ForeignKey(Bus)
contador = models.IntegerField()
cerrado = models.BooleanField(editable=False)
class Bus(models.Model):
...
0
votes
0answers
9 views
Postgres+Hibernate: Mapping UUID to BYTEA fails after upgrading to Hibernate 5.0
How to store java UUID as byte array in Postgres using Hibernate 5.0?
Model:
@Entity
@Table(name = "childs")
public class Child {
...
@Type(type = "pg-uuid")
private UUID parentId;
...
}
...
0
votes
1answer
7 views
group by time or hour in knex
I am using Postgres with NodeJS and Knex. I have a number of records with created_at format 2016-12-12 14:53:17.243-05. I want to group all records by the hour such that:
Hour 14:00:00-15:00:00 would ...
0
votes
1answer
19 views
PostgreSQL Use a value from a select statement as a variable within that same statement
I have a pretty complex SQL statement in my PostgreSQL database which I use as a basis in several views. But in this statement, a sum aggregate function is called multiple times. I'm now seeking to ...
0
votes
1answer
11 views
Create an index over distinct values of a column in PostgreSQL
I have a PostgreSQL table that looks like this:
CREATE TABLE items (
name TEXT NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (name, value)
);
I frequently do a query to see what values are ...
0
votes
1answer
23 views
How to Optimize “JOIN” in PostgreSQL
I have four tables to pull information from user: first_name, mongouser: email, card_status,transaction: transaction_type, balance, posted_at, is_atm, is_purchase, user_login: user_id, login_date, ...
-2
votes
0answers
18 views
Data analysis - trending on SQL/R [on hold]
I'm relatively new to data analysis, and have this problem I'm trying to solve but I'm not sure how I should go about it. The tools at my disposal are excel, Postgres SQL, R, and then coding languages ...
3
votes
2answers
19 views
psycopg2: How to know when cur.rowcount does not mean number of rows?
I'm looking for a way to programatically determine the difference between cur.rowcount describing the number of rows available to fetch versus the number of rows affected.
For example:
>>> ...
0
votes
1answer
9 views
Audit table with postgresql
I want to do an audit table with triggers in postgresql.
My example:
CREATE OR REPLACE FUNCTION fn_editorial_audit()
RETURNS trigger AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO ...
0
votes
0answers
15 views
Should I store UTC timestamps or localtime for shifts
I am working on a function that will work out how many workers is on duty and clocked in (or not clocked in) for their shift.
The workers "clock in" and "out" and the time stamps of these events will ...
0
votes
1answer
19 views
How can I evaluate data over time in Postgresql?
I need to find users who have posted three times or more, three months in a row. I wrote this query:
select count(id), owneruserid, extract(month from creationdate) as postmonth from posts
group by ...
0
votes
0answers
7 views
JDBI Iterators and maxFetchSize
I'm trying to use JDBI to return an Iterator connecting to PostgreSQL and limit the fetch size with a cursor.
I have the code:
public Iterator<Stuff> getAll() {
try (Handle handler = jdbi....
0
votes
1answer
8 views
liquibase - create index for postgresql foreign keys
We are using liquibase changesets to support usage of MySQL and PostgreSQL; Now I've stumbled over the fact that MySQL creates indexes for foreign keys automatically where postgres does not.
Question:...
0
votes
0answers
19 views
FATAL: database “[DB]” does not exist on remote connection
I'm running a postgresql database on our server. I know the error is common if you do not specify a database and haven't created on with the same name as your user. If I ssh into the server I can ...
0
votes
0answers
13 views
Postgres 9.6 parallel aggregates with GROUPING SETS or alternative
I need the functionality of GROUPING SETS to sum up the values for each column. However, Postgres 9.6 docs state that it cannot perform parallel aggregates when using GROUPING SETS.
Is there a ...
0
votes
2answers
20 views
How do I screen for integers that are too big in my Rails model?
I’m using Rails 4.2.7 with PostGres 9.5. I have this column in my Rails migration ...
my_num | integer |
In my model, I would like to screen for numbers that are ...
-1
votes
0answers
10 views
angularjs login page with postgres not work
My login page doesn't save data in the database table.
This is the database operation:
(function () {
'use strict';
angular
.module('app')
.factory('db1', db1);
db1.$...
0
votes
2answers
17 views
How can I setup a new user, password and database all in one command for my PostgreSQL database
How can I setup a new user, password and database all in one command for my PostgreSQL database? I have a test script that needs to run a command to setup a database. This is what I have tried:
psql -...
0
votes
1answer
32 views
Why postgres calculate percentage wrong (based on columns result)
I am trying to get % of discount between two columns, first problem which i was solved is that other column may be 0 or null,
Now when I calculate it it always give me 100 or 0 as a result of column '...
0
votes
1answer
20 views
How to read Postgres SQL data into Grafana graph
I want Grafana to read the Postgres SQL data to plot the graph. Can anybody share some links or information about how to do the export of data from postgres to Grafana.
This information would be ...
0
votes
1answer
25 views
Postgresql query to get count per months within year with multiple tables
As per reference link I'm used to get count based on months within one year with single table it works. But here I need get count's with two tables with relations based on user type and estate.
Here ...
0
votes
1answer
19 views
How to batch up Postgres files
I have a number of files which need to be run against a postgres database. They are stored as separate files for reasons of maintenance and configuration management. I am using this type of method to ...
1
vote
1answer
10 views
icCube driver not found: org.postgresql.Driver
I'm trying to evaluate icCube and I have a problem when I try to connect a PostgreSQL datasource. When I test the connection I get an error message about PostgreSQL driver missing:
Failed to ...
0
votes
0answers
21 views
Alter sequence in H2
I'm using Postgres database in production and H2 for tests.
I want to create a new sequence for an existing table - so in Liquibase I wrote this:
<changeSet id="Add sequence for BOOKS" author="...
0
votes
1answer
43 views
Getting the oldest datas from a table that are older than a 100 days
I've been struggling with the following problem:
EXPLAINING
I have a table called part_subhourly_data that holds production data for a part (For the purpose of the problem, no need to know what a ...
0
votes
0answers
35 views
Hibarnate does not write to db sometimes
I have a threadpool that produces listenableFutures from guava. Upon failure of the task I write in the DB using two methods.
First method writes an entry with various information and second updates ...
0
votes
1answer
27 views
Postgresql query for updating multiple columns in different tables during join
We're working on migrating data from MySQL to PostgreSQL.
Sample table structure:
Table "public.model"
Column | Type | Modifiers
-----------...
0
votes
1answer
45 views
extract single cases from array
An array of records being generated by
@signatures = Signature.where('action_id IN (?)', @actions).all
will have actions with one or more signatures.
Signature id: 1, action_id: 1
Signature id: 2, ...
-2
votes
0answers
26 views
CREATE TABLE AS VIEW OR SELECT >> script.txt [on hold]
Hello I need to create several tables for different views or selects, and need to generate CREATE .sql file.. is possible?? only create sql..
!!! UPDATED QUESTION... !!!
CREATE TABLE films_artist
...
0
votes
0answers
27 views
Lock a Select on the Postgres Database but not Updating
I have a case where I create a csv file from php. I need to build the name of this file from some factors like the current Date and columns on the Database.
This page is opened to all my colleagues ...
0
votes
2answers
37 views
Validation for date/times that overlap
In my rails app I have a model called Booking. Users can book by selecting a date and then selecting a time.
Currently, I have a validation that checks whether the date and time combination already ...
0
votes
0answers
10 views
How to display multiple query in one line for Odoo Qweb Report
I am making a report from sale.order.line. Here I want to display the product_id.product_tmpl_id.name product_id.code, sum(product_oum_qty) in last month, sum(price_unit) in last month, sum(...
0
votes
0answers
16 views
Odoo's backing up database using pg_dump, Odoo's UI command being used
I've been studying how to use pg_dump and creating a batch file in order to backup the database of my PostgreSQL since the pgAgent is not appearing on the pgAdmin 3 that Odoo installed on my machine. ...
0
votes
3answers
34 views
Postgresql Query to split the array into rows
I am running a query which is creating a view for me with following details
id name brand_id
1 E1 {3,4}
2 E2 {5,7,8}
3 E4 {1}
I want to ...
0
votes
2answers
41 views
How can I return all rows for a value where at least one row meets a condition?
I'm trying to return all rows for IDs where one or more Num_Occurrence rows is >=10.
Here is an example of the raw data:
+------+-----------+----------------+
| ID | YearMonth | Num_Occurrence |
+-...
1
vote
1answer
25 views
Conditional logic for ON DELETE foreign key reference
I want to be able to perform some logic for foreign key ON DELETE constraints. Depending on the logic, I will either want to CASCADE or RESTRICT.
My tables:
CREATE TABLE users (
user_id uuid ...
2
votes
2answers
40 views
How do I return nested array from a psql database in json format
I'll start from the top... I have two tables, quotes and comments. One quote 'has many' comments. My end goal is to have two .map() functions on the front end, one that renders all the quotes to my ...
0
votes
1answer
39 views
Slow Postgres 9.3 Queries, again
This is a follow-up to the question at Slow Postgres 9.3 queries.
The new indexes definitely help. But what we're seeing is sometimes queries are much slower in practice than when we run EXPLAIN ...
0
votes
2answers
27 views
SQL - Contacts, Companies DB Design
Im working on a db to manage customer data for a small company.The customers are companies and institutions (schools..etc) and of course people/contacts. There will be a lot more scope added in time, ...
0
votes
0answers
21 views
Trouble importing .dat files in postgreSQL
Hi all I am trying to import data from the openflights website but I am having trouble since the files are in .dat format. On top of that I keep getting permission denied when the query tries to read ...
0
votes
1answer
27 views
Stack Overflow for DateTime with spray-json, Slick and PostgreSQL
Hope you can help me figure this one out, please. Using Akka HTTP, Slick and PosgreSQL, I'm trying to implement a bulletin board as an excercise. You can find the latest version of it in here for a ...
1
vote
1answer
17 views
Splitting daterange into columns
I have defined a table in PostgreSQL with a column of type daterange, and now need to split it into two columns of type date (start and end date). How can I do that, I was unable to find anything.
...
0
votes
2answers
30 views
Find rows where a minimum number of columns match a pattern
I have a schema like the following (just an example):
CREATE TABLE example (
id int primary key
, text1 text
, text2 text
, text3 text
, text4 text
, text5 text
, text6 text
);
Question:
I'm ...
0
votes
1answer
27 views
PostgreSQL jsonb nested pattern matching text search across multiple rows
as stated in the title, I am attempting to do a query that preforms a full text search on multiple rows, agains a jsonb data type, with nested data, the problem is as follows:
CREATE TABLE books (id ...
