PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

1
vote
2answers
58 views

Return the id after insert or select

I want to build a function which will insert an email if the email value doesn't exist in the table and return the email_id of the row. How can I do this? Also how can I return the id if the email was ...
0
votes
1answer
67 views

How to make a single row result query to return multiple rows

I have the following WITH SQL Query which does select a set of filling stations along a route within the distance of 1000m. To have the measurement in meter I cast the geometries to geography. I also ...
2
votes
1answer
53 views

Retrieving latest record using DISTINCT ON is slow [duplicate]

Using Postgres 9.4. I have a table cartests with 5.5M rows. Each row is a car test: \d log.cartests; Table "log.cartests" Column | Type | Modifiers ...
1
vote
1answer
225 views

Designing a High Score/Leaderboard table

I'm looking for input on how to design a highscore table for a game I'm creating. The database I'm using is postgres. That's not really something I can or want to change (unless there's very good ...
0
votes
1answer
45 views

How does Amazon create the RDS for Postgres? Some kind of Docker?

I am really curious about the infrastructure of Amazon. When I create a new instance of RDS, it gives me a host, username, password.. Everything. Behind the curtains, how do they do this? It´s some ...
4
votes
1answer
78 views

Is there a way to insert multiple rows into a table with default values for all columns?

I can insert multiple rows into a table with default values for all columns the RBAR way: create table course(course_id serial primary key); do $$ begin for i in 1..100000 loop insert into ...
1
vote
1answer
92 views

Partial unique constraint spanning multiple tables in postgres

To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so: CREATE TABLE possession ( possession_id serial ...
0
votes
0answers
264 views

Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade

I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this: sudo pg_dropcluster 9.4 main ...
1
vote
0answers
30 views

PostgreSQL timezone setting

While I've spent many years as a SQL developer, I'm not really a DBA, but I'm the closest thing that my company has to one. We have recently started moving many of our PostgreSQL databases from Heroku ...
1
vote
1answer
48 views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT ...
6
votes
1answer
483 views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
1
vote
1answer
33 views

Pre-existing shared memory block (key 5432001, ID 32768) is still in use

I have been seeing this error lately that is causing the server to crash all of a sudden. I have seen elsewhere that the solution is to delete the postmaster.pid and postmaster.opts files and restart ...
2
votes
1answer
95 views

How to return a record from function, executed by INSERT/UPDATE rule (trigger)?

I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4! Here is an SQL fiddle. The ...
0
votes
0answers
24 views

Using Code to Access Database Dump via pgAdmin

I looked over this question on how to import a data dump using pgadmin and found the following code: CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$ tables RECORD; gmdeclare ...
0
votes
1answer
74 views

PostgreSQL and MONEY data type for currency values

For a project, I created a table with a column price MONEY NOT NULL column. And I thought it would handle decimals properly, unlike a floating number (i.e. IEEE rounding issues), but I end up having ...
1
vote
1answer
39 views

How do you drop an unnamed check constraint in a PostgresQL table?

I created a PostgresQL table but I added an unnamed check constraint on one of the columns: CREATE TABLE FOO ( id serial primary key, price_range smallint CHECK (price_range > 0), url varchar(255) ...
0
votes
2answers
49 views

What indexes should one use to optimize a PostgreSQL query with a JOIN depth of 2?

Disclaimer: I am relatively new to PostgreSQL. I'm wondering how to optimize a query that does 2 INNER JOINs. My scenario is fairly simple: Select Posts with a photo (Posts.photo IS NOT NULL) and a ...
5
votes
2answers
165 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
61 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
21 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
61 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 ...
3
votes
3answers
140 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
54 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 ...
2
votes
1answer
81 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
48 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
40 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
91 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 ...
1
vote
1answer
343 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
65 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
46 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 ...
-1
votes
1answer
35 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
33 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 ...
0
votes
1answer
77 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 ...
2
votes
2answers
75 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
31 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 ...
-2
votes
1answer
21 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
47 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 ...
3
votes
1answer
183 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 ...
-4
votes
1answer
92 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: ...
2
votes
1answer
83 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(' ', ...
-1
votes
2answers
43 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. ...
0
votes
1answer
352 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
35 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 ...
1
vote
1answer
64 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 ...
0
votes
1answer
17 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. ...
7
votes
1answer
98 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
29 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
86 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 ); ...
0
votes
1answer
46 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 ...