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
3 views
Concatenate varchar[]'s in PostgreSQL, keeping distinct values
I've got a database containing network assets, each of which can have multiple hostnames. Sometimes, a we'll mistakenly create two records for the same machine, and then later have to "merge" these ...
0
votes
0answers
11 views
postgres 9.5 pg_hba.conf not allowing access from the outside.
Today I upgraded from 9.4 to 9.5, on the same VM.
Wehn I tried to copy over the existing pg_hba.conf file, it threw some error, and I have been trying to rebuild it all day.
/pg_hba.conf
# TYPE ...
1
vote
1answer
15 views
How to separate exported data from Postgres into distinct tabs in CSV format?
I am trying to export data from Postgres to CSV so that I can import it to stormpath, which requires:
"It also requires the CSV file to have columns:
username
email
given_name
...
1
vote
1answer
7 views
When doing an ActiveRecord query, how do I prioritize a record that has an attribute marked as true first then created_at desc after?
I have a bunch of Answers on a Question.
What I want to do is order the answers on each question in two steps:
If there is an accepted answer, return that one first, THEN...
Return the rest of the ...
1
vote
0answers
18 views
SQL - How to eliminate duplicates from the below query in POSTGRES
I have been working on the below query. Basically there are two tables. Realtime_Input and Realtime_Output. When I join the two tables and take the necessary columns, I made this a view and when i ...
0
votes
0answers
16 views
Week - Ending Conversion Error
The vast majority of this query works. I'm just attempting to convert week_ending from Monday to Saturday.
WITH tag_feature AS (
SELECT o.id as orgs
,(date_trunc('week', ...
0
votes
1answer
16 views
Select events based on criteria in belongs to table
class User < ActiveRecord::Base
has_many :events
has_many :trainings, -> { distinct }, through: :events
end
class Event < ActiveRecord::Base
belongs_to :user
belongs_to :training
end
...
0
votes
0answers
25 views
Postgresql flatten column & show entire information [duplicate]
I have a table as follows:
conversation_thread |postid| postType | post_text
AA11 | 1 | Question |'How many whiskers do cats have?'
AA11 | 2 | Reply |"I think ...
-1
votes
1answer
20 views
Postgresql insert data error when using python
I am trying to insert data to the table that was created earlier using python script. Here is the code I am trying to execute. I want to insert data into table with date as well.
date_today = ...
0
votes
1answer
5 views
Specify PostgreSQL passwords in Ansible Playbook
New to Ansible, Running version 2.1.0. I've written an Ansible playbook that runs a PostgreSQL query against a group of hosts. When I specify the SQL DB password in the shell command it works, but ...
0
votes
1answer
20 views
How to add a user to a rails app manually?
I've been assigned to work on a legacy Rails app, and nobody has a development environment working. I've gotten everything up and running, but I can't create a new user on my development environment ...
0
votes
1answer
23 views
Can I express in SQL that an aggregation has only a single value?
Suppose I have a query like this:
select *
from (values
-- A B C
(1, 11, 92),
(1, 11, 117),
(2, 11, 97),
(3, 22, 65)
) x(a,b,c)
And then assume that we know that for each ...
0
votes
0answers
10 views
Spring/JPA PostgreSQL - Spring Data Auditing INTEGER instead of bytea, Envers
In my Spring/JPA/Hibernate/Envers PostgreSQL application I'm trying to implement Spring Data Auditing
I have a following entity:
@Audited
@AuditTable("levels_history")
@Entity
@Table(name = ...
0
votes
2answers
48 views
Rails - show last record
I am trying to show the last updated record of 'weight' from this DB:
DB structure
I am trying to call it using:
<%= @user.weights.last %>
I didn't get an error but I get this string ...
0
votes
0answers
14 views
Create new tables using existing table in sqlalchemy
I have a table A from which I want to query data and create two new tables, one with exactly same schema as the original table, and the other with selected columns from the table A.
When creating a ...
0
votes
0answers
18 views
Psql function with array of json
I am trying to write a function that has 2 arguments: one array of integers, and one array of JSON
an example of my JSON {[{day:1},{day:2}]}
my function:
create or replace function ctrl_func(theids ...
0
votes
1answer
27 views
Is a two table solution a performant and scalable solution to implement tagging in Postgres 9.5?
Background
I work for a real estate technology company. An upcoming project involves building out functionality to allow users to affix tags/labels (plural) to a MLS listing (real estate property). ...
0
votes
1answer
14 views
casting back to a type when calling a function on a query result (in postgres)
I'm passing the result of a query to a function, the function returns a SETOF or a TABLE (Tried both). The final result is an anonymous record, and I can't seem to cast it back to the type of the ...
0
votes
1answer
34 views
Converting from sqlite to postgresql
Hello I started my rails application using sqlite, however when I tried deploying it on heroku I found out that I needed to use postgreSQL. So I went through the trouble to change my gemfile and ...
0
votes
0answers
16 views
can't connect to Postgres and fetch metrics using pgbench
if [ "$2" == "--debug" ]; then
debug=on
echo "Debug is on. Data will be sent to stdout."
fi
if [ -f ${libPlotter} ]; then . ${libPlotter}; else echo "FATAL: libplotter must be installed"; ...
0
votes
1answer
10 views
How to aggregate Postgres table rows
I have a table constructed like this :
Date | Account | Total
1 | A | 1000
3 | A | 1000
4 | A | 200
5 | A | 3000
2 | B | 100
3 | B ...
0
votes
0answers
12 views
Nested transaction in Spring app with JPA (postgres)
Is it possible to have PROPAGATION_NESTED in @Transactional() with JPA using postgres? I am getting:
org.springframework.transaction.NestedTransactionNotSupportedException:
JpaDialect does not ...
1
vote
2answers
19 views
psycopg2 unable to insert into specific columns
I have a postgres database that I am accessing via Python using the psycopg2 library. The table I am attempting to insert into looks like this:
...
0
votes
3answers
24 views
PostgreSQL difference over time period
I've a series of data being collected every minute and stored in a Postgres database. I'm trying to create a query that will show the difference in the value over that 5 minute period.
So where I ...
0
votes
1answer
16 views
How to install PgBouncer on Windows 7 (x64)
I am attempting to install PgBouncer on my computer with no success. In the installation page, im told that PgBouncer has two dependencies GNU Make 3.81+ and libevent 2.0. Never having used or ...
0
votes
1answer
18 views
Ruby & Rails - Seeding Data With Associations from a CSV
I'm new to Ruby & Rails and I am trying to seed a rails database with data from two CSV files and create an association between them. Here is what I have so far:
seeds.rb
require "csv"
...
-1
votes
1answer
15 views
Postgresql: Insert number in language specific format
How can I insert number in language specific format to Postgresql database (locale are set)? Numbers are like this: 2,4 or: 4,5 (comma instead dot - that is correct in many languages). Should I ...
0
votes
1answer
17 views
Using window functions to reduce amount of lines implementing data ranges
I want to reduce the amount of lines in a table, which has a lot of duplicated data. My first thought was to use some window function to define the date ranges to save in the table, so that whenever I ...
-1
votes
0answers
38 views
PostgreSQL 9.4 Insert data from backup file into the table with UNIQUE constraint
I've seen a lot of similar topics, but no any clear solution.
What i have:
Production DB on Amazon RDS. I need to restore/input data to one of the tables in that DB from the dump (.backup) located on ...
-1
votes
0answers
19 views
Sending psql request in Python w/ Export statements
I'm attempting to perform a bash psql request structured like so:
export PGHOST=my.really.long.hostname.com
export PGDATABASE=mydb
export PGPORT=1234
export PGUSER=mydb_usr
export PGPASSWORD=Ysnp!
...
0
votes
3answers
14 views
What's the relationship between res.partner and res.user?
I am new to odoo v8 and i am not able to understand the relationship between res_partner and res_users tables and also with hr_employee table are they all related?
0
votes
0answers
30 views
ruby on rails query on jsonb field does not work
I have a table in the PostgreSQL 9.5 with jsonb column called segmented_data and I have a record with some data in this field
ProjectKeyword.first
=> #<ProjectKeyword:0x007fa83a17e7f8 id: 2201, ...
0
votes
1answer
30 views
Why PostgreSQL json_agg() function does not return an empty array?
I'm returning a JSON array from a PostgreSQL request with the json_agg function. However, when no row is found, json_agg returns an empty string instead of an empty JSON array [] (square brackets are ...
0
votes
0answers
11 views
How to drop Postgres Types using Spring boot's liquibase options?
I want to completely drop my Postgres database every time i run my application while developing. There is a Spring Boot property liquibase.drop-first, which i set to true. Unfortunately it only drops ...
1
vote
1answer
47 views
How to query most recent five transactions per item in sql
There is a transaction table and in there there are columns such as transaction id, name, and createdat.
There are multiple transactions for each name whose createdat data will be all different.
I ...
0
votes
1answer
34 views
Java getTime() getting time from `time with time zone` not getting correctly
I have time with time zone in my postgresql database. My location is India so it is stored as 10.00.00+5.30. I am getting the result using java Resultset as follows:
Suppose:
String time = ...
0
votes
1answer
31 views
Select next 24 hours for every record in postgresql
My Table looks like this:
| Attackerip | timestamp |
Now I want to check how many attempts per 24 hours from the first attempt each IP has made. I want to have all results with less than 5 attempts ...
0
votes
0answers
16 views
How to set lc_numeric in Postgres permanently for specific database?
I want set LC_NUMERIC permanently for my databases. I know I can use show LC_NUMERIC; to check current value and then set LC_NUMERIC=pl_PL;. But it only works until I disconnect. postgres.conf is no ...
2
votes
0answers
27 views
Hibernate not creating Database in PostgreSql Using Spring boot
I am trying code first approach to create database using Spring Boot and Hibernate.
But, I am not able to do that yet.
Please suggest how can I Solve this.
Here is my POM
<?xml version="1.0" ...
0
votes
0answers
20 views
Do I use the postgres 'default administrative connection database' to house my schema?
We are attempting to convert our Oracle database to PostgreSQL (due to Oracle's ludicrous pricing model). The system is not in production yet but will soon so this needs to be done ASAP. Thank ...
0
votes
1answer
25 views
Hibernate _AUD Table - FOR statement from SELECT
I have to update an hibernate _aud table manually using Liquibase and Postgres.
I will explain my algorithm:
FIRST : Select all distinct id from person_AUD table
select distinct id from ...
1
vote
0answers
15 views
How to use whitelists and prepared-statements with Postgresql in php?
I understand that I need to implement whitelists and prepared-statements into my php code. But I'm not sure how to do this with Postgresql and is it really necessary for my code? I'm using select ...
0
votes
1answer
31 views
postgres data fetch between a defnite time everyday [on hold]
i am writing a query to get data from a table where last_activity is between 12 am to 3 pm everyday basis.
my code is:
SELECT *
FROM person_info
WHERE (EXTRACT('hour' FROM last_modified_date) >= ...
1
vote
2answers
29 views
Limit per some field
Suppose we have such an sql query, with joined data from another table.
SELECT
pr.num, pr.resort_id, p.src_mask
FROM
rutraveler.rt_photo_resort AS pr
JOIN rutraveler.rt_photo AS p ON ...
0
votes
1answer
47 views
How can I have a dynamic connectionString for each user (each user have connection string) with ASP.NET MVC4 and postgres, I'm using entityFamework
Code:
String cstr = "User Id=snameche;Password=xxx;Host=localhost;Database=guce;Initial Schema=public";
using (var connection = new PgSqlConnection(cstr))
{
...
0
votes
1answer
34 views
SQL: Dynamically Transposing Column as Row in Chartio
I would love to have your help transposing this table's column into a row. I am trying to have the Project Names (column 1) each becoming their own column header with the MUVs value entered as the ...
0
votes
1answer
32 views
Unable to execute the function in plpgsql/postgres
I want to calculate distance from address points to all streets within a distance of 50 meters using plpgsql. I have tried to write following function:
Create or Replace Function get_dist(ad ...
0
votes
2answers
36 views
How to remove elements of array in PostgreSQL?
Is it possible to remove some elements in array?
Before removing elements Array1 is :
{1,2,3,4}
Array2 that contains some elements I wish to remove:
{1,4}
And I want to get:
{2,3}
How to ...
0
votes
2answers
46 views
Can't add foreign key on relation table
I have two models:
class BracketMatch < ActiveRecord::Base
belongs_to :match
belongs_to :bracket
end
and
class Bracket < ActiveRecord::Base
has_many :bracket_matches
# Has a STI ...
