Tagged Questions
2
votes
1answer
25 views
How to determine if postgres database contains GiST indexes and what type?
Is there a way to easily check if a PostgreSQL database has any GiST indexes and of what type they are?
0
votes
2answers
40 views
Auto increment char varying column prefixed with some characters
I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well.
Is there a way to achieve ...
1
vote
0answers
60 views
Where Can I find *Current* PostgreSQL advice for a SQL Server DBA? [closed]
Been a MSSQL DBA for about 7 years. I have the fundamentals of performance, backups, replication, security and design down for MSSQL.
I'm not setting up new applications using PostgreSQL 9.2. I have ...
0
votes
0answers
34 views
On Heroku in production, how can I copy database A to database B on command?
From my local terminal, I can use the following commands to do what I need to do:
heroku pgbackups:capture --expire
heroku pgbackups:restore charcoal --confirm max-kiosk
However, I want to do this ...
0
votes
1answer
22 views
Creating unique constraint to be validated from input
I have this table name tbl with this table definition:
create table tbl (
id serial primary key not null,
counter integer not null default 1,
ref_id integer not null,
date_booked date ...
0
votes
1answer
23 views
PostgreSQL: After restoring data, unable to use Views (permission denied)
I'm attempting to get a PostgreSQL environment running locally. I'm using 9.2.8, and have restored data from a backup made by the DBA of the production environment.
The restoration worked fine ...
1
vote
2answers
75 views
Function execute query, manipulates result and then return the same result
I need a function that does something like this pseudocode:
function get_data() RETURN SET OF something... as
BEGIN
myResultSet = select id, some_other_column from ...... limit 20000;
update ...
1
vote
1answer
30 views
Limit balancing results by a column
I have a web crawler that have a table with tokens to crawl and each token have a fk_location column that is the website where that token must be processed.
I also have a function that returns me the ...
2
votes
1answer
121 views
Could not open file “pg_clog/0000”: No such file or directory
My local PostgreSql 9.2 database won't start anymore this morning. I am on Windows 7. Yesterday, I performed a Windows Update before going to bed.
I have checked the log and found:
2014-05-28 ...
0
votes
2answers
36 views
Get distinct results based on column and satisfy condition
I have the below select which is selecting from a table with the columns:
id, project_id, keyword_id, position,
Data example:
i, p_id, k_id, p
1, 1, 1, 4
2, 1, 1, 5
3, 1, 1, 6
5, ...
2
votes
1answer
56 views
Postgresql AUTOVACUUM ANALYSE parameters for a specified table
I would like to set autovacuum parameters for a specified table.
My problem is that AUTOVACUUM ANALYSE on a small table takes way to long to complete.
This table has only 2 columns (including the ...
1
vote
1answer
106 views
Upgrading using pg_upgrade on windows fails
When upgrading from postgres 9.2 to 9.3 by following the upgrade procedure found in PostgreSQL 9.3.4 documentation about pg_upgrade, the following command fails:
RUNAS /USER:postgres "CMD.EXE"
...
3
votes
2answers
92 views
PL/pgSQL fuction to find circular references
I have two tables
ID Task
1 1
2 2
3 3
4 4
Col1 depend
2 3
2 4
3 1
4 2
ID and Col1 are related through FK constraint. I want to find out all circular references. Here ID ...
2
votes
1answer
142 views
Postgresql not using GIN trigram index when performing non-ASCII LIKE query?
Steps to reproduce
Create database
CREATE DATABASE citiesdb
WITH OWNER = citiesowner
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'C'
LC_CTYPE = 'C'
...
1
vote
1answer
115 views
INSERT using results of CTE INSERT to provide unique id values
I am writing a job to transform data from an old design into a new design. In this process, I need to take the id from an insert into a separate table and use that in an insert to the target table, as ...
0
votes
0answers
28 views
Postgresql replica lagging behind
I restored a master database after a fallback by creating a new basebackup and making the promoted slave stand_by again. Per the logs everything is fine, however the following functions are not ...
0
votes
1answer
32 views
Postgresql: How to add a role that inherits from another?
Pretty cut and dry, I am just a total noob. Is there a way to make a given role inherit from another?
1
vote
0answers
49 views
Postgresql 9.2 - Auto vacuum running continuously for 18+ hours
I currently have a table that is updated daily (easily 20%+ rows are updated, all floats/integers) and the vacuum is kicking off and running for more than 18 hours. There are 19 million rows total.
...
-1
votes
0answers
48 views
Bloating effect in Postgresql [closed]
I've done some performance analysis on my DB and I have releved that:
Read head 8192
CPU power used 25%
Bloating 56% (84GB of 150) 20% occupied by index unused
Now I have find that 99% of ...
1
vote
1answer
72 views
Function quote_nullable(timestamp without time zone) is not unique
When I run this query on "enterprisedb 9.2", it raises an error:
select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date;
Error:
LINE 1: select quote_nullable(to_date('09-02-2014 ...
4
votes
1answer
124 views
Order by distance
If I have a query returning nearby cafes:
SELECT * FROM cafes c WHERE (
ST_DWithin(
ST_GeographyFromText(
'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')'
),
...
0
votes
1answer
55 views
Close all connections to postgres without having a connection
I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague log in to run ...
1
vote
2answers
113 views
Extract all tags from hstore field
I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know ...
1
vote
2answers
53 views
Nearest value for each foreign key
I have a table with a primary key, timestamp, value, foreign key. I would like to find the nearest value to a timestamp for each of the foreign keys in a list.
Unfortunately, the gap from the given ...
0
votes
0answers
43 views
Postgres 9.2 on Windows
Simple question, but utterly infuriating. Trying to use psql from a command prompt, but can't access any of the previous commands with the up/down cursors. I could also really do with finding out ...
1
vote
1answer
43 views
Postgres settings reported differently in pgsql
We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = ...
2
votes
1answer
59 views
PostgreSQL 9.2; Altering a table to use another sequence
I've created a table like so:
create table doki_data_item2 (like doki_data_item including defaults);
but this new table uses the same sequence as the old table.
So two questions:
How can I copy ...
0
votes
1answer
245 views
Streaming replication between postgresql 9.2 and 9.3
Does anyone know if it's problematic to have a postgresql 9.3 slave setup to do streaming replication from a postgresql 9.2 master?
2
votes
1answer
142 views
Postgresql 9.2.6 ignoring archive_command
I'm attempting to set up a 3-server chain by leveraging WAL-E. I've done this in the past with replication/wal segments and I'm attempting the exact same method, just with using WAL-E in between.
My ...
3
votes
1answer
155 views
2 B-tree indices OR 1 GiST index on tsrange — which will perform better?
I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
2
votes
1answer
235 views
How to view the query of another session in pg_stat_activity without being super user?
I have a Postgresql 9.2 database. Two users are created on this database. When I perform the following query as superuser, I can see everything.
select * from pg_stat_activity
However, is it ...
1
vote
1answer
404 views
Can't restore my distribution DB postgres 9.2 backup into my staging DB postgres 9.3
I keep getting this pg_restore: [archiver] did not find magic string in file header when i execute a pg_restore -Fc -U "username" -d "dbname" "filename".dmpon my staging DB.
These DB are running on ...
0
votes
2answers
162 views
Can't run PostgreSQL locally
I am working to run PostgreSQL is running locally but I cannot connect.
I installed postgress using brew, then I downloaded http://postgresapp.com
When I try to create a database
createdb: ...
1
vote
1answer
66 views
PostgreSQL - Create view with autoincremental column
I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N.
Is this possible to do without effecting the ...
0
votes
3answers
70 views
Postgresql: Make Column Default a Query
Is there a way I can set up a column in PostGreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
0
votes
1answer
74 views
Complex “activity” based query optimization
I'm working on a query in the same style as Facebook's feed on a table that currently has 500,000 rows. For now I am just looking to get the 20 newest items (new records created by a user's ...
1
vote
1answer
114 views
Slow queries related to subqueries using aggregation
Here's a simplified version of the queries I am running:
select
...
from
editions
join edition_events on edition_events.edition_id = editions.id
join events on events.id = ...
0
votes
1answer
254 views
pg_upgrade 9.2 to 9.3 error
I'm trying to upgrade a PostgreSQL 9.2 database cluster to a 9.3 cluster on a new server.
I've copied the old 9.2 cluster (using rsync) to /srv/data/postgres on the new server and want to convert it ...
2
votes
1answer
220 views
Strange Postgres Hstore syntax with curly braces {key=value}
I'm using the following program https://sourceforge.net/projects/jailer/ to extract a subset of data from my database with correct associations following my foreign key constraints. The program ...
5
votes
1answer
96 views
Is Postgres ignoring my function cost annotation?
Why does this:
create or replace function dummy() returns double precision as $$
SELECT random() $$
LANGUAGE SQL
COST 777;
explain select dummy();
return this:
Result (cost=0.00..0.01 rows=1 ...
2
votes
2answers
243 views
How to define alias in an ARRAY_AGG expression?
I'm trying to return pure JSON from a Postgres 9.2 table.
SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW))
FROM (
SELECT
album,
max(release_year) AS release_year,
...
4
votes
1answer
183 views
Stored procedure deadlocking itself
I have a strange situation, seen from the log:
Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807.
Process 53807 waits for ExclusiveLock on ...
1
vote
0answers
304 views
PgPool is pushing Postgres to 100% cpu usage
I think I've followed the configuration for pgpool correctly, but he's pushing my postgres machines to 100% cpu usage unnecessarily. I have one pgpool machine that works just fine, no overload at all, ...
3
votes
1answer
547 views
Can column name be “Group” in Postgresql or in any databases
I was designing a project which specifies that in a table what column name should be and one of the column name in the specification is "group".
I tried creating it but it always throw a syntax ...
1
vote
1answer
162 views
PostgreSQL 9.2 number of days in a tstzrange?
How can the number of days contained within a range be found?
For example, with these timestamp ranges, get these (integer) number of days:
tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 ...
1
vote
0answers
2k views
PostgreSQL isn't listen on 5432 in Windows 7
I have a PostgreSQL 9.3.1 DB Server running in Windows 7 Ultimate. I have checked the file postgresql.conf and this lines are uncommented:
listen_addresses="*"
port=5432
And this is the ...
2
votes
1answer
132 views
PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK
I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'.
Various revisions of this SQL isn't constraining '-/+infinity' input:
CREATE TABLE bill
(
id ...
2
votes
1answer
205 views
How to solve 'tuple concurrently updated' error?
The following query is performed concurrently by two threads logged in with two different users:
WITH raw_stat AS (
SELECT
host(client_addr) as client_addr,
pid ,
usename
...
1
vote
3answers
294 views
Newly installed Postgresql 9.2 on same box as 9.1
I have a new project at work that is using a PostgreSQL 9.2. But, I'm still having to support a project that uses 9.1. So, I'm trying to configure my local dev box to have both installed.
I have ...
0
votes
1answer
78 views
How to index refined null values?
I have table with two indexes:
latest_channel_snapshots_views_idx (view_count DESC NULLS LAST)
latest_channel_snapshots_network_views_idx (network_id, view_count DESC NULLS LAST)
What I want is ...
