How to fix a slightly corrupted table on PostgreSQL 9.0.4

Posted on 2012-08-10
Last Modified: 2012-08-10
SQL select is returning strange results on a table that I have:

select DISTINCT symbol from historical_prices where symbol='ACAS'


So the table seems to be slightly corrupt.  One fix that I confirmed that works in a test environment is to export all contents to a flat file, truncate the table and import again.

The complication is that the table has over a million rows and the database is replicated using streaming replication.  I'm afraid that dump/delete/reload of such a large table might kill the replication.

Are there any suggestions?  Perhaps some form of the VACUUM command?

Here are the details on this table as prepared by PGAdminIII:

Property Value
Name historical_prices
OID 24783
Owner produser
Tablespace pg_default
Of type  
Primary key symbol, date
Rows (estimated) 1096900
Fill factor  
Rows (counted) not counted
Inherits tables No
Inherited tables count 0
Has OIDs? No
System table? No

-- Table: historical_prices--
DROP TABLE historical_prices;
CREATE TABLE historical_prices(
symbol text NOT NULL,  
date date NOT NULL,  
open numeric(6,2),  
high numeric(6,2),  
low numeric(6,2),  
"close" numeric(6,2),  
adjusted_close numeric(6,2),  
datecreated timestamp without time zone DEFAULT now(),  
lastupdated timestamp without time zone DEFAULT now(),  
CONSTRAINT "PrimaryKey" PRIMARY KEY (symbol, date))

ALTER TABLE historical_prices OWNER TO proddb;
-- Trigger: audit_trig on historical_prices--
DROP TRIGGER audit_trig ON historical_prices;
CREATE TRIGGER audit_trig  BEFORE INSERT OR UPDATE  ON historical_prices  
Question by:sevzas
    1 Comment

    Accepted Solution

    VACUUM FULL VERBOSE historical_prices

    did the trick, however "VACUUM VERBOSE historical_prices" (without "FULL") did not work.  The standby of the streaming replication still has the problem, so I'm just going to sync it up manuall.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    sql statement mysql calculate  score 12 375
    Postgres random sample 2 85
    while installing  postgresql there is an error... 21 238
    Hours Worked 10 76
    Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: ( This requires some add-o…
    Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now