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)

1
vote
1answer
221 views

Equivalent of MRG_MYISAM in databases other than MySQL? [closed]

Does anyone know if other database have something equivalent to MRG_MYISAM (aka the MERGE table type/storage engine)? I know about fragmenting, but this is not quite the same AFAIK. We're using ...
4
votes
1answer
5k 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
0answers
322 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
6
votes
1answer
3k views

Recovering a dropped table in PostgreSQL

I have : CREATE TABLE ketoan_vn.Customers ( CustomerId character varying(10) NOT NULL, CustomerName character varying(150), CONSTRAINT Customers_PK_CustomerID PRIMARY KEY (CustomerId) ); I ...
0
votes
1answer
115 views

PostgreSQL, How to keep only one schema?

I don't want some users be available to create schemas, and only use the public schema. I'd like to remove the permission of creating schemas, but I can't found anything on PotsgreSQL 9.1 ...
0
votes
1answer
48 views

look for missing datetime records in database

I have a python script that scrapes a webpage every minute of every day and saves the results in a postgres database. As I browse through the database, I've noticed there are minutes here and there ...
1
vote
2answers
4k views

PostgreSQL stored procedure to return rows or empty set

I have a stored procedure, which RETURNS SETOF ct_custom_type and inside I do RETURN QUERY EXECUTE 'some dynamic query' And I want to do this: If this 'dynamic query' returns >= 10 rows, I want to ...
0
votes
1answer
1k views

How to insert binary data into a PostgreSQL BYTEA column using libpqxx?

I'm a newbie at SQL, and even greener when it comes to using the PostgreSQL C++ API libpqxx. What I'd like to do is insert some binary data into a BYTEA column, but I find the Doxygen output is ...
0
votes
1answer
7k views

Using postgres with the command line without pgAdmin

I started working with PostgreSQL and till now I only had issues with it ( pgAdmin3 issues the most ) now I work a lot with MySQL and sometimes on the command line by just doing mysql -u ... -h ...
0
votes
1answer
2k views

postgres deadlock without explicit locking

I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock ...
1
vote
2answers
912 views

In Postgresql, is it possible to change the maximum number of columns a table can have?

I'm tasked with keeping a postgres table in sync with a non-postgres table. The non-postgres table has more than 1600 columns. When I try to create the table in postgres, I get: ERROR: tables can ...
1
vote
1answer
2k views

Pass a table array as a parameter to an upsert function in postgresql

i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
1
vote
2answers
770 views

PostgreSQL stored procedure which can return one of two custom rowtypes

I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this: CREATE OR REPLACE FUNCTION ...
1
vote
1answer
310 views

Drop database from remote that has connected users

I have a "small" problem. There is a database server that contains a database where some test frontends are connected to. I want to update that database from a dump + some scripts to the most recent ...
0
votes
1answer
380 views

How is PostgreSQL extension feature compared to SQL Server extended and CLR stored procedures?

I am new to PostgreSQL. If I am not wrong, SQL Server has similar mechanism - extended stored procedures for adding new functionality to the base SQL Server functionality. CLR stored procedures could ...
5
votes
1answer
2k views

PostgreSQL concurrent increment of counter

I need to maintain a statistical table for a project, composed by a list of items and their usage (Think about something like a website in which you would like to count pageviews). Each time an item ...
1
vote
1answer
221 views

How to allow a user to create databases only with a certain prefix?

We're having a multi-user Linux server. I'd like to give each user the ability to create and manage databases at will, but to keep things sane, force them that each database name they create must ...
1
vote
2answers
3k views

PostgreSQL cannot start after replacing contents inside its Data folder

When recovering the Postgresql database from a hacked Ubuntu 12.04 server, I copied the data directory to another location, reinstalled the OS and PostgreSQL, stopped PostgreSQL service, removed the ...
1
vote
0answers
134 views

Identical Postgres index scan taking 5x longer on server [closed]

I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject: Column | Type | Modifiers ...
47
votes
3answers
77k 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;
1
vote
1answer
131 views

Hiding variable input in psql

I am attempting to prompt for a username and password when running a database creation script, and I want to hide any text input for my password prompt. In Oracle's sqlplus, I can use HIDE at the end ...
4
votes
1answer
2k views

How to fine-tune Postgres for IO usage?

I have a Postgres database I am hosting in a quad core server with 8 GB of RAM. At any time, there can be 100 or so reads and writes occurring concurrently. I am experiencing high IO wait time. When ...
1
vote
4answers
7k views

How to change the data directory on Postgresql 9.1 on Ubuntu

I am having a problem changing the data directory on postgresql 9.1 on ubuntu 13.04: I first did a: sudo pg_dropcluster --stop 9.1 main and then attempted to create a new one in the desired path: ...
2
votes
1answer
1k views

Why does pgAdmin3 set OIDS=FALSE and owner to postgres by default?

Why does pgAdmin3 automatically set the table owner to postgres when its created and set OIDS=FALSE? I am new to PostgreSQL and want to know the rational behind this default behavior. -- Table: ...
3
votes
2answers
492 views

How to implement posts with “seen by” like facebook?

Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me. And if I see a specific post inside a group thought a url ...
4
votes
1answer
537 views

Streaming Replication Failover - how to point second slave at new master?

I have a Postgres database HA cluster I've set up in a lab. I'm using PgPool-II to manage fail-over. For the cluster itself, I have three identical Postgres 9.2 servers set up like so: Server A - ...
2
votes
2answers
19k views

Use CASE to select columns in UPDATE query?

I can use CASE to choose which columns to display in a SELECT query (Postgres), like so: SELECT CASE WHEN val = 0 THEN column_x WHEN val = 1 THEN column_y ELSE 0 END AS ...
0
votes
1answer
256 views

Postgres sql dump import error

I'm facing a issue while importing the dump in my system, i'm getting the error as " Error: syntax error at or near "" LOCK TABLESDB_NAME` WRITE;" and exiting with "out of memory" We had ...
3
votes
1answer
1k views

In Postgres, How can I merge two tables?

I have a set of web services that all need updating with a new set of static data. I can't use a distributed transaction, but would like to be able to ask each service to rollback it's changes if I ...
9
votes
1answer
3k views

How to determine if there are [idle connections with] uncommitted transactions in PostgreSQL?

According to a comment on this question I asked about idle connections in PostgreSQL 9.2, some uncommitted transactions (possibly related to some of those idle connections) might cause some ...
5
votes
1answer
4k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
1
vote
3answers
1k views

PostgreSQL newbie - how to create objects in a database

I've just finished installing PostgreSQL and pgadmin3 on Ubuntu. Using pgadmin, I've been able to create a new user called 'test' and also a new database with 'test' as the owner. Now I'm trying to ...
4
votes
3answers
1k views

Is a REINDEX required after CLUSTER?

I'm considering using CLUSTER to reorder a table by an index. I understand that this recreation of the table data makes all the existing indexes either bloat or be useless. I've seen some indications ...
13
votes
3answers
20k 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 ...
2
votes
1answer
291 views

PGPool Frequent Queries

We recently setup a PostgreSQL Cluster with PGPool in front of it. This is our first go at such a thing. We setup our QA environment to use the new PGPool, and we cranked up the logging on the ...
3
votes
2answers
73 views

disallow SET command in a postgresql server

Im planning to publish my postgresql server to a few untrusted clients. I dont want them to modify any runtime setting, like work_mem or something risky to my server. In general I assume the ...
1
vote
1answer
65 views

POSTGRES DB Server Blunder

This weekend I had to rebuild my laptop which had Postgres running. For some reason, I forgot to dump the data but I did make a complete copy of the original hard drive. Is it possible to recover ...
0
votes
3answers
768 views

Incremental backup in postgresql

I want to migrate a production database from one linux server to other. I am not concern about upgrading my database version, just I want to move the database from one server to other with minimum ...
5
votes
2answers
2k views

Transactions, references and how to enforce double entry bookkeeping? (PG)

Double entry bookkeeping is a set of rules for recording financial information in a financial accounting system in which every transaction or event changes at least two different nominal ...
3
votes
1answer
602 views

How to turn off all RAISE NOTICE on production server?

I put a lot of RAISE NOTICE in functions for debugging purpose. I think this will affect performance, so they should be removed on production environment. Is there any way to opt-out those ...
6
votes
2answers
437 views

Postgres performance difference on insert

I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions? I went quite straight forward way (i know that it is not ...
0
votes
1answer
264 views

Object-oriented programming CRUD design pattern

At first I have posted this question at http://cstheory.stackexchange.com/questions/17170/object-oriented-programming-crud-design-pattern - that's because I think it's a subect question, and it can ...
1
vote
1answer
882 views

How to grant only functions while revoking tables?

I am a newbie to PostgreSQL, and configuring a new database. I wrote several functions for indirect access(read/write) to data, and I want to prohibit all direct access to table. GRANT CONNECT ON ...
0
votes
1answer
400 views

Postgres 9.1 wait events

Is there anything in Postgresql 9.1 similar to wait events dynamic views of oracle? I need to find queries which are waiting for a long time, and the events they are blocked on.
1
vote
1answer
179 views

Write performance of Postgresql 9.1 with read-only slave

I have a Postgresql 9.1 database that is being hosted by Heroku. It currently has a read-only "follower". I need to truncate several large tables (over 100GB) of data and reloads them. Is the ...
1
vote
1answer
3k views

How I can copy from local file to remote DB in PostgreSQL? [closed]

I am a novice in psql and need some help. How can I load a local CSV to a remote DB? I am using the following command \COPY test(user_id, product_id, value) FROM '/Users/testuser/test.tsv' ...
0
votes
1answer
153 views

Comparing two tables for a UUID change and fix it

I have two tables which I'm trying to reconcile the differences of in postgresql. Table A is old and needs updating. Table B is an updated, schema identical version of Table A which I have the data ...
1
vote
1answer
604 views

Postgresql querying trends

Firstly apologies if this is a duplicate, I am fairly new to SQL and so Im not sure what the correct terminology to use in my searches So I have a database which records motor races, with the ...
0
votes
1answer
1k views

Set field values to newly imported rows in PostgreSQL table with existing data

I have a PostgreSQL table with existing data, and needs to import all the rows in a CSV file into that table. I am using pgadmin3's Import tool to do the import. Question: For the newly imported ...
4
votes
2answers
2k views

Inner join using an array column

Having trouble indexing and executing a query in O (log n) time. The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...