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
7 views
Defining a custom week using days of the month or year in postgresql
I am running a simple query to get weekly revenue from our sales
SELECT date_trunc('week', payment_date) AS week, sum(payment_amount)
FROM payment
WHERE payment_date BETWEEN '2010-jan-1' AND '2016-...
0
votes
0answers
6 views
Redshift subquery not accepted
I'm trying to execute the following query against my dataset stored in Redshift:
SELECT v_users.user_id AS user_id,
v_users.first_name AS first_name,
v_users.email AS email,
...
0
votes
0answers
5 views
insertion in Citus hash distributed tables using JDBC in Pyspark
I am trying to insert some data in a hash distributed table which I created using Citus. And in Pyspark I am using JDBC postgres drives to insert data into tables.
code which I am using to insert ...
1
vote
0answers
13 views
Converting MySQL POINT datatype to PosgreSQL
I am migrating a database with geometric data from MySQL to PostgreSQL. I've run into a snag because the POINT datatypes are incompatible between the two databases.
In MySQL, the POINT values are ...
0
votes
1answer
12 views
PLPGSQL sql injection
I have this stored function in plpgsql:
CREATE OR REPLACE function login_v(em varchar, passwd varchar)
RETURNS users AS $$
DECLARE
cu users;
BEGIN
...
0
votes
0answers
11 views
omit fields from api response using hapi
I'm using Hapi.js for my API and trying to determine the best way to limit the fields returned in a response based on a user's role.
For context, I'm running Hapi.js and using Bookshelf.js to handle ...
0
votes
1answer
36 views
Absolute value in postgres
I'm looking for the best way to get the sum of the absolute values with Ruby and Postgres.
I've tried the line below but it provides the wrong sum:
Day.last.transactions.sum { |r| r.value_cents.abs ...
-1
votes
1answer
15 views
Ubuntu Java Postgresql JDBC [duplicate]
How can I get Java to query a Postgresql server running on Ubuntu 16.04? I have downloaded postgresql-9.4.1212.jre6.jar (JDBC Driver), but I do not know what I am suppose to do with it...
java ...
1
vote
1answer
18 views
Batch insert data counting new inserts
Suppose i have a simple schema with a composite pk with columns. e.g
pk1: string
pk2: int
date: Timestamp
I am reading data from somewhere else in batches of about 50 and would like to store this. ...
0
votes
0answers
10 views
Hibernate with spatial can not create such fields in PostGis
I have a spring servlet application which uses a bunch of geographic data, and I want to store them in PostgreSQL with PostGIS to use its gis features with Hibernate.
I have a simple class so far:
...
0
votes
0answers
4 views
Removing node from Backend Cluster throwing undefined method `replication_slot_in_use?'
I am trying to build new Chef High Availability Backend Cluster but somehow while adding 4th backend cluster process failed due to firewall issue.
Now I am trying to remove this node and rejoin its ...
0
votes
0answers
16 views
Hibernate JPA Query multiple join tables
i can't understand one thing.
Here is test DDL:
CREATE TABLE public.role
(
id SERIAL PRIMARY KEY
);
CREATE TABLE public.role_translate
(
id SERIAL PRIMARY KEY,
name VARCHAR(30),
roleId ...
0
votes
0answers
26 views
PostgreSQL database doesn't seem to work
I've downloaded a project from internet, that is supposed to let me draw some polygons, points and so on on the map, then save it on the PostgreSQL database. You can also upload KML files to show ...
0
votes
0answers
13 views
How to compare value in parent with count value in join table in Postgres (Active Record)?
I am trying to compare a count value in a join table with a field value in the parent table in Postgres and select all parent records where the child table count is smaller than the value in the ...
0
votes
1answer
13 views
How should I upgrade from bradjasper's django-jsonfield to Django's built-in jsonfield?
I have a Postgres 9.4 / Django 1.8 database that uses bradjasper's django-jsonfield package. (See https://github.com/bradjasper/django-jsonfield ) It works well, but I would like to upgrade the ...
0
votes
0answers
10 views
Rspec Not Failing on PG::GroupingError
Ruby: 2.1.8
Rails: 4.2.7
When I run a search on my Rails server, I put in a bad group statement in my query so that it would intentionally fail with a PG::GroupingError. When I run my tests, I don't ...
0
votes
1answer
26 views
find diff between 2 numbers in different rows
I have a Postgres table writing every minute int value (requests count).
I have some requests types on some servers, all of them on the same table:
time | key1 | key2 | key3 ...
0
votes
0answers
9 views
How to perform full text search on PostgreSQL via Loopback?
I am using Loopback for my application — I need to perform a full text search on tables in the database. I've read the PostgreSQL documentation on text search but I'm not sure how to make these ...
0
votes
1answer
14 views
Sum value and merge data when mapping data with duplicate product names
We're mapping data in order to quickly save an API dump to the database.
However, some of the items are listed twice so how would we add their value to each other?
For example, we're getting ...
0
votes
0answers
24 views
Crystal Report formula error when accessed from VB.NET 2015CE
I have a Crystal Report I designed using a Function in my PostgreSQL database. The report's data source is the following SQL command (redacted):
SELECT * FROM [SCHEMANAME].[FUNCTIONNAME]({?[...
0
votes
1answer
12 views
Users created, with different parameters in a single result, grouped by week
In Postgres, I am trying to find each weeks' statistics broken down for users. Not only would I want to know the number of users created, I would want to know the number of users created that didn't ...
2
votes
2answers
20 views
PostgreSQL Partition range - strange behaviour
I'm partitioning a table (t1) using an integer range using the check like this:
CONSTRAINT t1_201611_check CHECK (date_id >= 20161101 AND date_id <= 20161130)
CONSTRAINT t1_201612_check CHECK (...
0
votes
2answers
21 views
Extract date-by-date values also when no data
I have this table
records
- id
- date
- typology
I want to extract a day-by-day count of records with typology = 999. I know there's at least 1 record for each day, but most of days I don't have a ...
3
votes
0answers
34 views
Integrity error: update or delete violates foreign key constraint. Django + PosrgeSQL
This my UserProfile modification
class UserProfile(models.Model):
user = models.OneToOneField(User)
fb_id = models.IntegerField(primary_key=True,null=False,blank=True)
...
0
votes
0answers
7 views
Docker: skygear server not starting, connection refused
I followed the Docker manual on https://docs.skygear.io/server/guide/getting-started-docker.
I used Kitematic to download Skygear Server from SkygearIO.
When the server tries to start it can't connect ...
3
votes
2answers
35 views
How to sanitize or randomize sensitive database fields
What's the most efficient method or tool to randomize a list of database table columns to obscure sensitive information?
I have a Django application used by several clients, and I need to onboard ...
0
votes
0answers
7 views
i want to add jlabels on my frame dynamically and set their text according to entries in my postgresql database
I want to add jlabels on my frame dynamically and set their text according to entries in my postgresql database...the problem is that the labels are not being visible on frame... I'm using netbans ide....
2
votes
1answer
18 views
A Postgresql function stuck in for loop, the query never ends querying
The function is stuck in for loop, the query remains querying and never ends:
ALTER TABLE movement ADD COLUMN bar_id INTEGER;
CREATE OR REPLACE FUNCTION get_all_movement() RETURNS SETOF movement AS
$...
1
vote
0answers
14 views
PostgreSQL odbc_fdw issue creating foreign table, doesn't import two fields
I have been working with PostgreSQL again and i'm doing a communication between sql server 2005 and PostgreSQL 9.6. I used the Foreign Data Wrapper odbc_fdw.
I created with no problem like 11 foreign ...
1
vote
1answer
15 views
Restore PostGreSQL materialized views
I'm unfortunately getting stuck on database stuff with PostGreSQL..
I created some materialized views in two schemas "schema1" and "schema2". These materialized views work well.
Then I made a backup ...
0
votes
0answers
18 views
SqlAlchemy: How can SUBSTRING used in GROUP_BY (with PostgreSQL)?
I can get the all major version numbers with the next raw SQL query from the table which is stored the all published versions:
SELECT SUBSTRING(version FROM '6\.\d+|[7-9]\d*') AS major FROM ...
0
votes
0answers
6 views
Deploy BIRT Report on TomCat with Postgresql
I've a tomcat 8 instance with Birt Report installed on Ubuntu. With a very simple report (no database connection), I can generate my report online.
Now I would like to use my "real" report that use ...
0
votes
1answer
15 views
SQLALchemy getting error when try to find and update atomically datetime fields
I need to update atomically one row
my code:
class TestModel(Base):
__tablename__ = 'table1'
id = Column(Integer, primary_key=True, autoincrement=True)
col1 = Column(String)
col2 = ...
0
votes
1answer
20 views
Vagrant port fowarding on macOS?
I'm running postgresql on a Vagrant centos 7.2 box on macOS. I have confirmed postgres is up and running on the Vagrant box on port 5432 by connecting to it using psql on the box itself. I am ...
0
votes
0answers
37 views
postgresql - Subquery to look for random instance following a specific instance. Not working as part of master query
Just to clarify for moderation etc, this is a follow up from my previous post below:
postgresql - Subquery to look for random instance following a specific instance
That question was resolved ...
0
votes
0answers
19 views
Is STABLE volatility safe for an idempotent data-modifying function?
I have a PL/pgSQL function which updates data (update_data). Within the context of a transaction this function can be considered idempotent, i.e. the function, if called with the same arguments within ...
-1
votes
0answers
7 views
Is AgensGraph possible to support HA?
I have used PostgreSQL and ever heard that AgensGraph is based on PostgreSQL and Graph Database, so it is very robust, fully-featured and ready to enterprise use.
So, I wonder how AgensGraph supports ...
0
votes
0answers
18 views
how to define my database connection correctly in postgres with nodejs
var config = require('config.json');
var _ = require('lodash');
var jwt = require('jsonwebtoken');
var bcrypt = require('bcryptjs');
var Q = require('q');
var pg= require('pg');
var conString = "...
2
votes
1answer
39 views
Clustered index in PostgreSQL?
I have a table :
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
cell_id BIGINT
...
1
vote
0answers
22 views
Postgresql invalid input syntax with windows CSV files
For some reason Postgresql wont read my CSV files in the form:
2017-10-20T21:20:00,124.502,CAM[CR][LF]
2017-10-20T21:21:00,124.765,CAM[CR][LF]
(thats an ISO compliant timestamp right?) into a table ...
0
votes
1answer
22 views
C++ Poco ODBC Transactions - AutoCommit mode
I am currently attempting to use transactions in my C++ app, but I have a problem with the ODBC's auto commit mode.
I am using the POCO libaries to create a connection to a PostgreSQL database on the ...
2
votes
1answer
33 views
Skip some ranges in postgresql sequence?
I want to skip some ranges in sequence:
Create sequence id_seq;
Consider I have a sequence as Id_seq.. And it starts from 100..
When it reaches to 199.. Then it should start with 1000 and when it ...
3
votes
0answers
33 views
Conditional sum in the having clause of a grouped query with ActiveRecord
Given two postgres tables samples and sample_values
Table "public.samples"
Column | Type | Modifiers
------------+-----------------------------+----------...
1
vote
1answer
13 views
Creating TEMP TABLE dynamically in Postgresql and selecting the same table in FOR loop. But getting the error near PIPE symbol
do
$xyz$
declare
y text;
i record;
begin
y := to_char(current_timestamp, 'YYYYMMDDHHMMSS');
raise notice '%',y;
execute 'CREATE TEMP TABLE someNewTable'
||y
||' AS select * from ( VALUES(0::int,-...
0
votes
1answer
25 views
PostgreSQL perent with child list
I have a table structured as
Id int(10)
ROOT_ID int(10) --reference I'd
Name
This table contain structure of parants and sons. Example data
Id | parent | name
1 Null A
2 1 B
3 ...
0
votes
3answers
34 views
Exporting bit data type data from SQL Server to PostgreSQL
I have table tbl_mak in SQL Server which contain's the following data:
Table:
CREATE TABLE tbl_mak
(
col_bit bit
);
insert into tbl_mak values(1);
insert into tbl_mak values(0);
After ...
-1
votes
0answers
11 views
How can I Convert a shapefile to postgis table?
I have using postgres sql pgadmin3 and i need to create GEO Server PGIS Layer.
thats why i need to convert shapefile to postgres sql database table.
-4
votes
0answers
27 views
Optimize Postgres TOP-n query
Table with two columns (transaction_id, user_id), both with index. Approx 10M records in table.
transaction_id is unique
transaction_id count on user_id varies from very few to thousands.
What I ...
0
votes
0answers
40 views
Segmentation fault (core dumped) on bulk deletion of objects
I am trying to delete large number of entries (approximately 1 million entries) from PostgreSQL database table. I am using a management command to execute this.
Using Foo.objects.delete() errors ...
2
votes
1answer
46 views
Result of where query in rails
I am performing a db search as below:
abc = params[:search]
Model.where("column_name ilike ?", "%#{abc}%")
When I am searching nothing it is running like below:
Model.where("column_name ...
