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 ...
0
votes
0answers
5 views
Calling a postgre db function in Node API
I am trying to call this postgre function core.get_age(bigint) in my queries.js
function getAge(req, res, next){
var customer_id= parseInt(req.params.customer_id);
db.one('SELECT * FROM ...
0
votes
0answers
3 views
pgadmin4 1.3 raises CSV export broken
I installed pgadmin4 1.3 (in a docker container) and everything is working fine except CSV exports.
I filled in Binary Paths properly in Preferences but it is still not working. I have no error ...
0
votes
1answer
15 views
Output difference of OR and UNION in sql
In which cases, the output of using OR and UNION differs in SQL?
I know in some situations using one of them is more efficient in terms of processing time than the other...
[Time efficiency ...
0
votes
0answers
3 views
Google cloud sql: How to import postgres database?
I have a Postgresql database that is running on premises which I want to import into Google Cloud SQL. My database is called test and is owned by user test
Steps I've taken:
created Cloud SQL ...
google-cloud-sql
0
votes
1answer
10 views
Select from two inherited tables in PostgreSQL
Say there is a PostgreSQL parent table files that is empty; it's 'abstract'. And then there are tables that inherit from files that contain data: red-files, green-files, blue-files.
I can select ...
0
votes
0answers
9 views
Best way to check deleted item when doing data synchronization?
I have a system that periodically calls an API to fetch a list of attendees for an event, and store/synchronize the attendees in my local RDB.
Each record has a timestamp field which I can use to ...
0
votes
0answers
6 views
Connect On-Premises Postgresql to Azure SQL
I'm wondering if there's a way to have an on-premises PostgreSQL server that can be accessed in Azure the same way as an ordinary PostgreSQL server. So to have an endpoint in Azure with port 5432 and ...
0
votes
1answer
19 views
Why does my LIKE statement fail with '\\_' for matching?
I have a database entry that has entries that look like this:
id | name | code_set_id
I have this particular entry that I need to find:
674272310 | raphodo/qrc_resources.py | 782732
In my ...
0
votes
0answers
8 views
Copy new comboBoxModel to multiple comboBox components from single SQL Query
I need to copy a ComboBox model in multiple java forms that contains a list of "States", currently in my project the list is obtained from my PGSQL server, there's about 6 ComboBox components that ...
0
votes
2answers
38 views
Better way to write a query
I'm creating a view after joining a table and a view.
The view contains some actual columns in the join and some of them are calculated columns(lets call them type1). Some of them are calculations of ...
1
vote
1answer
14 views
How to search through array of strings using BETWEEN in Active Record POSTGRES,
Subscriptions
canceled_at: ["2017-04-07T09:52:17-05:00", "2017-04-01T09:52:17-05:00"]
Above is my subscriptions table. I am trying to query through and find records that have a canceled_at date ...
1
vote
1answer
21 views
Filtering for only integer/numeric values in a varchar field
I have a table with a member id column (non-PK) that should all be integers. But many are NULL or non-integer values with a mixture of letters, numbers, and other random characters (the field is of ...
2
votes
2answers
17 views
Lateral join for ARRAY in SQLAlchemy
I'm using PostgreSQL's ARRAY to store tags for images.
How can I write an ORM query in SQLAlchemy, which returns the set of all tags found in the table, for the following model:
from sqlalchemy....
0
votes
2answers
13 views
How to insert pdf into postgresql(remote db)
I have a pdffile.
I could insert the pdf file and db is available in same server using below function and command.
function
create or replace function bytea_import(p_path text, p_result out bytea)
...
1
vote
5answers
33 views
Formatting table with partitions
What's the best way of only getting the rows between the occurrences opened and closed in PostgreSQL?
+------------+----+------------+---------------------+
| event_id | ID | occurrence | ...
1
vote
1answer
27 views
Why prepared statements are managed by each session?
Why prepared statements are managed by each session?
I am not sure all of databases handle prepared statements per session. but postgresql and mysql(maybe) do.
Why they are not shared between ...
0
votes
2answers
28 views
How to combine in one sql query in extra column the result of 2 group by queries?
Considering the following mdl_course_completions table that describes a course completion for a user:
id,bigint
userid,bigint
course,bigint
timeenrolled,bigint
timestarted,bigint
timecompleted,bigint
...
-2
votes
1answer
19 views
Php postgresql error, prepared statement already exists
Am getting an error of prepared statement "my_query7" already exists, i call this function each time a user tries to update table leader_info in the database, i have gone through the documentation for ...
0
votes
0answers
22 views
Node.js data validation with multiple processes
I noticed that in node.js not so easy to validate data in db. For example I have next snippet of code
return dbService.checkPossibilityAddEntityBySomeData(t, data)
.then(function(...
0
votes
0answers
19 views
Upload MP3 Files - Invalid byte sequence in UTF-8
I'm working with the Rails 5 API (carrierWave), and i need it to put some binary data (mp3 files) in my postgres Database.
First i create a simple model like that :
rails g scaffold Capsule name:...
0
votes
2answers
31 views
Group records with multiple sets in sql
I have to optimize the select query to get less no. of records when we have same data but with different dates. I need to sort all the rows by date and should combine all the rows into a single until ...
1
vote
0answers
17 views
Getting black screen while opening PgAdmin4
I am new to PostgreSQL. And I am trying to install PostegreSQL with WAMP. And I followed here
I downloaded WampServer, PostgreSQL, phpPgAdmin.
First, install the PostgreSQL. It is a simple ...
1
vote
0answers
17 views
JPA, Hibernate with c3p0 and Postgres. Detect database connectivity issues
I'm writing an application which connects to Postgres DB via Hibernate with c3p0 pooling. Before the main interface shows up I'd like to detect whatever database connectivity settings valid and ...
0
votes
1answer
15 views
pg_restore: [compress_io] could not uncompress data: invalid code lengths set
I've been problems to execute pg_restorecommand in Mac.
It's returning this error:
pg_restore: [compress_io] could not uncompress data: invalid code lengths set
I'm using postgres 9.6.2.
0
votes
1answer
19 views
Invalid status code : 0 in node.js API
I am building a simple api in node and express with postgre as Db following a tutorial.I am still learning and new to this tech.I followed everything as said and gave full permission to postgre ...
1
vote
2answers
21 views
Entity Framework core - Contains is case sensitive or case insensitive?
"Contains" in Entity Framework core should equivalent to the SQL %like% operator. Therefore "Contains" should be case insensitive however it is case sensitive! (at least in postgres????)
The ...
0
votes
1answer
27 views
How to get recent n items average by ActiveRecord
There is a Company that has many DailyDatum. DailyDatum has date and volume columns.
class Company < ActiveRecord::Base
has_many :daily_data
end
I want to get a average of recent 10 volumes. By ...
0
votes
1answer
22 views
Postgresql - implementation of LRU cache - eviction of items
I am implementing a LRU cache in Postgres.
I have a list of items. Each item has a priority and a weight. The lower the priority, the earlier is must be evicted from the cache. Let's take following ...
0
votes
2answers
40 views
Sum of json array
I have json type field, something like this
data
{"age": 44, "name": "Jun"}
{"age": 19, "name": "Pablo", "attempts": [11, 33, 20]}
{"age": 33, "name": "Maria", "attempts": [77, 10]}
Here some ...
0
votes
1answer
19 views
How to choose from - Multiple versions of PostgreSql on Ubuntu desktop
I have version 9.5 installed . For creating a local backup from Heroku i need version 9.6.1 as thats the one on Heroku - otherwise pg__dump fails with an error.
Ubuntu console dump below -
(...
0
votes
3answers
31 views
How to combine multiple sql queries into single query?
I am working on spring boot framework with postgressql.
Below are example queries 1,2,...n. These queries are working fine. But I have to combine these queries into single query because table is ...
0
votes
0answers
14 views
PgPool2 load ballance
I have 2 PG servers, one master, the other one slave - normal stream replica that works great.
I need to put pgpool before them, that would make the load balancer on both machines - just select ...
0
votes
2answers
27 views
how to calculate only days between two dates in postgres sql query .
Suppose I have given two dates, if difference is one month then it should be display as 30 days.Even months also need to convert into days
I have tried with age( date,now()::timestamp without time ...
-2
votes
1answer
36 views
How To simplify in this query?
This My query,
SELECT item_id,prod_name,closein,closeout,closein-closeout as closing_stock,stockin,closein-closeout+ stockin as current_balnce,stockout,
closein-closeout+ stockin-stockout as balance,...
0
votes
0answers
24 views
Django JSONField: Unexpected array element
I am trying to save a record JSONField in Django which is declared like this:
class MyClass(models.Model):
myfield = JSONField()
with this code fragment:
MyClass.objects.create(myfield = {'a': 1,...
0
votes
0answers
19 views
Save array of objects in Postgresql using Sequalize.js
I have comething like this
[{
"id":"3",
"clientName":"John Doe",
"address":"street, 15",
"latitude":"50.1212",
"longitude":"30.1111",
"timeFrom":"2017-04-05T14:48:00.000Z",
"timeTo":"...
0
votes
1answer
25 views
Fetch two table data in single query and data should include only that row which has max rank
I have two table users(id,name,email) and comments(id,user_id,rank,content). In comments table i have user_id as foreign key for user table. Now i want to join users table with comments table and want ...
0
votes
1answer
22 views
how run express js project on secure shell?
1.please explain how to create secure shell in express project?
var express = require('express');
var app = express();
var pg = require('pg');
app.listen(7207);
var bodyParser = require('body-parser')...
0
votes
1answer
15 views
Postgres: Best way to determine changes in table without scanning entire table
I am designing a etl to take incremental changes from postgres table.
How to detect whether table rows modified after last etl run without doing full table scan ?
0
votes
0answers
30 views
postgreSQL FDW query performance test setup
previously I did a test on time of the query execution and I came up with unreliable results regarding the time of execution difference between FDW and direct access to postgreSQL table. (This is my ...
0
votes
1answer
23 views
Datatables response 500 error internal server when return from multiple tables
I get this error when I use search box
I have joined my tables with this query
$query = LoginQuarter::join('wholesalers','login_quarter.username','=','wholesalers.user_id')
->select('...
0
votes
0answers
21 views
How I can copy my local PostgreSQL database to Heroku for SpringBoot app
I have deployed my SpringBoot app to Heroku. Now I would like to copy my local PostgreSQL to Heroku.
I have found some information on devcenter.heroku.com.
However I don't understand enough about ...
0
votes
0answers
10 views
PostgreSQL Horizontal scaling of database per customer cloud service
I'm looking for the best way to serve thousands of PostgreSQL databases for application tier. Each database is used for a specific customer.
The goals are:
1. Serve as much databases on minimal ...
0
votes
1answer
15 views
single quote escape - sequelize migration
I'm using NodeJS with Sequelize ORM and Postgress database
I'm creating ENUM column named education_level in table using migration file as below and enum value (Bachelor's Degree) has single quote and ...
0
votes
1answer
32 views
Select first object of group by in postgresql/sqlalchemy
I have a request in sqlachemy with SQLite which return an object of the group by and a value (avg) :
result = session.query(
obj, func.avg(obj.value).label("value_avg")
).group_by(
...
2
votes
0answers
28 views
Large string(40 mb) insertion in Database - logging / Performance issue
Java version : 1.7
Database : PostgreSQL
I need to insert large String in Database in multithreading application for each activity where Sting size is around 40 mb. I have separate Database to just ...
0
votes
0answers
40 views
Best way to get the count of data in big data table using SQL
I have a table with 12 millions of data and I want to get a count of data which matches my criteria. Table is well indexed and when I want to test the worst case of get the count of all the records ...
0
votes
1answer
15 views
Get events accoding to user's time zone not DB + Rails4
I have a three events. They contains start_time of show.
But the problem what I am facing is with time zone. Value saved in db is in UTC as Rails default behaviour.
How it works: User can select ...
0
votes
0answers
13 views
postgresql check session config setting availability
I have a sensor data table full of 'holes', the data is only saved when it changes over an specified threshold.
| time stamp | value1 | value2 |
| 2012-01-01 | 1 | |
| 2012-01-02 | ...
0
votes
1answer
29 views
Ruby Rails: wont save as decimal in postgresql
Im using Ruby 2.4.1 with rails 5.0.2
Here is my code:
total_price = 600.0
o = client.bills.new(client_id: client, total_price: total_price.to_f)
#<Bill id: nil, client_id: 21, created_at: nil, ...
