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
1answer
13 views
ERROR: aggregate function calls cannot be nested
I am trying to include one more column in my following query to show the average of two columns within. I am new to SQL and PostgreSQL, but referring the following link ...
0
votes
0answers
19 views
Pivot table where column names are based on row values
I'm wondering how to make a pivot table that contains time series data where each column represents a different product. In general, the column names would be:
date | var1 | var2 | var3 | ...
0
votes
1answer
8 views
rake aborted not accepting TCP/IP connections when pushing to heroku
I'm just sitting here trying to deploy my app to heroku for the last 4 hours.
I just want to push my changes, but the following appears each and every time I push my changes.
rake aborted!
The ...
-1
votes
0answers
10 views
If there is a row, update other insert into the table
I am trying to create function in nodejs that will query a postgresql table. If a row exists, update that row, if it doesn't then insert it into the table. I have been trying to do this with this ...
0
votes
1answer
13 views
Can't create a simple Postgresql function
I'm trying to create an user-defined function in Postgresql:
CREATE FUNCTION get_balance(user_id integer, statuses integer[]) RETURNS INTEGER
AS $$
select SUM(table1.credit)
from table1
inner join ...
0
votes
1answer
12 views
Postgresql, maintaining hierarchical data with triggers
I have adjacency list table account, with columns id, code, name, and parent_id.
To make sorting and displaying easier I added two more columns: depth, and path (materialized path). I know, postgresql ...
0
votes
1answer
22 views
how to calculate total time between begin and end timestamps across multiple records in postgresql
I have a postgresql 9.1 table with begin and end timestamps that record time periods that may overlap for a userid. Example:
Userid Begin End
1 2014-01-19 21:14:59+00 ...
-1
votes
0answers
20 views
database call with function (err, result) where result is what?
I am trying to build a rest api using nodejs, express, and postgresql. Thanks
client.query(
if exist(select * from waiter where waiter_id = 'somevalue')
{
update waiter set (...) ...
2
votes
0answers
13 views
PostgreSQL v9.X have real “array of record”?
This query works fine,
WITH test AS (
SELECT array_agg(t) as x FROM (
SELECT 1111 as id, 'aaaaa' as cc
) AS t
) SELECT x[1] FROM test;
but, can I access the recod elements? I try ...
0
votes
1answer
14 views
Postgres SSL connection failing to be established
I'm attempting to get a vagrant environment up to run a Rails application that connects to a remote Postgres database hosted on Heroku. Running the app from my host machine (Mac OS X) works just fine, ...
2
votes
0answers
13 views
Is there a way to address all elements of JSON array when creating a constraint in PosgreSQL?
Does PostgreSQL provide any notation/method for putting a constraint on each element of a JSON array?
An example:
create table orders(data json);
insert into orders values ('
{
"order_id": 45,
...
1
vote
1answer
11 views
Using sqlalchemy how invoke a function that return a table
I haven't find a way to return data from a postgres function that return a TABLE:
CREATE OR REPLACE FUNCTION doc_codes(device_id TEXT) RETURNS TABLE("name" TEXT, "suffix" TEXT) AS $$
SELECT name, ...
0
votes
1answer
5 views
pg_search and multi column indexing (Rails 4)
I have a full-text search query in PostgreSQL that looks like this:
to_tsvector('english', coalesce("products"."name"::text, '')) || to_tsvector('english', coalesce("products"."uid"::text, '')) || ...
0
votes
1answer
15 views
What is the difference between #> and ->> operator in PostgreSQL?
We can access any JSON element in PostgreSQL 9.3 using the -> and ->> operators. Seems to me that the #> along with #>> only provide a shorter form of writing the JSON path. Or is ...
0
votes
0answers
32 views
PostgreSQL and general SQL information
Normally, I work in IT/Help Desk type roles, but finding work has been challenging since getting back from the Philippines so I'm taking what I can find. I made some good contact with the technical ...
-5
votes
0answers
31 views
What would the result value be? if the if condition went through or if the else condition when through [on hold]
I would like to know what result would return if I ran this code. I am trying to build a rest api using nodejs, express, and postgresql. Thanks
client.query(if exist(select * from waiter where ...
0
votes
2answers
31 views
What is the best database structure for this situation?
I'm writing a stock manager with these tables:
harvest
sale and purchase
customer and producer
sale_item and purchase_item
and others
I would like to know what the best way to organize the ...
0
votes
2answers
18 views
What's the best way to track versions on a specific column of a table?
I have a webapp that for these purposes can be considered a collaborative blog or wiki. People view a set of documents and edit then republish them, and we need to keep track of a revision history on ...
0
votes
3answers
53 views
Upsert in node.js - what is `IF EXIST`?
Could someone explain the if exist command that is used in SQL?
for example:
if exist(select * from waiter){
update waiter set (...) where waiter_id='somevalue'
} else{
insert into waiter ...
0
votes
1answer
32 views
What is PostgresSQL Query Language Considered? [on hold]
When searching for Oracle query help I know to look for PLSql for Microsoft it is T-SQL. What is PostgreSQL query considered?
0
votes
2answers
21 views
Find all rows that have a value in a certain column that occurs more than once
Suppose I have a database with two columns, an id (bigint) and text (text). I want to find all rows in the database that have an id that occurs in more than one row. How could I do that?
Example:
...
0
votes
1answer
17 views
cannot insert None value in postgres using psycopg2
I have a database(postgresql) with more than 100 columns and rows. Some cells in the table are empty,I am using python for scripting so None value is placed in empty cells but it shows the following ...
0
votes
1answer
22 views
How do I delete all records related another record and greater than a certain threshold efficiently?
I'm trying to delete all notifications older than the first 99 for every user. Here's what I'm currently doing in rails:
User.all.each do |u|
u.notifications.order('created_at ...
0
votes
1answer
26 views
Two client queries in that same function
I have a question about if it is right to do two client.queries one right after the other. I am trying to build a REST API. Example:
client.query("select waiters_id from waiters", function(err, ...
1
vote
1answer
11 views
How to work with custom SGBD types with jOOQ
I'm wondering how to leverage the new range types you can find in the latests versions of PostgresSQL.
I can find the jOOQ documentation on how to manage normal column with custom Java types, ...
0
votes
1answer
19 views
When result.rows is [], how do I test for that?
So I have a question about when result is []. I am using express, nodejs and postgresql to create an REST QPI. Now, the question that I have is. When result.rows is [], does that mean that is null? I ...
0
votes
0answers
9 views
INFO output despite “SET client_min_messages TO WARNING” just before
postgresql-9.0.15 on CentOS 6.5. I have a plperlu function that outputs an INFO message. I want to suppress it during testing (using psql, which also behaves as below), but I can't even seem to do ...
1
vote
1answer
12 views
Converting UTC time in local time in PostgreSQL 8.3
I run a Postgres 8.3 database where times seem to be stored in UTC without time zone.
I am trying to display in local time but not with '+01' suffix :
With select scheduled_start_ts I get :
...
0
votes
1answer
23 views
postgres: Delete rows that has foreign key while not deleting data of the foreign key table
I have postgres database, I my application is build on using django and I used south migration to maintain the database schema. I have following scenario:
user_table has a foreign key relationship ...
0
votes
3answers
24 views
How to store different date precisions in one PostgreSQL column
I have different date types for some single events.
They can built up like this:
YYYY-MM-DD
or
YYYY-MM
or
YYYY
I want to store the data in one column. How do I have to set up my column ...
0
votes
0answers
32 views
How to pre-validate a value
I have a float columns which I insert data to them with ruby
Sometimes, the numbers come malformed, not a number or just a really big number beyond the range of PG's float
Is there a way to test a ...
0
votes
0answers
16 views
Configuring mongodb and postgres in NodeJS (CompoundJS)
I have successfully configured mongodb with my CompoundJS Application by defining driver
in database.json file.
is it possible for me to define postgresql as a database in same database.json file?
or ...
0
votes
1answer
32 views
PostgreSQL query for multiple update
I have a table in which I have 4 columns: emp_no,desig_name,from_date and to_date:
emp_no desig_name from_date to_date
1001 engineer 2004-08-01 00:00:00
1001 ...
0
votes
2answers
12 views
connecting a remote server database from a database funcion in postgresql9.1
Friends
i just want to know is it possible to connect another server database which is in same lan network from a store function of another database in postgresql 9.1
example
server a have a database ...
1
vote
0answers
37 views
Duplicate values present in primary key column in postgresql
I have a table in which one of the columns is primary key and it is of data type citext.
Many threads will update the table, they are properly synchronized. Those threads write the data in ...
-1
votes
2answers
21 views
Get COUNT of hours on 30 mins Interval
I have this below data.
Date Interval
2014-01-01 12:00 AM
2014-01-01 12:30 AM
2014-01-01 1:00 AM
2014-01-01 1:30 AM
2014-01-01 2:00 AM
2014-01-01 2:30 AM
2014-01-01 3:00 AM
2014-01-01 ...
0
votes
0answers
26 views
Long chained exists query with multiple one-to-many mappings in the chain
Edit: Following piece seems to be the right way:
session.query(User).join("userGadgets", "gadget", "components","gadgetComponentMetals")
Original:
I have the following tables configured:
class ...
-4
votes
1answer
29 views
First 5 entries for a single user
I am using PostgreSQL. I need to get the dates for the first 5 transactions of every user on my DB.
Transaction - trans.id, trans.date, trans.cust_id, trans.value
Customer - cust.id, cust.created_at
...
1
vote
0answers
31 views
DB can not find the true relation
I have two postgres DB (e.g: DB one & DB two)
In the both of them I have the same relation ,the problem is that when I want to use relation on DB two it stores the data on relation of DB one ...
0
votes
2answers
30 views
Between use two years in postgresql
I want to delete the data between some years, like Table name is t1, Columns id,name date.
The values like this
+--+----+----------+
|id|name|date |
+--+----+----------+
|1 |Raj |2013-03-01|
...
0
votes
1answer
19 views
How should column-level PostgresSQL encryption implemented in SQLAlchemy using pgcrypt?
For example, there is a repo for doing this in Django: https://sourcegraph.com/github.com/dcwatson/django-pgcrypto.
There is some discussion in the SQLAlchemy manual, but I am using non-byte columns: ...
0
votes
0answers
11 views
Set postgres DateStyle GUC on connect (Laravel ORM)
In postgres, I simply want to set the date format every in my application when it connects. I'm using laravel 4 and RDS. I could simply change it in the database config, but it appears that RDS ...
0
votes
1answer
20 views
Why does this PostgreSQL full-text search break on a different (RDS) server?
On several development servers, this query has returned expected records:
SELECT name, name_tsv FROM vision
WHERE name_tsv @@ plainto_tsquery('Washington Square Park');
name_tsv was originally ...
0
votes
0answers
23 views
Npgsql returning no result and PgAdmin does
I have an issue with Npgsql.dll.
The code below returns me no results.
If I take the sqlString and paste it into PgAdmin after the parameters has been added, then I get the correct result.
// ...
1
vote
0answers
32 views
Manage Connection Pooling in multi-tenant web app with Spring, Hibernate and C3P0
I'm trying to setup a multi-tenant web application, with (ideally) possibility for both Database-separated and Schema-separated approach at the same time. Although I'm going to start with Schema ...
0
votes
1answer
40 views
calculate total minutes between two timestamps across multiple records using postgresql
I have a postgresql 9.1 table with 2 timestamps that record time periods that may overlap. Example:
ID Start End
1 2014-01-19 21:14:59+00 2014-01-19 21:30:00+00
1 ...
-1
votes
0answers
25 views
Drupal 7, PDOException: SQLSTATE[25P02]
After migrate from mysql to PostgreSQL (DBTNG Migrator module) i get errors:
PDOException: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until ...
1
vote
1answer
36 views
bugzilla can't connect to postgres server [Resolved]
I just installed bugzilla-4.4.1 on version 6.4 of Scientific Linux, and I'm having problems connecting to the database (Pg version 8.4).
When I try to fetch the main page (index.cgi), I get the ...
0
votes
0answers
8 views
heroku pg:pull password authentication failed
I'm trying to run the heroku pg:pull command, but I can't seem to get the amazingly cryptic authentication process.
The command I'm running:
> heroku pg:pull app_name::RED localdb
I then get a ...
0
votes
1answer
28 views
PostgreSQL, one-to-many query, without duplicating the one?
Is there a good way to return, in a single query, a one-to-many relation, without sending duplicates of the fields for the tuple on the one side? Say you have two tables with a typical 1-to-many ...
