All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

1
vote
2answers
2k views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
0
votes
2answers
433 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
1
vote
1answer
236 views

Improve performance on concurrent UPDATEs for a timestamp column in Postgres

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here. The queries all have the same format UPDATE "managers" SET "updated_at" = ...
0
votes
2answers
1k views

Filling in missing dates in record set from generate_series()

Consider: with days as (select day::date from generate_series(date '2013-01-01', date '2013-01-01' + 365, interval '1 day' day) day ) select 'Inspections'::text as data_label, count(i.reporting_id) ...
2
votes
1answer
142 views

Why is my composite index not used for this table?

I am working with a fairly complicated schema, but I've simplified it for the purposes of this question. Table "public.scheduler_appointment" Column | Type ...
0
votes
1answer
1k views

PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who ...
0
votes
2answers
321 views

How to change schema so that account_id reference is unique among 3 tables

Original image: Updated to have more-correct terminology and an 'is_debit' column: I am designing a (PostgreSQL) schema for a lottery website that uses double-entry accounting. 'jackpots', ...
1
vote
2answers
100 views

How to construct a set-based query for subset problem?

I have a DB of the following structure (picture shows simplified version) How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe ...
2
votes
0answers
128 views

postgres - generating documents for full text search taking forever and eating up disk space

We use PostgreSQL and Django-Haystack with Elasticsearch for search on our service, and were thinking of switching to Postgres's built in full text search because of various annoyances using a NoSQL ...
5
votes
1answer
848 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
0
votes
1answer
100 views

Custom personal monitor sql by writing the .psqlrc file

I want to custom my personaly monitor sql by writing the .psqlrc file , but I encounter some problems . --tablespace skypcsuit=> \db List of tablespaces Name ...
2
votes
1answer
377 views

Prevent postgres function of running more than 1 instance at the same time

DBMS: Postgres 9.3.4 OS: Debian 7 I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 ...
1
vote
0answers
49 views

Selecting aggregated string from database

I have a table in my database consisting of one column which is NewLanguage(varchar). I insert new languages to the table and I want to select the rows of the table and assign them to a string ...
9
votes
1answer
1k views

Fast hamming distance queries in postgres

I have a large database (16M rows) containing perceptual hashes of images. I'd like to be able to search for rows by hamming distance in a reasonable timeframe. Currently, as far as I properly ...
1
vote
1answer
173 views

Will insert and update wait while a table is being vacuumed?

I want to vacuum a table in PostgreSQL version 9.1 which will take between 10 to 12 minutes. This needs to be done in real-time, in production use. I just want to know that if any INSERT or UPDATE ...
4
votes
2answers
408 views

How to avoid the SORT operation in SQL query that contains ORDER_BY condition?

I am optimising a PostgreSQL query which involves three tables and a ORDER_BY condition. It seems that PostgreSQL prefer to arrange a SORT operation to perform the ORDER_BY condition, rather than ...
2
votes
2answers
309 views

Conditionally sum a column based on a mod result [closed]

I have the following query: SELECT y.datamov, y.n_conta, y.sinal, y.valor, y.qtde, a.nome, '', 0, a.lrdarc FROM ( SELECT k.datamov, ...
3
votes
2answers
449 views

How to inner join some tables and outer join others?

I'm certain I'm going to be embarrassed by the eventual answer, but I've been googling around and banging my head against the wall and can't figure it out. I have four tables described below. I'm ...
4
votes
1answer
5k views

duplicate key violates unique constraint when using multi-threading

I am using ExecutorService with a fixed thread pool of 50, and a fixed database connection pool of 50, using HikariCP. Each worker thread processes a packet (a "report"), checks if it is valid (where ...
3
votes
1answer
259 views

Very large btree index with few rows (openstreetmap gis data)

I tried to import a subset of open street map data to a Postgres database using the standard tool (osm2pgsql). I then tried to rebuild the indices on the table using plain sql (dropping them, then ...
1
vote
1answer
492 views

Explain PostgreSql ssl_cipher values: ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH

In the postgresql.conf file, one can configure authorized values for ssl_cipher. Yet I could not find documentation explaining what the ALL, ADH, LOW, EXP, MD5 and STRENGTH values correspond to. MD5 ...
0
votes
1answer
33 views

PostgreSQL: How to list the comments attached to a database

How do I display the comments associated with a database, not a table? I've tried \dd and \list , but can't seem to find this. Thanks,
0
votes
0answers
138 views

What's causing Postgres to freeze?

I'm using Amazon RDS to run my Postgres database, v9.3.3. Recently, I had 2 instances where updating the schema caused the database to stop responding and I had to restart it in order to put the site ...
1
vote
1answer
207 views

PostgreSQL, PostGIS: Copying a schema with a geometry column

According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn(): AddGeometryColumn — Adds a geometry column to an existing table of attributes. I ...
1
vote
2answers
152 views

PostgreSQL/ Linux, way to have DNS forward to a port/cluster?

Aws linux(centos) ec2 Host: mydb. 10.10.10.10. 3 clusters: ports 5432, 5433, 5434 What I'd like to see is: db2.mydb or mydb/db2 connect/fwd to cluster2 (5433). Anything Linux or psql come to mind? ...
1
vote
2answers
680 views

Primary key with randomly varying increments (so it cannot be guessed easily)

I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100... then the auto generated keys would be something like below: - ...
0
votes
1answer
2k views

Postgres 9.3 changes the standard_conforming_strings value

I'm migrating a legacy application's database from Postgres 8.1 to Postgres 9.3 In order to avoid compatibility issues, I'd like to keep the standard_conforming_strings parameter to it's default ...
2
votes
1answer
6k views

Postgresql: set default psql client encoding

When I connect to a Postgresql DB using psql, I often get these messages: => SELECT * FROM question_view ; ERROR: character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in ...
0
votes
1answer
590 views

Mixed search in multiple columns

I have a table which has a particular column of type JSON, where some user's contact information are stored. I can alter the table and add new columns with cached values to increase search performance ...
1
vote
0answers
60 views

Strategy for multiple distinct databases in PostgreSQL

I have a 30 gb db in PostgreSQL 9.2. Running On Amazon Linux on an ec2 (web virtual machine), with the data directory mounted from its own drive (EBS). I'll call the database db30. On production ...
1
vote
1answer
647 views

Split two rows into two columns

I have the following table: id | name | action | count ------------------------------ 1 | Value1 | 0 | 27 1 | Value1 | 1 | 49 2 | Value2 | 0 | 7 2 | Value2 | 1 | ...
3
votes
1answer
1k views

Index for finding combinations of attributes in JSON array

Based on this related question on SO. This works fine if you only want simple matches. Suppose tracks have both Artist and Title field in the JSON data. So we have something like: INSERT INTO tracks ...
2
votes
1answer
1k views

Explicitly granting permissions to update the sequence for a serial column necessary?

Recently I did create a table as a superuser including a serial id column, e.g., create table my_table ( id serial primary key, data integer ); As I wanted my non-superuser user to have ...
2
votes
1answer
7k views

Streaming replication Postgresql 9.3 using two different servers

Settings in master server: max_wal_senders = 1 wal_level = 'archive' archive_mode = on archive_command = 'cd .' wal_keep_segments = 10000 Settings in slave server: in recovery.conf file: ...
1
vote
0answers
329 views

On Heroku in production, how can I copy database A to database B on command?

From my local terminal, I can use the following commands to do what I need to do: heroku pgbackups:capture --expire heroku pgbackups:restore charcoal --confirm max-kiosk However, I want to do this ...
1
vote
1answer
465 views

Postgresql pgAdmin, count(*), max(pk_field) giving inconsistent results

I am running postgresql 9.3.4 on a Centos 6.5 server as a back end for a web app which I am currently load testing. Presently, if I visit the state table in pgAdmin, right click on "count", I get a ...
1
vote
2answers
1k views

How to make a table name an attribute in PostgreSQL?

I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data. I would like to Union ...
2
votes
1answer
4k views

How to pass a parameter into a function

At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date? Also is it good ...
3
votes
1answer
851 views

Promotion of PostgreSQL standby and application reconnection

Background: I work for a small company. We have been using PostgreSQL on Heroku as one of our core technologies. While, in general, we've been happy with Heroku, the company has made the decision ...
3
votes
2answers
11k views

Connect to PgAdmin III via localhost

I just want to login for first time, from pgAdmin III via localhost on openSuSE 13.1 64x. My pg_hba.conf: # TYPE DATABASE USER ADDRESS METHOD # "local" is for ...
1
vote
0answers
252 views

How to configure pgmodeler?

I am having some troubles configuring pgmodeler, particularly the Connections. I put Connection DB: postgres Host/Port localhost 5432 User postgres ...
1
vote
1answer
114 views

Statistical probability of an incomplete transaction committed by Postgres

tldr After spending time with bad databases which I will not mention, I came to question even the point of a database. I knew that they could serve a purpose to keep permanent data organized, but ...
3
votes
2answers
2k views

Are ORDER BY clause & ROW_NUMBER duplicating functionality when I need top n queries

I have a table that tracks violations for a student. I want to count the number of violations and select top 2 violators from each class. The query would look like this SELECT * FROM ( SELECT ...
1
vote
1answer
1k views

Update new column with value from old columns for all the rows

I've got an odd db design in place that was here when I got on my new work space. We have a column named params which contains some string data which appears that it belongs in more than one column, ...
4
votes
1answer
674 views

Pull data from multiple tables in a view or function

There are 3 tables from which I need to pull data, and print it in a specific way. http://sqlfiddle.com/#!15/59481/8 Refer to this question to see how the query was produced. The version in the ...
1
vote
1answer
144 views

Wildcard subnet mask for IPv6

To allow any host to connect to PostgreSQL we can use the IPv4 wildcard subnet mask: 0.0.0.0/0 I need this to setup my PostgreSQL's pg_hba.conf file correctly. What is the IPv6 equivalent?
2
votes
3answers
2k views

How to log DML statements executed by a pl/pgsql function?

I have a pl/pgsql function (see below) that lists some fields and clears their contents using dynamically constructed UPDATE commands. When I set log_statement = 'mod', I can see nothing on the log ...
1
vote
2answers
74 views

Is there a way I can use currval() without selecting it more than once?

I want to be able to use the current value of an auto ID by giving it some kind of name and just referring to it (instead of calling SELECT for each record): Requests table has a serial column named ...
1
vote
1answer
144 views

Calculate value differences of rows before and after bounded by multiple referenced rows

Supposed I have a user travelling table like this: id | start_time | end_time | location | ----+--------------+------------+------------+ 1 | 1 | 2 | Loc_A | 1 | ...
1
vote
1answer
279 views

Meaning of query in pgbouncer.log

I could not find any documentation about pgbouncer log, I'm assuming it has the same meaning as the SHOW STATS ? req/s - number of requests since last stat period in b/s - Bytes per sec received ? ...