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

learn more… | top users | synonyms (1)

0
votes
2answers
19 views

postgresql - how to find and extract text from a description column

I'm trying to create a new table using data from an existing. I have the following structure: Table "public.def" Column | Type ...
0
votes
1answer
26 views

Find unused indexes

I'm using the following query to find unused indexes: SELECT PSUI.indexrelid::regclass AS IndexName ,PSUI.relid::regclass AS TableName FROM pg_stat_user_indexes AS PSUI JOIN pg_index AS PI ON ...
0
votes
0answers
20 views

Time Series on Postgresql

I am developing a software that have a dataset time series like this: id_equip, Timestamp, Double v1, ..., Double v30 The size estimate is 5.000.000 rows by year and the time of response is very ...
2
votes
1answer
38 views

PostgreSQL won't take new timezone

In Venezuela a timezone change have been undertaken on May 1st 2016. The old offset for the Venezuela timezone was -04:30 and the new one is -04:00. All database servers have been updated with the ...
0
votes
0answers
8 views

outdated stats or missing records?

I'm using the following query go gather index usage statistics: SELECT t.tablename, indexname, c.reltuples AS num_rows, pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS ...
0
votes
1answer
10 views

Missing / Orphan Indexes?

In the process of finding duplicate indexes, I've been using the following query to identify them: SELECT indrelid::regclass AS TableName ,array_agg(indexrelid::regclass) AS Indexes FROM pg_index ...
0
votes
0answers
21 views

Cannot drop constraint due to FK references

I have detected a duplicate index on an id field on one of our tables, as that column is the PK, but there's also a separate UNIQUE index on such column, which is useless. But I cannot delete the ...
1
vote
2answers
54 views

How to ensure that only unique row combinations are present?

I have three tables which can be called set, item and assignment. The assignment table assigns an item to a set, thereby my goal is to ensure that each combination of items is unique. Hence, if item A ...
1
vote
1answer
1k views

pgpool-II configuring multiple master databases

I could not find a solution for this anywhere, not even in the pgpool manuals. How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master ...
0
votes
2answers
25 views

Is there a way in PostgreSQL to set an default owner for all future create table statements in a session

We have a program that transfers cobol data (index files) to a database. Until now we transferred the data to an oracle database. In the beginning of the program I logged in with user sys and ...
2
votes
2answers
193 views

Limiting read access in postgres to few rows

We have a requirement where we want to limit the maximum no. of rows that a select query can return when its done using a particular login. So basically we would like to append a limit clause to any ...
-1
votes
1answer
17 views

How can I sum values of one columns based on the distinctness of another column in PostgreSQL?

I have a table me id | name | type ----+--------+------ 1 | Ali | a 1 | Sami | a 3 | Kamil | c 3 | Imran | c 2 | Wali | b 4 | Yousuf | d 5 | Kamran | e ...
0
votes
0answers
20 views

Creating mini postgresql dump

My production postgresql database is about 70GB now . so i want to create a mini database dump from it so i could restore it on the development environment for testing purposes . any ideas ?
-7
votes
2answers
211 views

Postgres Dump and Restore on Linux Terminal [closed]

I need to take a dump in postgres db and to be restored on some other server. please tell me the command in Linux with compressed and encrypted format.
1
vote
1answer
56 views

How can I dump all tables to CSV for a PostgreSQL schema?

I have a database with a lot of schemas in it and I want to dump the each of the table contents to CSV. I'm aware of the COPY command but I'm not sure how to script something that will read all of ...
0
votes
1answer
10 views

What is the impact of DROP SCHEMA on concurrent read only workloads in PostgreSQL?

I have an ELT process that deals with a pretty small amount of data. I would like to have 1 schema called prod with the results of the previous ELT run and 1 schema called dev where I will perform the ...
1
vote
2answers
25 views

Optimizing PostgreSQL with Index

I'm having a hard time trying to get PostgreSQL to use an index that I created. I understand that PostgreSQL is smart enough to determine when an index should be used because in most cases a seq scan ...
1
vote
1answer
34 views

Duplicate key value violates a unique constraint

I've been looking at other posts. It seems many people have had this problem, but I'm somewhat new to Postgres and tried other solutions with no luck. I have ny_stations table: CREATE TABLE ...
1
vote
1answer
25 views

index creation - Slow Query - PostgreSQL 9.2

I've got the following query: SELECT * FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id, split_part(full_path, '/', 6)::INT AS note_id, split_part(full_path, '/', 9)::TEXT ...
6
votes
2answers
4k views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My ...
3
votes
1answer
13 views

Postgres Array into Geoserver

I'm working with OS Highways data, which when loaded into Postgres using ogr2ogr has a few columns that are set as arrays. Geoserver does not like this, so I need to transform the arrays into a simple ...
1
vote
1answer
29 views

Tricky condition for PostgreSQL UPDATE query

I need help with a PostgreSQL query. I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to ...
1
vote
0answers
16 views

Profiling postgres with gprof: no data/gprof folder and 'no time accumulated'

I'm attempting to profile Postgres with gprof. A gmon.out file successfully gets created in both the data directory and in the working directory where I launched the client (pgbench) process from. ...
4
votes
1answer
1k views

Could not open file “pg_clog/0000”: No such file or directory

My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed. I have checked the log and found: 2014-05-28 ...
0
votes
0answers
8 views

Find chunk_id for given record in Postgres

Is there a way to find details of the TOAST pointer for given row and column in Postgres (8.3)? Especially the chunk_id?
0
votes
0answers
10 views

Windows 7 service manager thinks postgresql 9.4 is stopped, but database is up

I have several machines running postgresql 9.4 on windows 7. The database is configured to start automatically after a reboot, and it seems to start ok. pgadmin is accessible, and I can run queries ...
30
votes
5answers
63k 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 ...
15
votes
4answers
24k views

tuning postgresql for large amounts of ram

I have two identical servers (in terms of hardware), they are both standard installations of windows server 2008 r2, with minimal software installed (basically my code and required stuff like jvm ...
2
votes
2answers
68 views

Filtering UNION ALL result is much slower than filtering each subquery

(edit: see end for a simpler example) I'm searching in a table named "cases" (135k rows, 29 columns). Some of the rows in this table have a type of parent-child relationship (of different types), ...
3
votes
1answer
20 views

Constraint on row value based on values in other rows

Is it possible to have a table with four columns: CREATE TABLE accrual ( add_date date NOT NULL, user_id integer NOT NULL, rate integer NOT NULL, amount numeric(7,3) ); and then ...
1
vote
1answer
43 views

Can the subquery be simplified/optimized?

Here's a simplified version of a query I run. I've removed a bunch of key/value pairs for simplicity. The constraint is that I can't change anything outside of the ARRAY function; but the ARRAY ...
0
votes
1answer
21 views

pg_stat_activity shows lots of easy queries running for minutes

After application server start its connection pool fills quickly and application can't work properly. I went through pg_stat_activity and noticed lots of queries running for minutes: But postgres's ...
0
votes
0answers
4 views

How can I clear my database on Heroku?

I have an application that I deployed to Heroku. Everytime I try to sign-up, it gives me and error that tells me my password is invalid. I've type in everything correctly and I'm sure I've never ...
0
votes
2answers
221 views

PostgreSQL pg_upgrade --link mode performance issue after upgrade

Trying to do PostgreSQL upgrade from 9.0 to 9.3. I'm using pg_upgrade with the --link mode since the downtime is very minimal compared to other options like pg_dumpall and pg_upgrade without using ...
0
votes
1answer
211 views

Sessionizing Log Data Using SQL window functions: tweaking a query (incrementing session ID and event length)

I'm referencing to a very good blog entry, that describes how to sessionize log data with SQL window functions. I'm no database pro and I don't really understand the logic behind this query (you can ...
0
votes
1answer
27 views

Dumping and restoring tables in PostgreSQL - ERROR: extra data after last expected column

I am trying to dump and restore multiple tables from a database on one server, to a different database on another server. Both are running Postgres 9.4 on linux. The command for the backup was: $ ...
1
vote
2answers
29 views

Security of streaming replication?

If I want to use streaming replication between two Postgresql databases on different parts of the internet, is there a way to secure or encrypt the connection between the two? Or are there reasons ...
0
votes
1answer
41 views

Effectively searching through entire 1 level nested JSONB

Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level). How does one effectively optimize a query to ...
6
votes
1answer
213 views

Surprising results for data types with type modifier

While discussing a recursive CTE solution for this question: Get the last 5 distinct values for each ID @ypercube stumbled across a surprising exception, which lead us to investigate the handling ...
4
votes
1answer
892 views

Default value for UUID column in Postgres

In Postgres 9.x, for a column of type UUID, how do I specify a UUID to be generated automatically as a default value for any row insert?
1
vote
2answers
30 views

Postgres: set client encoding default

When querying from certain tables, I'll often receive the error: character with byte sequence 0xef 0xbf 0xbd in encoding "UTF8" has no equivalent in encoding "WIN1252" Varying bytes, but you get ...
0
votes
1answer
15 views

PostgreSQL, display role members

Is there a way to visualize which users are mebers of a role in PostgreSQL / pgAdminIII, like role: council_stuff members: Harry Ben Steve Melinda
0
votes
1answer
22 views

Make a (copy of a) postgis database available to customer?

Our application uses a PostgreSQL database (with the PostGIS spatial extension). Now one of our customers has requested direct access to the database; because, as they claim, it’s their data. We’d ...
2
votes
3answers
53 views

Extract string in PostgreSQL 9.2

I got the following column: full_path "gorfs"."absolute_pathname" It keeps the path of a BLOB, like on the example below: Note that the 4865 is the account_id.The path is always like: ...
0
votes
1answer
15 views

Can't figure out how to update a column in one table based on multiple boolean values in another table in postgresql

What I want to happen is boolean milestone.milestonecompleted will turn true only if ALL of the linked boolean task.taskcompleted values are True I've looked around and come up with this. Any help ...
1
vote
1answer
24 views

Versioning entities in db with draft option, select query

I have postgresql db where i keep data for jee application. User can edit those data without creating new version of entity(draft). During edit, entity is copied as draft(version set to -1) after ...
0
votes
1answer
141 views

Postgresql Streaming replication - monitoring

I have setup streaming replication for my postgres servers. I am using PostgreSQL 9.2.6. Replication seems to be working fine. I am monitoring this with two ways using nagios: Log_delay and ...
2
votes
0answers
25 views

What software or cloud service do you recommend PostgreSQL to get (more) interoperable with? [closed]

We're trying to expand PostgreSQL ecosystem by developing features for interoperability in the PostgreSQL community, as well as asking software vendors and other OSS projects to support PostgreSQL. ...
1
vote
0answers
13 views

How to find the number of tuples that are vacuum-eligible?

I am trying to find the number of vacuum-eligible tuples in one of our postgres tables. This is the output of vacuum verbose on that table INFO: "act_msgs": found 0 removable, 2217083 nonremovable ...
0
votes
1answer
112 views

When does pg_dump not export CREATE EXTENSION?

I have a production database that I am trying to dump then load (in plain text format) locally. The problem is, the database being dumped uses the hstore extension in several tables, and contrary to ...