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.
0
votes
0answers
3 views
PostgreSQL LSH or another
How i can use locality-sensitive hashing in postgres or another simular algoritms?
Data:
[10,10,10,50,1,1,1,1,1]
[10,50,10,50,1,1,1,10,1]
[10,33,10,50,1,20,1,1,1]
[12,16,10,50,1,1,40,1,1]
...
0
votes
0answers
7 views
Saving check boxes choices with Perl and PostgreSQL
I'm trying to create a user's preference page that has a bunch of check boxes letting the user decide how they want to receive their notifications. Here is my current HTML form for preferences:
...
0
votes
0answers
21 views
postgresql max(count(*)) - php
I have a problem in postgresql.
I have one cohorte (gathering of people) and i would like counting the persons in this cohorte.
Begin date : "2014-09-01", End date : "2014-11-30".
I have 5 persons ...
0
votes
0answers
3 views
How can I prevent materialized views from refreshing during pg_restore?
I have created a dump of the database using pg_dump in "custom" format (-Fc). This format allows for pg_restore to be invoked with the "jobs" option (-j8). The jobs options starts 8 processes, and ...
1
vote
1answer
8 views
PostgreSQL function returns all records
How to modify existing function to return all rows from table sd_users?
CREATE OR REPLACE FUNCTION getusers()
RETURNS sd_users AS
$BODY$
DECLARE
groups sd_users;
BEGIN
...
0
votes
0answers
12 views
Filtering Invalid Byte Sequences for UTF8 Encoding in C# for a PostgreSQL Database
I have been searching and attempting a lot of different solutions to this problem, but have not found an answer yet. Basically, I am inserting data from an ODBC connection into a PostgreSql database ...
1
vote
1answer
23 views
Cascading selections in a simple SQL query
Let's say we have the following relations in a database:
likes(moviefanname, movietitle) which relates fans to the movies they like and its key is the combination of the 2 attibutes. Suppose ...
-2
votes
0answers
7 views
Function to export bytea data from database to a directory
I have the following function that imports the images to the bytea column, however I need to export images from database to a directory, can some please be kind enough to convert this function for me ...
0
votes
0answers
3 views
GitLab Omnibus - connect to built-in postgres
I used gitlab with GitLab Omnibus and the built-in postgres.
How can I find the psql password to connect on the database with my client ?
0
votes
0answers
9 views
Postgres multiple database connection
This can fall into the category of MSSQL USE database. I'm building a Postgres data warehouse for an existing transactional Postgres application. One of the main goals is to offload report generation ...
0
votes
1answer
9 views
Doesn't granting privileges on database include granting the privilegs on all database's tables?
I used to get errors when calling a simple select statement in PostgreSQL. The error description was telling, the user can't access the relation 'A'
My schema looks like this:
CREATE TABLE A(
id ...
0
votes
1answer
10 views
How to turn groups of rows into separate columns?
I have a postgresql table that looks like this:
a|b|c|result
0|3|6|50
0|3|7|51
0|4|6|52
0|4|7|53
1|3|6|54
1|3|7|55
1|4|6|56
1|4|7|57
Is there an easy way to SELECT something like:
a|result for ...
-1
votes
1answer
15 views
Revel with Gorm “undefined: Page”
I'm just trying to create new project with revel, gorm and pq.
I have model Page in app/models:
package models
import (
"time"
)
type Page struct {
Id int64
Title string ...
0
votes
1answer
24 views
Insert a string at a specific position in PostgreSQL
I have the following query
SELECT email_text INTO v_result
FROM test.record_notification
WHERE record_notification_id = p_recordnotification_id;
which returns the below result
[br/] ...
0
votes
1answer
33 views
Select Query for checking either or condition
I'm using postgresql 9.1 and wish to select some records from table.
Details are as below :
table name : muser
fields to be selected : userid, mailid, phonenumber
I have used query like :
SELECT ...
0
votes
0answers
18 views
I cant insert data continuously in my postgresql database using play framework
I cannot insert data continuously in my postgresql database. If i gonna insert first data to my database, the data was inserted in my DB. When I gonna insert next data, it is not get inserted. But its ...
0
votes
2answers
20 views
Ideas on getting average max on historic data transactions
I do hope this kind of question is OK. I do not know what to search for here on SO or Google etc. to find possible solutions.
I'm using PostgreSQL 9.3.
I've got loads of data with parking ticket ...
0
votes
0answers
14 views
PHP - pg_connect() cannot translate host name to address (via VPN)
I'm trying to connect via PHP to a remote PgSQL server, inside my VPN.
Here is the code (a simple script that extracts information from a table that contains a list of users):
$query = "SELECT name, ...
1
vote
2answers
19 views
Postgres pl/java caveats [on hold]
I would very much prefer writing DB side code in Java but would like to know if doing so has any disadvantages compared to pl/pgsql (especially performance & error handling related).
-4
votes
0answers
19 views
Converting rownum from Oracle to Postgres [on hold]
I have a dynamic function "WHERE ROWNUM < V_APPS';". I want to use this Oracle where clause in Postgres.
Please guide me how to change rownum in Postgres.
0
votes
0answers
27 views
manydimentional distance in postgres
Postgres / Postgis support distance calculations between points, sorting by distance from a given point and so on, I'm trying to abuse this facility :)
This is an example, just to illustrate what I'm ...
0
votes
1answer
10 views
sails connect to postgres
my adapter looks like this
config/connections.js
herokuPG: {
adapter: 'sails-postgresql',
url: 'process.env.DATABASE_URL',
pool: false,
ssl: false
}
package.json
{
"name": ...
0
votes
1answer
26 views
Selecting Positive/Negative Values of a computation SQL
I have a User model and a Workplace model. User has a field gender(m/f) and each workplace has many users. I want to select the count of total number of users in a workplace and also the count of ...
0
votes
0answers
15 views
Optimising Sidekiq, Redis, Heroku and Rails
So I'm trying to process a CSV file via Sidekiq background job processing on a Heroku Worker instance. Whilst I can complete the process, I feel it could certainly be done quicker/more efficiently ...
0
votes
1answer
19 views
Validate column using regular expression in postgre SQL
I need to check whether a column in a table having a numeric value followed by decimal point and 3 precisions after the decimal point. Kindly suggest how to do using regular expression in postgre SQL ...
0
votes
0answers
8 views
Error at server side while executing dss query
I'm using wso2dss 3.1.0 and wso2esb 4.7.0.I wish to fire select from esb.I have write select query in dss and dbs for this is like :
<query id="Capp_select_emercontactid" useConfig="default">
...
0
votes
0answers
29 views
Updating multiple records - poor performance
I'm wondering why it takes forever to execute statement below using postgres:
UPDATE left_table AS l
SET some_col=r.some_col
FROM right_table AS r
WHERE l.uid=r.uid
Both tables have index on column ...
2
votes
1answer
22 views
Effective way how to handle application settings in PL/pgSQL functions
Consider following situation: I have PL/pgSQL function which checks, If given auditor has some prerequisites for QS Auditor function. Thresholds of this prerequisites are defined in separate table ...
1
vote
2answers
23 views
Selecting Multiple Counts from the database
I have a User model and a Workplace model.
User has a field gender(m/f) and each workplace has many users.
I want to select the count of total number of users in a workplace and also the count of ...
0
votes
0answers
19 views
database table design foreign key to multiple table
In my PHP and psql application, I have large number of customers and service providers, both can login to the system, for login details we created a table USER but how can we map both CUSTOMER table ...
0
votes
1answer
9 views
generate json with column value as json dict key
I'm using postgres 9.2, and I'm trying to construct a query that generates json in a particular format. I've gotten pretty close with a simple json_agg expression, but now I'm stuck moving forwards.
...
0
votes
0answers
15 views
Postgresql shell query wrapping
I am using Postgresql's shell client, psql. If I type a query that extends past the console width, the shell does horizontal "scrolling", i.e. I have to move the cursor around to see the full contents ...
0
votes
1answer
14 views
ActiveRecord Postgres adapter generating invalid SQL WHERE clause: WHERE(“fieldname”)
ActiveRecord 4.1.1 is generating some peculiar SQL on save that postgres is choking on:
SELECT "projects".* FROM "projects" WHERE ('uid') LIMIT 1
PG::InvalidTextRepresentation: ERROR: invalid ...
2
votes
1answer
18 views
UnicodeDecodeError: 'ascii' codec can't decode byte in position : ordinal not in range(128)
I have done a bit of research on this error and can't really get my head around what's going on. As far as I understand I am basically having problems because I am converting from one type of encoding ...
-1
votes
0answers
23 views
Linux Apache server keeps crashing [on hold]
My apache server keeps crashing and I need to restart/reload it again manually. This error appears once a day. However,I see a pattern - the error times are the same (somewhere around 2'0 clock), but ...
2
votes
0answers
19 views
jqGrid JSON WIth SQL JOIN
I'm having difficulties coming up with a good solution to handle retrieving data from SQL (Postgres 8.4) and displaying it in jqGrid. The tables of concern are:
meta ("main" table)...
...
0
votes
1answer
17 views
Get best offers from the database query
I am running this query:
SELECT p.id, p.descricao, p_s.preco as antigo, pr.preco as novo
FROM produtos_supermercados p_s
JOIN produtos p
ON(p_s.id_produto = p.id)
JOIN promocoes pr
ON(pr.id_produto = ...
0
votes
1answer
33 views
Codeigniter PHP checking
how can i writer checking code with different table.
if data already exist in table STAFF user are allow to edit only
else user allow to insert then after that edit permanently.
more explanation ...
0
votes
2answers
24 views
Recursive sum in Redshift
CREATE TABLE testit (
id INT, v1 INT, v2 INT, result INT);
INSERT
INTO testit (id, v1, v2, result)
VALUES
(1, 1, 2, 1 )
, (2, 4, 3, 4 )
, (3, 6, 7, 6 )
, (4, NULL, ...
0
votes
0answers
30 views
Speed up PostgreSQL query in JDBC
I'm running a query against a large PSQL database in order to do a bit of parsing on the results, but I'm having a problem with the speed of it. The selection query is as follows:
SELECT cycle_id, ...
0
votes
2answers
19 views
Postgresql: Gaps Between tsranges, empty set
I have a tables of reservations for each user:
reservations_development=# \d reservations
Table "public.reservations"
Column | Type | ...
1
vote
1answer
16 views
Heroku nil class error
I just pushed my local Rails 4 app to Heroku, and I've got a strange error. As is often the case, it works fine locally with no issues. I think that the Heroku DB / environment is basically the same ...
0
votes
0answers
14 views
Configure STS with JPA 2.0 using PostgreSQL 9.3
I have an old Spring MVC project which I used to run with Eclipse on JBoss and using JPA 2.0 with (I believe) a PostgreSQL 9.1 DB. Also using Maven as dependencies resolver.
I managed to get the ...
0
votes
1answer
17 views
Dynamic conditional insert in postgresql
I am attempting to define a conditional insert in postgresql, on an index over 3 columns (which gives uniqueness). I'm trying to follow the following example from the postgresql documentation:
...
0
votes
1answer
14 views
rails 3.2 / postgres - how cast SUM() to return an integer not string?
Postgres aggregates like SUM(foo) return a string, even if foo is an integer.
How do I cast the sum to an integer so the resulting array of relations contains an integer not a string?
I tried to ...
0
votes
1answer
12 views
How do I write a has_many :through a scope by a particular ID?
My model reads as so :
class Registration < ActiveRecord::Base
has_many :registered_courses
has_many :courses, through: :registered_courses
has_many :sections, through: :courses
The ...
0
votes
1answer
16 views
postgres query has no destination for result data
I'm very new to pgSQL.
Can any one help with resolving this error?
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
...
0
votes
0answers
12 views
will_paginate, Rails 4.1.1 pg error with count
I receive this error:
Completed 500 Internal Server Error in 73ms
2014-06-24T16:23:01.334241+00:00 app[web.1]: Processing by CollectionsController#index as HTML
2014-06-24T16:23:01.409751+00:00 ...
2
votes
3answers
41 views
Get ANY(col) instead of MIN(col) from a group
I have a SQL Query (simplified from real use):
SELECT MIN(cola), colb FROM tbl GROUP BY colb;
But actually, I don't need the minimum value- any cola value will do- it's only used to show an example ...
2
votes
3answers
29 views
Avoid having the same function called multiple times when expanding the composite result
I have an SQL function retuning a composite result, e.g.
CREATE TYPE result_t AS (a int, b int, c int, d numeric);
CREATE OR REPLACE FUNCTION slow_function(int)
RETURNS result_t
AS $$
-- just ...
