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
1answer
10 views

How to create unique index for both cases

For example, i have table with sender_id, and receiver_id. I have unique index for it: add_index :my_table, [:sender_id, :recipient_id], unique: true Table will created, if sender_id: 1, and ...
1
vote
1answer
18 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
20 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
0answers
13 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 create table with owner postgres and then drop it as another user. What privileges should I revoke to disallow it? ...
2
votes
1answer
23 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
40 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
23 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
36 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 ...
0
votes
1answer
29 views

is there a GUI client that can execute multiple queries and show its multiple results?

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
18 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
17 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
25 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
36 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
165 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
46 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
43 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
74 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
58 views

Fatal error when not numeric value - PostgreSQL 9.2 [on hold]

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
0answers
69 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
39 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
33 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
595 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
61 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
19 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 ...
5
votes
2answers
149 views

Trigger to create symmetrical rows

I've got a table that describes some relation between two items. The items are referenced by ID. I'd like to create a trigger, so whenever you create a row (a,b), a symmetrical row (b,a) is created. ...
2
votes
1answer
42 views

How to create an index to be picked by query

I have created multiple indexes one after another, but my query doesn't want to pick them :( CREATE INDEX x ON gan.paid (project_id, country_iso_code, source, date(created_at)); CREATE INDEX x ...
14
votes
1answer
913 views

Why does time zone have such a crazy offset-from-UTC on year 0001 in Postgres?

In Postgres 9.5, I was surprised to see the result seen below while experimenting with year 0001 (no year zero 0000). Offset of -07:52:58? Some example code. Note that I mixed use of TIMESTAMP WITH ...
1
vote
1answer
55 views

Data structure design for supporting arbitrary number of columns in table or database [closed]

I am currently working on a sort-of a meta-modeler to build a free web service so that people can input data and run several models on that data. The task I am currently struggling is: user needs to ...
0
votes
2answers
49 views

PostgreSQL surrogate keys: use sequence/bigint or OID?

I'm looking at a need for surrogate keys in tables I'm designing on a PostgreSQL-derived DBMS (Citus). Would OIDs suffice? Is there a downside to using them instead of creating a bigint field and a ...
3
votes
0answers
73 views

Some delays in heavy inserts

I have a lightweight web-application with current load of about 500 reqests/sec. Almost each request generates an INSERT request to the database. Some time ago I found that my application has short ...
0
votes
1answer
36 views

Getting records for yesterday, but per hour

I am currently getting all data for yesterday from my database but I want to get data for each hour. This is my query: select camera_id, count(*) as snapshot_count from snapshots where ...
4
votes
1answer
394 views

How to design a database

The file which contains the day to day activities of each employee. And it shows the detailed description of employee what they done at work time. So these files are prepared by employee. Now, my ...
4
votes
1answer
48 views

dumping database with “compressed data is corrupt” errors

I'm using pg_dump to dump a large (396 GB) PostgreSQL database: pg_dump --clean --create mydatabase After running for a day, a statement fails with ERROR: compressed data is corrupt and pg_dump ...
0
votes
0answers
31 views

How can I display values as columns dynamically using postgresql

I have a data table containing finance data about some filings like: filing_id | custom_label | value ------------------------------------ 987345 | cash | 567 987345 | ...
1
vote
1answer
61 views

PostgreSQL optimistic reads (equivalent to SQL Server's Read Committed Snapshot)

Does PostgreSQL provide a built-in optimistic concurrency control mechanism such as SQL Server's Read Committed Snapshot Isolation? Readers can read all the rows without blocking, but writes will ...
0
votes
0answers
59 views

Postgres, promoting slave to master issue

We had the following setup in place db01 -> Master Postgres 9.3 db02 -> Slave Postgres 9.3 Master data got corrupted (logically) and as soon as it was identified we shutdown the server. Dev team ...
3
votes
1answer
41 views

Use specific array column in the where clause

Having a function that returns an array and in a query we use the function to return results such as: {"TUNDUMA GOING (BORDER)","ARRIVAL DATE",09/01/2016,22,"AT CHECKPOINT"} {"TUNDUMA RETURN ...
1
vote
1answer
36 views

Postgres CTE query for a single row not optimized

From my database I am trying to get a weighted sum of a user's stats, and I will only be querying the table one or two users at a time, so I wrote it as a view. Since it's a view, I pretend to ...
3
votes
1answer
51 views

Trigger function taking column names as parameters to modify the row

I am trying to write a trigger function in Postgres 9.4. Something like this (not working, yet): CREATE FUNCTION set_point_from_coords(source _regclass, target _regclass) RETURNS trigger AS ...
2
votes
1answer
28 views

Can I get pg_rewind to show me lost data after a Postgres failover?

Say I have Postgres setup with a warm standby using the build in replication features. Now, imagine a scenario where the data centre the master loses power and the slave is in another data centre and ...
1
vote
1answer
27 views

How to get particular object from jsonb array in PostgreSQL?

I have a field called 'user' that holds a json array that roughly looks like this: "user": [{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}] Now I want a query like: select count ...
1
vote
1answer
68 views

Ordering in postgresql

I'm facing an issue whereas I do not understand. Running PostgreSQL on mac and have this query: select tldid, tldaction, to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, ...
3
votes
1answer
23 views

Postgres reduce by key if key is not null

I need to select N rows with seq > X which reduce_key is null and for each row group with the same reduce_key select the one with the biggest seq. Result should be strictly sequential (but with ...
0
votes
1answer
20 views

Is it possible to set order in which inherited tables a scanned in postgresql?

I have a postgresql measurement table which is partitioned based on logdate column by month. Suppose I have city_id integer column and want to select latest row with city_id = 125. When I run explain ...
1
vote
0answers
31 views

Three Vital Postgres Columns Corrupted

I run a small website which uses a Postgres database. There are regular back ups based on the amount of traffic we have every few days but there was a recent surge of activity just before a power cut ...
2
votes
0answers
41 views

Insert performance

I am having trouble with Posgresql insert performance. There are 2 tables, documents and words, where documents is essentially a mapping from an external path to a surrogate id, which is foreign ...
1
vote
0answers
17 views

In Postgres, is it possible for me to have a table partitioning constraint based on data type?

Say I have the following schema for my Postgres database: CREATE TABLE sources ( source TEXT NOT NULL, PRIMARY KEY (source) ); CREATE TABLE ipaddrs ( addr INET ) INHERITS (source): CREATE ...
1
vote
1answer
26 views

Disable/enable PostgreSQL trigger inside function for the same table

I have a trigger that protects updating some column. I also have a trigger that should update that same column after insert on that table. I've used triggers like this and it works if I need to update ...
3
votes
2answers
67 views

Understanding a PostgreSQL join [closed]

I have seen the following query in the PostgreSQL documentation. Unfortunately I don't understand the join criteria. create temp table tmp_stat_user_tables as select * from pg_stat_user_tables; ...