PostgreSQL version 9.2

learn more… | top users | synonyms (1)

1
vote
1answer
28 views

Impact of CLUSTER on performance

I'm trying to optimize my Postgres 9.2 database to speed up queries with date restrictions. I have a timestamp column, but mostly I'm asking for some day, so I have created an index with timestamp to ...
0
votes
1answer
14 views

ilike search really uses its index?

I have a column called nickname and have created index for it. create index test_idx on test_table using btree (nickname); My query is, select * from test_table where nickname ilike '%pga%'; ...
0
votes
0answers
8 views

postgres create a view with data transformation rules

I was hoping someone could point me to a resource that can help me solve my problem. I have a largish dataset (25 Million rows), and I need to export the table based on a set of rules. 6 rules to ...
0
votes
1answer
22 views

SELECT records based on return of COUNT query

I would like to create a query that returns records if values in a column occur more than once. I came across other Stack Exchange questions but these only dealt with returning the number of ...
0
votes
1answer
76 views

CTE Query copy value if exists into an existing table [closed]

I got the following query: WITH table_data AS ( SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description, UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) ...
3
votes
1answer
29 views

How to copy part of a database with inconsistent privileges?

I have a Postgres database and I would like to copy certain tables from a single schema into a new empty database. So far I have copied a schema-only dump from from the old DB and applied it to a ...
1
vote
0answers
19 views

postgresql 9.2 hash join issue

This query is a bottleneck in one of my views.How should I reduce my running time production it seems to be a CPU intensive query rather memory intensive query it is taking too much time. When should ...
3
votes
2answers
82 views

Automate COPY command - Postgres 9.2

I've a backup table with several distinct IDS... I wanna do a BACKUP of those rows, by ID. The function has to be executed by a limit of account_id ( select dump(21); - Where 21 = number of ...
0
votes
1answer
26 views

Get the highest value [duplicate]

I have the following data: account_id note_id size id ---------- ------- ------ -------- 48 1554 196036 28229509 48 1554 18777 28229588 48 1554 4861 28229566 ...
0
votes
0answers
36 views

How we can make “statement_timeout” work inside a function?

I have a requirement to perform a query operation in a specified time limit in postgresql. If the query is not performed in that time limit, we need to catch a exception/a action and need to kill all ...
0
votes
1answer
68 views

How to optimize query with order by

Have query which generate OpenERP ORM. Table have 100k rows. SELECT "tbl".id FROM "tbl" WHERE (("tbl"."active" = 'True') AND ("tbl"."is_company" IS NULL or "tbl"."is_company" = false )) ORDER BY ...
1
vote
2answers
73 views

Trigram index for for ILIKE patterns not working as expected

I've got a simple but slow query: SELECT DISTINCT title FROM ja_jobs WHERE title ILIKE '%RYAN WER%' AND clientid = 31239 AND time_job > 1457826264 ORDER BY title LIMIT 10; Explain analyze: ...
0
votes
0answers
11 views

SQL query for setting value

I found this example for configuration databse tables: http://stackoverflow.com/questions/10204902/database-design-for-user-settings Can you show me some example for SQL query for getting ...
1
vote
0answers
56 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 ...
1
vote
1answer
49 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. ...
2
votes
1answer
22 views

Multiple columns from one table rely on one single table

I'm working with PostgreSQL 9.2 and have the following relation between 2 tables: The table town stores a list of town codes. The table perimeter stores perimeters denomination. Towns are almost ...
0
votes
0answers
16 views

Remedying psycopg2:OperationalError in postgresql 9.3.10 database

I have a Django app + postgresql 9.3 DB, hosted on Ubuntu VM. Recently my VM ran out of disk space, resulting in no space left on device errors showing up in my logs. I cleared the space on the device ...
1
vote
1answer
27 views

How to Increment duplicate strings in a column

I have a table similar to what is below. I have listed only one column since that's what I'm interested in. | column --------- | foo | foo | foo | bar | bar | bar Below is the output that I ...
1
vote
1answer
26 views

Paged query against timestamp populated by trigger missing items

We're having a strange problem with a PostgreSQL 9.2 database that I've been racking my brain trying to figure out. First some background. We have a "tombstone" table that is populated using a ...
2
votes
1answer
95 views

Optimize performance for query with CTE using a multicolumn index

I've got this slow query: WITH flks AS (SELECT id FROM public.ja_feedlog WHERE gtime BETWEEN EXTRACT(EPOCH FROM TIMESTAMP '2016-01-24')::BIGINT AND EXTRACT(EPOCH FROM ...
1
vote
0answers
76 views

How to identify file for “The system cannot find the file specified.” during a pg_ctl -D start?

I'm getting one message during pg_ctl -D start of the standby server. Perhaps more of a general troubleshooting question. This is what I'm seeing when I do a pg_ctl -D start: D:\>pg_ctl -D d:\...
1
vote
1answer
53 views

Importing CSV file - PostgreSQL 9.2

I'm trying to import data from a CSV file on a PostgreSQL 9.2 server, but I get errors when doing that. Pgadmin: ERROR: Cannot execute COPY FROM in a read-only transaction Why am I getting that ...
0
votes
0answers
15 views

How to change shutdown mode when pg_ctl is running as a service?

I work on a Windows-based application that uses PostgreSQL as its backend database. When we install the application, it installs pg_ctl as a Windows service. According to the logs, stopping this ...
0
votes
1answer
36 views

Filter query result

I'm using this query: 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 AS variation, ...
1
vote
1answer
37 views

How to optimize view with group by

Have view with query : SELECT min(lbl.id) AS id, lb.order_id, lb.par_id, lbl.lb_id, lb.categ_id, lbl.mt_id, lbl.uom_id, lbl.tracking_id, sum(lbl.orig_qty) AS orig_qty, max(lbl.qty) AS qty, sum(lbl....
2
votes
1answer
34 views

SETVAL in a Window Function

I'm attempting to do an INSERT into multiple tables using the same key field. According to Sequence Manipulation Functions: Important: Because sequences are non-transactional, changes made by ...
3
votes
1answer
94 views

Full text search in XML documents

I would like to do full-text search in XML documents. However, I can't get matches for attribute values (I get a syntax error if I provide the full attribute value including the : and / characters in ...
4
votes
5answers
984 views

Enforce NOT NULL for set of columns with a CHECK constraint only for new rows

I have a table and need to add a new column without a default value: Constraint: ALTER TABLE integrations.billables DROP CONSTRAINT IF EXISTS cc_at_least_one_mapping_needed_billables, ADD ...
2
votes
4answers
224 views

ERROR: check constraint is violated by some row

I've got the following error: ERROR: check constraint "cc_at_least_one_mapping_needed" is violated by some row Query: ALTER TABLE integrations.tax_aggregates DROP COLUMN IF EXISTS myob_id, ADD ...
1
vote
1answer
428 views

When can I clean up archived WAL files?

I need some help understanding the how archived WAL files are used and when I can clean them up. Currently we have a master database replicating to a slave, but every so often our master database ...
1
vote
1answer
49 views

Performance issue using view vs. direct statement

I have a table with structure similar to this: table values ( id1 int, id2 int, value real, value_at timestamp, constraint primary key (id1,id2,value_at) ) I need to get the "current value" for a ...
1
vote
0answers
44 views

Slow query - PostgreSQL 9.2

I've got a Query that takes some time to be executed... Can you guys help to improve the total time? Thank you! QUERY: SELECT file.inode_id AS file_id, file.parent_inode_id AS ...
0
votes
0answers
25 views

Slow Query - Optimize - PostgreSQL 9.2

I've got a slow query.. I'd like to make it faster.. Make add an index? Query: SELECT j.clientid AS client_id, ni.segment_index AS note_id, f.inode_id AS file_id, f....
3
votes
0answers
97 views

Recovery from Live to a new Slave Server - PostgreSQL - ERROR

I've started a new SLAVE PostgreSQL server set up. master: 192.168.100.1 slave1: 192.168.100.2 slave2(NEW SLAVE) 192.168.100.3 * NOTE: I run the pg_basebackup from another STANDBY SERVER. Not ...
0
votes
0answers
27 views

Postgres 9.2 replica can't catchup to master

I have configured a master/replica setup for postgres 9.2 on Amazon Linux. PS output shows that the replica is connected and streaming, but also recovering. This has been going on for about 24 hours. ...
0
votes
0answers
34 views

postgres versions differ for root / user

I have upgraded postgres 9.2 to 9.4 on Centos7 following the instructions here, but then I am left with what I think is a slightly weird result: [user@machine]$ sudo bash [sudo] password for user: [...
1
vote
1answer
88 views

How do I GRANT for all tables across all schemas

I want to GRANT SELECT ON ALL TABLES TO foo; But postgres tells me I have a syntax error at "TO". It expects a GRANT like: GRANT SELECT ON ALL TABLES IN SCHEMA public TO foo; ...
0
votes
1answer
38 views

What's the difference between betwwen 2 DB and 2 Schema [closed]

My question is : what is the difference between the two next mode in PostgreSQL, for an production application. using two DB in the same SGBD (the same table-space and HDD) Or using two schema in ...
4
votes
1answer
151 views

How to avoid invoking functions twice when using GROUP BY and HAVING?

I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents. The following query works and returns the correct results: ...
1
vote
0answers
120 views

PostgreSQL DML statements

I am trying to trick an application into executing a DML statement AFTER starting a SELECT statement without using stacked queries so I cannot use the ; character anywhere in my query. I have resorted ...
1
vote
1answer
103 views

how to obtain a list of restore points?

i am implementing restore points using pg_create_restore_point, and will then have a set of scripts to enable my users to restore to one of these restore points. Clearly i have work to do with taking ...
0
votes
0answers
33 views

what happens to WAL files when standby server is in recovery

in the case where we have SERVER1 (prod db) and SERVER2 (hot standby), what happens to the WAL files from SERVER1 when SERVER2 goes into recovery mode and is opened? On SERVER1, the ARCHIVE_COMMAND ...
0
votes
1answer
174 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 ...
0
votes
0answers
378 views

pgpoolAdmin for pgpool-II 3.4 : superuser: unknown (Connection error)

I cannot start/stop/reload my nodes through pgpoolAdmin and it says "superuser: unknown" even though I'm logged with the postgres account. What could be the cause? pgpool.conf is identical to another ...
-7
votes
2answers
239 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.
0
votes
0answers
28 views

Using pipelined order by while fetching only first ten rows

PostgreSQL 9.2 I have the table table: id user_id amount PK integer integer and I need to fetch the first 10 rows as follows: SELECT user_id, SUM(amount) s FROM table GROUP BY ...
2
votes
1answer
193 views

Postgres 9.2 - postgres - FATAL: XX000: relation mapping file “global/pg_filenode.map” contains incorrect checksum

We have a ~400GB Postgres database (v9.2.6) (running on CentOS 6.5) that had a problem with free space and with every command (ls, pwd etc) we were seeing a segmentation fault error. We were able to ...
3
votes
1answer
69 views

How to validate column while creating Table?

Need to validate the column while creating the tables in postgres , An example before developer create table We are in need to validate the Audit columns, Example : create table tab_1 ( a integer, b ...
0
votes
2answers
68 views

Can I Show Multiple “WHERE” Filtered Results In A Single Query/Output Table?

I am using Postgres 9.2.4 on Red Hat 6. Tables --------------------------------------- | Table 1 | --------------------------------------- | WorkflowID | DataSet | ...
4
votes
1answer
162 views

is it possible to run postgres with no WAL files being produced?

I am looking at a way of running a 9.2 postgres database on redhat 6 without any WAL files being produced. We have a end of day batch process which generates a large number of WAL files (and has ...