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 ...

learn more… | top users | synonyms (1)

2
votes
1answer
80 views

How to Substitute Variables in PostgreSQL

The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL? INSERT INTO control_threshold ( threshold_id, group_name, description, sql, ...
2
votes
2answers
186 views

How slow is slow in postgres?

I'm working with postgres for the first time. I have a lot of experience with small / medium size data analysis (i.e. things that fit in ram and can be analyzed in R, Stata, Matlab, etc.), but am now ...
2
votes
2answers
126 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 ...
2
votes
1answer
441 views

How to view the query of another session in pg_stat_activity without being super user?

I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything. select * from pg_stat_activity However, is it ...
2
votes
3answers
133 views

In what order does postgresql handle queries which have conflicting locks?

Let's say that I have a long running query SELECT and this query holds a AccessShareLock on a table. While this query is being processed a new query which needs a AccessExclusiveLock on the table ...
2
votes
4answers
223 views

psql, getting a list of databases to be parsed by a script

I want to use psql to list all of the databases on a Postgres server, to be parsed by a script. This command lists them: psql -l -A -t but the output shows an obvious issue: the records are ...
2
votes
2answers
228 views

how to create an incremental database copy in postgresql?

Virtual machine software like VirtualBox allow one to make incremental VM clones. That is data, once "touched" (opened writable), will be copied and stored in the incremental cache of the new clone. ...
2
votes
2answers
348 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
2
votes
2answers
434 views

Creating sequence for longer numeric values

I need to create a database model that includes a field to store 20 digit unique values. Which will be used by many clients over an API. The value must be 20 digit long, numeric and unique. Best ...
2
votes
2answers
116 views

Is there a way to avoid duplicating the definition of a foreign key?

I was wondering about this example CREATE TABLE cities ( city varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references ...
2
votes
2answers
359 views

Combining two similar SQL queries

I'm using the following SQL query twice with different values of limit. In my case, 40 and 100, though it doesn't really matter. My question is - can I combine these two queries into one query? It ...
2
votes
1answer
217 views

How do you figure out what planner cost constants to use in Postgres?

I'm using Postgres 8.4 right now. Performance has started to become an issue as our tables have grown in size and our queries in complexity, so I've started to look into some performance tuning, but I ...
2
votes
2answers
136 views

Which option do I have to modify to see more records without press `q`

I'm connected remotly to my database server with cygwin in full screen. When I query my database with select id, name from table limit 27; I get 27 records and I can do a new query seeing the ...
2
votes
1answer
2k views

How to run specific version (8.4, 9.1) of postgresql pg_* command (e.g., pg_dump)

I have Postgresql versions 8.4 and 9.1 installed. For any given Postgresql command, how do I specify a specific version of the command to run? (e.g., psql, pg_dump, pg_ctlcluster, pg_restore, ...) ...
2
votes
3answers
6k views

Starting postgresql service and specifying data directory

I have recently installed pg 8.4 on Ubuntu 10.0.4 using apt-get install. It installed pg as a service which starts ehenever the machine starts. However I have created a db cluster in a specific ...
2
votes
2answers
242 views

speeding up a deletion in postgresql

I came up with: drop table if exists idtemp; create temp table idtemp as select documentid from taskflag where taskid='coref' and state = 2 order by statechanged asc limit howmany; ...
2
votes
2answers
5k views

How to convert postgresql dump files to oracle dump files?

How can I convert postgresql dump files to oracle dump files? If anyone has experience in this, or knows of an open source tool I can use, please share it with me.
2
votes
2answers
879 views

How to specify trigger execution order under PostgreSQL?

I'm using "classic" time-based partitioning using triggers. I have found a need for a separate trigger, which runs on the original table. CREATE TABLE twitter_interactions(...); CREATE OR REPLACE ...
2
votes
2answers
1k views

Reference a non primary key in another table in postgresql

I have a two columns should references two columns in another table. And those two columns are not primary key. I think I can't use foreign key, because it can only references primary keys.
2
votes
2answers
1k views

What is the best way to automatically create a snapshot of the table at the certain time?

I have running and quite loaded PostgreSQL server. I need to take snapshots of the certain tables at the certain time (midnight). Those tables are pretty loaded (a lot of updates and inserts). What ...
2
votes
1answer
115 views

Why is CTE open to lost updates?

I don't understand what Craig Ringer meant when he commented: This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected ...
2
votes
2answers
162 views

How many database servers do I need?

This may be a no-brainer to some of you, but I am a newcomer to DBA and this question popped into my head and I couldn't answer it on my own and so I am turning to the experts here. I have a app that ...
2
votes
2answers
182 views

Problem translating MySQL to PostgreSQL

We're using Trac for issue tracking. I migrated it from MySQL to PostgreSQL, but some of the ticket queries no longer work. In the below, all I did this far is replace IFNULL() with COALESCE() and ...
2
votes
2answers
180 views

Postgres point() index not being used

\d aggregate Materialized view "public.aggregate" Column | Type | Modifiers ------------------+-----------------------------+----------- ...
2
votes
2answers
198 views

UPDATE table with another table data

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function. I think it's a little difficult.Any suggestions? The code is the first carid example. UPDATE car_check ...
2
votes
1answer
191 views

Asynchronous commit on Postgres over iSCSI with BBU storage

It is my understanding that it's alright to turn synchronous_commit off if your server has a local, battery backed up storage controller. In my environment I am on a VM whose block storage is ...
2
votes
2answers
157 views

Set random value from set

I need to put some random values into database, but I don't want to end up with completely randomized text (like 7hfg43d3). Instead I would like to randomly pick one of values supplied by myself.
2
votes
1answer
75 views

Postgresql behavior at end of sequence

As per subject when a sequence hits its max val what happens? What is the behavior? I have a big table with a sequence as a primary key and would like to know. Thanks.
2
votes
2answers
2k views

How to dump PostgreSQL database whose owner has no password?

First, some background on my setup: The server has root access disabled. So, I log-in as (say) john who also belongs to the sudo group and is therefore able to run superuser commands. I created a ...
2
votes
1answer
1k views

PostgreSQL: How to list all stored functions that access specific table

Introduction: PostgreSQL database with several hundred of stored functions, including obsolete, not used etc. Problem I need to find out all the stored functions that have any relationship to the ...
2
votes
1answer
76 views

Why does PostgreSQL require its own user/group for installation?

When installing PostgreSQL via MacPorts (and I assume via other means too), a postgres:postgres user/group is created. Are there any reasons why this user/group is needed?
2
votes
2answers
2k views

TOAST Table Growth Out of Control - FULLVAC Does Nothing

Recently, I've had a PostgreSQL 8.2.11 server upgraded to 8.4 in order to take advantage of autovacuum features and be in line with 30ish other PGSQL servers. This was done by a separate IT group who ...
2
votes
2answers
11k 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 ...
2
votes
1answer
3k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
2
votes
2answers
1k views

Changing PostgreSQL port using command line

My PostgreSQL default port is 5432, I want to change default port by using command line (in Windows 7). I saw this solution before: http://stackoverflow.com/questions/187438/want-to-change-pgsql-port ...
2
votes
4answers
2k views

Database design - People and Organisations

Databases are not my primary skill and I am finding I need some assistance please. The software we are building has "Customers". The Customer could either be a Person or an Organisation. I really ...
2
votes
2answers
559 views

Read Committed Isolation Level

Quote from docs: Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data ...
2
votes
1answer
271 views

How to access the number of rows accumulated by RETURNs in PL/pgSQL

When performing RETURN QUERY ... in a PL/pgSQL function, is it possible afterwards to directly access the number of rows accumulated into the pile of records, which are returned when the function ...
2
votes
2answers
912 views

postgres backup / restore: restored database much smaller?

I am worried that my restored database is very different from the original: #check size of postgres database postgres@db1:/tmp$ psql -c "select pg_size_pretty(pg_database_size('test_db'));" ...
2
votes
1answer
4k views

Declare variable of table type in PL/pgSQL

I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like: q1 = select * from foo; q2 = select * from bar; ...
2
votes
1answer
3k views

How does postgres use the schema search path when making indexes?

Using Postgres 9.1.4 with PostGIS 1.5, as part of a Rails 3.2.x app. I have a DB containing schemas A and B, with schema A empty and schema B containing tables foo and bar, with indexes on each table ...
2
votes
2answers
881 views

Postgresql after 'for each row' triggers don't actually execute after each row?

If I have a single sql statement that inserts or updates lots of rows and a db trigger that runs after each row is inserted or updated, it seems like the triggers run after all the rows are inserted ...
2
votes
2answers
1k views

Select rows with repeat data within certain timeframe

As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on ...
2
votes
2answers
2k views

Trouble recovering data from corrupted PostgreSQL tables

I'm trying to recover my database after a recent corruption of the same. Now I have two tables that are really giving me trouble. When I do select count(*) One table returns: ERROR: could not ...
2
votes
2answers
261 views

Can I replicate views with Slony?

Can I feed some SQL that creates a view to Slony? I have some SQL that does CREATE OR REPLACE VIEW name_of_view and so on, passes it to slonik_execute_script, and then tries to run slonik_create_set ...
2
votes
1answer
1k views

Postgres multiple joins slow query, how to store default child record

Ive got a small database in Postgres, about 10,000 records storing company customers. I have a "slow" performing query (about half a second) which is performed very frequently, and my boss wants me to ...
2
votes
2answers
3k views

Reindexing a large database

I have a very large database (millions of rows) which has been indexed. I receive a diff of rows from another source that I need to update in my database. The problem I am facing is that is it ...
2
votes
2answers
937 views

Monitoring Progress of Index Construction in PostgreSQL

Is there a way to monitor the progress of the creation of an index in PostgreSQL. I am creating an index on a large table and I would like to see how fast this is occurring. Is there a way to monitor ...
2
votes
2answers
2k views

Understanding time format of the EXPLAIN command - Postgres

When I run the EXPLAIN ANALYZE command on a given query, I'm having a difficult time interpreting the outputted time value. For example (actual time=8163.890..8163.893). Do the internal decimals ...
2
votes
2answers
504 views

Degrading PostgreSQL 8.4 write performance

Load testing of postgresql 8.4 for OLTP application suitability resulted in unpleasant situation when throughput of the database significantly degraded over time from thousands of write transactions ...