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
56 views

Is there a timeout option for remote access to PostgreSQL database?

I'm working via pgAdmin III on a remote PostgreSQL database. After a not so long period (say 10-15 min.) without doing anything in pgAdmin, the connection automatically expires. I therefore have a ...
5
votes
1answer
349 views

Replace a materialized view in Postgres

I have a materialized view in Postgres 9.3 that I'd like to update with new columns. Other materialized views also depend upon this view, but the error message indicates that dropping a view isn't ...
1
vote
0answers
18 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 ...
0
votes
1answer
38 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 ...
3
votes
2answers
641 views

Do I risk losing the benefits of indexing if I have an index on every column?

I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of ...
0
votes
1answer
53 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
22 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 ...
1
vote
1answer
48 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: ...
2
votes
1answer
516 views

Trouble with installing additional modules on Postgresql 9.1 on ALT-Linux

I'm trying to create extension under postgresql 9.1 (Linux): CREATE EXTENSION pg_trgm; but got error: ERROR: could not open extension control file "/usr/share/pgsql/extension/pg_trgm.control": No ...
2
votes
0answers
24 views

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

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
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 ...
2
votes
1answer
279 views

REST webservice as a Foreign Data Wrapper in postgresql

I know it's possible to embed data from a foreign resource in a postgresql database as a foreign table and have seen examples including redis, another postgres database, etc but I can't really find ...
0
votes
1answer
143 views

pg_basebackup giving “could not receive data from client: Connection reset by peer”

I'm trying to get data to a new slave box from my new master (previously the slave). I've tried the steps already and it worked previously. Now when I run the following command: pg_basebackup -h ...
0
votes
1answer
394 views

pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib 9.1.11 having initially database ...
0
votes
1answer
904 views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...
2
votes
1answer
90 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 ...
3
votes
1answer
157 views

How to determine cost/benefit of adding an index?

According to Craig Ringer: While it's usually a good idea to create an index on (or including) your referencing-side foreign key columns, it isn't required. Each index you add slows DML operations ...
0
votes
0answers
24 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
24 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
2answers
188 views

Postgres multiple columns to json

I am running postgresql 9.3.4. I have a table with 3 fields: id name addr --- ---- ---- 1 n1 ad1 2 n2 ad2 ... I need to move the data to a new table with fields like: id data ...
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 ...
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 ...
1
vote
4answers
64 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
153 views

Pros and cons of using many schemas in PostgreSQL as opposed to just one?

For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the ...
0
votes
1answer
23 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
2answers
31 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 ...
3
votes
1answer
2k views

Cannot get my slave - replication server to start

My replication server will not start. I have followed the instructions here: http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/ As well as several other ...
2
votes
2answers
108 views

manual execution of specific remote query with postgresql_fdw

In 9.4b2, postgresql_fdw doesn't know how to "push down" aggregate queries on remote tables, e.g. > explain verbose select max(col1) from remote_tables.table1; ...
0
votes
0answers
12 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
1answer
32 views

Select records as groups based on sequence and date

I have data stored as: CompID | Seq | Udate | Utime --------+-----+-----------+------- mx433 | 1 | 1/1/2012 | 13:54 mx433 | 1 | 1/2/2012 | 13:43 mx433 | 2 | 1/4/2012 | 14:23 mx433 ...
0
votes
2answers
39 views

How can I find table name for each row result?

How can I find the table name for each row result in my query? I have tried ideas suggested on the stack network (overflow, dba) 1 How to make a table name an attribute in PostgreSQL? 2 ...
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
2answers
125 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
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 ...
1
vote
1answer
2k views

PgPool 2 with HA Proxy

So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for. E.g.: PostgreSQL ...
2
votes
1answer
43 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 ...
0
votes
0answers
35 views

How to load multiple files into a foreign table in POSTGRESQL?

I have several files starting with file_*.dat and I want to load these into one foreign table. I created the extension and server: file_fdw and file_server. I created this table - CREATE FOREIGN ...
2
votes
1answer
107 views

PostGIS: Cutting Holes in Self Geometry

I have a table of geometries in PostGIS, some of which overlap. When I display the geometries, this has the unfortunate consequence of blending the two stacked geometries together and producing an ...
1
vote
1answer
18 views

psql with '-c' option or '-f' option is not terminating properly

When I run a command such psql -d database -c 'select * from table' or psql -d database -f sqlcommands psql does not terminate cleanly. Interestingly piping the the contents of the file instead of ...
39
votes
8answers
46k views

How do I find PostgreSQL's data directory?

I forgot how I last time started postgres (it was months ago) and I don't remember where the data directory is located at. The postgres command seems to require to location. I'm on MacOsX if that ...
0
votes
1answer
42 views

Install PostgreSQL using source code or using apt-get?

I want to install PostgreSQL on a server that runs Ubuntu 14.04.2 LTS. I understand that I have two options: 1) Install PostgreSQL using the source code distribution e.g., ...
0
votes
2answers
2k views

postgres service suddenly not working after the computer crashed while working

I've been working with postgres on a linux server for the last couple of months. Last week, half of our servers crashed and my one with them. I was in the middle of uploading data to the postgres ...
0
votes
1answer
42 views

Memory usage on select for large number of rows

I am trying to dump the entire contents of a large table, from the command line using pqsl, but am running into a problem where memory usage goes up, to the point where the process is killed, before ...
0
votes
1answer
396 views

PostgreSQL + PG_Monz (Zabbix) DB size too large > 1073741824

I an new to Postgres (9.3 on Ubuntu 14.04) monitoring and I have been using Zabbix/PG_monz. I am seeing errors that my database has "too many temp bytes" and that my database is "too large". My ...
1
vote
1answer
590 views

Tree structure hierarchical query sorting

I have the following structure of table. CREATE TABLE table1 ( serial_num integer NOT NULL, parent_num integer, parent_key text, key_ary_num integer[], level integer, rank integer, ...
0
votes
2answers
62 views

Postgres: difference between CTE and temporary table

In Postgres is there a difference between a CTE and a temporary table other than the fact that the CTE exists just for the context of one statement? Documentation says that Common Table ...
1
vote
1answer
17 views

PostgreSQL: restore a record from a backup

We have a Rails app backed by a PostgreSQL DB and hosted on Heroku. We recently had a bug that deleted some (very few before we caught it) records. Excuse my ignorance but is there a straightforward ...
2
votes
1answer
24 views

Does postgresql pl/lua language have luajit performance?

I am trying to use postgresql for a financial system and I need the best performance I can use, so I decided to use stored procedures for managing all the math and logic of the system. I have read ...
3
votes
1answer
5k views

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...