All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.
1
vote
1answer
18 views
Create dynamic WHERE clause from a JSON field
We have tables like this:
people_people (
"id" integer...
"attributes" HSTORE
)
object_permission (
"permissions" JSON
)
An example of an object_permission.permissions is:
...
1
vote
2answers
29 views
PostgreSQL: Calculate balance working backwards from current balance
I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, ...
0
votes
1answer
19 views
INSERT INTO using the RETURNING values from another INSERT
I'd like to do something like this:
INSERT INTO table2(name)
INSERT INTO table1(name, address) VALUES ('me', 'home')
RETURNING name;
And then I would expect a new record in table2 with 'me' in ...
0
votes
0answers
5 views
Permission denied to crate “pg_catalog.tablename” during pg_dump targetting another machine
I am trying to dump some tables from a 9.5 beta 2 to a 9.4.4 server. The form of the command I am using is the fairly standard:
pg_dump -t table dbname | psql -h hostname -d dbname
I am using the ...
0
votes
0answers
5 views
Timezone information is lost while importing dump in PostgreSQL
At my working PC (PostgreSQL 9.3.11) I use pg_dump to export my database like this:
pg_dump my_db --clean > tmp/my_db_dump.sql
Then I upload the exported file to the server (PostgreSQL 9.1.12) ...
0
votes
0answers
16 views
Postgresql 9.4 replication not working
I have setup my replication as per https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps.
When I start my slave and check for ...
4
votes
4answers
127 views
Extract MIN and MAX values related to datetime values on Postgres 9+
I am trying to query a table that has a collection of student grades recorded over time. I would like to produce a result set that grabs the student id, the first grade, first grade date, last grade, ...
0
votes
0answers
11 views
pg_toast growth out - PostgreSQL 9.2
Trying to understand how can the pg_toast table be 90% of my entire DB?
SELECT nspname || '.' || relname AS "relation",
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT ...
1
vote
1answer
24 views
Insert multiples rows/columns - PostgreSQL 9.2
I've got a table that has several columns, But I need to insert data into two columns of that table.
I need to add data into those columns, from a select statement.
Would it be something like:
...
0
votes
0answers
7 views
Deleting Schema - Saving up space - PostgreSQL 9.2
I've got four servers:
1 - Master
2 - Slave Hot Standby (Same hardware)
3 - Slave Hot Standby
(Same hardware)
4 - Slave Hot Standby (VM - Very slow machine)
On the master ...
-1
votes
0answers
20 views
Moving slow sub-query to With clause
I am trying to optimize this query :
SELECT sum(a-alias.story_view_count) AS count FROM a-table a-alias
WHERE(a-alias.created_at_minute_id >=
(floor(extract(epoch from(select ...
1
vote
0answers
6 views
Moving extensions in AWS RDS (Postgres)
Initially we installed all extensions in default "public" schema.
Now we have more and more other schemas - thus we want to add extensions and common functions to the schema common.
Unfortunately AWS ...
1
vote
0answers
35 views
Postgresql string array vs join for user management schema?
I am creating a database schema for user management in PostgreSQL. As, it supports array, so I am trying with three approaches.
Which one is best, querying with joins or getting/setting role data ...
0
votes
0answers
37 views
How to make sure vacuum is happening in postgres sql db
I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed ...
0
votes
1answer
24 views
PostgreSQL Bloat Warning: Vaccuum does not help
We use check_postgres to monitor our DB. We get this warning:
POSTGRES_BLOAT WARNING:
DB "foo" (db foo) index foo_text_index_id_uniq rows:?
pages:485435 shouldbe:330673 (1.5X) wasted ...
6
votes
1answer
1k views
Why is PL/Python untrusted?
According to the docs:
PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted ...
0
votes
0answers
13 views
Constraint to prevent overlapping ranges in an array field in PostgreSQL
If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, ...
0
votes
1answer
38 views
INSERT INTO + Selects - PostgreSQL 9.2 [duplicate]
I wanna add some data into two columns in a table.
Would be something like:
INSERT INTO dm.billables_links (billable_id) VALUES ((SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike ...
0
votes
1answer
39 views
Postgres Data Dump problem
I am trying to dump this data into Postgres:
COPY address_types (game_id, device_type) FROM stdin;
1 Ps3
2 Xbox
3 Other
\.
But I always get this error:
[Err] ERROR: syntax error at or ...
0
votes
2answers
29 views
Declare variable for SELECT statement in Postgres function
I have modified my function, but I have problems with declaring variables. I use Postgres 8.4.
CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz)
RETURNS TABLE ...
1
vote
0answers
22 views
CTE ordering guarantees [duplicate]
I'm starting to make heavy use of CTEs for my postgres based application, and am wondering if selecting from a previous CTE statement guarantees any ordering?
E.g. is the order of rows for this query ...
0
votes
1answer
25 views
Constraint Information - PostgreSQL 9.2
I was trying to delete some data from the table dm.billables:
1 - Select the id - PASS
SELECT * INTO junk.clientid_10306 FROM dm.billables WHERE account_id = 10306;
2 - Delete the data from the ...
2
votes
1answer
41 views
SELECT very slow when no results and a LIMIT is specified
I am running into an issue where a SELECT query is very slow because it does not use an index when the number of final results is 0 and a LIMIT clause is specified.
If the number of results is ...
1
vote
0answers
26 views
Accessing a row directly at a table in PostgreSQL by using the PostgreSQL's internal library
I am writing a PostgreSQL extension using the C language. I would like to do the following tasks:
Store a set of rowids (or other row identifier, like ctid). Each rowid uniquely represents a row in ...
0
votes
0answers
6 views
Using the puppetlabs postgresql module with postgres plus advanced server (ppas 9.5)
I am new to puppet and I am trying to use the puppetlabs postgresql module to install and configure an EnterpriseDB (ppas9.5) database. I started by unsuccessfully trying to change the ...
0
votes
0answers
15 views
Syntax error on REVOKE? [on hold]
I'm setting up a database for a new application and I'm trying to secure the passwords. I used http://www.p2d2.cz/files/hagander-secure-passwords.pdf as a reference to generate the following SQL:
--
...
1
vote
2answers
43 views
Using sub-query column in where clause
This question has been asked several times and even answered as well but it's not helpful to me, that's why I am posting it again. I have this query:
select u.*,l.*,
(
select count(cr.id)
...
0
votes
0answers
24 views
PostgreSQL not running every update on my script
I'm running a dozen update statements on a loop and some updates just won't happen (they return 0 affected rows), If i recall the script it will slowly update the rows, I don't know how to debug ...
0
votes
1answer
19 views
postgresql insert from select query, plus static values
Referring to How to insert values into a table from a select query in PostgreSQL?,
I would like to INSERT into a table rows from another, specified by a SELECT DISTINCT, plus some static values, ...
3
votes
0answers
22 views
Should I do the maths within PostgreSQL for ACIDity?
It is my first question on Database Administration Stack Exchange. Please be kind if something is not done the nice way..
I would like to be advised concerning the way I'm working on my database, in ...
3
votes
0answers
51 views
Paid support for PostgreSQL
We're planning to sell a small ERP product that utilized PostgreSQL on the backend, targeting between 10 to 500 users per client. The management insisted on getting paid support for PostgreSQL, but ...
0
votes
0answers
14 views
Postgresql 42883 error code with encrypt function
Ok, the thing is that I'm doing a login controller on my database. Part of it is this function to create users that goes like this:
create or replace function crear_usuario(par_usuario ...
0
votes
0answers
25 views
unique BIGINT as foreign key instead of primary key in distributed system
I am creating user management database schema. I am using Postgresql as database. Following is my approach. Please suggest if there is any performance issue if I use this structure.
Requirement:
...
0
votes
0answers
14 views
Postgresql create view for child displaing parents [migrated]
all about Postgres, I have three table: children, parent, children_to_parent.
It is many to many relationship.
children_tab:
children_id | name | lastname
parent_tab:
parent_id | name | lastname
...
1
vote
1answer
42 views
Query to back up a PostgreSQL database
I made a CRUD web application that uses a small PostgreSQL database. It is a donors database with the columns name, telephone, blood type, etc.
I have some SELECT and INSERT INTO queries, but now I ...
0
votes
1answer
26 views
How to return value for key from a jsonb column?
I've successfully created the below table on 9.5.1.0:
CREATE TABLE json_test (
id serial primary key,
data jsonb
);
and successfully inserted this record:
INSERT INTO json_test ...
0
votes
1answer
35 views
How to add a foreign key constraint to a postgresql table
I have a postgresql table called Message. It's columns are text and creation_time.
How do I alter this table such that a new column called sender is added to it, which is a foreign key on the User ...
0
votes
0answers
21 views
Resetting deadlock count statistics
It seems that just restarting the PostgreSQL service doesn't clear / reset the detected deadlock count. How can it be restarted to zero?
select version();
PostgreSQL 9.4.5 on ...
0
votes
0answers
19 views
N-dimensional vectors in PostgreSQL Spatial Index
I have many 8-dimensional vectors. And I want to construct on them a R-tree ('USING gist' in PostgreSQL). But I have got stucked at the choice of the correct geometrical type.
Whether the 'path' type ...
0
votes
0answers
25 views
Detect when a CREATE INDEX CONCURRENTLY is finished in PostgreSQL
If I create an index CONCURRENTLY in PostgreSQL, how can I see when it is finished?
I am attempting to rebuild indexes to solve index bloat, and I need to keep the old index around for a while until ...
1
vote
1answer
12 views
Cannot log in with default user on Mac 10.10
I am running PostgreSQL on a Mac 10.10
PostgreSQL 9.3.5 on x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn), 64-bit
I am having trouble ...
2
votes
1answer
61 views
Index columns with arithmetic operators
I commonly make this query on my database
SELECT * FROM Example_tab WHERE Column_a + Column_b < 10;.
Is it possible to create indexes based on Column_a + Column_b < 10 to speed up this lookup. ...
1
vote
2answers
39 views
Postgresql: how to remove a GRANT on multiple tables?
I have a big database with 40 schemas and plenty of tables inside each one. Some tables have a GRANT SELECT ON TABLE schema1.table1 TO user1grp. I no longer need user1grp group role.
If I delete ...
1
vote
1answer
20 views
Check if two databases have same structure [duplicate]
I have a postgres database that was developed during time, adding tables and keys when needed.
Now I'm using a tool (Enterprise Architect) to document it. The tool allows to create automatically the ...
-2
votes
0answers
13 views
Accessing a column from PostgreSQL database and plotting it on Google Maps [closed]
I have a database created. This database has a column called Latitude/Longitude.
I wanna access this Latitude/Longitude field and plot it on Google Maps. What could be the best way to do this? I was ...
7
votes
1answer
90 views
Sum Interval of Dates within same column
How do you best sum the differences of a range of dates in the same column between interleaving rows? I have a Datetime column and want to calculate the difference between Rows. I want the difference ...
0
votes
0answers
12 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 ...
1
vote
0answers
13 views
Very different query plans with and without LIMIT in PostgreSQL.
I have the following query which I am using to create vector tiles for a web map. I'm pulling together just the geometries within the selected tile using a CTE, then joining it to get additional ...
0
votes
0answers
30 views
Cannot create database PostgreSQL [closed]
Working with Ubuntu on a server. I installed PostgreSQL.
Baiscally I am following this tutorial.
I am logged in with my user accoung max. I logged into the PostgreSQL account from the bash prompt ...
0
votes
1answer
30 views
How to select number of affected rows to variable in function?
create or replace function test()
returns void as $$
begin
update tbl set col1 = true where col2 = false;
-- now I want to raise exception if update query affected more than 2 rows to rollback the ...
