PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...
0
votes
1answer
25 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 ...
0
votes
2answers
63 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 ...
0
votes
1answer
66 views
PostgreSQL stored procedure to return rows or empty set
I have 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
71 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
39 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
1answer
46 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
1answer
34 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 ...
-1
votes
2answers
60 views
strategies to get data from diff database [closed]
Problem
I have two databases user_works database and pricing database.
I want to take a join of two tables user_table which is in user_works database & rent_table which is in pricing database ...
2
votes
1answer
59 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 ...
0
votes
1answer
40 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
3answers
56 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 ...
1
vote
1answer
124 views
Upgrade production database during software development lifecycle
Background
Using Oracle, there are a number of tools that help with migrating and applying development changes into the production environment (such as Embarcadero Change Manager). These tools can be ...
1
vote
2answers
44 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
25 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 ...
1
vote
4answers
72 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:
...
1
vote
1answer
48 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: ...
1
vote
2answers
306 views
Postgresql 9 table lock resolution
I have an ALTER TABLE statement that according to pg_stat is "waiting", and I'm wondering if there is an easy fix to allow the statement to execute?
From my limited understanding I think it's a ...
3
votes
3answers
94 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 ...
0
votes
1answer
78 views
Permission Denied Copy [closed]
I am a beginner learning how to load data and I can't figure out why I can't load this data into postgres. I am using postgres 8.4 on MAC OS.
copy tname from '/Users/Shared/folder/file.cxv' ...
3
votes
1answer
62 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 ...
2
votes
2answers
90 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 ...
3
votes
2answers
48 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 ...
3
votes
0answers
63 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 - ...
0
votes
2answers
87 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 ...
1
vote
1answer
2k views
What GUI tools can be used for PostgreSQL in Mac OS X?
I have recently started using PostgreSQL with my rails app.
I was looking for a SQL GUI to the PostgreSQL database (for Mac).
Anything better than pgadmin3?
4
votes
1answer
96 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 ...
0
votes
1answer
39 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
80 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 ...
10
votes
3answers
2k views
PostgreSQL Stored Procedure Performance
Coming from a MySQL background, where stored procedure performance (older article) and usability are questionable, I am evaluating PostgreSQL for a new product for my company.
One of the things I ...
0
votes
0answers
32 views
Define default operator class for gist on enum data type
I have my own enum data type (called direction), which can be IN or OUT and I want to use EXCLUDE USING gist constraint to prevent inserting row with the same direction (and two more columns) like ...
3
votes
5answers
1k views
Alter table on live production databases
How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?
I know the correct way is to ...
1
vote
0answers
38 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 ...
1
vote
1answer
40 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
80 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
How do I create an index to speed up an aggregate LIKE query on an expression in postgres 8.4?
I may be asking the wrong question in the title. Here are the facts:
My customer service folk have been complaining about slow response times when doing customer lookups on the administration ...
2
votes
1answer
37 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 ...
0
votes
2answers
76 views
Trying to create PostGIS database
I am trying to create a spatially enabled PostGIS database. I am following the PostGIS documentation, http://postgis.refractions.net/documentation/manual-1.5/ch02.html#id2648455.
In the short ...
0
votes
1answer
107 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
46 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 ...
4
votes
1answer
876 views
PostgreSQL: Unable to run repmgr cloned database
I'm running tests with PostgreSQL hot standby with 1 master, and exactly 1 slave.
I am using the instructions on this guide: ...
2
votes
1answer
278 views
pg_dump and ERROR: missing chunk number 0 for toast value
I'm using PostgreSQL 8.4.15. While running pg_dump to backup a database, I got the following error:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: missing chunk number 0 for ...
0
votes
1answer
36 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
73 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 ...
1
vote
0answers
35 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 ...
0
votes
0answers
51 views
How I can copy from local file to remote DB in PostgreSQL?
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
24 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 ...
9
votes
4answers
2k views
Tools to migrate from SQLite to PostgreSQL
I need to continuously migrate from SQLite to PostgreSQL. By continuously, I mean that I will reimport the SQLite database into PostgreSQL every day. It would be nice if I could make changes to the ...
0
votes
1answer
40 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 ...
2
votes
0answers
57 views
How to execute a non-table-locking update operation on PostgreSQL? [closed]
Looking for a good way to update the value on a column for all the rows in a database (not huge, but big enough - about 10M records), without locking the whole table, so operations can continue while ...
5
votes
4answers
1k views
Why are so many MPP solutions based on PostgreSQL instead of MySQL?
Astor Data, Greenplum and GridSQL all allow Massive Parallel Processing of SQL queries. They are also all built around PostgreSQL technology. Is this just because of licensing issues or are there ...
