Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

Filter by
Sorted by
Tagged with
332 votes
4 answers
721k 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 ...
  • 30.5k
2 votes
2 answers
2k 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 ...
  • 509
2 votes
1 answer
3k 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 decimal;...
  • 30.5k
49 votes
5 answers
128k 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. ...
  • 30.5k
129 votes
2 answers
248k 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 ...
  • 30.5k
4 votes
3 answers
1k 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 ...
  • 1,145
17 votes
1 answer
50k views

Pass an array or record to a function in PostgreSQL?

I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.
  • 509
4 votes
2 answers
677 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, (timeofday())::...
50 votes
3 answers
162k 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 mytable;...
  • 30.5k
18 votes
5 answers
22k 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?
  • 181
6 votes
1 answer
2k 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 (...
  • 163
4 votes
1 answer
3k 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 ...
  • 193
22 votes
2 answers
16k 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.7k
11 votes
5 answers
11k 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 ...
  • 1,145
2 votes
1 answer
2k 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) ...
3 votes
2 answers
10k 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. ...
3 votes
2 answers
2k 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
2 answers
2k 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... ...
  • 173
52 votes
7 answers
60k 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 ...
  • 6,459
11 votes
3 answers
2k 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 --------------...
  • 855
25 votes
3 answers
12k 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 ...
  • 353
9 votes
3 answers
3k 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 ...
  • 191
17 votes
1 answer
11k 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?
  • 10.7k
4 votes
2 answers
133 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. ...
  • 1,145
43 votes
6 answers
50k 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?
  • 10.7k
5 votes
2 answers
2k 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 pg_size_pretty(pg_total_relation_size('...
  • 10.7k
164 votes
12 answers
193k 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 * -...
  • 10.7k
17 votes
2 answers
18k 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 ...
  • 10.7k
20 votes
7 answers
11k 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 ...
  • 423
23 votes
3 answers
57k views

How do I install pgcrypto in PostgreSQL 8.4?

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 first....
  • 30.5k
9 votes
2 answers
10k 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 ...
  • 431
51 votes
6 answers
184k 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 ...
39 votes
3 answers
37k 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 --------+-----------...
  • 726
8 votes
3 answers
6k views

Is WAL Database log on separate disk good idea on 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 a lot of bulk inserts and updates, so that ...
4 votes
1 answer
3k 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 ...
  • 957
1 vote
2 answers
13k 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 get ...
  • 139
9 votes
4 answers
4k 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 ...
4 votes
2 answers
970 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 ...
  • 4,946
8 votes
2 answers
5k 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 ...
  • 10.7k
185 votes
17 answers
269k views

How do I find PostgreSQL's data directory?

I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory. ...
  • 2,001
12 votes
2 answers
5k 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 ...
  • 10.7k
1 vote
1 answer
238 views

How to design databases? [closed]

Describe the process in steps, please. What tools do you use for it?
  • 113
1459 votes
7 answers
3.0m views

How do I list all databases and tables using psql?

I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool. When I log in with psql --username=postgres, how do I list all databases and tables? I ...
  • 30.5k
21 votes
6 answers
20k views

Are there any good and free tools for managing a PostgreSQL database? [closed]

Before, I have used phpmyadmin to manage an MySQL database, but now I would like to manage a PostgreSQL database. The PostgreSQL database is on a server, but not a webserver, so I don't use PHP. Are ...
  • 30.5k
20 votes
3 answers
107k views

How do I specify that a column should be auto-incremented in pgAdmin?

I have started to learn pgAdmin III to manage a PostgreSQL database. But it wasn't an easy to use application. If I create or have created a table with pgAdmin III, how can I add "auto-increment"-...
  • 30.5k
12 votes
3 answers
515 views

How-to implement an entity with an unknown maximum number of attributes?

I am designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The ...
8 votes
4 answers
6k 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 ...
  • 1,145
2 votes
1 answer
1k views

How can I configure PostgreSQL 9.0 to start automatically under Windows XP, Part II?

Two weeks ago I asked "How do I configure Windows XP to start the postgresql demon after each boot/reboot?", and BenV helped me out with his answer: Make sure the service is set to start ...
  • 303
11 votes
1 answer
2k views

BLOBs or references in PostgreSQL

I need to store binary data files in a PostgreSQL database which runs on an Ubuntu server. Initially there will be a few dozen files of approximately 250kb in size each. However, the number of files ...
41 votes
3 answers
47k views

Is it a good idea/approach to index a VARCHAR column?

We're using PostgreSQL v8.2.3. There are tables involved: EMPLOYEE and EMAILLIST. Table 1: EMPLOYEE (column1, column2, email1, email2, column5, column6) Table 2: EMAILLIST (email) 2 tables are ...
  • 957