Tagged Questions
PostgreSQL is an open-source, object-relational database management system (ORDBMS) available for many platforms including Linux, UNIX, MS Windows and Mac OS X. Please mention your PostgreSQL version when asking questions. Greenplum Database, Amazon Redshift, ParAccel, Postgres-XC, ...
0
votes
0answers
5 views
PostgreSQL count number of times substring occurs in text
I'm writing a PostgreSQL function to count the number of times a particular text substring occurs in another piece of text. For example, calling count('foobarbaz', 'ba') should return 2.
I understand ...
0
votes
0answers
3 views
spring-boot foreign key not found
I have a sprint-boot 1.1.4.RELEASE app that uses liquibase and postgresql.
I have two entities:
@Entity
public class Menu {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private ...
0
votes
2answers
22 views
What kind of index will help speed up my join?
I have two table, meta (a small table) and metastatus (a huge table). I have index on the ipaddr column which I will use to join them.
My query is
select m.ipaddr, m.machineclass
from meta m
join ...
0
votes
1answer
18 views
How select data with AND condition to many rows
I have table cars_colors (car_id integer,color_id integer). That shows - which colors does car have. Example of data:
car_id, color_id
151, 1
151, 2
151, 8
190, 1
190, 2
190, 3
That means - Car ...
0
votes
0answers
4 views
Performance hits modeling polymorphic hasMany relations in one vs many join tables on postgresql?
TL;DR - I'm looking for best-practices feedback on a polymorphic relationship table. Is it better to have a single table without foreign key constraints (making an _id and _type column for each side ...
1
vote
0answers
13 views
Rails postgresql. Collision of two operations
I've got this shop-like application (Rails 3.2 + Postgresql), where two of my resources/tables are Users, and Operations. It has the following characteristics:
Amongst other attributes, Users have a ...
0
votes
0answers
15 views
Postgresql recursive query challenge — simple parent/child example
I have not been able to adapt a previous recursive query solution to the following inverted tree. I'm trying to write a query that for each node in this tree shows each of it's paths and the depth of ...
0
votes
0answers
9 views
Postgress and PHP “Ident authentication failed for user”
On my local server (Ubuntu 14.04) all work ok. But on test server (Centos) i cannot connect from PHP (Yii 1.1) to my database.
On both servers i used same dump, user name, password, postgres and php ...
-1
votes
0answers
9 views
Draw a polygon on maps api v3 and save it to postgresql geometric column
1.- User draw a polygon in a web app.
2.- When user click save i want to save the data in a postgresql DB.
I'm having trouble with the polygon coordinates...
POLYGON COORDINATES VALUES IN A TEXT ...
google-maps-api-3
0
votes
1answer
31 views
Postgres while loop really slow
I got this sproc where i get the volume depending on a "FlowVolume" it could be 1, 10, 100. The only way that I found to make this work is the current implementation, but is really slow. The other way ...
0
votes
0answers
27 views
django & postgresql - could not connect to server: Connection refused
I have an os x server running django 1.6 and postgresql 9.
I have just noticed that if I logout of the machine I can no longer connect to my app from another machine.
The error is:
could not ...
0
votes
0answers
9 views
Redshift UPDATE prohibitively slow
I have a table in a Redshift cluster with ~1 billion rows. I have a job that tries to update some column values based on some filter. Updating anything at all in this table is incredibly slow. Here's ...
0
votes
0answers
4 views
PostgreSQL failing peer authentication with Ansible
I am running PostgreSQL 9.3 on FreeBSD. FreeBSD uses pgsql as the default system user for PostgreSQL. My /usr/local/pgsql/data/pg_hba.conf looks like this:
# TYPE DATABASE USER ...
1
vote
2answers
25 views
regexp_replace replaces wrong part of the string
When running:
select regexp_replace('( (test :Name (x) :Table (y) )','\s+\:Name \(.*?\)',' avner ');
I get:
"( (test avner "
But if I run:
select regexp_replace('( (test :Name (x) :Table ...
0
votes
2answers
21 views
initlocation not working in postgresql
I am using postgresql 9.2 on a RHEL 6.5 machine and would like to set up a database in a non-standard location as it is filling up the root partition. The instructions tell me to use the initlocation ...
2
votes
1answer
34 views
Getting a subquery to run N times
I'm trying to write a query that scans a table with multiple status entries for each date, for each test, for each area in a system. The goal being to get the newest status for each date for each test ...
4
votes
2answers
31 views
How to know the actual count in a huge table with high insertion rate
Let's say I have a table with exactly 10M rows. I need to know the exact count of the rows. A COUNT request takes 5 seconds. Let's say exactly 100 rows are added to the table every second.
If I ask ...
0
votes
0answers
12 views
ASP.NET MVC 4 with NHibernate and PostgreSQL
I have created a sample project using ASP.NET MVC 4 with NHibernate and MS SQL server which works fine but I need to use PostgreSQL instead of MS SQL Server. Can someone explain to me how to do this?
0
votes
2answers
20 views
Get list of database names using psql
I'm trying to get a list of database names using the psql command. So far I have:
psql -h example.com -U backup -c '\l'
This however gives me the results in a table-like format. I ONLY want the ...
2
votes
2answers
23 views
Writing in SQLite multiple Threads in Python
I've got a sqlite3 database and I want to write in it from multiple threads. I've got multiple ideas but I'm not sure which I should implement.
create multiple connection, detect and waif if the DB ...
0
votes
1answer
34 views
Slow PostgreSQL Query for Mins and Maxs within equal intervals in a time period
I have a system with lots of devices that take measurements. These measurements are stored in table "sample_data".
There could be 10M measurements in a single year per device. Most of the time the ...
0
votes
0answers
27 views
PostgreSQL Instead of update rule with multiple queries
I am having problems with INSTEAD OF UPDATE rule with multiple queries in Postgres - currently version 9.2.4 but I think I had the similar issue with previous versions too. I have a rule with three ...
0
votes
0answers
13 views
smtp connection in postgresql
Trying to implement mail funtionality through postgresql procedure-
Below is the procedure
PROCEDURE send_mail(p_smtp_host character varying, p_smtp_port integer, p_from character varying, p_to ...
0
votes
0answers
11 views
Postgresql default privileges for new schema in multi-tenant Rails application
In my Rails 4 multi-tenant application, I create new schemas in Postgresql with the gem Apartment.
It works very well but I have a problem with the privileges for new schemas.
In Postgresql, I ...
0
votes
0answers
26 views
simpleJdbcCall returning only 1 row
I have written a stored procedure in pl/pgsql and it is working fine in pgAdmin. And i am using simpleJBDCCall to call the procedure which returns a set of record. When i call the stored procedure ...
0
votes
1answer
27 views
Stop rails from escaping hstore array in views
I'm trying to use a postgreSQL column of type hstore array and everything seems to work just fine. However, my views escapes the array and turns it to bad formatted string. My code looks like that:
...
0
votes
1answer
20 views
How to separate rails applications due to postgres database?
I'm new in Ruby on Rails, and even more new in postgres. I have several applications in RoR with postgres db. I would like to separate psql databasis for every of rails app. Now it seems like every ...
0
votes
2answers
24 views
How to create association one to many and many to one between two entities in gorm?
I'm new in Grails. I have a problem with generation association many to one and one to many between two tables. I'm using postgresql database.
Employee.groovy
class Employee {
String firstName
...
0
votes
1answer
19 views
create connection to localhost postgre server
I am creating a rails app and want to use postgresql. I have installed pgadmin, postgre sever on Ubuntu 14.04. I've tried creating a connection through pgAdmin but not able to create it. I am unable ...
1
vote
2answers
27 views
postgresql daysdiff between two dates grouped by month
I have a table with the date columns (start_date, end_date) and I want to calculate the difference between these dates and grouped by the month.
I am able to get the datediff in days, but I do not ...
2
votes
1answer
26 views
How to restore specific data from previous backup on Postgres Heroku? (Eg. Accidentally deleted rows)
Here's the situation : With Heroku & Postgres, you can have automatically generated backups dump file. But what can you do with it?
Dump it on your database, if you want to fully go back to the ...
0
votes
2answers
10 views
fetch datas from PostgreSQL database
I have created two tables in a PostgreSQL database named doctor and department. I have used dept_id as foreign key in the doctor table. Now I have listed the departments in Eclipse but when I'm ...
0
votes
1answer
14 views
AWS: Can a Beanstalk instance be deployed with a Postgres RDS?
Does anyone know whether an Elastic Beanstalk (EB) instance can be deployed with a Postgres RDS?
While Postgres is one of four options for RDS, the EB doc curiously only mentions the three others: MS ...
0
votes
2answers
17 views
Can't rename PostgreSQL database
I'm using PostgreSql 9.2.
Trying to do
ALTER DATABASE 3_8_dev_test6 RENAME TO 3_8_dev_test
but get:
ERROR: syntax error at or near "3"
LINE 1: ALTER DATABASE 3_8_dev_test6 RENAME TO 3_8_dev_test
...
0
votes
1answer
17 views
postgresql extract non alpha numeric
I want to return the non-alpha numeric characters in my character fields in postgresql. Excluding spaces in the result as well. I want to be able to see only all non-alpha numeric characters in my ...
0
votes
1answer
17 views
JPA(Hibernate) Map String to postgres XML datatype
I'm trying to get the following setup to work:
Wildfly 8.1.0 (comes with Hibernate as JPA implementation)
Postgres 9.3 database
I want to map a Java String to a colum of postgres' xml-type, but i get ...
0
votes
1answer
6 views
PgHero Rails - column “pid” does not exist
I am trying to setup PgHero in an application. I keep on getting following error
Started GET "/pghero" for 127.0.0.1 at 2014-09-09 12:35:41 +0530
Processing by PgHero::HomeController#index as HTML
...
0
votes
2answers
20 views
How do I discover the underlying query of a materialized view I created?
I created a materialized view in Postgres 9.3 but I have since lost the underlying SELECT query that created it. I would like to DROP the materialized view, rewrite the query to include more data, and ...
0
votes
0answers
16 views
rails 4 params on hash and save it into db with virtual attributes
first, sorry for my bad english..
i got some problem here
i have the output data from view form
{
code: 'code supplier',
name: 'name supplier',
address: 'address supplier',
phone_number: 'phone ...
0
votes
1answer
14 views
Ruby on Rails - order by updated_at by ignoring seconds and milliseconds
Want to display all Users order by updated_at column.
But I should ignore seconds and milliseconds.
Using Postgres database.
Currently updated_at is having values like "2014-09-08 12:59:32.61052"
...
2
votes
0answers
9 views
Connecting Tableau to Heroku Follower Database
Have created a follower database for a Heroku Postgres DB. I am able to connect to the follower DB by using pgAdmin, no problem.
When I try to connect with Tableau 8.2 Postgres connector using the ...
0
votes
2answers
17 views
Use Ruby to query a Postgres DB using a db url
In a Ruby script, I want to connect to a Postgres DB via URL, and do a simple SELECT * FROM users hello world query.
What is a gem to do this? I haven't found any gem that lets me connect through ...
0
votes
2answers
25 views
R or QGIS direction for distance
Looking for either a R or QGIS solution: I have file with lat long, and group name. I'd like to find either a) distance from a starting point to an end in a group, b) average distance between ...
2
votes
1answer
15 views
How do I store an unsigned int in postgres sql?
How do I store an unsigned int (uint32) in postgres? I noticed that numeric(10,0) would fit the number of digits, but is this the best way?
On further research another similar problem is storing a ...
0
votes
2answers
27 views
SQL left outer join but detect what row were “filled” with nulls
I have a SQL LEFT OUTER JOIN with a one to many relationship. Here is an example
Table A
+------+------+------+
| Col1 | Col2 | Col3 |
+------+------+------+
| a1 | a2 | a3 |
| b1 | b2 | ...
0
votes
0answers
12 views
Solutions to migrate databases [migrated]
I'm working in redesign an application. This application has a MySQL database and has approximately 50 tables (there are no relations , there are not standars, etc). We want to migrate to Postgresql ...
0
votes
0answers
16 views
Rails States I do not have permission to access the table?
I am trying to run bundle exec rspec and I run into:
`async_exec': PG::InsufficientPrivilege: ERROR: permission denied for
relation schema_migrations (ActiveRecord::StatementInvalid) : SELECT
...
2
votes
2answers
32 views
PostgreSQL query to detect overlapping time ranges
I have a table in PostgreSQL 9.2 that looks like this (simplified):
CREATE TABLE my_features
(
id integer NOT NULL,
feature_id integer NOT NULL,
begin_time timestamp NOT NULL,
end_time ...
0
votes
0answers
21 views
return nested hours of operation in a postgresql query
I have a table of garages. I am allowing for an API query that should also be able to return the hours of operation for the garages. The hours of operation are related to the garage in a TimeBlock ...
0
votes
1answer
16 views
Postgresql function from a SQL Server stored procedure
I have a procedure in SQL Server as follows:
ALTER procedure [EI].[GetAdmByYear]
@YR int ='2012',
@ReportId nchar(7)='ECCSUR1'
as
begin
Declare @TestTypeID int
select top 1 @TestTypeID = ...
