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

How to sort data in a table and save the enumerated data 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 | ...
0
votes
0answers
8 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
24 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
7 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 ...
1
vote
2answers
19 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, ...
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
22 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
10 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
6 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
33 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!
0
votes
0answers
73 views

Trigger to set value - PostgreSQL 9.2

I have the following column: users.code CHARACTER VARYING The customer can add any value he wants into that column, but he also can chose by not adding any value. That column can't be null, so a ...
1
vote
1answer
26 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
24 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
11 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
18 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
10 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 ...
0
votes
0answers
8 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. ...
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
7 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
16 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
17 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
198 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
33 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
9 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
23 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
27 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
19 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
3k 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
9 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
23 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
12 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
5 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
9 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 ...
-1
votes
0answers
15 views

PL/pgSQL trigger function syntax error at END; [on hold]

I'm writing a simple trigger function, as seen below. For context, it's meant to enforce uniqueness of a key across multiple tables. It will be made more generic once I've got it working. CREATE OR ...