All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

3
votes
1answer
706 views

'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?

I'm running PostgreSQL 9.3 on Mac OS X Yosemite. I try to create an unaccent lowercase trigram index. To achieve it I did this: mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1"; ...
1
vote
2answers
18 views

Using ILIKE with unaccent and with only right end wildcard

I use Postgresql 9.4 and I have a big table named foo. I want to search on it but I get long execution times if the search text is very short (e.g. "v") or long (e.g. "This is a search example with ...
-4
votes
1answer
21 views

Postgresql Group by [on hold]

I created a table in postgresQL like Now I ran a query like:: SELECT name FROM emp.company GROUP BY name And I am getting output like I am unable to get it whats exactly happening over here.
0
votes
1answer
26 views

PostgreSQL conditional join and value distribution with materialized view

So I essentially have two tables in a database. One which is the base table of all of our media data, and another that has potential override data for spend. Not everything in the display table will ...
0
votes
0answers
8 views

Limit connection numbers with pgbouncer

So I have this pgbouncer config: [databases] my_db = host=10.10.10.10 my_db_with_conn_limit = host=10.10.10.10 dbname=my_db pool_size=55 And the goal is to limit the number of connections from a ...
2
votes
1answer
21 views

How to get max sequence of rows?

I have a table with statistic of games which has next rows (I only write about rows that has interest in this context): id BIGINT PRIMARY KEY date TIMESTAMP WITH TIME ZONE status ...
-1
votes
0answers
8 views

Good Schema model for this use case

It have following entity/data models: Service Provider --- Has Many(basically gives many) --- Services User Orders --- Has Many(can select many) --- Services (and it shall be having options to ...
1
vote
2answers
315 views

What is the most efficient way to count the number of rows in a table?

I am using postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
24
votes
3answers
23k views

How to turn json array into postgres array?

I have a field user that holds a json document that roughly looks like that: { name: 'foo', tags: ['foo', 'bar'] } I would like to turn the tags array into a concatenated string (foo, bar). ...
3
votes
1answer
52 views

Debug query on big table that is sometimes slow

I have a web API that is backed by a Postgres database, and the performance is generally very good. I monitor the performance of both the database and the application as a whole. Most of my queries ...
0
votes
0answers
25 views

Source of error on psql data load

Upon importing a file derived from rails establishment, psql is hitting the error of foreign key constraints: ERROR: insert or update on table "documents" violates foreign key constraint ...
1
vote
1answer
24 views

Ordering in subqueries

This is my query: select locid,tlid,locdescription from ( select locid, tlid, locdescription from trip_log left join trip_log_det on tlid=tldtripid left join locations on tldlocation=locid ...
1
vote
0answers
13 views

what are the options for the psql \copy command?

I was looking at the psql documentation: \copy { table [ ( column_list ) ] | ( query ) } { from | to } { 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [ [ with ] ( ...
2
votes
1answer
32 views

How do I recover my database after losing a tablespace with indexes?

I'm thinking of moving all the indexes in my PostgreSQL 9.3 database to a tablespace on an SSD for improved performance. This SSD is not in a RAID array and I need to consider a scenario where it ...
2
votes
1answer
27 views

RDS crosstab: ERROR: permission denied for language c

We're currently using PostgreSQL 9.2 in our production database, we want to migrate to a new RDS instance, using PostgreSQL 9.4. However, we need to have permission to create a function, but only ...
1
vote
1answer
34 views

Filter created_at and count distinct minutes

I have this query to get records from a database: SELECT camera_id, count(*) AS snapshot_count FROM snapshots WHERE created_at >= timestamp 'yesterday' AND created_at < timestamp 'today' ...
1
vote
1answer
291 views

When is Postgres autovacuum executed

I am using an older version of Postgres (8.4.20). I know that the autovacuum process is executed frequently to free disk space of queries that deleted or updated data in tables. I have a database ...
3
votes
1answer
137 views

Error while restoring Postgres DB Dump

Using below command to restore the database Restoration command: cat my_db_dump.sql.gz | gunzip | psql -d new_database -U my_username Note: my_db_dump.sql.gz size is 4.5 GB, (extracted size will ...
4
votes
4answers
5k views

How to get all roles that a user is a member of (including inherited roles)?

Let's say I have two Postgresql database groups, "authors" and "editors", and two users, "maxwell" and "ernest". create role authors; create role editors; create user maxwell; create user ernest; ...
4
votes
1answer
734 views

Should one always VACUUM ANALYZE before REINDEXing in PostgreSQL 8.4?

Early in the morning every day a pgAgent job refreshes the contents of table A from table B on my PostgreSQL 8.4 database. Table A contains around 140k records across 91 columns and has two indexes - ...
0
votes
0answers
7 views

How to estimate PostgreSQL reindexing time? [duplicate]

How do you estimate the total time reindexing an entire table will take in PostgreSQL? I have a large PostgreSQL database, the primary table of which has 15 million records and 100 columns, and 200 ...
0
votes
2answers
187 views

Postgresql Chained Slaves and Slave Promotion

So I have a setup like Master -> Slave1 -> Slave2. Slave1 replicates from Master and Slave2 replicates from Slave1. If I promote Slave1 to master is it possible for Slave2 to continue replicating from ...
0
votes
1answer
45 views

Reject Specific Database PostgreSQL User Authentication

Lets say I have two databases: Sales and Inventory And two users: A and B I want user A to be able to do: psql sales B But user A must not be able to do: psql inventory B Right now user A can ...
0
votes
0answers
24 views

move a postgres cluster to another machine

I have a postgreSQL 8.3 cluster I should move onto a new machine which runs postgres 9.3 (Ubuntu 14.04) . First of all I copied my whole cluster and I pasted into ...
3
votes
2answers
44 views

Generate a sequence of timestamps but skip given periods

I have a table things_to_do in PostgreSQL. For example: thing_id --------- 1 2 3 4 5 6 And I want to add a timestamp to every row: SELECT thing_id, '2016-02-10 19:57:00'::timestamp ...
1
vote
1answer
41 views

Getting the next bigserial value in PostgreSQL [on hold]

Say I have a table with an id BIGSERIAL column. I just started a new session. I want to know what the id of the next row I insert will be. How can I tell this? I can't simply SELECT MAX(id) FROM ...
5
votes
1answer
69 views

How to Choose Between VALUES and SELECT for INSERT?

This answer raised the question for me how to choose between VALUES and SELECT in such a function . Using PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: ...
2
votes
1answer
28 views

Cascade deletes from multiple tables

I have a fairly standard restaurant model with a restaurant table in a PostgreSQL DB. All restaurants have ratings (which consist of average, vote_count and vote_sum) but in order to avoid repeating ...
3
votes
1answer
68 views
0
votes
2answers
110 views

Whats the best and fast way to import a huge DBF file to a Postgres in Windows?

I have some huge DBF files (about 35 GB) to import to Postgres. Whats the best and fast way to do it? OBS: I have 300 GB of free disk space and 8 GB RAM.
1
vote
2answers
423 views

Importing CSV into multiple tables using PostgreSQL

I have a very big CSV file with the following format: TAG,TIME,VALUE as an example row: footag,2014-06-25 08:00:00.0,3400.0 I used to import it easily inside PostgreSQL 9.3 using the following: ...
6
votes
4answers
34k views

Getting SELECT to return a constant value even if zero rows match

Consider this select statement: SELECT *, 1 AS query_id FROM players WHERE username='foobar'; It returns the column query_id with value 1 along with a player's other columns. How would ...
0
votes
1answer
125 views

How to predict the growth of a PostgreSQL database

I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so. I searched on Google, but I found only means to determine the ...
0
votes
0answers
14 views

How to properly implement a relationship between a table with an array of foreign keys and the foreign table? [duplicate]

I'm not entirely sure how to implement this kind of relationship correctly. As an example, assume that we have: CREATE TABLE menu_item ( mi_name TEXT PRIMARY KEY, mi_ingredients TEXT[] ); ...
0
votes
0answers
29 views

PGPool for Postgres load balancing, where to sit it

So I've been doing a stack of reading. I'm still not 100% clear of the best way to go about it. Let's take a simple setup. 3 nodes to server UWSGI apps (in random), 3 nodes for PGSQL, 1 nominal ...
0
votes
1answer
29 views

Get rows from last insert statement by specific user

I have many users in my web app (not the postgres user). Each of them capable of inserting item transactions on their own location. How to get only rows just inserted by user_a?
0
votes
0answers
32 views

Why default db privilleges are not applied on a new schema?

I have a user with all privileges granted by default in database properties. I create a new schema as postgres and table there. I expect that the user should have an access to this table but it looks ...
1
vote
1answer
148 views

Slow query - Optimize - PostgreSQL

I've got a slow query here... could you please help me to optimize it? Query: SELECT concat(company, ' ', customer_name_first, ' ', customer_name_last) AS customer, sum(revenue) AS revenue, ...
0
votes
1answer
140 views

PostgreSQL ODBC and Microsoft Access date/time in aggregate functions

I am trying to replace our MS Access 2003 databases with PostgreSQL 9.3. The first step is to use our MS Access front end to the upsized databases in PostgreSQL. Everything seems to work very well ...
1
vote
1answer
58 views

Best way to transfer millions of rows from one database to other

There are two databases: DatabaseA and DatabaseB with the same schema. These databases have millions of rows. The plan is to convert them into one single database (DatabaseCommon) and multiple ...
1
vote
2answers
1k views

How to view full blocking postgres queries?

I'm using a statement from this page to view blocking queries in Postgres SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, ka.query ...
5
votes
3answers
104 views

Do Postgres GIN indexes need to be rebuilt periodically?

Do they get bloated like btree indexes? If so how can the bloat be measured e.g. by an automated reindexing script. I am interested in improving query performance and saving space.
0
votes
0answers
20 views

search pgadmin for column

I'm a front end dev and this is my first time doing serious database work. I have json sent to the client that looks like this: prop[0].event.history = 'foo'; //can also be 'bar' I wanted to ...
3
votes
1answer
144 views

Connect to PostgreSQL from different host

I am facing error while connecting to the PostgreSQL server from different host, I configured both postgresql.conf and pg_hba.conf but unfortunately with no result. So I have installed PostgreSQL on ...
55
votes
3answers
113k views

How do I list all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. It's a 3rd party product, they are slow on answering some questions, and I know the data is lying inside ...
0
votes
1answer
31 views

Why can I drop table when logged in as not owner?

I read that only owner can drop table and no other user may be granted to do so. I created a table with owner postgres and then drop it as another user. What privileges should I revoke to disallow ...
0
votes
1answer
25 views

How to create unique index for both cases

I have table in PostgreSQL with sender_id and receiver_id and a unique index: add_index :my_table, [:sender_id, :recipient_id], unique: true Table will created, if sender_id: 1, and recipient_id: ...
0
votes
1answer
109 views

Is it possible to set a timer for autovacuum in PostgreSQL

I am running PostgreSQL 9.4.1. I am currently cleaning up data, which requires me to run expensive queries. It seems like my tables get "locked" up every day at the same time in the afternoon. My ...
9
votes
2answers
12k views

Is there a simple way in PL/pgSQL to check if a query returned no result?

I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this: select c.data into data from doc c where c.doc_id = id and c.group_cur > ...
0
votes
1answer
345 views

OCIEnvCreate failed to create environment handle after installing oracle_fdw

I'm want use Postgresql 9.3 in CentOS 7 and add extention oracle_fdw. but when use this extenstion i get error OCIEnvCreate failed to create environment handle after installing oracle_fdw. my ...