PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

0
votes
0answers
7 views

How to execute two transactions concurrently in Postgresql? [on hold]

I want to execute concurrently two transactions in Postgresql and that's what I've got --The first transaction should modify the age of Paul BEGIN; DECLARE newage INTEGER; --the original age of Paul ...
0
votes
0answers
5 views

PostgreSQL ODBC and Microsoft Access date/time in aggregate functions

I am trying to replace our MS Access 2003 databases with PostgreSQL 9.3. The first step is to use our MS Access front end to the upsized databases in PostgreSQL. Everything seems to work very well ...
0
votes
0answers
7 views

Sharing state across CTEs more efficiently?

Let's say I am trying to build an opinion poll app, such that I can create a template of an opinion poll, give it multiple sections/questions, assign multiple people to different copies of a given ...
1
vote
1answer
28 views

Performing search on a column by query

Up until today I've used LIKE %term% to query for rows on particular column term. Then I've read about FULL TEXT index. And I think I misunderstood how Full text index works. I have an API that ...
0
votes
0answers
17 views

Is there some tricks to better index a URL column in postgres?

I'm working with a table that needs fast retrieve rows by its url column. But they all will be starting with http://www.: http://www.google.com http://www.amazon.com http://www.stackoverflow.com I ...
0
votes
0answers
21 views

Postgres 9.4 - How to really grant all privileges on a database to another user?

Just a heads up, I'm fairly new at this whole DBA thing. Okay, with that out of the way I've been struggling mightily lately with granting access to a database in postgres to a user. Let's call the ...
0
votes
0answers
12 views

Conditionaly query in postgresql function

I have a simple query below : CREATE OR REPLACE FUNCTION companies_all_teams( ...
0
votes
1answer
14 views

PostgreSQL: a Login Role which can select from any Views

I need to write a SQL file which must do the following: create a role called 'selection' grant connect/usage to the current database grant select/execute to all views of all schemas (or at least on ...
0
votes
0answers
26 views

Postgres uses a lot of cpu

First of all, I am a bit newbie in case of postgres database but I was requested to check this out. The problem is that postgres processes occupy time to time almost 100% inside the linux system - I ...
0
votes
0answers
18 views

Postgres Query Optimisation (Rails App)

I have a very slow running SQL query that is run from my Rails app using the Model.find_by_sql method. I've made a number of optimisations but it's still extremely slow. It would be great if anyone ...
2
votes
1answer
19 views

Postgresql: Aggregate objects into an (json) array (subquery issues)

I'm sorry for the vague title, but I simply don't know the right words to describe this. I have this query that transforms a bunch of columns into an object that works just fine: SELECT ...
1
vote
1answer
19 views

Spiky COMMIT results in performance slowdowns with Postgres 8.4

We just installed performance monitoring on our web application, and we're seeing a spiky pattern in response times from our Postgres 8.4 server. The spikes correspond with significant slowdown in our ...
0
votes
1answer
23 views

PostgreSQL Backup and restore, sizes are different

I Have a Wordpress database in my postgreSQL, in phppgadmin this database is shown with the size of 273 MB. I've ran sudo -u postgres pg_dump -Fp -f wordpress.sql wordpress to do the database backup, ...
0
votes
1answer
10 views

How to update array fields in a table in Postgresql?

Say I have a table defined as following: CREATE TABLE my_tbl ( id bigint, a1 bigint[], a2 bigint[] ); I would like to create a stored procedure to append values to a1 and a2. Is this ...
2
votes
0answers
14 views

PostgreSQL query - works locally but not on AWS RDS

We have a large query that works fine locally but deadlocks on AWS RDS. According to cloudwatch we are not resource bound on CPU, disk or iops and nothing else should be accessing these tables. The ...
0
votes
0answers
22 views

how to migrate data between two distincts tables

I have a database with millions of records in a table users. I need to collect this data, tranform to a new format (removing a field, for example) and save it to another database. I'm currently doing ...
0
votes
0answers
28 views

Starting the Database Server postgres [on hold]

OS X 10.10, postgres 9.3.5, installed from here I had to initialize the datacluster manually, as stated on the site, through $ /usr/local/pgsql/bin/initdb -U postgres \ That worked fine. At the ...
0
votes
0answers
17 views

Unable to point out Exact cause behind anonymous block failure(Postgresql) [on hold]

DO $$ DECLARE customer_list varchar[]; customer_schema varchar(100); BEGIN customer_list=ARRAY(select cname from customer); -- Get a ...
0
votes
1answer
12 views

Use a value for a Postgresql setting in a trigger

We're using a trigger to track changes in various database tables. This works fine. Now we want to be able to track the changes back to the web request that caused them. I'd like to be able to use ...
0
votes
0answers
10 views

Print to console as SQL executes

When executing multiple SQL commands, such as in the pgAdmin SQL Editor, how can I print some strings to the console (output pane in pgAdmin). I want to monitor execution. UPDATE x SET a = b WHERE a ...
1
vote
1answer
25 views

Optimize view (and underlying table) for averaging timestamps into hours

I have this table: CREATE TABLE spp.rtprices ( "interval" timestamp without time zone NOT NULL, rtlmp numeric(12,6), rtmcc numeric(12,6), rtmcl numeric(12,6), node_id integer NOT NULL, ...
0
votes
0answers
33 views

Tuning a Postgres INSERT only database

I am designing a database which will have many inserts and selects, but no updates. Are there any considerations to take (in order to have high query performance) while knowing no tuple updates will ...
1
vote
1answer
13 views

List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index. I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY ...
0
votes
0answers
21 views

Postgresql indexing for IS NULL

I have two similar queries, whose only difference (in the last two lines) is that one uses IS NULL while the other compares defined values. Query #1 takes only a few minutes: UPDATE ...
0
votes
2answers
21 views

How to use Italian locale for Postgres messages

On Oracle I have set: export NLS_LANG=Italian_ITALY.UTF8 .. on Unix shell and now I get status and error message in Italian. How to set in Postgres shell (psql)? I have set: SET lc_messages TO ...
3
votes
1answer
87 views

How to index for timestamp queries

I have a products table where I insert around 150,000 records a day. Most of them are redundant, but I need to keep them because of the new expiration date. I get product feeds from about 5 vendors a ...
2
votes
0answers
20 views

Warm Standby PostgreSQL 9.4 error; FATAL: the database system is starting up [on hold]

I want to setup a Master and a warm standby, so far here's what I have setup: Master postgresql.conf listen_addresses = '*' wal_level = archive archive_mode = on archive_command = 'copy "%p" ...
0
votes
0answers
35 views

How can I calculate the difference between my time column in seconds? [on hold]

I have got a large database, one of the columns is the time with this format "08:27:22" and I am looking to find out the difference of seconds between the "open doors" and "close door" events. The ...
0
votes
1answer
43 views

Reduce the number of queries to the same table

I need to build some reports using two tables: AuthorTitles: id(pk), author_id, title_id AuthorReports: id(pk), author_id, title_id, items_sold, date My report needs to output the fallowing: ...
0
votes
1answer
21 views

Postgres: Disable “similarity” column output on “SELECT SIMILARITY”?

I've tried disabling similarity column output on SELECT SIMILARITY but still cannot. Below query will generate a column similarity: SELECT SIMILARITY(title, 'Nation'), title FROM gallery WHERE title ...
0
votes
0answers
24 views

PostgreSQL: options for large table

I'm working on a project dealing with genomic data in a Postgres database. There is one table in particular which is causing trouble. It is slowly approaching a billion rows and has three columns: ...
2
votes
1answer
88 views

Multi-row uniqueness constraint, checking a field has one of two values

I'd like to enforce that only one row in a database matches value A in column 1, and either value B or value C in column 2. A small example create table builders ( id text serial ); create table ...
0
votes
1answer
32 views

Postgres: Subquery in select similarity [on hold]

I need to find similar entry of title, just like in "Similar post" of many blog. By the way, I'm not sure if this is the right way I need to find list of rows similar to title where the uri is ...
1
vote
1answer
26 views

postgresql won't install dependencies

Please help me figure out why postgresql isn't playing nice with updating. I can't find anything on SO or Google that remedies this problem. This is a production server and a distribution reinstall ...
0
votes
0answers
26 views

postgresql: How to pass jsonb to user defined C function and process it [closed]

I'm trying to write a C function that get's jsonb passed as parameters which are then converted into const char * for the cJSON library. (Do I even need my own JSON parser here or are there postgres ...
0
votes
0answers
23 views

What are the compression options for storing large amounts of text in Postgres?

I need to store large amounts of text in Postgres, mainly command logs and the output of long running commands and need to know if there are some settings that could help compress the data ...
0
votes
0answers
21 views

Writing audit trail funtionality in postgres, can I use pl/V8 instead of pl/pgSQL?

I'm using postgres 9.4 on Amazon RDS. This the fast and loose "application developer" definition of an audit trail. The audit trail isn't for security nor is it a legal requirement - I don't even ...
1
vote
0answers
14 views

Compound key that only allows nulls if they are null in the referenced table

I want to create a compound foreign key constraint that only allows a null value in one of the columns if there is a matching row with a null value in the referenced table. Additionally, the ...
0
votes
1answer
26 views

How to change database encoding in an Amazon RDS Postgresql instance?

I need to create a database with SQL_ASCII encoding in Amazon RDS PostgreSql database instance. Since template0 and template1 are both with utf8 encoding, I get the following error $ createdb -h ...
0
votes
1answer
19 views

Autovacuum on windows

I just installed postgres on my windows system. I configure also postgressql.conf but when I look into the windows processes I cannot see autovacuum process running or even with process explorer ...
0
votes
0answers
18 views

Postgres uses a lot of memory [closed]

I got client which requested to check why his postgres sql database consumes so much memory. Due to that his provider blocked his account on some hosting. I am not postgres expert, can you avice me ...
0
votes
0answers
11 views

Managing View Dependencies

I am using PostgreSQL and I am adding a single column to a view. Unfortunately, I will have to drop and recreate that view as well as the 9 other views that are dependant on the view. What ...
0
votes
2answers
28 views

change column type from string to numeric

so I have a table with 5.2 million records. 3 columns in this table are for salaries. Those column are currently stored as string and I'd like to change the data type to numeric (I understood while ...
0
votes
0answers
12 views

Syntax error on CSV import into postgresql [closed]

I'm using the console in pgAdmin III to import a CSV into a database. The command I'm using is: COPY 2014Q1TRIPS FROM 'C:\path\to\csv\file.csv' DELIMITED ',' CSV; The syntax error is on the '2' in ...
1
vote
1answer
12 views

Is it possible to use a column value as the argument to a hypothetical-set aggregate function?

Say I have a table like the one described below. Table "public.foo" Column | Type | Modifiers ...
0
votes
0answers
21 views

How to work with multiple Postgres databases in psycopg2

I have two different Postgres databases on two different servers (one is actually local). Similar to this question, I would like to work with both databases at the same time. However, I can't figure ...
1
vote
4answers
61 views

Getting an aggregate (here: min) for a group of values [closed]

Example table: ID | value A | value B ---------------------- 1 | abc | 3 1 | def | 5 1 | ghi | 1 2 | cba | 9 2 | fed | 4 I want the ...
0
votes
1answer
28 views

Unable to grant permission on table

I've been given credentials to a postgres database that I'm told it has privileges to create new users and grant them access to tables. This is half true. I wish to create a new user: create user ...
0
votes
0answers
32 views

When performing an UPSERT, how to keep track of which rows were inserted vs updated? [closed]

This question is not necessarily specific to one particular database platform, but I'm looking for an answer for any of one of the following databases: MySQL PostgreSQL SQL Server Oracle MongoDB ...
2
votes
1answer
32 views

Error 42P01: relation does not exist

I'm new at creating databases, and this error has me dumbfounded, as I am super new with DB admin things (I mostly do reporting type queries). I created a new database through pgAdmin3 GUI, and I'm ...