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
0answers
51 views

postgres database program to save sql templates and search where used it

I have a postgres database with tables and functions build using sql templates. And I would like to use a database program to save these templates and search where I used it. What i want to do is to ...
1
vote
1answer
128 views

Battery backed write cache and Postgres

I will be getting a SAN with a battery backed write cache for a new Postgres data warehouse and am not sure what settings to apply to make proper use of it. If the cache is enabled in the SAN is that ...
1
vote
1answer
71 views

constraint view CASE values

Below is a sscce to motivate the case. I keep a table of persons: CREATE TABLE person (name VARCHAR); ALTER TABLE person add PRIMARY KEY (name); INSERT INTO person (name) VALUES ('Bob'), ('Mike'); ...
0
votes
0answers
1k views

PostgreSQL Install Error

When I install the server for the first time, when almost finished it reports: (from log file "%temp%\bitlock_installer1376.txt") Loading additional SQL modules... Executing cscript //NoLogo ...
2
votes
1answer
66 views

Selecting distinct records

I have a user relation to address (1 to 1). So the address has two types which is a type column. One is the VerifiedAddress and the other one is the SearchAddress. So the user who had verified his ...
0
votes
0answers
116 views

Unable to exit a Greenplum SQL script

Is there any way in psql (Greenplum flavour) of exiting a script? I have tried with \q and \quit, e.g. \echo "Hello"; \q -- or \quit but I get: my_database> \i my_script.sql ...
4
votes
3answers
887 views

PostgreSQL: Extending database storage when disk is full

I have a database that takes about 60GB in my disk storage. And it doesn't alow to insert into database anymore. I have read the documentation about tablespaces link But It's like create space for a ...
1
vote
2answers
3k views

Very slow execution of a simple query

I try to create a report for my data, but it is really slow on a big table. The table structure is: CREATE TABLE posts ( id serial NOT NULL, project_id integer, moderation character ...
0
votes
1answer
856 views

PostgreSQL, storing encrypted data or encrypt database with pgcrypto - what is more secure? [duplicate]

I have two machines: client and server (a database server). I want to connect from client to server and manipulate data from database (insert, update, delete, select) using dblink. I would like to ...
0
votes
1answer
688 views

PostgreSQL dblink password encrypted or not?

I have two PostgreSQL servers: server 1: 192.168.0.1 server 2: 192.168.0.2 I want to connect to the database on server 2 from server 1. I do it like this: SELECT ...
1
vote
1answer
668 views

Postgres database migration “zero” downtime

I have a setup with a JBoss EAP server, postgres 9.1 database. I will soon update so I have two JBoss servers in a cluster. This is mostly to avoid downtime when doing application deployments. With ...
1
vote
1answer
174 views

Optimizing Postgres query

I've got a one to one relation from users to addresses table. Where one user can have one search address and one verified address. I've got two indexes on addresses table : Index on state field ...
2
votes
1answer
109 views

Understanding postgres reason for picking this query plan

I ran a query: select * from orderline where orderid = 12345 and status > 0 that gave me the following plan: Bitmap Heap Scan on orderline (cost=286370.00..531410.78 rows=76372 width=155) ...
0
votes
0answers
36 views

Changed password on Postgres but I still can log in with the old one [closed]

I changed my password via alter user, but can still log in with the old one. In the 9.1.6 version of postgresql I resolved this problem by killing all the processes through pg_terminate_backend, but ...
4
votes
1answer
635 views

2 B-tree indices OR 1 GiST index on tsrange — which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
8
votes
2answers
4k views

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE

There is a long and quite elucidating answer on the differences between TIMESTAMP WITH TIME ZONE -vs- TIMESTAMP WITHOUT TIME ZONE available in this SO post. What I would like to know is: are there ...
2
votes
2answers
233 views

Most performant way to fetch last record

I have a Postgres table Prices with the columns price (Decimal) product_id (Int) Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table ...
5
votes
1answer
169 views

redundant foreign keys in PostgreSQL - are there any indexing benefits?

Note: this is not the same as this SO question which is about adding redundant information in the tables themselves. Does the final statement in the below series of statements buy me anything in ...
3
votes
1answer
251 views

Enforcing constraints “two tables away”

I ran into some trouble modeling an electrical schematic in SQL. The structure I'd like to capture is part ←────────── pin ↑ ↑ part_inst ←───── pin_inst where "inst" is short ...
0
votes
2answers
740 views

Configure postgresql recovery again to be slave

I completed a replication setup using PostgreSQL. Everything was working fine, so I proceeded doing a test by bringing down the main database and promoting the slave to be new master (via touch ...
4
votes
0answers
287 views

User locking tables without privileges [closed]

This is a restatement of a question asked on the GIS Stack Exchange here. Although it has GIS-specific elements it may be be a more general PostgreSQL effect. I have a PostgreSQL 8.4.4 database ...
1
vote
1answer
729 views

Restore postgres data/tablespace to new tablespace (at new mount point)?

I have a bunch of test/development databases which we currently refresh with production data as-needed using a NetApp filer's snapshot capabilities - we have a production slave with its datadir on a ...
11
votes
1answer
4k views

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed a MATCH SIMPLE or MATCH FULL option in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
1
vote
1answer
2k views

Postgres replication: Do I need to rsync pg_xlog files if I have archiving setup in master?

I am trying to replicate a master to slave, without shutting down my master. In the tutorial here: http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial, they recommend these steps: ...
0
votes
0answers
455 views

PostGres- How to loop in Table to update every record in Table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and i want to update the ...
2
votes
1answer
3k 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 ...
1
vote
0answers
704 views

Postgres database zero down time production deployment

I need some idea about how I can deploy a financial application in production environment using PostgreSQL DB with zero down time. I have two servers in Master-Slave design. Currently I am giving ...
1
vote
1answer
935 views

How do I access a old saved tablespace after reinstalling postgres?

So a lot of things might have gone wrong here. I first installed postgres 9.3 on mac 9.1 via homebrew to manage some data. I am using postgres.app. Turns out my harddrive was too small so I decided to ...
0
votes
1answer
182 views

Multiple source CASCADE DELETE behavior

I'm having trouble understanding Postgres's CASCADE DELETE behavior. If table_A REFERENCES table_B, and the column referenced could have multiple instances of the same value, will the table_A row be ...
0
votes
1answer
1k views

create a batch script on windows that periodically starts a sql function

I need to create a batch script on windows that periodically starts a sql function written in 'plpgsql'. I can use windows schedule task for creating a cron job but I don't know how to write the ...
2
votes
1answer
131 views

Where is the generic plan stored when using PREPARE statement in PostgreSQL?

According to the document, if a prepared statement is executed enough times, the server may eventually decide to save and re-use a generic plan rather than re-planning each time. My question is where ...
1
vote
1answer
957 views

Use result from table_schema query as <table> in select * from <table>

Is it possible in PostgreSQL to use the result of the query below as table name in a SELECT * FROM . SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'seam_event_%' ORDER BY ...
4
votes
0answers
2k views

How to properly monitor the number of PostgreSQL database connections?

I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 ...
1
vote
1answer
248 views

Postgresql wal saving for standby servers that go offline

I use postgresql 9.1 replication to a lot of workstations that have postgresql on hot standby mode. However sometimes these workstations go offline and disconnect from the master server. If they go ...
0
votes
1answer
3k views

Creating a table if it doesn't exist (old versions of PostgreSQL)

In recent versions of postgreSQL one can use the syntax: create temp table if not exist my_table as ... to create a temporary table if it doesn't exist yet. However, the clause if not exist is a ...
3
votes
4answers
3k views

pgpool replication not allowing md5 authentication

I have installed both pgpool and postgresql via apt. In pgpool.conf, I have made the changes necessary to use replication mode, as well as my two postgresql databases (currently all running on the ...
1
vote
1answer
263 views

How to add CYCLE to BIGSERIAL in phpPgAdmin

I'm trying to apply CYCLE to a BIGSERIAL in phpPgAdmin. I've seen how to do it with a query, but I'd much rather be able to do it from phpPgAdmin. Can this be done with phpPgAdmin? If so, how? If ...
0
votes
1answer
265 views

Postgres CYCLE and existing values

I love that SERIALs can wrap around with CYCLE. However, I can't seem to figure out if that there are lingering values, will Postgres skip over them or throw an error. In other words, I have a table ...
2
votes
1answer
2k views

PostgreSQL - how to rename CamelCase column?

When I tried to rename a CamelCase column like this: ALTER TABLE mytable RENAME COLUMN camelCaseColumn TO camel_case_column; I get the following error: ERROR: column "camelcasecolumn" does not ...
2
votes
1answer
398 views

Postgres: Purpose of archiving in master?

I am reading tutorials on how to setup streaming replication in Postgres from master to slave. Some tutorials recommend setting up archiving in the master (and restoring in slave) . In the master, ...
2
votes
0answers
278 views

Postgresql | Database with OID doesn't exist

So, how it started, DB started throwing errors about "database with OID 1" 2014-02-07 22:54:46.561 GET >ERROR: database is not accepting commands to avoid wraparound data loss in database with ...
1
vote
1answer
985 views

pgpool2 setup on master-slave replication using postgreql 9.1

I have already setup 2 ubuntu server 12.04 LTS on my local machine using virtual box and is using the master-slave replication (postgresql 9.1). 192.168.0.109 = master 192.168.0.110 = slave my ...
2
votes
1answer
146 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 ...
0
votes
1answer
165 views

Postgres recovery.conf options

I am setting up Postgres binary replication. In my slave's recovery.conf, I am suggested to have 2 options. I am wondering if the directory specified in restore_command and archive_cleanup_commands ...
2
votes
2answers
793 views

Return ID from partitioned table in postgres?

When I insert a record I need to return the inserted id by RETURNING. The problem is that the table is partitioned and in partitioned table I can't use RETURNING. I run multiple queries at once so I'm ...
5
votes
1answer
5k views

MySQL failing and very slow on importing a large file

I have a 47 GB MySQL dump of a single table: http://dumps.wikimedia.org/commonswiki/latest/commonswiki-latest-image.sql.gz I ultimately want it into PostgreSQL, but since I didn't figure out an easy ...
0
votes
2answers
68 views

How to create several builds of PostgreSQL?

I'm building PostgreSQL(version 9.3.1) from source code in Ubuntu. I'd like to create several builds of Postgres, like pg1, pg2, etc. How can I do it? Thanks in advance.
1
vote
1answer
67 views

Understanding the output of \d <table> in psql

Say I run the following command: \d mtb.big_table I get the output shown below. My questions are: What are column modifiers? (what does default now() mean?) Under Indexes, Why do some indices ...
1
vote
2answers
152 views

How to determine how much disk-space postgresql 9.1 requires as temp_space to run a query?

Given a query q to execute. Is there a way to determine how much disk-space postgresql will (or used) as temporary_space in order to execute it (during its execution) ? I'm under Ubuntu 12.0.4 with ...
4
votes
2answers
1k views

Insert RECORD into a table in a trigger function

I would like to insert a RECORD data type variable (NEW variable) into a table in a trigger. What would the SQL look like? The following attempts were unsuccessful: EXECUTE 'INSERT INTO my_table ...