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
4 views
Ruby and Postgres
I am new to both Ruby and Postgres.
I just want to open up the first two observation of the following table (named eightks).
Here is my Ruby code
Ruby thems that have been installed via terminal ...
0
votes
0answers
7 views
Trouble importing txt files into postgresql
I'm having a hard time importing this txt file: http://www.ars.usda.gov/SP2UserFiles/Place/12354500/Data/SR27/asc/FOOD_DES.txt
into Postgresql. What should the delimter be?
I'm using navicat to do ...
0
votes
1answer
8 views
Postgresql - Using a variable in From in another select statement
What I am trying to do is optimize my query by 95 percent as part of an assignment to learn psql. What I am trying to get is all the customers who have equal or greater than the person with the 3rd ...
0
votes
1answer
7 views
RuntTimeError: YAML syntax error (Unknown)
I have started using and learning Ruby on rails, and I wanted to use Postgres instead of sqlite3, after a bunch of stuff i went through to install the gem (i'm using ruby 2.1.2 and rails 4.1 in Ubuntu ...
0
votes
1answer
15 views
Daily sum by type to show date and '0' even when null (no activity for that type on that date)
I'm querying amazon redshift (basically postgresql save for a few features) and am trying to get a daily sum by type for every day (agg daily), but would like to include null as '0' on days where no ...
0
votes
0answers
10 views
Trouble using @JoinFormula in Hibernate
I have two tables in my PostgreSQL database:
CREATE TABLE tableOne (id int, name varchar(10), address varchar(20))
CREATE TABLE tableTwo (id int, info text, addresses varchar(20)[])
now I want to ...
1
vote
3answers
35 views
JOIN using negation clauses
I've got the following table in a Postgres database:
Table Example:
---------------------------
name | number
---------------------------
DefaultName | 1
DefaultName | 2
...
1
vote
1answer
10 views
Rake file is seeing old version of database on Heroku
I'm using a rakefile to seed my database. I was seeing weird behavior (see Additional user attributes results in UnknownAttributeError and NoMethodError) and have concluded that it is operating on an ...
0
votes
2answers
20 views
PostgreSQL - Deleting data that are older than an hour, and then inserting new data to the same table
I wrote a function called insert_offset_data(text, double precision) as the following:
BEGIN
INSERT INTO tempoffset(id, location, offset_factor, ts_insert)
VALUES (uuid_generate_v4(), location_in, ...
0
votes
0answers
21 views
Rails - Determine how much data comes back in a query (Postgres)
I'm working on performance tuning a rails application and one of the performance issues I've found is when you do a query without selecting which columns you want, so you get a SELECT(*). When you ...
-2
votes
2answers
39 views
Inserting a row in between two rows in a table
I have a table with two columns subj-id,subj-name in the below format.
101 Maths
102 Science
103 Biology
201 Maths
202 Science
203 Biology
204 Geography
301 Maths
302 Science
Now i need to insert ...
1
vote
2answers
18 views
Perform action before exception call in Postgres trigger function
Postgres 8.4 here. Imagine this code snippet from Postgres doc:
CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
BEGIN
-- Check that empname and salary are given
IF NEW.empname IS ...
0
votes
1answer
13 views
Create PostgreSQL read only user ERROR: permission denied for relation _foo
I have an issue while trying to create a readonly user in PostgreSQL.
I want user ckan_default to create tables in database datastore_default.
And I want readonlyuser to read tables created by ...
2
votes
1answer
17 views
Update Returning Order by in postgresql
I have a query updating rows in a table. I want the query to update the rows and return the rows affected.
Currently I have
UPDATE employees SET name = 'John' RETURNING employees.*;
This works ...
0
votes
0answers
2 views
OpenERP 7 import performance depending on each table?
As an assignment I am importing data (~500 < x < ~2500 columns) to several tables. Some tables have an acceptable time to import, but other tables have completely impossible import times.
Is ...
1
vote
0answers
3 views
Doctrine2 with postgres error
i tried to make a simple query with doctrine and i get an error. I dont understand.
$em = $this->getDoctrine()->getEntityManager();
$dql = "SELECT n FROM Bundle:entity n WHERE n.value = true";
...
0
votes
1answer
19 views
How to run initdb for postgresql 9.2 on centos7?
So,
My admin installed (don't know how) postgresql 9.2 (64 bit version) on a centos7 machine. The thing is I need to initalize the database so I should run something like:
service postgresql-9.2 ...
0
votes
1answer
14 views
Constraint to avoid combination of foreign keys
I've here a problem that I couldn't find a proper solution on my researches, maybe it's because I couldn't find out the exact terms to search for it, so if this is a duplicate I will delete it.
My ...
0
votes
0answers
11 views
Postgres: How the fulltext search work with TSVECTORS and TSQUERY
I use the fulltext search of postgres with tsvectors and tsquerys.
In my tsvector i have the entry like:
("'22305':3 'de':2 'kingsroad':4 'winterfell':1",)
when I now query to this tsvector with:
...
0
votes
0answers
6 views
Connecting intel galileo to database
I'm new to all this.. I have programed c++, php, postgreSQL, html, arduino,... I also know how to connect php to a database, but I have no clue how I could connect intel galileo to my database, I know ...
0
votes
1answer
12 views
Is there in plpgsql a possibility to get record item names and use them?
I need to report record items using "raise notice", but including item names.
raise notice 'rec=%',r; -- is not very good
Is there a possibility to do someting like:
declare
r record;
...
0
votes
2answers
19 views
Django: distinct on a foreign key, then ordering
I have two models, Track and Pair. Each Pair has a track1, track2 and popularity. I'm trying to get an ordered list by popularity (descending) of pairs, with no two pairs having the same track1. ...
0
votes
0answers
13 views
PostgreSQL: could not determine data type of parameter [xtend]
I have the following code written in Xtend..
val st = getConnection?.prepareStatement("SELECT id FROM inscenation " +
" WHERE name = ?1")
st?.setString(1, ...
0
votes
0answers
35 views
Postgres function INSERT INTO
I have this function which insert into an account some values.
If the function will find on my DB a tuple with the number(i) already picked the transation abort.
CREATE FUNCTION insert(num integer) ...
0
votes
2answers
191 views
How to install PostGIS on Oracle Linux 6.4 x64?
I am reading the following guide http://www.postgresonline.com/journal/archives/329-An-almost-idiots-guide-to-install-PostgreSQL-9.3,-PostGIS-2.1-and-pgRouting-with-Yum.html (this link is from ...
0
votes
1answer
33 views
How to write changes to database when within a begin/commit/rollback block
We have implemented a DB begin/commit/rollback system where you can have a begin begin/commit in code anywhere and if there when you do a begin/commit in a particular place, you are within another ...
0
votes
1answer
14 views
Is it possible to partially refresh a materialized view in PostgreSQL?
In Oracle, it is possible to refresh just part of the data. But in PostgreSQL, materialized views are supported since 9.3 (the current version now), which is not so long. So I wonder: is it possible ...
1
vote
0answers
24 views
org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host
I am trying to connect to postgres database which is in remote location using spring jdbc template.
I am getting org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host ...
0
votes
1answer
18 views
Rails Handle Join Table UniqueViolation
I am trying to insert a row into the join table, that I created in this migration (with timestamp):
class CreateUserNewsItemJoinTable < ActiveRecord::Migration
def change
create_join_table ...
1
vote
2answers
22 views
Database optimization - Encoding fields
A bit of a theoretical questions.
Was just wondering is there a way to optimize data fields?
Say for a given field you only have 3 possible strings but those are for some reason very long (say 50 ...
0
votes
1answer
12 views
How to ensure a unique number field with zero order
Here is a table that has fields id, id_user, order_id.
Required when creating a record to find the last number of user and insert the following in order.
I wrote a stored procedure that takes the next ...
0
votes
2answers
39 views
PostgreSQL can't check proper datatype
I had employed MySQL for a couple of former projects. But now have
decided to switch to PostgreSQL...not that version 8 also works on that,
ahem...other OS...which I'm stuck with at work.
But alas, ...
0
votes
0answers
11 views
Returning lastval of the table in redshift
Anyone explain is there any method to return the last inserted id of the identity column of a table in redshift.
i tried
insert into table1(name, address) values(abc,NY) returning (id)
but its not ...
0
votes
0answers
20 views
Convert XML data to PostgreSQL automatic
I have one data *.xml and will downloading everyday via FTP and the data need to insert in pgsql table.Currently having problem on how to convert data automatically and how the script look ...
0
votes
1answer
23 views
How to exclude primary key selection from the query when using CDbCriteria on YII
I faced a problem with the default selection of primary key when using CDbCriteria joined with another table.
I have 2 tables:
Content (
id int2 pk,
title varchar,
content varchar,
...
1
vote
1answer
18 views
Getting Started with Python on Heroku - pg_config executable not found
I have been following the documentation until I got to the point of installing the requirements.txt file. It always fails when trying to install line 6 "psycopg2==2.5.3". Here is the message -
...
2
votes
1answer
41 views
SQL:find strings that contains unexpected words
I have a list of words a b and c.
In my database, there is text column containins many words separated by a space.
I would like to list all records that contains other words that the one I am ...
0
votes
3answers
23 views
Do databases use foreign keys transparently?
Do database engines utilize foreign keys transparently or a query should explicitly use them?
Based on my experience there is no explicit notion of foreign keys on a table, except that a constraint ...
1
vote
1answer
33 views
PostgreSQL find top N rows grouped by date
I'm working on a typical blog app and have a view that returns the following data:
| post_id | title | publish_on | tag_id | tag_name |
| 1 | Why is Postgres awesome | 2014-09-02 | 1 ...
1
vote
1answer
26 views
lib/pq connects but query fails with bad connection
I'm running a fresh install of Ubuntu 14.04 from Digital Ocean. I've installed Postgres by calling sudo apt-get install postgresql postgresql-contrib. I installed the Postgres driver by calling go get ...
0
votes
1answer
21 views
how to use arrays and sets using postgresql?
Could someone help me fix the below code so that it returns all the records that are not in the array being passed to the function? The array contains the pKey values. Thanks.
CREATE OR REPLACE ...
0
votes
1answer
28 views
postgres ERROR: column “id” does not exist?
I have created a table in postgres using the StaticSentence below. The table reads is created from another table products, and the data within is deleted because I just want the schema(I'm really new ...
1
vote
1answer
51 views
Pull large amounts of data from a remote server, into a DataFrame
To give as much context as I can / is needed, I'm trying to pull some data stored on a remote postgres server (heroku) into a pandas DataFrame, using psycopg2 to connect.
I'm interested in two ...
0
votes
2answers
27 views
How to massive update?
I have three tables:
group:
id - primary key
name - varchar
profile:
id - primary key
name - varchar
surname - varchar
[...etc...]
profile_group:
profile_id - integer, ...
0
votes
1answer
22 views
Rails Impressionist making expensive queries
I'm using charlotte-ruby/impressionist to track impressions within my rails app.
I have a very simple implementation, similar to what's shown in the quick start guide. Effectively:
- Controller: ...
0
votes
0answers
4 views
dotConnect for PostgreSQL Query Linq to SQL Join with dynamic table name
Trying to pass in a table to a Linq to SQL join clause, which would allow me to set any table to the variable foo.
join id in foo on bar.Id
With dotConnect the foo is of type,
...
-3
votes
0answers
17 views
PL/SQL fundamental Cursor [on hold]
What are the advantages of cursor?
When I should use %rowtype?
What the important to use FETCH?
0
votes
0answers
26 views
Performing range filters on aggregated field (e.g. Avg of score) in Elastic Search
I am using latest Elastic Search Version 1.3.2.
I just want to perform range filter on an aggregated field (e.g. Average of students score). I am using the following query ..
{
"query": ...
1
vote
1answer
15 views
Most efficient way to upgrade postgresql 9.0.1 to latest minor version
What is the most efficient way to upgrade postgresql to the latest minor version when I'm running an operating system (Fedora Core 10) that's too old to be supported by official RPM releases?
I have ...
0
votes
2answers
24 views
Postgres sqlx prepared statement with table name bindvar
I am trying to create a prepared statement in using the Golang sqlx library. I want to have the table name be a bindVar
stmt, err := stmtTx.Preparex("SELECT * FROM $1 WHERE question_id=$2;")
...
