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
0answers
33 views

Function for matching email address returns full table of emails

I am using Postgres 9.4.6. I have created a Function for finding email address that match an email variable that is passed. However, when I search for 'user@email.com' I get a full table of email ...
0
votes
0answers
41 views

Knowing the most juicy client!

Good evening! I'm studying business informatics in Paris and I just created an amazing database that will help the supermarket down my street to win the loyalty of its clients. We want to know, ...
0
votes
1answer
31 views

Set values conditionally in Postgres UPSERT

We have an UPSERT feature achieved through on conflict do set. How can I set the values conditionally in the set statement, like if(excluded.col1 is null) col1=table.col1 else col1=excluded.col1 ...
1
vote
0answers
48 views

Replicate Postgres 9.2 to 9.4 with no downtime

My current set up has a database running on Postgres 9.2.4 and would like to move from AWS EC2 to RDS. However, AWS requires Postgres be running at least Postgres 9.4 in order to perform this ...
0
votes
1answer
24 views

How to duplicate the database in postgresql?

I need to duplicate the existing database including its schema and structure to another new database. I need this in shell command environment and not in pgadmin. Please kindly help me. nohup pg_dump ...
0
votes
0answers
13 views

How to upgrade PostgreSQL 7.3 to 9.3?

I want to upgrade the PostgreSQL database from 7.3 to 9.3. Please help me how to perform the upgrade.
2
votes
0answers
26 views

Materialized Views and Indexes

Is there any way to force PostgreSQL to use an index that I created on a materialized view table? I tried every combination possible of moving the column attributes around to make Postgres to use my ...
3
votes
1answer
16 views

Sort table rows and save the row numbers using UPDATE

I have Postgres 9.5 with a table movimientos that has the following data: | id | concepto | movimiento | numero | orden | | 1 | AJUSTE 1 | 2542 | 0 | 2 | | 2 | APERTURA | 12541 ...
1
vote
1answer
18 views

Add variable value in single quoted string. Expand variable within single quotes

I have tried different variations of the below commands but non work. I simply want to run the following command using a variable within the location string: CREATE TABLESPACE data OWNER appuser ...
0
votes
1answer
35 views

Who deleted a record?

Is it possible to audit a Postgresql 9.4.5 database in such way that I could find when a specific record was deleted? I don't know what do I need to have enabled / available for such
0
votes
0answers
14 views

Track updated columns postgresql trigger?

I am using postgreSQL and I have tables on which I use triggers to get notified of changes on tables. Now, I have a usecase where when an update on a table is done, I want to notify only the updated ...
0
votes
2answers
29 views

Index for column with two possible value

I use PostgreSQL 9.4. I have a database where a table have index in column which can contains two possible value only(gender: male/female). When I execute a query with condition on this column index ...
0
votes
1answer
19 views

Postgres Error: could not read block 167593 in file… SQL state: XX001

When creating a new column on a table, I'm getting the following error: ERROR: could not read block 167593 in file "pg_tblspc/16384/PG_9.4_201409291/16386/157223695.1": read only 0 of 8192 bytes SQL ...
0
votes
0answers
21 views

pgpool: buffer overflow detected

My pgpool(use docker create) met this buffer overflow detected : And It generate many core file in server which is very big (about 6MB) Why would this happened? I try google it ,but there is seldom ...
0
votes
0answers
45 views

How to schedule the daily backup of a specific database in PostgreSQL?

I have a database 'ahrims', I want to schedule PostgreSQL under linux to backup this specific database (not all databases) at 4:00 PM daily. How can I achieve this? Please help!
5
votes
2answers
67 views

Postgres Update Limit

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID. For example, say I have a users table and login_coordinates table. Every time the user logs in, ...
1
vote
1answer
50 views

Can we export data from PostgreSQL to xlsx file?

I want to export the result of a query to xlsx file format. I know we can export it to csv file, but I want to export it to xlsx file. Like the following: copy(select name_related from ...
2
votes
1answer
28 views

How to append datetime in filename in postgresql

How to append datetime in filename in below command: COPY (select query) To '/tmp/test.csv' DELIMITER ',' CSV HEADER; I want my filename like test_date_tim.csv.
1
vote
1answer
30 views

How to select redundant rows

Suppose a table data recording values of some foo and bar over time: time | foo | bar -----+-----+---- 1 | a | a 2 | a | a 3 | a | a 4 | B | a 5 | B | a 6 | a | a 7 | ...
1
vote
1answer
15 views

I have a query which does not return result with the desired alias names of columns in PostgreSQL

The following is my query which does not return result with the desired alias column names: with d as (select distinct emp.ahrims_id, rank.name as rank from hr_employee as emp, ana_rank as rank, ...
1
vote
2answers
35 views

Slow query / Indexes creation (PostgreSQL 9.2)

I have the following Query: explain analyze 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
1answer
51 views

Does issuing DDL against a view in PostgreSQL take out the same locks as issuing DDL against a table?

I understand that views are implemented using the rule system but I'm unclear if this has any advantages/disadvantages when running transactional DDL against them. Does issuing CREATE OR REPLACE VIEW ...
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 ...
1
vote
1answer
30 views

How to set up Amazon RDS parameter group for Postgres?

I'm trying to create a parameter group in Amazon Web Services for an Postgres 9.5 RDS instance. I'm a little confused by the units being used for things such as shared_buffers and min_wal_size. ...
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
25 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
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 ...
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
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
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 ...
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
votes
1answer
16 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
9 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
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 ?
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 ...
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
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 ...
1
vote
1answer
33 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
54 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
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. ...
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?
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 ...
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
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 ...
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 ...
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 ...
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 ...
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 ...