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.

learn more… | top users | synonyms (3)

0
votes
1answer
12 views

How can I sort the postgres column with certain special characters?

I am trying to sort on a character column in a Postgres database: Select column1 from table order by column1 Output dir1 dir2 dir3 #num1 t1 I want the sort to print #num1 first the way sqlite ...
0
votes
0answers
11 views

JPA/Hibernate + Postgres SequenceGenerator

I'm trying for the all day long get the Sequence from a Postgres with JPA/Hibernate, but it gives me all the time: INFO: Hibernate: call next value for tb_cdr_id_seq WARN: SQL Error: 0, ...
0
votes
0answers
17 views

Timestamp breaks sqlite3 import to postgresql

I have a rails app that I used Sqlite3 for the db, but am now switching to use Postgresql. Per this excellent post, I have set up the postgresql databases, and am now importing the sqlite3 db's to ...
2
votes
2answers
25 views

How to update only a certain percent of matching values

I have a table which contains, apart from other, such fields: id integer, status_id integer, add_date date. I would like to execute a query similar to this: update table set status_id = ...
0
votes
0answers
15 views

Rails/Postgres query rows grouped by day with time zone

I'm trying to display a count of impressions per day for the last 30 days in the specific users time zone. The trouble is that depending on the time zone, the counts are not always the same, and I'm ...
0
votes
0answers
16 views

Hibernate autocommit true for a single command

Hibernate automatically sets autocommit to false, and indeed even recommends against setting it to true. I find myself in the need of having autocommit set to true for a single database command (I ...
0
votes
0answers
7 views

How to create GPX (xml) file with segmentation from psql table?

Im having problem exporting database table to GPX in psql, is there some step by step guide for this? thanks in advance.
1
vote
2answers
16 views

Sum a sequence of device-labeled, data using most recent data for device

I am using Postgres 9.2. I have the following problem: Time | Value | Device -- Sum should be 1 v1 1 v1 2 v2 2 v1 + v2 3 v3 3 ...
0
votes
0answers
3 views

configuring heroku database connection in padrino - DATABASE_URL fails

I had a problem getting a padrino (0.11.2) application to run today on heroku. The errors I was getting back suggested that the connection URL wasnt working, e.g. 2013-06-20T12:55:59.040527+00:00 ...
0
votes
0answers
16 views

Rails + SQL SERVER 2008 + Deadlocks

I built a Rails app that uses SQL SERVER 2008 because it was a business requirement. Fast forward a year later and now am starting to feel the deadlock pain. Whats the best way to migrate the ...
0
votes
1answer
12 views

Using WITH statement in dynamic query

How to use WITH statement in dynamic query? for example, what is right syntax for something like this: // begin function DECLARE my_var INTEGER; EXECUTE 'WITH a AS ( SELECT 4 )'; EXECUTE ...
1
vote
1answer
27 views

How can I use a Postgres EXCLUDE constraint to prevent inserting two primary rows?

Postgres 9.0 supports exclusion constraints, which are kind of like a general unique constraint (http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE). I figured ...
1
vote
1answer
27 views

Sum across partitions with window functions

I have the following problem... Time | A | B | C -- Sum should be 1 a1 b1 c1 a1 + b1 + c1 2 a2 b2 x a2 + b1 + c1 3 a3 x x a3 ...
0
votes
1answer
26 views

Get the difference between two values

I got totally stuck on comparing two tables and getting the difference between them So here we go: I got table a with the following columns Name|Value|Date and the second table b with the same ...
0
votes
2answers
25 views

The type initializer for 'NHibernate.Cfg.Configuration' threw an exception. Unit Testing

I have UnitTesting project with one TestMethod which calls repository and repository creates new record in PostgreSQL When I run this test its throws exception: Message: The type initializer for ...
0
votes
1answer
15 views

some sequences not in dump from pg_dump

I want to pg_dump my database and have the problem that some sequences are generated in the dump file and some are not. With the table infrastruktur_pictures it works with the table hoehenprofile it ...
0
votes
2answers
17 views

PostgreSQL - Conditional aggregation - Avg() in Select statement

I have this table | user | Mark | Points | |--------------|------------|----------| | John | 0 | 2 | | Paul | 5 | 3 | | John | ...
0
votes
1answer
39 views

Is it a good idea to use mongodb , inmemory db and postgres together?

Our application deals with both report and transactions (OLAP and OLTP). The application needs to display the generated report in form of charts and tables. And other part of application deals with ...
0
votes
0answers
16 views

Call SQL-function via engine.begin()

I'm trying to call a function that I imported into my database schema via SQLAlchemy. My setup looks something like this: create schema "bla"; create extension "uuid-ossp"; create table "bla"."test" ...
1
vote
4answers
23 views

postgres: Index not being used or how to optimize

I have a table with an index on a boolean field ("test"). When it's the true, it uses the index so it's loads fast but when it's false it doesn't use it. Is something wrong? I have here the explain ...
0
votes
1answer
31 views

Double saved as int in PostgreSql Database using CASE-WHEN

I have a problem updating a table. The number 3,25 is saved as 325. This happens only if i use this UPDATE: using(var con = new Npgsql.NpgsqlConnection(constr)) { var UpdCmd = "UPDATE ...
0
votes
0answers
14 views

How can I automatically load a OSM data set into my rails postgresql db on rake db:create or migrate

I am using Open Streetmap Data in my rails app. I have downloaded the relevant data as a .osm and can import that data into my db using osm2pgsql. So far no problem. But how can i arrange the data to ...
1
vote
3answers
35 views

Create trigger that ensures there is one and only one NON-NULL in a set of column and reuse it for other tables

I know how to create a trigger that checks if a group of columns has one and only one NON NULL for one table but i would like to reuse the code because i will have some other tables with the same ...
1
vote
4answers
37 views

What's the easiest way to represent a bytea as a single integer in PostgreSQL?

I have a bytea column that contains 14 bytes of data. The last 3 bytes of the 14 contain the CRC code of the data. I would like to extract the CRC as a single integer to be stored in a new column. ...
-1
votes
0answers
27 views

How to take averages of NTiles in SQL?

edit: postgresql I have a bunch of sales figures for some employees... table looks something like this: SalesRep # of sales/day NTILE(2) -------- -------------- ---------- ...
0
votes
1answer
18 views

How to select id, first_not_null(value1), first_not_null(value2).. on Postgresql

I have a table like this: +--+---------+---------+ |id|str_value|int_value| +--+---------+---------+ | 1| 'abc' | | | 1| | 1 | | 2| 'abcd' | | | 2| | 2 ...
0
votes
0answers
20 views

Generate update queries from select

I'm looking for some easy way to create update queries based on my select query. The purpose is to create my private configuration file which I run after I will revert my database from "public" ...
0
votes
0answers
14 views

trouble creating a database with postgreql [migrated]

I just installed Postgresql on my Windows laptop. I am now trying to create my first database. I launched the Windows PowerShell and tried the following: PS C:\Users\Chris\Documents\test> & ...
0
votes
1answer
12 views

Can copy_to query by using binding arguments?

>>> cur.copy_to(sys.stdout, 'test', sep="|") 1|100|abc'def 2|\N|dada ... I review the document of psycopg2 , I want to use copy_to function to select large data, But I could not find the way using ...
3
votes
1answer
23 views

Unique Index on Postgres text column - can it cause high CPU load?

I have an unique index on a text column in a Postgres table. The database right now has over 10,000,000 rows and counting. I noticed my database has a HUGE CPU load (over 40.00) when inserts/updates ...
0
votes
1answer
31 views

JPA Integer Discriminator Values

I'm trying to follow the JPA tutorial and design a class hierarchy to represent the following table: CREATE TABLE Entities ( entity_id INT PRIMARY KEY, entity_type INT CHECK (0 <= entity_type ...
0
votes
1answer
16 views

Postgresql startup failure

I'm attempting to build a box via Fabric on Openstack. Part of the install involves installing and running PostgreSQL. This command works fine: $ sudo service postgresql initdb This command ...
0
votes
1answer
7 views

Restoring only some tables into Heroku Postgres via heroku pgbackups:restore

According to the heroku docs, the following command restores a pg_dump into a Heroku Postgres Database: heroku pgbackups:restore DATABASE 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump' However, ...
0
votes
1answer
34 views

Django ORM query to exclude grouped by row if certain conditions are met (postgresql)

This is about an application that enables some functionality for collaborative moderation, kinda similar to Stackoverflow. Current State I have a Django ORM query like this: versions = ...
0
votes
0answers
25 views

PostgreSQL table with foreign key may update first

everyone, I got a problem in my project using Postgresql. Say there are two tables A and B, both A and B have (unique) constraint ID. Now I set ID in A to be primary key, and ID in B to be foreign ...
1
vote
0answers
14 views

Performance of reads from slave database vs master database?

In my current scenario, I only have 1 master database that is handling a lot of concurrent writes/updates as well as reads. NO slaves. In my second scenario, suppose I replicate this database onto a ...
0
votes
1answer
27 views

How do I displaying large amounts of boolean values in PostgeSQL 9.1.9 without causing eye strain?

How do I display large amounts of boolean values in PostgeSQL 9.1.9 without causing eye strain? The issue I have is that various PostgreSQL clients display boolean values as t/f, and when looking at ...
0
votes
1answer
20 views

Heroku PG error on select

I have a piece of code working great in dev environment and firing an error in production. Here it is: venue.badges.where(:active => true).select([:title, :desc, :bonus]).each do |badge| While ...
1
vote
1answer
31 views

psycopg2 leaking memory after large query

I'm running a large query in a python script against my postgres database using psycopg2 (I upgraded to version 2.5). After the query is finished, I close the cursor and connection, and even run gc, ...
0
votes
1answer
36 views

Using bigint in PostgreSQL and SQLite

How would I specify bigint in a create_table for PostgreSQL and SQLite in a rails schema file? I've looked at the data types to see that there is a carry over between the two and checked their ...
0
votes
2answers
38 views

Non-breaking spaces displaying incorrectly in textareas (weird unicode)

Upon dumping my Postgres database and looking at the outputted queries data, here is how it should look: <p>Persons Name started August 1, 2011.&nbsp; We will provide a courtesy account for ...
1
vote
3answers
45 views

SQL optimisation - Word count in string - Postgresql

I am trying to update a large table (about 1M rows) with the count of words in a field on Postgresql. This query works, and sets the token_count field counting the words (tokens) in longtext in table ...
1
vote
1answer
33 views

What happens when a query gets very large results

most usages/tutorial/manuals explain how to use the methods in context of a manageable DB. So if User.where( some condition) would return a result of tens or hundreds it would reasonable to think ...
0
votes
0answers
15 views

SOCI: How to deal with many columns?

I'm using SOCI to access a PostgreSQL database. One particular table that I'm inserting into and selecting from has (at present) 72 columns. My question is how does one best deal with so many columns? ...
2
votes
4answers
58 views

Randomly select rows with chance depending on order?

I have a simple table: create table test (i int4 primary key); where there is million rows, with i >= 1 and i <= 1000000. I want to remove ~ 80% of the rows - so something like: delete from ...
0
votes
1answer
21 views

how to reference variables inside custom pgbench scripts?

I'm attempting to write a custom pgbench script to benchmark a new postgres cluster that I'm setting up. The official documentation makes it seem simple enough, however it looks like defined ...
0
votes
2answers
51 views

Writing an update query with LIKE

I have two tables, TableA and TableB. TableA contains name and member_id, as well as TableB. TableB currently contains the correct member_id, whereas TableA does not. I wish to migrate all the TableB ...
-1
votes
2answers
27 views

Django keeps referring to the local database instead of the remote database

I've recently migrated my postgresql database to a remote server. It used to be on the same machine where I ran Django. I decided to make the move to lighten the load on the machine. I ran into some ...
0
votes
2answers
20 views

posgresql accessing the 1st element of the record

I have a function that returns a record type: CREATE OR REPLACE FUNCTION a() RETURNS record AS '$lib/lib', 'a' LANGUAGE C VOLATILE STRICT COST 1; the function ...
0
votes
0answers
26 views

SQL porting issue from oracle

I'm using postgresql-9.1.6. What I'm doing these days is converting SQL from oracle to postgresql. The most continual issues are below... 1. decode --> case when 2. nvl --> coalesce 3. (+) ...

1 2 3 4 5 … 354