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
6 views
How to handle pgPL/SQL exceptions
Short and simple:
How to catch pgPL/SQL exceptions with QtSQL?
I haven't found any example or info at the docs.
More details:
I'm developing the front end for a system with Qt 5.3. The database ...
1
vote
1answer
15 views
Manually locking of DB necessary even if I use Hibernate/Postgresql/JDBC?
Sorry to ask this in case it has been answered before, but I heard (from a potential other noob) that Hibernate has/had some kind of connection pool manager that also handles locking of the database. ...
4
votes
2answers
20 views
How do I force filter evaluation order in SQL(Postgres)?
I have a table that, simplified, looks roughly like this:
id | type | header | body
===========================================
1 | A | {type: A} | {content: "Hi"}
2 | A | {type: A} | ...
0
votes
1answer
10 views
Postgresql copy command error using WITH and AND
I have the following command where i am trying to copy a csv into a postgre table.
copy "SIS_student"(
student_id, user_id, peims_id, district_id, prior_identifier,
...
0
votes
1answer
11 views
Find out which DB Heroku is currently using
I recently had a short term project served off of Heroku that approached 10,000 Postgres records and needed to be upgraded from hobby-dev to hobby-basic. Being new to Heroku, I did my best in finding ...
1
vote
2answers
23 views
PostgreSQL order of addition/subtraction and aggregate functions
What is the order that each step of this query are executed in PostgreSQL?
SELECT SUM(field1)+SUM(field2)+SUM(field3)-SUM(field4);
I gather that the addition/subtraction occurs in the regular order ...
1
vote
1answer
9 views
Batch replace with postgresql
I was wondering how to go replacing a series of strings in batch. For example if I wanted to remove full stops from a field and also change double spaces between characters into single spaces. I know ...
0
votes
1answer
12 views
Substituting variable into SQLAlchemy / Postgresql Query
I'm trying to run a SQLAlchemy query in my Flask app using. I want to grab the URL parameter ('menu') and substitute it's value into my query...
@app.route('/attend/')
def attend_by_role():
role ...
0
votes
3answers
16 views
Storing Zip file in Postgres
I've been storing the contents of zip files in a LONGBLOB in a MySQL database, but I'm moving to Postgres. From what I've read, the equivalent to LONGBLOB in Postgres is bytea. Unfortunately, I ...
0
votes
2answers
23 views
Postgres: Get aggregation query based on multiple values in a column
I have following database table:
id, owner_id, partner_id, level
1, 121, 234, gold
2, 121, 342, silver
3, 121, 423, silver
4, 233, 333, silver
5, 233, 434, gold
I am trying to get result in ...
2
votes
4answers
64 views
Postrges vs oracle doing 1 million sqrts am I doing it wrong?
We am trying to get an idea of the raw performance of Oracle vs PostgreSQL. We have extensive oracle experience but are new to PostgreSQL. We are going to run lots of queries with our data, etc. But ...
0
votes
0answers
14 views
PG::UndefinedColumn, Rails_Admin
Rails rookie here. Horrendous understanding of the backend. But trying. When I attempt to edit a product in my admin section (rails_admin), I get the following error message:
...
0
votes
0answers
4 views
postgresql convert from one encoding to other
I wrote a csv parser on Nodejs to parse csv files and store them on postgres database. The problem I am currently facing is when I try to upload the CSV file from windows. The default windows encoding ...
0
votes
0answers
12 views
<type 'exceptions.ValueError'> invalid literal for long() with base 10: 'My-Library-Name'
I'm writing a query code in controller:
books=db(db.book.lib_name=="My-Library-Name").select(db.book.title, db.book.ISBN, orderby=db.book.title)
but I keep getting this error:
<type ...
-1
votes
1answer
10 views
Creating an instance of a user defined data type in a stored procedure
I have a postgres function where it returns a data type, a data type that was user-defined, I am modifying the function by removing a function call, that returned that user-defined object. I now need ...
0
votes
0answers
15 views
Error in postgresql func with datatype
I have a function created as follows:
CREATE LANGUAGE plpythonu;
CREATE OR REPLACE FUNCTION unpickle (data text)
RETURNS text[]
AS $$
import base64
import pickle
return ...
4
votes
1answer
75 views
How can I avoid this three-way deadlock in Postgres?
I am hitting a three-way deadlock in Postgres, and I don't really understand what is happening that is causing it. The log message is,
Process 5671 waits for ExclusiveLock on tuple (33,12) of ...
0
votes
1answer
11 views
Python psycopg2: Access Tuple
We have a PostGres Database which I am accessing with Python. When Querying for a column with type bigint I get back a dictionary with in the following format:
[[263778L], [30188L], [97L], ...
2
votes
2answers
36 views
SQL - does order of OR conditions matter?
I have to SELECT one row which meets condition1 OR condition2. However, condition1 is preferable. If there are two rows, where the first one meets condition1 (and does not meet condition2) and second ...
0
votes
1answer
11 views
PostgreSQL with recursive -> prevent deadlocks/recursion
Hi i want to select objects with postgres recursive. But i cannot guarantee that every data in the database is correct. So i'ts not impossible that an object has for example itself as parent id.
Is ...
0
votes
3answers
27 views
How to make Postgres character field sortable by number
I have the following postgressql table:
CREATE TABLE businesses
(
id serial NOT NULL,
town character varying(200) NOT NULL,
name character varying(200) NOT NULL,
employees ...
-1
votes
0answers
13 views
while restoring i am facing error in postgresql
While restoring i am getting this error i dont know what exactly it says.?? any help....???
pg_restore: [archiver (db)] Error from TOC entry 1610; 826 16546 DEFAULT ACL DEFAULT PRIVILEGES FOR ...
0
votes
0answers
13 views
pg8000 and cursor.fetchall() failing to return records if the number of records is moderate
I'm using the adaptor pg8000 to read in records in my db with the following code:
cursor = conn.cursor()
results = cursor.execute("SELECT * from data_" + country + " WHERE date >= %s AND date ...
0
votes
0answers
9 views
Error: “No row with the given identifier exists[ITELnetERP.Model.Continent#AF ]” in C# with Castle
I am starting a new project using C# + Castle ActiveRecord with PostgreSQL. I have this two classes:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ...
-2
votes
2answers
25 views
Function to substract 2 dates in sql with PostgreSQL
I need a way to substract to dates and get the result in days in sql.
I'm using PostgreSQL 8.4
0
votes
1answer
20 views
Is there a convention for named arguments in a function in PostgreSQL
I come from a SQL Server background where the '@' symbol is used/encouraged in stored procedures. This is useful because you can easily see what is a column and what is a value. For example.
CREATE ...
-1
votes
1answer
24 views
postgres count customers with 2 or more orders [on hold]
I need to get monthly numbers of customers who made 2 or more orders. I'm not that good with postgresql so any help would be appreciated.
Table name is shop_orders
Relevant columns are 'email' - ...
0
votes
0answers
16 views
German special chars displayed incorrectly
We're using Django 1.5 and Postgres 9.1. The database encoding is UTF-8. The website encoding is UTF-8. Some non-ascii chars - including French é - are displayed correctly.
The only issue is the ...
0
votes
0answers
15 views
List as Input parameter in Postgresql Stored function using Java
I am new to Postgresql. I want to pass an ArrayList to the postgresql stored function. How can I pass this ArrayList to the Postgresql function using Java ?
Here is my function
CREATE OR REPLACE ...
0
votes
0answers
21 views
Simplest way to merge two postgres databases into one?
Currently I have two postgres 9 databases for two different datasources.
Database 1 is called musicbrainz, the main user is musicbrainz and tables are created in schema musicbrainz
Database 2 is ...
0
votes
0answers
40 views
Python: unable connect with database
Python 3.4 with psycopg2
I used this guide to set up a basic psycopg2 connection like so:
#!/usr/bin/python
import psycopg2
import sys
import pprint
def main():
conn_string = "dbname='CIBTST' ...
1
vote
1answer
22 views
PostgreSQL UTC to CET/CEST
I have some big trouble with timezones and Benjamin Franklin.
I have a table with an UTC timestamp field, no timezone is stored.
My goal is to group the rows of this table by day of week or by ...
-1
votes
0answers
19 views
VBA:Insert/update/delete cell values from excel sheet to postgresql
I am currently stuck with this issue for the last few days.
I have users who enter values into EXCEL (the repayment sheet), and when they are done they click on the commandbutton to run and this will ...
0
votes
4answers
55 views
Join two tables and remove duplicates
I'm trying to join two tables. Where table2 has duplates.
The tables look something like
CREATE TABLE ta
(
id int,
cno varchar(30),
d1 varchar(30),
d2 int
);
CREATE ...
-2
votes
0answers
22 views
Delete data from parameters in select query
I want to delete data from table entities from the select query for table Result
Delete FROM "Entities"
where
"Entities".accounts_id_credit,
"Entities".note,
"Entities"."date",
"Entities".document,
...
0
votes
2answers
21 views
PG::Error: ERROR: column reference “status” is ambiguous in active_admin
Using rails 3.2 with active_admin and seeing PG::Error: ERROR: column reference "status" is ambiguous when using a custom filter on active_admin in Rents.rb:
filter :travel_car_brand, as: :string
...
0
votes
1answer
17 views
Text column unique case insensitive
I have text column which has to be unique, but case insensitive. However I have to store text in original form (I can't transform it to lower/upper case). Is it possible in Postgres? Or I have to ...
1
vote
1answer
19 views
different sizes of postgres databases. move db from server1 to server2
I'm trying to move a database from server1 to server2. I read docummentation of postgres, and I think everything is right except that after I dumped db from server1 moved it and restored on server2 ...
-1
votes
0answers
11 views
Amazon Cloud PG database server set to my local PG
I deployed one site on Amazon EC2 with PgAdmin.
Now, I want to set Amazone PG Server database Set to my Local Machine.
So that I can view server database on my local Machine.
So How Can I do that ...
0
votes
3answers
39 views
Make Ruby on Rails application available within network
So I have a web application developed in Ruby on Rails 4.0 and accessible on my local PC via localhost:3000 (after I start the rails server by running rails s in cmd at the root of the app). It is of ...
0
votes
0answers
18 views
Actual rows value in explain analyze output changed by Sort Node
When I run this query in PostgreSQL9.3.4 on TPC-H database
explain analyze select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment from part,supplier,partsupp,nation,region where ...
0
votes
1answer
15 views
AWS - Connect to RDS via EC2 tunnel
My RDS is in a VPC, so it has a private IP address. I can connect my RDS database instance from my local computer with pgAdmin using SSH tunneling via EC2 Elastic IP.
Now I want to connect to the ...
0
votes
0answers
19 views
SQLAlchemy bug (?) triggers “(ProgrammingError) table name […] specified more than once”
SQLAlchemy (0.8.7 and also 0.9.7) is generating bad SQL for Postgresql 9.3, and I suspect it is a bug, but I am not confident enough yet to dismiss user error.
Here is a snippet of my test-case code.
...
0
votes
1answer
23 views
how to cause a trigger to calculate balances in an accounting software using postgres
Following to my earlier question that was answered, I would like to know how to do the similar balances calculations using postgres triggers.
how to calculate balances in an accounting software using ...
2
votes
3answers
40 views
How to append text to duplicate entries in a database using SQL
How can I find duplicates and append a random number to the duplicates so that they stop being duplicates anymore.
Sample table:
primary_id, student_id, student_name
1 80 John ...
0
votes
1answer
27 views
how to calculate balances in an accounting software using postgres window function
I'ved got a problem same as this but I am using Postgres.
Calculate balance with mysql
have a table which contains the following data:
ID In Out
1 100.00 0.00
2 10.00 ...
0
votes
0answers
16 views
Load a json object from postgres into ember-data via rails?
I am trying to create an Ember.js app where in the model, I load in a JSON as an object. (Just to display for now.)
Here is my rails schema:
create_table "recipes", force: true do |t|
...
0
votes
0answers
7 views
How to set up Django to run tests on PostgreSQL on Travis CI?
I cannot seem to figure out how to integrate my current Django project to run tests on Travis CI. Right now I have PostgreSQL set up to run on my local machine when unit tests are run.
language: ...
0
votes
1answer
31 views
Postgres backup data with PHP
I try to backup database with PHP script. My idea was to run the pg_dump with PHP as a batch file. (Yes I am using PHP for Windows).
I write this code:
$content = "set BACKUP_FILE=database.backup ...
-1
votes
1answer
27 views
ERROR: op ANY/ALL (array) requires array on right side
We are calling the proc public.update_records (p_record_id integer, p_notification_list text)
and assigning the p_notification_list value as
ct.new_notification_id::text = ...
