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
0answers
6 views

How do you pass objects around node express application?

I am building a node application using express and node-postgres (https://github.com/brianc/node-postgres). I only want to build the db client connection once, and I'd like to be able to access this ...
0
votes
0answers
7 views

Error connection to herokupostgresql locally

I am writing a struts2 app and using hibernate for persistence. I deploy may app on heroku and everything works ok, but when ever I run it locally I get: org.postgresql.util.PSQLException: FATAL:no ...
0
votes
3answers
33 views

Select distinct users group by time range

I have a table with the following info |date | user_id | week_beg | month_beg| SQL to create table with test values: CREATE TABLE uniques ( date DATE, user_id INT, week_beg DATE, ...
0
votes
2answers
19 views

Is it possible to reference an environmental variable within a postgres sql command?

For example, say I wanted to import a CSV file from a path on the same machine the postgres server is running on. There is an environmental variable MyPath set on the system to ...
1
vote
1answer
19 views

PostgresSQL combining tables

I am a little confused about merging one table into another. My two tables looks like so: Table A Table B id | name | likes | email | username id | ...
0
votes
0answers
3 views

using hive api for postgreSQL Embedded SQL in C

I'm looking for the hive api so that I can simply call it from postgreSQL Embedded SQL in C. I'm searching the web though, there're lots of reference using JAVA API. I wnat to access to HIVE through ...
0
votes
0answers
16 views

Math trigonometry functions with Sequel database toolkit?

How do I write ACOS(), SIN() and COS() functions as part of a select query using the Sequel database toolkit? Can't seem to find it in the docs anywhere... I want to write this query using Sequel: ...
-6
votes
1answer
42 views

Change PostGreSQL DB to MySQL DB [closed]

Question: is it possible to convert a dump.sql file which was written in PostgreSQL to a MySQL friendly version? I tried uploading the dump.sql file to a MySQL Database space in a linux server but I ...
-1
votes
1answer
10 views

a bash script to change postgresql user password [closed]

I can change the postgresql user password in this way (2 steps): $ su - postgres -c 'psql -U postgres -d postgres' # Alter user postgres with password 'password'; Now I want to use a single line ...
0
votes
1answer
18 views

Postgres partition by week

I can imagine table partition by a date (in particular for logs) is something widely used, but I am not able to find a good answer to my problem. I want to create a table partition by week (the ...
2
votes
0answers
14 views

postgres plpy.execute with hstore string literal FAIL

Hi I'm writing a plpython function using postgresql 9.2. Assume the code has already executed a query that returns hstore strings. I wish to then issue a query: "SELECT ...
0
votes
0answers
16 views

PostgreSQL 9.x - pg_read_binary_file & inserting files into bytea

I have been looking everywhere (google, stackoverflow, etc.) for some documentation on how to use the PostgreSQL pg_read_binary_file() function. The only meaningful thing I can find is this page in ...
0
votes
0answers
18 views

How to insert binary data into a PostgreSQL BYTEA column using the C++ libpqxx API?

I'd like to insert some binary data into a BYTEA column, but I find the Doxygen output is lacking in details, and http://pqxx.org/ has been down for the past few days. How would I go about inserting ...
0
votes
3answers
44 views

Schema design: How do you incorporate “total”?

I am currently building a scoring table for my items. Each item has a score so the database (postgres) can sort the item by scores and return them to the user. Currently, the total score of the ...
0
votes
1answer
13 views

Django model: NULLable field

I'm working on a django project where I need a DateField to sometimes be empty. My model looks like this: #models.py end = models.DateField(default=None, blank=True) But when I run python manage.py ...
0
votes
2answers
14 views

Rails migration — rake db:status says migration is down, but database is already migrated?

I have a local PSQL database setup and am having issues getting rake db:migrate to function properly. For instance, my database has already had its high_school column name changed to high_school_name, ...
1
vote
1answer
25 views

Select random row for each group

I have a table like this ID ATTRIBUTE 1 A 1 A 1 B 1 C 2 B 2 C 2 C 3 A 3 B 3 C I'd like to select just one random attribute for each ID. The result ...
0
votes
2answers
26 views

heroku, postgreSQL, django, comments, tastypie: No operator matches the given name and argument type(s). You might need to add explicit type casts

I have a simple query on django's built in comments model and getting the error below with heroku's postgreSQL database: Traceback (most recent call last): File ...
0
votes
1answer
21 views

rake db:setup is failing for production because I am referencing a Model for a table that yet to be created in an initialier

I'm trying to deploy my application on EngineYard. Right before assets:precompile it checks for database changes as part of the migration. This is causing a failure for me, because I am pre-loading ...
1
vote
1answer
21 views

How does RAID work with EBS Drives on Amazon Web Services?

I'm trying to wrap my head around the concept of RAID and what the advantages are for something like a database for example. I've been doing a lot of readings into RAID setups however, I don't know ...
0
votes
0answers
14 views

Sort dataclips in heroku

Is there a way to sort the SQL dataclips generated in heroku's postgres add-on? I am building a decent collection of dataclips and would like to put them in an order that makes sense to me and my ...
1
vote
1answer
36 views

prefix similarity search

I am trying to find a way to build a fuzzy search where both the text database and the queries may have spelling variants. In particular, the text database is material collected from the web and ...
1
vote
2answers
19 views

postgres deadlock without explicit locking

I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock ...
0
votes
2answers
28 views

Advice for data storage on Amazon EC2 especially for databases

I've been playing around with Amazon's Web Services for over a year now, however I don't quite understand how it works. When I for example select an AMI of my choice from the EC2 console and I ...
0
votes
2answers
30 views

Rails 3.2 - undefined method `where' for #<Array:… - Query of Model.where()

I am trying to compare fields from a given Header to other fields in the Alarm model. As you can see in the code I filter the alarms in 3 different steps. The first 2 work perfectly. However, the last ...
1
vote
1answer
20 views

Rails 3.2 - Query to extract records not working properly - Is this syntax correct?

I got 2 models. One called Header and another one called Alarm Alarm has a column called keyword. Header have 4 columns called title_en, title_es, title_eu, title_fr I would like to compare these 4 ...
0
votes
2answers
28 views

Optimize Postgresql Between Date Statement

I need help in optimize a postgresql query which uses the BETWEEN clause in a datetime with timestamp field. I have 2 tables: ONE(int id_one(PK), datetime cut_time, int f1 . . .) containing ...
0
votes
1answer
26 views

Transfer regex from Java to PostgreSQL

How can I transfer the following regex from Java to PostgreSQL syntax: Pattern.compile("(?imu)(P\\W*Ř\\W*E\\W*D\\W*V\\W*O\\W*L\\W*Á\\W*N\\W*Í)")); I have tried: select regexp_matches('P Ř E D V O ...
0
votes
1answer
8 views

Spring Data JPA with @OneToOne relation fails to update already persisted entity

We got this setup: A document has a list of elements and these elements are subclasses of Element, in this example Link. A Link has a @OneToOne relation to Resource. Everything worked as long as we ...
1
vote
1answer
23 views

Postgresql csv importation that skips rows

I have a Postgresql script that automatically imports csv files into my database. The script can detect duplicate records and remove them, do a proper upsert but still cannot tackle everything. ...
1
vote
0answers
33 views

Strange encoding issue in PostgreSQL query

hope someone could help in this I have a database containing text in English and German, I am using php with Postgres database, querying text in English works fine, but when the query string contains ...
1
vote
5answers
53 views

Limit amount of rows used by average

I've a query (postgresql) where I'd like to limit the rows used for computing the average SELECT username,avg(income),count(*) FROM Events WHERE to_timestamp(eventtimestamp) >= ...
0
votes
1answer
25 views

strategies to get data from diff database from diff tables

Problem I have two databases user_works database and pricing database. I want to take a join of two tables user_table which is in user_works database & rent_table which is in pricing database ...
1
vote
2answers
25 views

pgbouncer can't connect to local postgresql database (timeout)

Im trying to get pgbouncer to work on my standalone postgresql server, but when I try to connect to the database using the pgbouncer port, I get an timeout error. Postgresql: 9.1.8 pgBouncer: 1.4.2 ...
0
votes
0answers
19 views

How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures?

I am new to PostgreSQL. If I am not wrong, SQL Server has similar mechanism - extended stored procedures for adding new functionality to the base SQL Server functionality. CLR stored procedures could ...
1
vote
1answer
16 views

Import CSV text array into PostgreSQL 9.2

I have data something like this: Akhoond,1,Akhoond,"{""Akhund"", ""Akhwan""}",0 pgAdmin's import is rejecting this. What format does the text[] need to be in the CSV? I also tried this: ...
0
votes
2answers
26 views

PostgreSQL: how to delete rows using a join

I've got a problem to delete records from a PostgreSQL table, using a LEFT JOIN. I'd like to delete rows I get with the following query: SELECT * FROM url LEFT JOIN link_type ON url.link_type = ...
-4
votes
4answers
51 views

SQL - Select only when the condition matches [closed]

I have a table like below. ID NAME 1 JOHN 2 JANE 3 JACK In a simple select statement, how can I query JOHN and JANE from the table? The conditions are: i. Result has to return JOHN and ...
0
votes
0answers
12 views

Duplicate index names with DBIx::Class and PostgreSQL

I have an existing MySQL database which I'm trying to migrate to PostgreSQL using the following steps. The database is fairly simple - it has a few foreign keys and other constraints but no triggers, ...
0
votes
0answers
26 views

What is the purpose of pgScript in PostgresSQL?

I am new to PostgreSQL. I have failed to understand the need for pgScript, which could be executed using pgAdmin tool. When it should be used? What it can do that plpgSQL cannot do? What is equivalent ...
0
votes
1answer
27 views

Postgres/SQL: modeling charges and refunds against a payment source

I have the following models and I want to write a query which will return unspent credits. Credits have many charges which in turn have many refunds. My attempt at the query so far is below but I have ...
0
votes
1answer
28 views

How to delete a table using Auto?

I want create function SQL: CREATE OR REPLACE FUNCTION medibv.delAuto(tableName nvarchar(50), columnName nvarchar(100),value nvarchar(100)) RETURNS void AS $BODY$ begin DELETE from tableName ...
1
vote
3answers
43 views

Custom ordering in Rails query

I have a Post model with some has_many associations. class Post < ActiveRecord::Base ... has_many :votes has_many :comments has_many :ratings end I want a query that orders the posts by ...
-4
votes
1answer
42 views

Convert one row to array in postgresql [closed]

Convert one row to array in postgresql I have table Id Code Name 1 001 Marry I want result is: {1,001,Marry}. you can help me? thanks
0
votes
1answer
50 views

Relational DB racing conditions

I'm working with Ruby On Rails (but it doesn't really matter) with a SQL backend, either MySQL or Postgres. The web application will be multi-process, with a cluster of app-server processes running ...
1
vote
1answer
18 views

Is pg_test_fsync contrib module in postgresql 9.2 deprecated?

I have installed postgresql 9.2.4 on my machine with Ubuntu 12.04.1 LTS. Based on this documentation page (http://www.postgresql.org/docs/9.2/static/pgtestfsync.html), it seems *pg_test_fsync* contrib ...
0
votes
0answers
68 views

Identical Postgres index scan taking 5x longer on server

I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject: Column | Type | Modifiers ...
0
votes
1answer
30 views

Index inside a table postgreSQL

I am transferring from mySQL to postgreSQL and wondering how can I add index inside the table of postgreSQL CREATE TABLE IF NOT EXISTS game_history ( id SERIAL, PRIMARY KEY(id), INDEX ...
1
vote
1answer
28 views

Extracting value of xml tag in PostgreSQL

Below is the column response from my Postgres table. I want to extract the status from all the rows in my Postgres database. The status could be of varying sizes like SUCCESS as well so I do not want ...
0
votes
1answer
29 views

Translate MySQL to Postgres

I am looking for help converting my MySQL query to work in PostgreSQL. This is a query on the model of RoR application. Any help is appreciated. SELECT * FROM( SELECT @row := @row + 1 AS rownum, ...

1 2 3 4 5 … 329
15 30 50 per page