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)

10
votes
3answers
3k views

How should I represent a latitude and longitude in Postgres without using PostGIS?

How should I represent a latitude and longitude in Postgres without using PostGIS? The system I am using does not allow SQL passthrough so I cannot use POSTGIS.
2
votes
2answers
847 views

Effective backup and archive strategy for database and linked files

I am using Postgres to store a variety of application data for a webapp. Part of the application involves storing and retrieving user uploaded files. I am storing the files in the filesystem with ...
5
votes
3answers
2k views

Postgresql row-level encryption

Looking over the docs, I didn't see any mention of row-level encryption. Lets assume I have a database of highly classified information that needs to get locked down. How can I implement a table with ...
4
votes
3answers
2k views

Optimizing query using view on EAV structure

An application is writing into a database that follows an EAV structure, similar to this: CREATE TABLE item ( id INTEGER PRIMARY KEY, description TEXT ); CREATE TABLE item_attr ( item ...
7
votes
1answer
2k views

PostgreSQL: Disk space not released after TRUNCATE

I haveTRUNCATEd a huge (~120Gb) table called files: TRUNCATE files; VACUUM FULL files; The table size is 0, but no disk space was released. Any ideas how to reclaim my lost disk space? UPDATE: The ...
3
votes
1answer
11k views

How can I convert from Double Precision to Bigint with PostgreSQL?

I need to convert a value of Double Precision to Bigint with PostgreSQL. How can I do that? I have tried with to_bigint(myvalue) but that function didn't exist.
21
votes
3answers
3k views

Is it possible to quickly create/restore database snapshots with PostgreSQL?

First of all, I'm a developer, not a DBA or sysadmin; please be gentle :) I'm working on an application workflow where a single user action will trigger complex changes in the database - creating ...
2
votes
1answer
661 views

Pgpool-II: sequences advanced by 1 after PITR recovery

I have tested pgpool-II 3.0.1 and pgpool 3.0.4, PostgreSQL 8.4 In both cases I'm getting the same result: after online recovery on primary and target nodes all 'serial' values got "+1". Other nodes ...
3
votes
1answer
2k views

How to list all postgres tables in one particular schema

Using the psql command line tool, how do I list all postgres tables in one particular schema
2
votes
2answers
509 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 ...
4
votes
1answer
1k views

PostgreSQL on Ubuntu - Using conf files for recovery

I am using PostgreSQL 8.4 on Ubuntu LTS 10.04.02 server version. I use pg_standby to restore the data and for database recovery created a recovery.conf and placed it on /etc/postgresql/8.4/main where ...
2
votes
2answers
4k views

Postgresql - Recovery using recovery.conf

I am trying to recover a database using recovery.conf file for making point-in-time copy. On recovery.conf file, I added a recovery target time as below. recovery_target_time = '2011-06-16 04:10:00 ...
3
votes
2answers
1k views

Removing Archive Logs after PostgreSQL PITR online-recovery

I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2. Replication mode is "on" Load balance mode is "on" (between primary and standby1) I followed the official ...
4
votes
1answer
1k views

Source several files at once with psql

I want to execute several sql scripts sequentially with psql as a single transaction to set up my database schema. What is the best way to do this? In the past I know I had a master script that I ran ...
2
votes
1answer
2k views

How do I install pgcrypto on Windows?

I would like to install pgcrypto in Windows. I have a file C:\Program\PostgreSQL\9.0\share\contrib\pgcrypto.sql but when I try to run it in psql I get an error: C:: Permission denied How do I solve ...
19
votes
3answers
30k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
5
votes
2answers
6k views

How can I specify the position for a new column in PostgreSQL?

If I have a table with the columns: id | name | created_date and would like to add a column, I use: alter table my_table add column email varchar(255) Then the column is added after the ...
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 ...
5
votes
2answers
577 views

Save postgres database from broken ubuntu 10.04 server

Recently a database server with an important db broke (some grub linux issue that I'd prefer not to solve). I can still access the filesystem. Is there a chance to transfer the database by only ...
3
votes
2answers
2k views

PostgreSQL: count datasets / lines containing NULL values

I'm looking for a easy function to get the number of datasets (of any table) containing NULL values. It's not important how many columns are involved. For example: CREATE TABLE tab1 ( id INTEGER ...
41
votes
2answers
17k views

How can I time SQL-queries using psql?

I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?
3
votes
2answers
237 views

PostgreSQL: Displaying inheritance relationship?

I want to know if a certain PostgreSQL table is a part of any inheritance relationship (i.e. is a parent or a child of other table(s)). Is there a query for that? Where is the inheritance data ...
7
votes
4answers
3k views

PostgreSQL: Creation date of a table

I've recently finished a project, during which many DB tables were created. Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables. Is there a ...
5
votes
1answer
4k views

How to insert an IP-address into an inet column in PostgreSQL?

I would like to insert an IP-address into a column that has type inet. In what format can I insert the data? is it only binary or is there any way I can insert from text e.g. "192.168.1.082"? Are ...
0
votes
2answers
917 views

How fast should I expect to load data on a high performance computer?

I am considering a model where I use PostgreSQL COPY to copy data from a file into a table. I was wondering what kind of performance to expect on high-end hardware. An interval in MB/s would be nice ...
2
votes
1answer
702 views

Auto Increment Index Field For Create and Update

I'm trying to create an unique auto increment index field that is re-generated on both inserts and updates. I need it so that a client can send a server an integer and get back all outdated (created ...
8
votes
3answers
21k views

How to run psql on Mac OS X?

I installed PostgreSQL on a computer with Mac OS X using the One click installer. Then I try to access PostgreSQL using the psql command, but it doesn't seem to be available. I get this message: ...
35
votes
1answer
78k views

How to insert values into a table from a select query in PostgreSQL?

I have a table items (item_id serial, name varchar(10), item_group int) and a table items_ver (id serial, item_id int, name varchar(10), item_group int). Now I want to insert a row into items_ver ...
1
vote
2answers
802 views

Table-name.column-name in PostgreSQL?

I would like to use the following syntaxis in my PL/pgSQL code: INSERT INTO table (table.column1, table.column2) VALUES (1, 2); But somehow it doesn't work. Does anybody know if such a notation it ...
1
vote
1answer
983 views

How can I alter the type of a money-column to decimal in PostgreSQL?

I have a table in PostgreSQL where one column "vat" has type money but I would like to alter it to decimal instead. How can I do it? I tried with: alter table my_table alter column vat type ...
9
votes
5answers
15k views

How can I use a default value in a Select query in PostgreSQL?

I would like to use a default value for a column that should be used if no rows is returned. Is that possible in PostgreSQL? How can I do it? Or is there any other way I can solve this? E.g. ...
27
votes
2answers
32k views

How do I get the current unix timestamp from PostgreSQL?

Unix timestamp is the number of seconds since midnight UTC January 1, 1970. How do I get the correct unix timestamp from PostgreSQL? When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
4
votes
3answers
965 views

How much could sequential scan gain by GPU?

I am considering hiring someone to implement GPU processing in Postgres to speed up sequential scans on a database located on a ramdisk. How much do you believe I could gain using this approach? I ...
4
votes
1answer
3k views

Passing an array or record to a stored procedure in PostgreSQL

I have a task to pass arrays, records and in some cases array of records as a parameter to the stored procedures in PostgreSQL. Any ideas?
10
votes
7answers
5k views

Are there any good tools for monitoring postgresql databases

I am planing to have very soon few highly loaded postgresql databases. I have some expirience managing mysql databases with high load, but now we have to use postgresql. I want to know what are the ...
3
votes
2answers
388 views

Plpgsql seems to be deleting and inserting instead of updating - Why?

I am using postgreSQL 7.4. I have a large table , call it table_a: key1 INT NOT NULL, key2 INT NOT NULL, data INT NOT NULL, itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, ...
8
votes
2answers
22k views

How to get the timestamp column in only milliseconds from PostgreSQL?

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database. If I select colums, it has a nice and readable format per default: SELECT created FROM ...
7
votes
4answers
3k views

Finding empty columns of a table in PostgreSQL

What query would return the name of the columns of a table where all rows are NULL?
4
votes
1answer
1k views

Indexing to improve performance of range queries

I've got a relation in a Postgres 8.2 database similar to this: CREATE TABLE foo ( foo_id varchar(160) NOT NULL, bar_id varchar(160) NOT NULL, created bigint NOT NULL, PRIMARY KEY ...
2
votes
1answer
1k views

pgAdmin Query Tool throws error when I try to run multiple statements

I'm very new to PostgreSql and still learning the ropes. I have a really long script for setting up a new copy of my database. It creates functions, runs them, does some inserts and a bunch of other ...
10
votes
2answers
3k views

Trigger: move deleted rows to archive table

I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis. I would like to have a table called restrictions_deleted, where ...
10
votes
4answers
4k 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 ...
2
votes
1answer
928 views

master to multi-slave replication

I am looking at replication of the master database (PostgreSQL) and this is the configuration I have to setup master -- hot-standby (hs) | | --- read only slave (r1) | | --- read only slave (r2) ...
0
votes
2answers
1k views

Query PostgreSQL 9.0 table on foreign key value?

Noob-type question: Say I have two tables, candies and colors. The colors table holds just code/value pairs like 01 yellow, 02 blue, 03 green, etc. and is referenced in the candies table by code. ...
2
votes
2answers
608 views

How to hide metadata from Postgres users?

I need to provide views-only access to some subset of a database, for use by report-writing tools. It's easy enough to use permissions to prevent access to the data in the underlying tables, but I ...
3
votes
2answers
1k views

Postgres 8.4 — connection refused (remote)

I'm new to postgres and looking to set up a small environment here with it. I've installed PostgreSQL 8.4 onto an ubuntu server, able to login to the postgres, submit commands, do queries, etc... ...
10
votes
5answers
6k views

What are the differences between “Stored Procedures” and “Stored Functions”?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't ...
8
votes
3answers
1k views

Where does the magic column “name” come from?

I got this by accident: db=> select name from site; ERROR: column "name" does not exist LINE 1: select name from site; ^ db=> select site.name from site; name ...
9
votes
3answers
2k views

How is LIKE implemented?

Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it? The naive approach would be to inspect ...
6
votes
3answers
1k views

Postgres, MVCC, and Locking

I have a series of SQL statements that look like the following: BEGIN; SELECT counter FROM table WHERE id=X FOR UPDATE; REALLY COMPLEX QUERY; UPDATE table SET counter=Y WHERE id=X; END; I'd like to ...