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
2answers
65 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: ...
3
votes
2answers
44 views

Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT (*) AS "count" FROM "Posts" AS "Post" WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' AND "Post"."new" = TRUE; Index: CREATE ...
1
vote
1answer
42 views

Store table names to reference other tables

I'm designing a webapp where users can add widgets to their pages. Right now I have only two types of widgets that are different in nature, so they have different attributes, but both of them are used ...
2
votes
1answer
24 views

Limited utility in RECURSIVE VIEWS?

In the docs for recursive queries with WITH, you'll find this Recursive queries are typically used to deal with hierarchical or tree-structured data. This seems to be true, but how does this ...
1
vote
0answers
24 views

What are the pros and cons of storing two values in an array instead of two columns?

I have a project that plans to store an away team and a home team. I'm wondering if it is better to store the teams in a single array column like this: {Harvard,Colorado} Or create away_team and ...
0
votes
1answer
16 views

Finding time ranges in multiple rows

I have a table with (suppose) just 2 columns: - fecha (a date) - ignicion (0/1 representing boolean state) The goal is to retrieve which periods of time was ignicion held at '1', for such task I ...
2
votes
1answer
26 views

Table design for ticket sales

I'm designing a database for a ticket sales system in PostgreSQL, where the admin user can create a batch of tickets for a specific event. All tickets for a given event have the same attributes (like ...
1
vote
1answer
41 views

Auto start pgAgent

When my windows server 2008 start, both services postgres and pgAgentstart because are in automatic. But if my db is frozen and I need to restart the postgres service, the pgAgent is also stoped, ...
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
2answers
37 views

Aggregate function to return any (first) encountered value

How do I tell PostgreSQL to return first encountered value instead of an aggregate column? Table "public.cache" Column | Type | Modifiers | Storage | Stats target | ...
2
votes
0answers
37 views

Postgres update query complaining about text value when only NULL values are passed

I am using Postgres 9.4.6. Is there any reason why the below update query would execute successfully here:- UPDATE "Predictions" SET "userid" = c."userid", "Fixture_No" = c."Fixture_No",...
0
votes
1answer
10 views

postgresql fulltext slow

I am trying PostgreSQL for the first time, but am finding the fulltext search very slow. My database has a table with 5 columns and 5 million rows CREATE TABLE table_test ( id integer NOT NULL, ...
0
votes
0answers
116 views

Postgres wont install on windows 7 64 bit (Failed to load SQL modules into table)

I have been trying to install standard postgres 9.3 onto a windows 7 Machine. every single time it gives the same error: Failed to load SQL modules into table I have tried (from this question http:/...
0
votes
0answers
22 views

Backup options for database with large objects

I have a database that includes a table with references (as oids) to large objects that have been stored in the database via the JDBC Large Object manager. I need to make regular backups of the ...
4
votes
2answers
215 views

Optimize query to find top N users who commented on a post

Problem Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
5
votes
1answer
44 views

What permissions are required to return rows from information_schema.schemata?

In postgres (I'm on 9.3.4) what permissions are required to return rows for select * from information_schema.schemata The 9.3 documentation says "are owned by a currently enabled role". Does that ...
0
votes
0answers
20 views

Postgresql: find out all columns that have particular string and count the number

I want to create a function like this: enter table name and a particular text, then return a table that have 3 columns the : table name , column name, the number of that column have the particular ...
2
votes
1answer
68 views

Select where value in row is different and update

I have a large table that includes rows like the following: Query: select * from cartographic_text_vm where fid = 'osgb5000005104723440' order by ckpt; Results: -[ RECORD 1 ]---------------+---...
0
votes
0answers
18 views

PostgreSQL 9.3 overlapping pg_dump commands

I'm wanting to do a pg_dump or pg_dumpall of a database every 10 minutes. Unfortunately the database takes longer then 10 minutes to write out. This post confirms that database dumps produce ...
0
votes
1answer
23 views

Postgresql maintainenace queries fail with foreignkey violation

I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables ...
1
vote
0answers
45 views

My simple FK constraints appear to have been ignored. How?

I have two tables, device and platform where device.platform_id enforces the following constraint: Foreign-key constraints: "device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES platform(...
-4
votes
2answers
32 views

Postgres relationship statement [closed]

Lets say I have the table parents. A parent has many kids and a kid has many toys What would be the best way to do a SELECT statement for the parent and grab all of kids associated with that parent ...
0
votes
2answers
30 views

Group By Month With Overlapping years

I want my query to show all months grouped together, regardless of the year. For example, I would want this to be the output: 01/13/2015 01/01/2016 02/07/2015 02/01/2016 This is sample DDL - what ...
1
vote
1answer
27 views

Show Dollar Amount By Name On A Month To Month Basis

I want to show dollar amount by name on a month to month basis, but my syntax gives me an entry for each different dollar amount. Below is what I get from my query (subset of data) Name Value ...
1
vote
2answers
21 views

How to restore postgres database which is created using pg_dumpall?

I have dumped all of my databases using pg_dumpall and now I want to restore them. I used the command psql -f db.out postgres, but I am still not able to see my databases. Can anyone help?
5
votes
1answer
81 views

Indexes: integer vs string performance if the number of nodes in the index is the same

I hope this is the right place to ask! I am developing an application in Ruby on Rails with the PostgreSQL (9.4) database. For my use case, columns in tables will be looked up very frequently, as the ...
1
vote
1answer
21 views

INDEX size as bytes - PostgreSQL 9.2

Is there any way to get this query to return the index size as BYTES? SELECT relid::regclass AS table, indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid::...
1
vote
1answer
20 views

Postgresql: compare record against sibling, but limit search only to active records

I'm trying to find a way to compare records against it's sibling record while limiting the comparison only to the most recent entry for each set. I know it does not make to much sense so I'll go ...
1
vote
1answer
22 views

PostgreSQL ntile() partition

i have a point grid with population values. each point has an id and population value. i also have an state_id which say in what state the point is. now i want to calculate the percentile ntile(100) ...
0
votes
0answers
21 views

Postgresql relation from array column to multiple ids [duplicate]

I`m trying to create a simple postgresql database for payments accounting. Here are sql codes for several tables in my db. CREATE TABLE "buh"."campaign" ( "id" SERIAL NOT NULL, "campaign_state_id"...
4
votes
1answer
44 views

PostgreSQL join subquery can't restrict query

I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances. I'm trying to run a query ...
0
votes
1answer
17 views

postgres full text search modifies ts_query

I have following query: select vec.vec, vec @@ to_tsquery($$'fabr':*A$$), -- true to_tsquery($$'fabr':*A$$), -- fabr vec @@ to_tsquery($$'fabry':*A$$), -- false to_tsquery($$'...
1
vote
0answers
34 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
45 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
35 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
53 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
29 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
15 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
30 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 ...
4
votes
1answer
20 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
23 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
37 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
32 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
23 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
30 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
48 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
75 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
72 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
30 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.