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)

5
votes
1answer
150 views

PostgreSQL operator uses index but underlying function does not

I'm attempting to use JSONB with JDBC, which means that I have to avoid any of the operators which use the '?' character (as the PostgreSQL JDBC driver has no escaping for this character). Taking a ...
0
votes
2answers
137 views

PostgreSQL : dependencies on a schema

I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
1
vote
1answer
26 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
37 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
210 views

How to connect to an Amazon PostgreSQL database using SSL

I'm trying to log into an Amazon PostgreSQL DBS using SSL. I have all credentials but my psql version doesn't seem to support SSL. I've tried searching Postgres docs but all I see is how it has ...
1
vote
1answer
37 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 ...
2
votes
1answer
308 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
38
votes
3answers
62k views

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
0
votes
1answer
315 views

Taking hot backup of slave node in postgres (master-slave config with repmgr)

I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave ...
1
vote
0answers
37 views

rt_raster_to_gdal: Could not load the output GDAL driver

I have already set the environment in windows SET postgis.gdal_enabled_drivers = 'ENABLE_ALL'; Even I tried by adding following in environment path- POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL But ...
2
votes
1answer
47 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), ...
0
votes
0answers
34 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
28 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
2answers
79 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 ...
1
vote
1answer
23 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 ...
0
votes
1answer
232 views

WAL archiving from a standby backup strategy Postgresql 9.3

I am trying to figure out if wal archiving from a standby is possible and a viable option for a backup strategy on Postgresql 9.3. The idea is to be able to take regular base backups and wal ...
2
votes
3answers
123 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
33 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) ...
0
votes
0answers
20 views

Alternative when IN clause is inputed A LOT of values (postgreSQL) [migrated]

I'm using the IN clause to retrieve places that contains certain tags. For that I simply use select .. FROM table WHERE tags IN (...) For now the number of tags I provide in the IN clause is around ...
3
votes
2answers
48 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
0answers
30 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
36 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 ...
0
votes
1answer
563 views

pgpool-II configuring multiple master databases

I could not find a solution for this anywhere, not even in the pgpool manuals. How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master ...
3
votes
1answer
455 views

Is there a psql equivalent of bash's reverse-search-history?

I am very fond of bash's reverse-search-history (C-r) (command-line feature): Search backward starting at the current line and moving ‘up’ through the history as necessary. This is an incremental ...
1
vote
2answers
104 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 ...
0
votes
0answers
30 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( ...
2
votes
0answers
28 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 ...
0
votes
0answers
19 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 ...
1
vote
2answers
681 views

How do I use an Ispell dictionary with Postgres text search?

Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.
1
vote
1answer
30 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 ...
1
vote
1answer
66 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 | ...
0
votes
1answer
30 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
0answers
33 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 ...
2
votes
1answer
74 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, ...
0
votes
1answer
27 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
1answer
454 views

How to make pg_dump skip extension?

This is on 9.3 but I can remember simillar things happening since 7.x. So I create database and install plpgsql extension into it. Later on I create a pg_dump and before restoring it into databse I ...
2
votes
1answer
16 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 ...
0
votes
0answers
45 views

Log large queries with PGbouncer

Is there a way to ask pgbouncer to log any query that generates a result set larger than, say, 5MB?
2
votes
5answers
214 views

How to determine if a postgres database needs to be vaccumed?

Given a PostgreSQL database what command can be used to determine if the database needs to vacuumed? UPDATE I am working with client who is evaluating postgres but I am not a DBA, I just happen to ...
1
vote
1answer
30 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
21 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 ...
3
votes
1answer
46 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: ...
18
votes
3answers
41k views

Granting access to all tables for a user

I'm a new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, DELETE privileges on a low privileged user and enable those grants to apply to all ...
2
votes
2answers
58 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
1answer
2k views

How to edit `postgresql.conf` with pgAdmin?

How does one edit the 'postgresql.conf' file with the pgAdmin app? With pgAdmin 1.18.1 on Postgres 9.3.x, when I choose File > Open postgresql.conf I get a file picker dialog. And, yes, I'm connected ...
0
votes
0answers
19 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
1answer
460 views

Sync offline PostgreSQL server

I am new to PostgreSQL. I am looking for a solution wherein 2 mirror images of a database sit on a local and remote server. All the work is done on the local server and at periodically synced with the ...
16
votes
4answers
11k views

Measure the size of a PostgreSQL table row

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some ...
0
votes
0answers
14 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
15 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 ...