Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
17,165
questions
2
votes
1
answer
2k
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 ...
- 121
117
votes
11
answers
371k
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:
...
- 33.5k
404
votes
4
answers
951k
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 ...
- 33.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 ...
- 519
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;...
- 33.5k
55
votes
5
answers
146k
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. ...
- 33.5k
135
votes
2
answers
285k
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 ...
- 33.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,195
18
votes
1
answer
64k
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.
- 519
4
votes
2
answers
764
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())::...
- 165
59
votes
3
answers
198k
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;...
- 33.5k
18
votes
5
answers
25k
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
7
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 (...
- 173
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
24
votes
2
answers
18k
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 ...
- 11.8k
11
votes
5
answers
12k
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,195
3
votes
1
answer
3k
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)
...
- 31
3
votes
2
answers
11k
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.
...
- 133
4
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 ...
- 41
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
68
votes
7
answers
82k
views
What are the differences between "Stored Procedures" and "Stored Functions"?
So a comment to this question mentions, that there is a slight difference between "Stored Procedures" and "Stored Functions" in PostgreSQL.
The comment links to a Wikipedia article ...
- 7,357
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
--------------...
- 895
25
votes
3
answers
14k
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
10
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 ...
- 201
17
votes
1
answer
13k
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?
- 11.8k
4
votes
2
answers
144
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,195
43
votes
6
answers
59k
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?
- 11.8k
5
votes
2
answers
3k
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('...
- 11.8k
202
votes
14
answers
284k
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 * -...
- 11.8k
18
votes
2
answers
19k
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.8k
20
votes
7
answers
12k
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 ...
- 483
24
votes
3
answers
59k
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....
- 33.5k
9
votes
2
answers
12k
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 ...
- 441
58
votes
6
answers
218k
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 ...
- 939
49
votes
3
answers
47k
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
--------+-----------...
- 826
9
votes
3
answers
7k
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 ...
- 567
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 ...
- 987
2
votes
2
answers
15k
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 ...
- 149
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 ...
- 1,197
4
votes
2
answers
1k
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 ...
- 5,346
8
votes
2
answers
6k
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 ...
- 11.8k
204
votes
17
answers
346k
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,181
12
votes
2
answers
6k
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 ...
- 11.8k
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
1572
votes
9
answers
3.3m
views
How do I list all databases and tables using psql?
When I log in with psql --username=postgres, how do I list all databases and tables?
I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, ...
- 33.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 ...
- 33.5k
25
votes
3
answers
127k
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"-...
- 33.5k
12
votes
3
answers
544
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 ...
- 223
9
votes
4
answers
7k
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,195
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 ...
- 313