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
6 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 ...
0
votes
0answers
6 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 ...
0
votes
1answer
21 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 ...
-3
votes
1answer
20 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.
-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 ...
2
votes
1answer
18 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 ...
0
votes
0answers
22 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
23 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
11 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
29 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 ...
1
vote
1answer
32 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' ...
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
23 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 ...
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
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 ...
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
28 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 ...
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 ...
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
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
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 ...
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 ...
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 ...
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: ...
1
vote
1answer
32 views

What's the difference between group roles and login roles?

It looks like for single user I should create a login role and than respective group roles because I can't directly assign privileges on login role. Is it true? What's the purpose of such separation? ...
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 ...
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 ...
3
votes
1answer
30 views

What indexing to be used over string columns(text type) with string length around 3000 characters

I am using open source columnar dbms (postgresql+Cstore). I am trying to speed up the response time of queries. My database has tables with strings mostly. String columns contain huge data (up to ...
1
vote
1answer
47 views

What is default behavior of PostgreSQL without specifying COLLATION?

What is default behavior of PostgreSQL, if i don't specify collation externally? One of the main questions, is it case-sensitive or not?
1
vote
1answer
29 views

Show only duplicate values

How to select rows with duplicate values (non-distinct rows) and summarize with a count of the number of duplicate values in order of the most frequent duplicates? Given a table of: person_ name_ ...
4
votes
1answer
48 views

pgAdmin is extremely slow on any remote operation

I run this query from my local pgAdmin connecting remotely to our dev server: select * from users order by random() limit 1; it hangs for 17 seconds and shows Total query runtime: 148 ms. 1 row ...
1
vote
1answer
34 views

is there a GUI client that can execute multiple queries and show its multiple results? [on hold]

I'm currently using pgadmin and it appears that it is not possible to send more than one query and retrieve multiple data sets back. For example, if I try to do this: select travelerId, name from ...
1
vote
1answer
20 views

Postgresql uuid syntax error

I am trying to insert/update a foreign key field which is also a uuid to use the correct fk id for this set of data: zuri=# ALTER TABLE newarts ALTER COLUMN jurisdiction_id SET DEFAULT ...
1
vote
1answer
24 views

Postgres \copy from CSV: how to format range variables?

Consider a Postgres table containing a range variable: CREATE TABLE my_table (t_range tsrange, field1 text); If I want to manually insert a row via psql, I can run: INSERT INTO my_table (t_range, ...
1
vote
1answer
31 views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
1
vote
1answer
39 views

Is it possible to view a SQL query in the PgAdmin III Graphical Query Builder?

I often use pgadmin III's graphical query builder to design my SQL queries. I can then toggle back to the SQL Editor tab, and grab the SQL. Is it possible to paste a SQL query in to the SQL Editor ...
2
votes
2answers
166 views

How do I set the isolation level for SELECT statement?

Using PostgreSQL 9.5 I would like to make sure that when I run a SELECT statement I get consistent results, i.e. if the table is modified while the SELECT is running, I want to get the state that was ...
2
votes
2answers
48 views

Is there any point with a combined index on all columns in a table?

I have stumbled upon several cases of indices set on all columns of a table. In my mind this will be as efficient as the full table scan that would occur without the index. Is this correct or is there ...
0
votes
2answers
44 views

Execute system commands in postgresql

My requirement is to execute a system command like (ls) or C program when a trigger executes. Is there any way to create a trigger function to solve this problem.
5
votes
2answers
82 views

How to verify disk hit by a query

I want to know how to verify disk hits by a select query in PostgreSQL 9.4 on CentOS 6.5. Also please let me know how far enabling the log_statement_stats in the postgresql.conf will help me. I need ...
-2
votes
1answer
61 views

Fatal error when not numeric value - PostgreSQL 9.2 [closed]

Below is an example of the auto-generated update query, with client-supplied keys (_iid). There's a fatal error when _iid is not numeric. However; this should accept any value. I believe that's what ...
2
votes
1answer
72 views

How to estimate reindexing time?

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 rows with 100 columns and 200 ...
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
0answers
40 views

Stored procedure vs transaction anomaly

We have a stored procedure which updates reporting data in a table. We call this via Hibernate from Java. Within the stored procedure the current time is taken from the NOW() function. The anomaly ...
1
vote
1answer
38 views

Trigger for removing group with no users left in

I have three tables: USERS, GROUPS, and GROUP_MEMBERSHIP CREATE TABLE USERS ( ID BIGSERIAL NOT NULL, NICKNAME VARCHAR(20) NOT NULL constraint USER_EXISTS UNIQUE, PRIMARY KEY (ID) ); CREATE TABLE ...
4
votes
5answers
609 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 ...
1
vote
1answer
31 views

Plpgsql (Postgres) function for sanity check

While starting up my app, I need to do a sanity check within my database. In a table sensor, there's a column seconds. Now the function should take an argument id. It then should simply do something ...
0
votes
0answers
65 views

Index has no gain vs normal query

I have a table with a timestamp field. The table is loaded with rows which have ascending order of this field (time increases). I tried the following query, prior to establishing an index (there are ...
0
votes
1answer
20 views

PostgreSQL, how to query the database to retrieve the percentile elements of a dataset?

I need a PostgreSQL query that do the following job: given a percentage (e.g. 10%), the query should return each 1st element of each percentile, among all the elements selected. So suppose in this ...