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

learn more… | top users | synonyms (1)

7
votes
2answers
417 views

Feasible to have thousands of users in Postgres?

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
0
votes
1answer
112 views

Free universe tool monitoring all database [closed]

Is there any free tool to monitor most popular databases like Oracle, MySQL, MSSQL, PostgreSQL. I want to monitor the real time performance of all the above mentioned databases with a single tool like ...
0
votes
1answer
31 views

synchronizing schemas in server

In production server there's a database per company, with each produced from a single template. CREATE DATABASE company_x WITH TEMPLATE company_template; Let's say I have updated the schema, is ...
1
vote
0answers
30 views

PostreSQL execute a default query when no row are returned [duplicate]

I need to calculate the average score of a list of userId. I have a table "user_place_score" (user_id / place_id/ score). Sometimes I do not have the score for a user_id so I need to fetch a "...
1
vote
4answers
92 views

PostgreSQL execute a default query when no row are returned

I need to calculate the average score of a list of userId. I have a table user_place_score: (user_id, place_id, score) Sometimes I do not have the score (no row for (user_id,place_id) in the table)...
4
votes
3answers
949 views

Clustered index performance consideration in MySQL & PostgreSQL

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write. ...
0
votes
1answer
535 views

PGpool Promote to master and Slaves wont replicate on failure

Sorry for the copy paste from stackoverflow, but this is probably a better place to post this. Version pgpool-II-pg93-3.4.0-3pgdg.rhel6.x86_64.rpm I’ve seen some chat online about this from a ...
3
votes
2answers
594 views

Optimise huge Postgres table for fast read

I'm about to create a Postgres table with 1.5 billion rows. The table will just have a single TEXT column. The table is effectively a blacklist. When a user of my software saves certain data, this ...
0
votes
2answers
124 views

Can postgresql WAL be regenerated or resent to the standby server?

Does anyone know if there's a way to resend or regenerate a WAL file that was missed by the standby server? During my testing I've gotten into a situation where the main server fails to send a file or ...
1
vote
1answer
384 views

How do I determine if pgAgent is installed on a Postgres instance?

I have inherited some problems and I need to determine if pgAgent exists on some number of installations (CentOS) and is running background jobs. These jobs could be run from cron, pgAgent, or an ...
0
votes
1answer
140 views

PostgreSQL SELECTs not returning correct result following recovery?

I took a copy of the current database from a live slave using pg_basebackup and recovered it to a brand new isolated computer following the instructions in the docs. Once the new database had started ...
2
votes
1answer
8k views

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT ...
1
vote
1answer
263 views

How to store day and time in postgres?

I have an events table in which various events could be happening in time slots each day. What I mean is each event has many time slots each day of the week. For example, event A can be have slots ...
1
vote
0answers
222 views

postgresql 9.3 with Ubuntu 14.04.1 LTS throw 's ERROR: invalid page in block 105 of relation base/16385/17407

This is Ubuntu 14.04.1 LTS with postgresql 9.3 in production, Now i got this below error's in log. # tail -f /var/log/postgresql/postgresql-9.3-main.log 2015-01-08 14:10:16 IST CONTEXT: automatic ...
-2
votes
1answer
83 views

Postgresql function to create table in other database

I Want a function to create Table in the different database , Database name is the argument for the function and table structure and names are static {No need to pass as the arguments}
0
votes
1answer
54 views

How can I always get Timestamp in certain format without having to explicitly define it everytime

When I run a select on a timestamp I get something like: 2015-01-02T23:22:36.000Z, 1/7/2015 11:03:31 AM In order to format properly I have to do: SELECT to_char(entered, 'MM/DD/YYYY HH12:MI:SS AM')...
5
votes
1answer
2k views

Scaling PostgreSQL TRIGGER(s)

How Postgres triggers mechanism scales ? We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s). Basically we would like to ...
3
votes
2answers
361 views

Is there a way to “SELECT and TRUNCATE” without losing new data?

I have an application that writes raw log files to a table called new in a PostgreSQL database -- lots of rows and lots of duplicate data. Via a cron job running every minute, I normalize the raw data ...
1
vote
1answer
105 views

“No Loop” Constraint for hierarchical data in postgresql

I've got table like this in postgres: id primary key parent_id foreign key on id some data columns I need no a constrain which prevents cycles on the parent relations. There might be more tree in ...
-4
votes
1answer
38 views

I cant connect to PosgreSQl throgh PG Admin

While i try to connect throgh PGAdmin this error message displayed " Error connecting to the server: FATAL: Peer authentication failed for user "postgres" "
0
votes
1answer
230 views

Restoring a database using Postgres data files instead of .sql files

I backed up my Ubuntu 14.04 system using rsync to a USB stick, but apparently it wasn't formatted for booting. The original disk has been erased and I put a fresh install of Ubuntu on it. In migrating ...
4
votes
1answer
852 views

Monitoring Amazon RDS with pgtop

I recently migrated a Postgres database to Amazon RDS and I am lacking a good live monitoring tool. I tried pgtop, but I can't get it to work on Amazon. I can see the header with the DB statistics (...
-6
votes
1answer
1k views

How do i convert bigint to int in postgreSQL [closed]

I wanted to find out negative values in one of the columns of type bigint. so when i do select *from tablename where columname(bigint) <0 ... its throwing me an error. SQL Error [42883]: ERROR: ...
3
votes
2answers
208 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', album....
-1
votes
2answers
191 views

Logging all the queries in postgres

I am using Postgres as my Database. I need to know whether I can extract all the queries that got executed with out logging them as logging all the queries will degrade the performance of database. So,...
4
votes
1answer
12k views

Postgres incomplete startup packet error

I am attempting to install Postgres 9.3 on Ubuntu 14.04 and I am getting an irksome error on startup. Here's what I'm seeing: $ sudo service postgresql restart * Restarting PostgreSQL 9.3 database ...
0
votes
1answer
146 views

Syntax Error In Postgresql Function [closed]

Why am I getting: ERROR: syntax error at or near "'SELECT '" In: CREATE OR REPLACE FUNCTION GetListings (lon double precision, lat double precision, schemas name[]) RETURNS TABLE( schema ...
2
votes
1answer
582 views

Postgres Restore Not Restoring Bytea Data

This is a very urgent situation. We have a postgres 9.4 database installed on a CentOs machine. We are trying to take a backup from a Heroku Postgres database and restore it to the database on ...
3
votes
2answers
358 views

CRUD operations on json data

I have a large chunk of json data associated with each id and I have to somehow get the data for my front end by querying through that large chunk. My TaskDetails table has a structure something like:...
0
votes
1answer
21 views

Selecting one variant out of different similar results

I have a table that looks as follows: Book Format Contents A html ... A markdown ... B html ... C markdown ... I want to return only 1 version of a book. ...
8
votes
1answer
370 views

unable to insert row at id, but row does not exist

Here is the weird problem i am facing. I am trying to enter data using following query insert into product_product (id, product_tmpl_id, make_equip, model_equip, name_template, serial_num_equip, ...
0
votes
1answer
37 views

PostgreSQL Date Check Costraint

How could I add a Check-costraint for Date type in PostgreSQL? For example, CHECK 'DateXYZ>01/02/98' A typecast is needed?
1
vote
2answers
1k views

Understanding multiple table join with aggregation

I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2 CREATE TABLE main ( id SERIAL PRIMARY KEY, name text NOT NULL ); ...
1
vote
1answer
119 views

Can not run PostgreSQL after drop table

I've deleted database from PostgreSQL and after this I can do nothing. Here what i receive with createuser $ createuser -D new_user createuser: could not connect to database postgres: FATAL: role "...
2
votes
1answer
769 views

Postgresql Rename Table without Updating View Definitions

I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off? So I rename the ...
1
vote
2answers
66 views

Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine. SELECT DISTINCT nom.pais_id FROM negocio.fabricante_producto_solicitud ...
0
votes
0answers
276 views

PostgreSQL replace primary key and converting non-serial to serial type [duplicate]

I'm basically building my own version of phpPgAdmin (if I get good enough to make it good enough I'll post it on a repository hosting service) and currently I'm revising my primary key tool to replace ...
1
vote
0answers
58 views

How to use the value from record like as varchar?

I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.): DO $$ DECLARE _roles refcursor; _onerole varchar; _record RECORD; ...
2
votes
2answers
199 views

Does changing a Postgresql table's tablespace compact it like “vacuum full” or “cluster”?

I need to reclaim disk space in a table in a Postgresql 9.3.2 database to be used for other tables. The standard suggestions to accomplish this are: "VACUUM FULL" or "CLUSTER" or copy the contents ...
1
vote
1answer
2k views

Postgresql memory usage [closed]

I'm trying to optimize my database (its quite heavy), I have external server only for it with 6GB RAM. I was looking for settings and saw that WORK_MEM is setted to 36MB. I thought it is possible just ...
3
votes
0answers
85 views

Can CTE sub-statements be prevented from executing at all?

Through this strategy, time consumed by queries for my application have dropped to extreme lows; however, some queries consume more milliseconds than necessary. The reason why is that upon ...
10
votes
3answers
2k views

Email address unique or primary key?

I am a newbie in databases. I read around and found out that it's probably not a great idea to use email address as primary key because string comparisons are slower which effects performance in ...
1
vote
1answer
79 views

postgresql: accept subdomain wildcards?

I want to accept all db connections from servers with domain mydomain.com so that all subdomains like tiger.mydomain.com, venus.mydomain.com will have access to the postgresql server. I've read that ...
4
votes
1answer
259 views

Rollback on \include error in psql

I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this: \include ../tables/...
1
vote
1answer
94 views

pgAdmin-III - Option to load a favorite automatically when opening query window

pgAdmin-III - Query window option Does pgAdmin-III has any option to load a default favorite file automatically when opening new query window? This would be much useful to have commit/rollback ...
1
vote
0answers
261 views

export data to xml by the use of query to xml then importing it using xpath

i have use QUERY_TO_XML function in order to export data to xml , after that i want to export this data. this data might contain reserved chars in the xml tags like "' & ..." ex electrical ' ...
0
votes
1answer
438 views

Concurrent SELECT and UPDATE in multiprocessing python

I have to randomly match about 20 million rows in two tables without using the same row twice using PostgreSQL 9.3.5. Both tables have the following structure CREATE TABLE de_sim_points_end ( -- ...
0
votes
1answer
85 views

Postgres Table Ownership

I'm new to PostgreSQL and I'm trying to change the table owner for a bunch of tables. I was able to change the ownership by logging in a the postgres user and executing alter table owner to user1, but ...
3
votes
2answers
261 views

How to compare an Oracle database with a Postgresql database?

We are currently migrating an Oracle database 10gR2 to a Postgresql database 9.3. Thanks to Hibernate, all tables can be recreated seamlessly. However, the Oracle database is huge. We are going to ...
15
votes
2answers
6k views

Refresh materalized view incrementally in PostgreSQL

Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed? Consider this table & materialized view: CREATE TABLE graph ( ...