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
1answer
139 views

pgAgent : how to configure pgpass.conf for a local connection on Windows Server 2008

I try to configure pgAgent on Windows server 2008 to make automatic backup on the database. pgAgent is installed on the server and the service is running normally. I use PgAdmin3 and PostgreSql 9.3 I ...
0
votes
0answers
23 views

Does installing some PostgreSQL options create some related tables?

After restoring an old pgsql database tables I realized that it contains tables like pg_ts_cfg, pg_ts_dict, geometry_columns and spatial_ref_sys. I can't remember creating those tables and I wonder ...
1
vote
1answer
77 views

How to create Postgres DB with case insensitive collation

I am using the postgres version 9.4.1 64-bit on windows 7 64-bit. I need to create DB with the setting "case sensitive = OFF" but couldn't make this work. I refereed already many question on SO and ...
2
votes
1answer
47 views

What is the relevance of 'SET default_with_oids = true' in a Postgresql dump?

I have an SQL backup from an old database which may have been a PostgreSQL 7 or 8 database I am importing which contains a SET default_with_oids - true statement, into a an existing 8.4 database and ...
0
votes
1answer
57 views

How to handle user with no password in postgresql

I installed postgresql but it's asking me for passwords that I never had and never entered. There is a postgres ubuntu user and a database user named moodle. Neither of these were created with a ...
2
votes
1answer
57 views

Postgresql performance issues when issuing many small inserts and updates one at a time

I have a production web server with postgresql database. My server gets data from another server every hour on the hour. The other server wakes up and sends many requests to my server, each result in ...
-2
votes
1answer
32 views

Replace date function call with its functionality [closed]

I have zero knowledge of postgresql somehow I have to extract data from a postgresql database I am trying to replace function call in a query with its functionality. Function: -- Function: ...
0
votes
2answers
75 views

unable to run postgres with pg_ctl

I was reading a postgresql book (PostgreSQL from Novice to Pro) and it says you can start the postmaster daemon by invoking the postgres binary which was part of the installation: ...
0
votes
1answer
27 views

Is it possible to access column by index instead of it's name/alias in query?

I want do something like this: SELECT * FROM ( SELECT 1, 2, 3 ) AS subquery WHERE subquery[0] = 1 -- I want to reference cloumn by its index in row
0
votes
2answers
53 views

Speed up SELECT with WINDOW, (PARTITION … ORDER) with compound index

I have the following table: CREATE TABLE ranking_points ( id serial NOT NULL, store_id integer, book_id integer, list_name character varying(255), rank integer, created_at timestamp ...
0
votes
1answer
64 views

Postgres automatic insert time stamp with time zone (india) [closed]

Is there any way to insert timestamp with zone automatically when query is inserted ? (just like id auto increment as i don't want to pass the the parameters in sql as time its same every where if we ...
10
votes
3answers
150 views

Combining separate ranges into largest possible contiguous ranges

I'm trying to combine multiple date ranges that may or may not overlap into the largest possible contiguous date ranges. For example: Data: CREATE TABLE test ( id SERIAL PRIMARY KEY NOT NULL, ...
1
vote
1answer
43 views

Is there something I can do to make this UPDATE run faster?

I have a relation EnumeratedSensorReading that includes a siteid and a quantityid column. I want to replace these two columns with a single sensorid column. I have added this column, and it is ...
0
votes
1answer
48 views

I want to start the Postgres database

I run this: $ postgres -D foobar It just hangs. There is no error message. I newly created this database with the initdb command. What should I do?
1
vote
1answer
31 views

postgresql - granting schema to users

I have (2) postgresql databases. I basically used the same pg_dump export file to create the (2) db's on different machines. The DDL is the same, however, I am getting the no relations message on one ...
0
votes
1answer
67 views

Put Postgres query result in array

How can I put an sql query result in a string array with Postgres? I have some attributes in the column 'zone' of the table 'mytable' and I must put them all in a String[] areas; This is my first ...
0
votes
1answer
40 views

Is it possible to set a timer for autovacuum in PostgreSQL

I am running PostgreSQL 9.4.1. I am currently cleaning up data, which requires me to run expensive queries. It seems like my tables get "locked" up every day at the same time in the afternoon. My ...
1
vote
0answers
33 views

postgresql pg_dump no matching schemas [closed]

I have a local postgres 9.4 installation with pg_dump. I am trying to do a remote pg_dump where I'm looking to dump only the schema DDL with NO data. I am getting the error: pg_dump: No matching ...
1
vote
1answer
62 views

How to atomically replace table data in PostgreSQL

I want to replace the entire contents of a table, without affecting any incoming SELECT statements during the process. The use case is to have a table which stores mailbox information that is ...
0
votes
1answer
62 views

postgres fetch record from partitions without the check constraints

I am using postgres 9.4, I have a messages table that has 100M records, messages belongs to one feed_id, so I decided to partition the tables by posted_at. Table "public.messages" ...
0
votes
0answers
41 views

What's the greatest value for postgres maintenance_work_mem?

I set 1960MB successfully for maintenance_work_mem, and got about 30% performance improvement on my COPY batch inserts. So I'm trying to run the import with even more mem (my machine has 32GB). ...
0
votes
0answers
69 views

How to run pg_bulkload on windows?

Is it possible somehow to run the postgres batch tool pg_bulkload on windows? I could not find any documentation on this, only for linux. Though it should be possible as the docs state: If you ...
0
votes
1answer
29 views

Postgresql unique history

I am running several commands, some repeatedly, from the postgresql command line tool. In Bash, the history function keeps one command for each set of sequential identical commands, it seems that ...
2
votes
1answer
53 views

Create a grouping based on chains of pairs

Say I have two tables: CREATE TABLE datum ( datum_id SERIAL PRIMARY KEY, datum_text TEXT NOT NULL ); CREATE TABLE datum_pair ( datum_id1 INTEGER NOT NULL REFERENCES datum (datum_id), ...
2
votes
3answers
148 views

Unknown returned type in PostgreSQL query

The following query works: SELECT a, b FROM unnest(ARRAY[(1,2), (3,4)]) AS t(a integer, b integer); a b _ _ 1 2 3 2 However, I wasn't able to use a different column type such as varchar(255): ...
3
votes
1answer
37 views

Circular dependency in schema or boolean with CHECK?

Let's say I have the following tables: create table repositories ( id serial primary key, name varchar(255) not null ); create table remotes ( id serial primary key, url varchar(255) ...
1
vote
0answers
70 views

how to get a sum total of each column and row - postgresql [Crosstab or Case]

I have to count the records according to the criterion, they are much more filters but I have summarized. I could use either of two ways. 1# SELECT * FROM crosstab('SELECT dim_espacio.desc_estab, ...
2
votes
0answers
78 views

Postgres Streaming Replication: How to re-sync data in master (with standby) after failover

I am absolutely new to postgres 9.1 replication mechanisms. I have managed to set up streaming replication between a master and standby postgres (Both have x number of records). At one point when ...
1
vote
1answer
70 views

Implementing json_object_agg() in Postgres 9.3

I feel like I need the json_object_agg() function of Postgres 9.4 but I will not be able to upgrade from 9.3 right now. Is there a way to do what I want in 9.3? Here's my scenario. I have a table ...
3
votes
2answers
60 views

Is copy/rename a good way to improve temp table performance?

An application I am working with contains this sequence of queries at the end of constructing a large temp table; I believe it is an attempt to improve performance by pruning dead tuples - create ...
1
vote
1answer
26 views

pg_dump does not honor -n

I'm using pg_dump (9.2 database), to extract one schema and load this into another database (9.4). well, here's the command: pg_dump -f file.sql -F plain --inserts -n schema database Ok. My ...
2
votes
0answers
40 views

Postgres to Postgres foreign data wrapper, for tables based on queries

I have a db with sensitive data in a secure server, and a set of anonymization queries that I'd like to create foreign tables based on in a less secure server. The Postgres native wrapper only ...
3
votes
1answer
46 views

can't connect to remote postgresql database

I'm trying to connect to a remote psql database. Before I added the pg_hba.conf entry with the client's IP address, I was getting an error message : xdev@xdevbox:~$ psql -U postgres testdb -h ...
2
votes
2answers
101 views

Can I configure the PostgreSQL locale so that it ignores umlauts in ILIKE queries?

I'm using PostgreSQL to look for city names matching a user-entered string. I'd like to be able to match a row with the name "München" (German for "Munich") if the user types "Munchen". In other ...
0
votes
0answers
31 views

Pgsql Function to create multiple child tables inherited from parent tables

I need help with pgsql function to create multiple child tables concatenated with date. Inherited from parent tables. I am using the following queries -- Setting date range SELECT CAST( ...
1
vote
1answer
33 views

Postgres knowing which where column returned positive result

Assuming we have a query like: select * from table where a=1 or b=2 or c=3 and the where conditions met are a=1 and c=3 Would it be possible to know in the query (in the output) that a and c are ...
0
votes
1answer
31 views

Transfer data to New Laptop [closed]

I am completely new to PostgreSQL My data is in the directory c:\postgres.......data and the database name is GXPGRAN. I need to copy this to a new laptop. Can somebody please give me all the steps ...
1
vote
1answer
84 views

Group by maximum consecutive row

I have data like the following: id | src_ip | dst_ip | port | ----|----------------|--------------|------| 256 | 192.168.61.200 | 10.75.64.222 | 80 | 257 | 192.168.61.200 | ...
2
votes
1answer
82 views

Slow PostgreSQL query

I'm building a search interface for a web site that currently searches through approximately 2 million pieces of real estate with approximately 60 million attributes (address, city, state, ZIP, beds, ...
1
vote
0answers
36 views

Make a backup file or save a DB in PostgreSQL

I have deployed for my data base in production a new groups of users; An admin user with full access to the DB ; A read only user ; A super admin user to manage all the DBs ; The matter is for ...
0
votes
1answer
57 views

how do I configure postgresql to find the pg_controldata command [closed]

I am trying to execute the pg_controldata command, I got the error command not found. Then I checked: ls -l /usr/lib/postgresql/9.4/bin/ | grep "control" -rwxr-xr-x 1 root root 25960 feb 5 18:24 ...
1
vote
2answers
109 views

How to know how long my server is started

I am working with postgresql-9.4 and for support and monitoring tasks I need to know how long my server is started? Is there any function or way to know this? Is there an sql way to know it? I ...
1
vote
1answer
49 views

postmaster.opts in postgresql 9.3?

I am using PostgreSQL 9.3, but the book I am reading "Beginning Databases with PostgreSQL From Novice to Professional" is using PostgreSQL 8. It mentions there is a postmaster.opts file which passes ...
1
vote
0answers
49 views

Can I export a PostgreSQL database from Heroku and Import as a PostGIS database in Digital Ocean?

Disclaimer: This may be a dumb question. This is honestly way over my head... one of the pitfalls of using Heroku for too long. I have a PostgreSQL database on Heroku. Now I'm migrating to Digital ...
2
votes
1answer
32 views

Postgresql assign mutliple row query result to variable and continue execution

I'm trying to create a script that calls several stored procedures in PostgreSQL and print the result of some of them: CREATE OR REPLACE FUNCTION script( vtoken character varying) RETURNS void AS ...
3
votes
1answer
59 views

PostgreSQL partial index unused when created on a table with existing data

In PostgreSQL 9.3, I am attempting to create an efficient index on a rarely-used (0.00001% of total records) boolean column. To that end, I discovered this post on SO: ...
0
votes
0answers
28 views

Storing table history - is this a good use case for Postgres's table partitioning / inheritance?

I currently have a products table which stores product information (title, description, tags, manufacturer, etc.) for a website. I would like to store historical information of changes to this table ...
0
votes
0answers
21 views

Postgres: Migrate data with FKs to multi-database

I currently have a single DB with data from a number of clients. I have been tasked with writing a script that splits the data up into separate DBs per client (each DB will have the same schema). For ...
0
votes
0answers
20 views

Heroku Postgres Follower

I'm currently looking at creating a follower db of a live production database. The follower will be used for analytics purposes only. And will not be inserting any data - if it did then it would be by ...
1
vote
0answers
41 views

PostgreSQL 9.0 live migration/replication with native/bucardo between multiple architectures

I'm writing here because I'm in desperate need of a stable and good solution for resolving a issue we are facing. We have an old PostgreSQL server running on RHEL 4 i386 with PostgreSQL 9.0 ...