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
35 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
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
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: ...
2
votes
1answer
512 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
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
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
276 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
124 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
391 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
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 ...
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: ...
0
votes
1answer
898 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
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 ...
3
votes
1answer
151 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
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
2answers
170 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
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
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 ...
0
votes
1answer
146 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
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
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 ...
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
105 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
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
0answers
26 views

jdbc error org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend [closed]

I am using Java database pool to connect to PostgreSQL 9.4,the database is on SUSE 12 x64 SLES. I found a problem: jdbc error org.postgresql.util.PSQLException: An I/O error occurred while ...
0
votes
1answer
31 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
38 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
124 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 ...
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 ...
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 ...
38
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
40 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
38 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
376 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
586 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
59 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
15 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 ...