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
1answer
84 views

Trigger function - PostgreSQL 9.2

The idea: 1 - The customer can add any value into users.code column 2 - If the customer does not insert data into it, the trigger/funciton has to do the job I am able to do that, using this SQL: ...
0
votes
0answers
11 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
0answers
5 views

Setting values conditionally in on conflict - pgsql

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 ...
0
votes
0answers
9 views

Is Mongodb a smart decision for a social network?

So this question is more than a bit broad , I would appreciate input from those with experience working on social networking. I have been questioned many times why I have chosen MongoDB for a social ...
1
vote
1answer
16 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. ...
1
vote
0answers
15 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
16 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
1answer
160 views

pg_get_viewdef error while running pg_dump in PostgreSQL

Occasionally, I get the following error while doing a simple pg_dump ( pg_dump -U postgres -Fc -f mybackup.dmp mydb): pg_dump: [archiver (db)] query failed: ERROR: could not open relation with OID ...
1
vote
1answer
16 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
0answers
8 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
2answers
1k views

pg_basebackup giving “could not receive data from client: Connection reset by peer”

I'm trying to get data to a new slave box from my new master (previously the slave). I've tried the steps already and it worked previously. Now when I run the following command: pg_basebackup -h ...
1
vote
0answers
18 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 ...
0
votes
1answer
583 views

pgpool2 not working on my Ubuntu i686 athlon i386 GNU/Linux

I am trying to implement replication and load balancing on my Ubuntu i686 athlon i386 GNU/Linux.for creating database clustures I have installed postgresql contrib 9.1.11 having initially database ...
4
votes
2answers
40 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, ...
2
votes
1answer
13 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 ...
0
votes
1answer
111 views

PostgreSQL error moving data directory to home folder

I have a problem with the moving of my data directory of PostgreSQL. I found many different ways, but none worked. The most sensible way for me was: Stop PostgreSQL Copy data from old folder to new ...
0
votes
1answer
30 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
6
votes
2answers
123 views

Creating relationships between tables that where not specified in the given UML diagram

Good evening! I'm working on designing my very first actual database from the following UML class diagram (in French unfortunately): I'm at the point of creating the relational sketch of it which ...
0
votes
0answers
10 views

re-installing same version of Postgres to keep the data

I have installed Postgres 9.4 on my machine and I have some data in the db. Something is wrong with services and I would like to re-install it (same exact version) from scratch with the aim to fix ...
10
votes
1answer
13k views

How to use aes-encryption in PostgreSQL?

I tried aes-encryption by using following statement: SELECT encrypt('test', 'key', 'aes'); which worked, but I am not able to decrypt the value. I inserted it in a field of datatype bytea but I'm ...
0
votes
2answers
24 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
0answers
12 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
1answer
13 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 ...
4
votes
1answer
657 views

Get inserted row count when master table isn't modified

I have a partitioned table where data resides only in the child tables. The master table has an insert trigger that will distribute inserted data into appropriate child tables. Now, I need to create ...
0
votes
0answers
12 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
38 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!
1
vote
1answer
29 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 ...
8
votes
1answer
208 views

To partition or not to partition?

Having already read several questions on SO, external blog posts and manual SO: Foreign Key constraint to partitioned table in Pg dba.SE: Different ways of handling FK to partitioned table in Pg ...
2
votes
1answer
22 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.
0
votes
2answers
27 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 ...
1
vote
1answer
26 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
13 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, ...
0
votes
2answers
12 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 ...
2
votes
0answers
13 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
1answer
19 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
13 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
25 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
7 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
8 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
18 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
43 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
17 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 ...
0
votes
2answers
12 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
2 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
19 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
199 views

Postgres Dump and Restore on Linux Terminal [on hold]

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
35 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 ...