This tag is specifically for PostgreSQL version 9.1

learn more… | top users | synonyms (1)

25
votes
3answers
39k 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;
8
votes
3answers
9k views

Optimizing queries on a range of timestamps (two columns)

I use postgresql-9.1 with ubuntu 12.04. I need to select records inside a range of time: my table time_limits has two timestamp fields and one property integer. Indeed there are other info columns ...
8
votes
1answer
2k views

Database archive solutions

In continuation to a question posted by me on Is it a good idea to move high-volume and highly-accessed tables to a separate database?, am looking out for different techniques/solutions available for ...
7
votes
3answers
29k views

How do I insert a row which contains a foreign key?

Using PostgreSQL v9.1. I have the following tables: CREATE TABLE foo ( id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY, type VARCHAR(60) NOT NULL UNIQUE ); CREATE TABLE bar ( id BIGSERIAL ...
7
votes
1answer
747 views

Unexpected Seq Scan when doing query against boolean with value NULL

I have a database column called auto_review where column type is boolean. There is an index for that field, created using the ActiveRecord ORM. CREATE INDEX index_table_on_auto_renew ON table USING ...
7
votes
1answer
1k views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...
7
votes
2answers
6k views

upgrading from postgres 9.1 to 9.3 on ubuntu server

I have my production server(ubuntu 13.10) running with postgresql 9.1. I want to use few features of 9.3, hence want to upgrade. Could someone help me with upgrading from 9.1 to 9.3 so that there is ...
7
votes
3answers
1k views

Store a formula in a table and use the formula in a function

I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of ...
7
votes
2answers
3k views

How to do incremental/differential backup every hour in Postgres 9.1?

Trying to do an hourly hot incremental backup of a single postgres server. I have the following setup in postgresql.conf: max_wal_senders=2 wal_level=archive archive_mode=on archive_command='copy ...
7
votes
2answers
853 views

Convert units of measurement

Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes. Unit Conversion Table The unit ...
6
votes
3answers
556 views

Issue with composite type in an UPSERT function

I have a function in PostgreSQL 9.1 called fun_test. It has a composite type as input parameter and I keep getting a casting error when I call it. CREATE OR REPLACE FUNCTION netcen.fun_test(myobj ...
6
votes
3answers
2k views

How to pass a table type with an array field to a function in postgresql

i have a table called book CREATE TABLE book ( id smallint NOT NULL DEFAULT 0, bname text, btype text, bprices numeric(11,2)[], CONSTRAINT key PRIMARY KEY (id ) ) and a ...
6
votes
3answers
21k 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 ...
6
votes
1answer
3k views

Moving postgresql data to different drive

I am using AWS as my cloud environment. I installed PostgreSQL on the same drive as my root instance volume. I have attached and mounted the second drive to my instance. Now I want to move all my ...
6
votes
1answer
6k views

How to use aes-encryption in PostgreSQL?

I tried aes-encryption by using following statement: SELECT encrypt('test', 'key', 'aes'); which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
5
votes
2answers
164 views

PostgreSQL: Query DB for rows containing integer[] > or < than specified value

I have a table which stores historical temperatures for the past 5 days stored in an integer[] column based on city. The question is: How can I query for entries which have a value great than or less ...
5
votes
2answers
2k views

Replication has failed; how to get going on again?

I'm running Postgres 9.1.6 on Ubuntu and I have streaming replication setup between a master and slave. Everything has been running smoothly until the database crashed and we had to restart both of ...
5
votes
2answers
9k views

PostgreSQL: changing password for a user is not working

I installed PostgreSQL on EC2 machine and now I want to change the password of user postgres I do $ sudo -u postgres psql psql (9.1.5) Type "help" for help. postgres=# ALTER USER postgres WITH ...
5
votes
1answer
27 views

Postgresql 9.1 add nullable column costs more than 10 minutes

I have problems to add a new column on a table. I tried to run it a couple of times, but after more than 10 minutes running, I decided to cancel query because of lock time. ALTER TABLE mytable ADD ...
5
votes
1answer
58 views

How to see amount of reusable space for a table

I had a huge table(about 300mil rows). After massive DELETE and VACCUUM table files have stopped to grow. That's exactly what I expected. But is there any way to see how much space became reusable?
5
votes
1answer
647 views

PostgreSQL 9.1 streaming replication problem: replica fails to use an index properly

We use PostgreSQL 9.1.7 on Ubuntu Linux 12.04 on a master server and PostgreSQL 9.1.7 on FreeBSD 9.0-RELEASE on a replica server. The replica and master servers return different results on the same ...
5
votes
3answers
1k views

Maximum number of databases for single instance of PostgreSQL 9

Developing a multicustomer application we plan to use a different database for each customer. But it could be more than 1000 customers (applications). Will PostgreSQL handle it without any problems? ...
4
votes
2answers
2k views

Postgresql 9 speeding up indexed inserts (JPA)

I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a ...
4
votes
4answers
9k views

How to list all views in SQL in PostgreSQL?

How do I list all views for a database using an SQL command in PostgreSQL? I would like something similar to output of the psql \dv command, but preferably just a list of view names. e.g., SELECT ...
4
votes
2answers
1k views

Index max row size error

Is there a upper bound for an array column? I am getting this error when inserting into the array field - PG::Error: ERROR: index row size 3480 exceeds maximum 2712 for index "ix_data" Here's my ...
4
votes
1answer
2k views

Using Solr/Lucene for searching non-text tables?

I am creating a web application to retrieve subsets of one large (4m rows) table. The 4m rows only change once a year. The table has 200+ columns of types boolean and numeric. It has no text columns. ...
4
votes
1answer
3k views

PostgreSQL: Cannot change directory to /root

I am trying to copy a table planet_osm_polygon from one database osm to another test. I su postgres and performed the pg_dump. Problem: However I'm getting the error could not change directory to ...
3
votes
2answers
3k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
3
votes
2answers
427 views

Change built-in default privileges in PostgreSQL?

Introduction. When I create a database, postgres=# CREATE DATABASE test2 OWNER test2; it is created with an empty privileges column: Name | Owner | Encoding | Collate | Ctype | ...
3
votes
2answers
12k views

Forgotten PostgreSQL Windows password

This morning I’ve been trying to connect the Postgresql database on my Windows 7 professional desktop. The default value is ‘postgres’, but sure enough I forgot what password I used when I originally ...
3
votes
2answers
50 views

idiom to treat empty result set as zero

This is obviously a SSCCE. I have an inventory table that holds the number of items (numOfItems) in a warehouse at any given date (inventoryDate). Both are integers to keep things simple: CREATE ...
3
votes
1answer
262 views

PostgreSQL 9.1, how to restore a Hot Standby Slave w/o any Wal Files?

I have a PostgreSQL 9.1 hot standby slave that had all of its wal files removed. How can I restart the standby server and have the slave resync from the master? Thanks
3
votes
1answer
116 views

Very large btree index with few rows (openstreetmap gis data)

I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
3
votes
1answer
100 views

Use of integer instead of interval (of one type)

Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation. ...
3
votes
1answer
645 views

Profiling PostgreSQL

We have a system built with Python 2.7, Django, PostgreSQL 9.1 and PostGIS 1.5. In this system there is some extensive validation processes that are, well, intensive. Our machine: SO: CentOS 6 HW: 8 ...
3
votes
1answer
2k views

delete rows in 3 tables with on delete cascade

For a relation 0..n between two tables (t1,t2), the mpd generates an intermediate table (t3) with two fk. Let two constraints on the t3 table, each one with "on delete cascade", if I do: delete ...
3
votes
2answers
665 views

PostgreSQL: Can the archive command for master and standby point to the same directory?

I use streaming replication and PITR (WAL files) in my cluster, currently I have different versions of postgresql.conf for the master and the standby server. The only difference in the files is the ...
3
votes
1answer
269 views

Postgres Backup queries on AWS after reading Postgres docs

I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance. So there are 2 ways to do it. Create a dump using pg_dump. File level backup with ...
3
votes
2answers
393 views

Disable all constraints and table checks while restoring a dump

I've obtained a dump of my PostgreSQL database with: pg_dump -U user-name -d db-name -f dumpfile which I then proceed to restore in another database with: psql X -U postgres -d db-name-b -f ...
3
votes
1answer
86 views

timeline 2 of the primary does not match recovery target timeline 1

I want to create a configuration with postgresql and pgpool to get height availability (HA). I want to do this: .-----. .--------. | | W | DB | | APP ...
3
votes
2answers
3k views

PostgreSQL: How to backup only One Schema from a database and restore it on another server

I have a database named "A" which has two schemas "B" and "C". I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres. Do I have to create a new ...
3
votes
1answer
434 views

Postgres backup and WAL to S3

We are looking for a solution for the following problem: We have set up streaming replication so we have a master DB and a slave DB, we want to have basebackups and WAL files sent to our S3 storage ...
3
votes
1answer
64 views

restarting postgresql quickly

I'm reading over the postgresql admin cookbook. Some of the database server parameters require a restart and with a busy database, you may need to restart quickly. There are a number of things to do ...
3
votes
1answer
369 views

Users groups/permissions when working with PostgreSQL

While I've been working with PostgreSQL for approximately 1 year, I still feel like a bit of a Newbie. I feel like I'm constantly fighting with psql and pg_dump when it comes to files and ...
3
votes
0answers
130 views

What happen when Postgresql tablespace is null?

I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s. Long story short, when I check the disk usage per tablespace, I get different distribution ...
2
votes
3answers
2k views

Postgres connection access denied on IPv6 address

Installed PostgreSQL 9.1 x64 on Windows, set up a listen address, but when connecting with pgAdmin I get the following error. Not sure why PostgreSQL is seeing my IPv6 address and not my regular IP ...
2
votes
2answers
732 views

PostgreSQL performance degradation over time on a write intensive db

I have observed a weird situation that over time the performance of a query (a combination of queries explained below) degrades, meaning at the start of testing (for a few minutes) the time of the ...
2
votes
2answers
2k views

PostgreSQL difference between VACUUM FULL and CLUSTER

I have a table with 200 GB of size occupied by data and 180 GB of size by the 6 indexes on it. It is 30% bloated, so I want to reclaim unwanted space occupied by it. It is clustered on job_id_idx ...
2
votes
2answers
506 views

Is it a good idea to move high-volume and highly-accessed tables to a separate database?

Ours is a web-based application (built on multi-tenant architecture) running PostgreSQL v9.1.3. There are about 450 tables in our application, out of which 2-3 tables, specific to a module in the ...
2
votes
2answers
121 views

SQL order by query results in any arbitrary (but reproducible) manner

For testing purposes I need to get some data from a set of database tables in any arbitrary (but reproducible) order. The idea being that I can later compare two runs using a textual diff tool. Is ...