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
21 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
39 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
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
0answers
23 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 ...
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 ...
2
votes
1answer
42 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
2answers
34 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 ...
1
vote
2answers
74 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
votes
1answer
18 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 ...
2
votes
0answers
12 views

In PostgreSQL, can you read from an index during a ALTER INDEX … SET TABLESPACE operation?

I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production ...
0
votes
0answers
22 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 ?
1
vote
2answers
26 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 ...
0
votes
1answer
12 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
1answer
27 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 ...
1
vote
1answer
40 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
83 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 ...
1
vote
0answers
22 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. ...
0
votes
0answers
11 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?
1
vote
1answer
30 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 ...
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 ...
3
votes
1answer
25 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 ...
0
votes
1answer
24 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 ...
3
votes
1answer
16 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 ...
0
votes
1answer
59 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 ...
1
vote
2answers
37 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 ...
1
vote
1answer
20 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
31 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: $ ...
0
votes
1answer
24 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 ...
1
vote
2answers
51 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 ...
1
vote
1answer
44 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 ...
2
votes
0answers
26 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. ...
2
votes
3answers
56 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: /userfiles/...
1
vote
0answers
15 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
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 ...
7
votes
2answers
165 views

Normalizing a table with a field that generally uniquely identifies a row, but is sometimes null

forgive me if this has been asked and answered before. I'm roughing out a schema for an inventory management system, to be implemented in PostgreSQL. All of our products and services have a sku. ...
2
votes
2answers
73 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), ...
0
votes
0answers
40 views

Postgresql BDR problem compiling pluging

I'm trying to build deb packages for postgresql BDR plugin, but I'm getting the following error: gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-...
2
votes
0answers
21 views

What do LC_TIME and LC_NUMERIC settings actually change in PostgreSQL?

According to the PostgreSQL 9.5 documentation, LC_TIME affects "Formatting of dates and times" and LC_NUMERIC - "Formatting of numbers", but I can't seem to see any difference from changing these. ...
1
vote
1answer
25 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
0answers
31 views

PostgreSQL (Sync Replication) and Pgpool Load Balancing

I am trying to configure pgpool as load balancer for synchronized replicated PostgreSQL servers. I configured PostgreSQL servers and they are running normally. But I want to send write queries to ...
0
votes
0answers
14 views

postgresql service fails to start after bad reboot

I have a postgresql 9.3 running on Ubuntu 14.04. For some reason, my fellow developer rebooted the server and since then postgresql service is failing to start. Also, the reboot didn't go smooth as it ...
1
vote
1answer
21 views

Is it possible to tell when databases, schemas and tables were created in Postgres?

Is it possible to tell when databases, schemas and tables were created in Postgres? I've serached through PgAdmin and tried the command line interface with no joy. Also can't find any reference to ...
1
vote
1answer
66 views

Automatic index creation for primary vs. foreign keys in Postgresql

For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that A foreign key must reference columns that either are a primary key or form a unique ...
1
vote
1answer
22 views

PostgreSQL 9.3: Aggregate collection of JSON arrays

I have a table inventory with a value column that contains JSON strings, with the following data structure: { "category": { "item": ["price", "quantity"] }, "widgets": { "foo": [300, 15]...
1
vote
0answers
36 views

Postgresql with just index storage

I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated. ...
1
vote
1answer
27 views

Get rows that have ALL join criteria

I have three tables growth_spaces, equipment and growth_space_equipment which links the two tables together. I'm trying to write a query to get all growth spaces that have all of the equipment that I ...
1
vote
1answer
41 views

Most efficient way to retrieve data by timestamps

I'm using PostgreSQL 9.2.8. I have table like: CREATE TABLE foo ( foo_date timestamp without time zone NOT NULL, -- other columns, constraints ) This table contains about 4.000.000 rows. ...
0
votes
0answers
18 views

How can I improve this nested postgresql query?

I have a mildly complex query that is having rather poor performance: UPDATE web_pages SET state = 'fetching' WHERE web_pages.id = ( SELECT web_pages.id FROM ...
0
votes
0answers
51 views

PostgreSQL: Slow JOIN on three large tables

Edit: Proposed this question as a LEFT JOIN problem, but as @wildplasser pointed out, due to my WHERE clause I was actually doing a JOIN all along. Adjusting the question from LEFT JOIN to JOIN. The ...