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
1answer
5 views
Postgresql Logger Process
I'm trying to determine if Postgres 9.3 still has a logger process. It isn't referenced anywhere in the "PostgreSQL 9.3.4 Documentation". And I can't find it in my cluster's process list (see ...
0
votes
0answers
4 views
Heroku postgres pgpool-II support for read only query load balancing
TL:DR Does Heroku support pgpool-II for master-slave read only load balancing? Other solutions?
We are planning to go live with our application on heroku within the next couple of months. The biggest ...
0
votes
0answers
27 views
Database Normalization - increment in dedicated table or in referanceing tables
I have a data set that I am trying to normalize and I am bumping into a dilemma.
In Option 1, I have a table foo_events that only adds an event to table foo. This event is then referenced by tables ...
0
votes
0answers
8 views
Automating the reset of Max ID in PostgreSQL Table after CSV import in rails
In my application, I use CSV import to add content, however, after a CSV import, when I try to create a new object, I get an error saying that the IDs conflict. I have figured out how to solve this ...
1
vote
1answer
12 views
Rails & postgres - reindex after restore
Recently I ran into a situation where I had to restore a backup database from Heroku to my local setup because of a configuration error I had made.
This was very straight-forward from a Heroku post:
...
0
votes
0answers
5 views
Laravel migration and PostgreSQL shemas
i using PostgreSQL and want to try Laravel.
First - my up() function:
public function up()
{
Schema::create('entries.entries', function($t) {
$t->increments('id');
...
-2
votes
1answer
20 views
postgresql regexp_replace with character '
I have a table in which some variables are with the characters' and a space in front of the data, eg
name | code
manoel | '02
the code should be 4 digits thereby 0002
because the damn ...
0
votes
1answer
17 views
Ordering postgres database with ASC
I am trying to get my list of skills to be showed in alphabetical order. I have checked on:
http://guides.rubyonrails.org/active_record_querying.html#ordering
and am using this line in my Skill.rb ...
0
votes
0answers
6 views
Heroku: Secure data exchange between two applications
I'm working on a problem that requires data to be exchanged between two applications with their respective databases securely. Is there a way to do it in a way that doesn't requires a complex setup ...
0
votes
1answer
12 views
Postgres ignores table constraints, scans all inherited tables even if constraints don't match
I have a Postgres table from which about 1000 inherited tables have been created for partitioning data.
Each inherited table has a constraint on a timestamp column called capture_time
...
0
votes
1answer
34 views
PDO ERROR (too many clients already)
SOLVED by adding if statement before new PDO...
DB - POSTGRESQL
Hi, i have a cycle, that connects to db in every loop with this function:
class mypdo
{
public function open()
{
...
0
votes
0answers
21 views
How can I tell django to include “ON DELETE CASCADE” into the schema?
I use django (1.6) to create a postgreSQL database that I also access via other connections (currently java and C++). I have two tables that are linked with a foreign key and I want to use a cascading ...
0
votes
1answer
24 views
is it possible to connect two dbms MySQLand postgres SQL in codeigniter?
I am developing like fair size project which is in developing stage using MySQL as a primary database. Now, client has some data, which is in postgres SQL, which can not be converted into MySQL. So, ...
-1
votes
0answers
22 views
UniDac with sequence insert after postgresql upgrade
We're upgraded our PostgreSQL from 9.1 to 9.3
And now we have problems on insert with serial types:
2014-08-09 09:55:28 GMT ERROR: relation "spareparts_id_seq" does not exist at character 16
...
0
votes
2answers
23 views
String Manipulation in PostgreSQL [on hold]
since am new database development my question may very basic.
How to get a output like below in PostgreSQL
outputString
------------
a
aa
aaa
aaaa
aaaaa
aaaaaa
aaaaaaa
aaaaaaaa
aaaaaaaaa
...
0
votes
0answers
13 views
Django + Psycopg2: InterfaceError: only protocol 3 supported
When loading pages in my Django + Postgresql setup I occasionally get this error but not all the time:
psycopg2 in connect
InterfaceError: only protocol 3 supported
psycopg2/__init__.py ? in ...
1
vote
0answers
20 views
Running a function in postgresql Throwing a runtime error
I am getting a mysterious error when I try to run a function which returns table my function code is like
CREATE OR REPLACE FUNCTION FN_JOURNEY_SUMMARY(IN ENTITY INTEGER,IN VEHICLE VARCHAR2,IN SDATE ...
0
votes
1answer
26 views
Postgres: Is there a way of executing code following a INSERT statement?
This may seem strange, but I was curious to know if it was possible for a code block to be executed following an INSERT statement in a postgres database?
Specifically, I'm interested in executing ...
2
votes
1answer
32 views
Golang postgresql LIKE query
i'm working with golang and postgresql (pq driver), i have the following query
SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE %$1% ORDER BY ...
0
votes
1answer
6 views
Migrating a Postgres Table in Rails to Ensure a combination of column values is unique
In postgres you can do the following:
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c)
);
Is there anyway to specify that a combination of values on two columns must be ...
2
votes
2answers
10 views
Using pgadmin to check status of Postgres advisory locks
I'm trying to use Postgres advisory locks in my Django app, and it seems like acquiring is frozen.
I want to use the pgadmin GUI to see which locks are acquired and which aren't. I tried using it, ...
0
votes
2answers
19 views
PostgreSQL row inserting CTE with extra returning fields
Long time reader first time asker here!
In my database, I have the following (simplified) sample tables with some example data,
create table file_zips (file_id int, zip varchar(5), total int);
...
0
votes
0answers
17 views
Point plperl to different version of Perl in PostgreSQL
I have PostgreSQL 9.3 running on a Linux server with the plperl extension. When it was installed, it appears it picked up the normal /usr/bin/perl, which is 5.10, but I'd like it to point to a ...
0
votes
1answer
17 views
Django + Postgres + Large Time Series
I am scoping out a project with large, mostly-uncompressible time series data, and wondering if Django + Postgres with raw SQL is the right call.
I have time series data that is ~2K objects/hour, ...
0
votes
2answers
28 views
Select count of max items, get rank and percentile
I have a table with multiple entries per person_id column. I'm storing a score, a category_id, and a created column as well. So every time the person completes the indicated category I store a single ...
0
votes
3answers
35 views
Optimization for fetching from a bulky table
I have a PostgreSQL table that has millions of record. I need to process every row and for that I am using a column in that table namely 'isProcessed' so by default it's false and when I process it I ...
1
vote
1answer
22 views
Postgres combines two fields into one with UnknownType values
I'm using the squirrel sql client to work with Postgres and problem is that the same query returns different results.
If a query below execute as is, it works fine:
select foo.column1 as Field1, 1 ...
0
votes
2answers
20 views
postgresql create function with multiple IF ELSE condition
I am creating a function in postgresql which will do something like following:
CREATE OR REPLACE FUNCTION check_label_id_exist(_labelid integer, OUT result text) AS
$BODY$
DECLARE
BEGIN
SELECT ...
0
votes
3answers
19 views
creating multiple foreign keys on arbitrary number of columns
I'm creating a table for a Customer, with a lot of columns representing possible Contracts they may have (at least one). The columns for possible contracts will have an integer which corresponds to ...
2
votes
1answer
29 views
RAILS 4 ACTIVERECORD Retrieve newest records by key
I have a table in my DB (PostgreSQL) which is roughly as follows:
Currency Rate Created_at
USD 13.5 08/07/2014
EURO 18.5 08/07/2014
USD 13.3 08/08/2014
EURO ...
0
votes
1answer
9 views
pgbench for postgresql 9.3 for centos where to find it?
How can I install pgbench for postgresql 9.3?
I have basically set up my postgresql9.3 on centos 64 bit, and it runs fine. No problem at all.
I then installed postgresql93-contrib on my centos ...
0
votes
1answer
13 views
Django Postgres Primary Key Value Not Incrementing Correctly
I have a django model that started to have its ids increment strangely.
The postgres definition of the column (generated from Django Model):
id | integer | not ...
0
votes
1answer
29 views
rails activerecord sum works with .each but not with others
I am trying to calculate balance in activerecords, one works and the other doesnt.
1=> why is this does not work to get the balance:
@accounts_balances = Account.select('(sum("in")) - (sum("out")) ...
1
vote
1answer
31 views
Handle and display large data set in web browser
I am still a noob in web app development and sorry if this question might seem obvious for you guys.
Currently I am developing a web application for my University using Python and Django. And one ...
0
votes
1answer
18 views
PL/pgSQL return query null type is not returned
I in a PL/pgSQL function i have the following statement:
RETURN QUERY SELECT pd.* FROM "PushDevice" pd
WHERE pd."PushNotificationId" =$4 and pd."sentAt" is null;
the PushDevice table now ...
1
vote
1answer
11 views
Copying CSV to Amazon RDS hosted Postgresql database
I have a database hosted using Amazon's RDS service and I am attempting to write a web service that will update said database. The problem I am having is that it will not let me use the COPY command ...
1
vote
2answers
42 views
Using one SELECT instead of two to serve a side-loaded API request?
Let's say I create two tables using the following SQL,
such that post has many comment:
CREATE TABLE IF NOT EXISTS post (
id SERIAL PRIMARY KEY,
title VARCHAR ...
1
vote
1answer
17 views
Rails: track users group by hour created_at and ip address
I have the following impression model that tracks all users interactions within the app:
class Impression
belongs_to :user
end
create_table :impressions do
t.string :ip_address
t.integer ...
0
votes
3answers
41 views
Convert Minutes to Hours in PostgresSQL
Currently having Minutes(bigint) as
390
I want to convert this Minutes to hours. Is there any function to convert it?
Eg: 390 as 6.30
0
votes
0answers
21 views
How to grant privilages on all tables to user in postgresql 8.4 [duplicate]
Hi i created a dateabase user and i gave the user all the permissions of dsatabase
grant all privilege on database db_name to someuser;
but when i try to access it gives me an error .
permission ...
0
votes
1answer
24 views
Matching array values in PostgreSQL with ActiveRecord
In my Rails 4 app I have a goal to see all contacts, where field visible_to in contacts table equal to 1. My visible_to is :integer, array: true.
However, I get the following exception:
...
1
vote
2answers
16 views
How to get the trigger(s) associated with a view or a table in PostgreSQL
I have one requirement that I have to get the list of triggers associated to the given table/view.
Can anyone help me to find the triggers for a table in PostgreSQL?
0
votes
2answers
20 views
PL/pgSQL variable percieved as column
I have a problem with my Postgres and it looks like a simple one. I have done my research but I have not seen anything similar online and would like some clarification:
This is done inside a ...
1
vote
3answers
26 views
Postgresql function per row
I am currently learning postgresql and have a noob question. If I have a simple table like this:
CREATE TABLE rectangle
(
width int,
length int
);
And a function like this:
CREATE OR ...
0
votes
1answer
21 views
How to get pg_escape_literal on PHP 5.3?
I am developing some project, where I have postgre database. PHP is 5.3 (cannot be updated, because it is quite big webpage), so pg_escape_literal is not supported. I google everything about it, but ...
0
votes
3answers
35 views
Insert Result Query Into Table
I have problem with an insert query in Postgresql.
I have query like this :
select *
from (
select *,
row_number() over (partition by id order by id) as row_number
from lookup_temp
) ...
-1
votes
1answer
37 views
psql: FATAL: Ident authentication failed for user “root”
Hi i am using postgresql .
my
Postgres User : postgres
Passwprd : abc123
but when i type root@xxxx:~#psql -U postgres -W
it says
root@xxxx:~# Ident authentication failed for user "root"
...
1
vote
1answer
15 views
Autocommit in Flask-SQLAlchemy
How do I set initilize my Flask application to set Flask-SQLAlchemy to autocommit mode, to not use transactions unless I explicitly session.begin()?
The session "begins a database transaction as ...
-2
votes
0answers
15 views
ActiveRecord::StatementInvalid: PG::Error: ERROR: Array value must start with “{” or dimension infromation [on hold]
when to insert a new record into searches table following error occurred.
ActiveRecord::StatementInvalid: PG::Error: ERROR: Array value must start with "{" or dimension infromation
Here is the ...
0
votes
0answers
29 views
Calling functions in postgres like the package in Oracle {call schema_name.package_name.function(?,?,?,?,?,?) with placeholders
Converting a package from oracle to POSTGRES But POSTGRES does not support the package so firstly convert the packages to function through Ora2pg tool.
Situation is That the package is being called ...
