All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

0
votes
1answer
44 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
1
vote
2answers
91 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
564 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
377 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
52 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
90 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
62 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
347 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
108 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
192 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 ...
4
votes
1answer
717 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
103 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
108 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
251 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
38 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
88 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), ...
4
votes
3answers
336 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
61 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
308 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, ...
3
votes
0answers
488 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 ...
2
votes
1answer
568 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
89 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 ...
3
votes
1answer
94 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
1answer
133 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 ...
4
votes
1answer
2k 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
351 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
35 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
33 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
181 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
100 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
40 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
625 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
138 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
168 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
259 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
238 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 ...
6
votes
1answer
119 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: ...
1
vote
0answers
92 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
23 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
67 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
121 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 ...
0
votes
1answer
75 views

PostgreSQL column for year

I want to save the year in a PostgreSQL database - just the year. How should I create this field? There is a lot of information out there how I can extract the year from a date field, but not on how I ...
0
votes
1answer
62 views

How to load 100k + Records into progres database from a CSV file?

Our project needs to load 100k+ rows into a Postgres table. We have a huge .CSV file with all these 100K+ rows and we need to load this data into a table effectively and quicker (may be in the form of ...
2
votes
2answers
973 views

Many columns vs few tables - performance wise

Yes, I am aware that data normalization should be my priority (as it is). I've got a table with 65 columns storing vehicle data with columns: used_vehicle, color, doors, mileage, price and so forth, ...
1
vote
2answers
287 views

Normalization of library book table with multiple authors

I have a database structure with two tables: The first one is called Author, has two columns (AuthID, AuthName) and its Primary Key is (PK_AuthID). The second one is called Book and has three ...
2
votes
1answer
125 views

Was using `set datcollate='C'` a mistake?

In a pre-production environment running django 1.6 on PostgreSQL 9.1 we had a problem with database records arriving in a different order from what the application was expecting. So we ran this ...
1
vote
0answers
155 views

How to create index on a postgres table as fast as possible?

I have a large database import (100GB) on a postgres table without indexes. After the import I want to create the index for the queries as fast as possible. No data is accessed on the table as long as ...
3
votes
2answers
562 views

Postgres Slow Queries - Autovacuum frequency

We've noticed the performance of our platform drop in recent weeks so I've run the following: select relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables ...
3
votes
0answers
91 views

Restoring Postgres fixtures into a different database with different premissions

I've got an application called BigSense that uses two database users/rolls. One has full permissions and is used by my application to create/adjust table definitions. Then the application starts with ...