Tagged Questions

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)

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
236 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
912 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
700 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
800 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
982 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
31k 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
961 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
386 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
922 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
606 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 ...
6
votes
1answer
3k views

PostgreSQL: COUNT(*) uses a sequential scan, not index

Why does PostgreSQL sequentially scans the table for COUNT(*) query, while there is a very small and indexed primary key?
3
votes
2answers
112 views

How hard would it be to have PostgreSQL run a test to adjust the cost parameters?

By using EXPLAIN one can see the cost of running a query. The cost is in artificial units, and they are based on pre-defined parameters. 1 is one disk fetch and a CPU cycle is 0.01 units, I believe. ...
11
votes
5answers
7k views

PostgreSQL: Force data into memory

Is there a systematic way to force PostgreSQL to load a specific table into memory, or at least read it from disk so that it will be cached by the system?
4
votes
1answer
425 views

PostgreSQL relation size does not sum up

I have a very large table, with blob fields, called data. I've tried to figure out why it isn't cached well and repeated SELECTs are quite slow: => SELECT ...
28
votes
7answers
14k views

SQL: SELECT All columns except some

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table. Something like: SELECT * ...
8
votes
2answers
5k views

Is REINDEX dangerous?

I've been trying to COUNT(*) a table with 150,000 rows that has a Primary key. It tool about 5 minutes, so I figured out this is an indexing problem. Citing the PostgreSQL manual: REINDEX is ...
11
votes
5answers
3k views

Efficiently select beginning and end of multiple contiguous ranges in Postgresql query

I've got about a billion rows of data in a table with a name and an integer in the range 1-288. For a given name, every int is unique, and not every possible integer in the range is present--so there ...
16
votes
2answers
17k views

How do I install pgcrypto for PostgreSQL in Ubuntu Server?

I'm using Ubuntu Server 10.10 and I have installed PostgreSQL 8.4 using apt-get install postgresql. I would like to use the built-in sha1() function, but it seems that I have to install pgcrypto ...
6
votes
2answers
3k views

Non-unique multicolumn foreign key

I have a "comments" table that models a conversation on a topic, like this: id serial topic_id integer parent_comment_id integer body text So, every comment has a reference to its topic AND ...
11
votes
4answers
29k views

How to insert (file) data into a PostgreSQL bytea column

This question is not about bytea v. oid v. blobs v. large objects, etc. I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This ...
5
votes
2answers
2k views

How to wrap long lines when SELECTing SQL text columns?

I'm selecting from a table with long text columns. I'd like to wrap long lines to a maximum line length. From: SELECT * FROM test; test_id | text ...
4
votes
3answers
1k views

WAL Database log on separate disk good idea RAID10?

I have a RAID10-Server and have Postgres writing the log(pg_xlog a.k.a WAL) onto the same RAID10-Array. I use the WAL in sync-mode with a big buffer and do alot of bulk inserts and updates, so that ...
2
votes
1answer
712 views

How to upgrade PostgreSQL minor releases without a dump/restore?

Our production server is running PostgreSQL v8.2.3 on CentOS5.2. We're planning to upgrade to the latest version in 8.2 series, that is v8.2.20. Also, in future, we've planned to upgrade to the most ...
0
votes
2answers
786 views

PostgreSQL CREATE TABLE error

I'm new in PostgreSQL. I try to create table in database. In psql i write: CREATE TABLE mail_user ( user char(50) NOT NULL, domain char(50) NOT NULL, password char(50) NOT NULL ); But i ...
6
votes
3answers
2k views

How to version control PostgreSQL schema with comments?

I version control most of my work with Git: code, documentation, system configuration. I am able to do that because all my valuable work is stored as text files. I have also been writing and dealing ...
1
vote
2answers
370 views

Get a list of databases that have a certain table

I'm trying to get a list of databases from a Postgresql instance that contains a certain table. I've started using the system catalog tables to try and figure this out. I was trying to use ...
3
votes
2answers
1k views

PostgreSQL: SET-ing from psql arguments

Following this answer regarding limiting resources per query in PostgreSQL, I've been advised to set a timeout value for query execution: SET statement_timeout TO '1min'; This is great, but ...
31
votes
7answers
32k views

How do I find PostgreSQL's data directory?

I forgot how I last time started postgres (it was months ago) and I don't remember where the data directory is located at. The postgres command seems to require to location. I'm on MacOsX if that ...
5
votes
2answers
1k views

Postgresql 8.3: Limit resource consumption per query

I'm using PostgreSQL 8.3+PostGIS 1.3 to store geospatial data on Ubuntu 8.04 Hardy. This particular version of PostGIS has a bug when calculating a buffer() on very complex segments, which causes ...
0
votes
1answer
205 views

How to design databases? [closed]

Describe the process in steps, please. What tools do you use for it?