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

What difference between these 2 SQL styles?

If I have a simple table named: conn_log in my Redshift/Postgresql In the performance of view, what difference between these two commands ? select t1.wifi_name, (t2.sconn*100)::numeric/t1.ttconn ...
0
votes
1answer
69 views

How to remove duplicates and keep the one with the most additional information

I have a postgres table (postgres version 9.3.4) of addresses with duplicate addresses: Column | Type | Modifiers ...
0
votes
0answers
36 views

For Postgres, how would I define a custom function to generate primary keys?

In postgres, primary keys are typically generated using a SEQUENCE. I am hoping to generate a unique random 12 character string for use as a primary key. How would I do this in Postgres?
-3
votes
1answer
20 views

In Postgres, what is the relationship between a SEQUENCE and a SERIAL? [closed]

Postgres has the ability to define SERIAL datatypes and SEQUENCE but they seem to overlap in function. What are the main differences ?
0
votes
1answer
27 views

Migrate MySQL 3.23 Data

I have .FRM, .MYD and .MYI files from a MySQL (version 3.23 I think) database from back in the early 2000's. I managed to get the files into a Windows installation of 3.23 and then dump it, however I ...
0
votes
1answer
27 views

Are file locations stored in PostreSQL catalog?

I am trying to learn a bit of PostgreSQL, and I am currently trying to understand the catalog. I think that if it stores all the metadata of the DB, should it also store things like the file ...
0
votes
0answers
25 views

New Table vs Java Beans to allow user to dynamically change data representation?

I am building a database to store the assessment records of students, along with other relevant information and functionality like reporting on average scores etc. Currently my company uses Excel for ...
0
votes
1answer
43 views

Query across schemas in PostgreSQL [closed]

In PostgreSQL, I have a table which, due to sharding, exists across many schemas. In sub-optimal cases, I need to execute a query on all of these schemas (due to an exception which violates the ...
1
vote
1answer
54 views

Installing PostgreSQL and PostGis on Mac OS X

I downloaded and installed PostgreSQL and PostGis from http://www.kyngchaos.com/software/postgres, I work on OS X 10.10. When I go into the terminal now and type psql it says -bash: psql: command ...
1
vote
1answer
46 views

Postgresql query history hacking

I believe that PostgreSQL can log slow or unsuccessful queries. Probably I can set also PostgreSQL to log all queries executed. Contrarily, I am interested to know if there is a way that a malicious ...
0
votes
1answer
32 views

Simple join in postgresql [closed]

I have a simple join query below : select jobs.location_id from companies, jobs where jobs.company_id = companies.id and companies.name = 'name'; it doesn't seem to get the job location of ...
0
votes
3answers
40 views

Last occurance of time relative to now()

What's the most elegant way to ask Postgres for the last 4:05pm relative to now()? I have been writing a fairly elaborate CASE with date_trunc's and my guess is it can be accomplished in a much ...
0
votes
0answers
35 views

Forcing FK constraint to validated in system catalog

I have a big table (~60gigs, 35mill+ rows) that I need to add a FK to. Adding a FK directly to the table causes a validation which runs for about 30secs that I want avoid as it's a 30sec access ...
0
votes
1answer
68 views

Postgres privileges: psql \dp - what is about the plus-sign

I used psql-command "\dp" to find out the given grants. Versions on test-server: PostgreSQL 9.1.15 on armv7l-unknown-linux-gnueabi, compiled by gcc (Debian 4.6.3-14) 4.6.3, 32-bit Out of the ...
0
votes
0answers
36 views

Postgresql Hot Standby - How to determine it's fully mirrored?

I have set up a hot standby of a postgresql server. It all seems to be working, but I just want to be sure that I'm not missing something. In /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log I have ...
0
votes
1answer
56 views

Is there a timeout option for remote access to PostgreSQL database?

I'm working via pgAdmin III on a remote PostgreSQL database. After a not so long period (say 10-15 min.) without doing anything in pgAdmin, the connection automatically expires. I therefore have a ...
1
vote
0answers
67 views

Postgres-XL does not insert rows

I have successfully installed and started postgres-xl on localhost(Ubuntu 14.04). (Two datanodes, 1 coord) The problem is: 0 rows after insert CREATE TABLE test1 ( id bigint NOT NULL, profile ...
0
votes
0answers
32 views

Postgresql match all in full text search

Is there anyway that we can specify the tsquery to match all ? I have tried SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ '*'::tsquery; but it doesn't seem to work. It returns ...
2
votes
1answer
32 views

Converting a GraphDB schema to a relational database schema

I currently am using a graph database and am considering moving off of it to a relational database like Postgresql. My concern is that I have lots of relationships between entities (since this is ...
0
votes
0answers
16 views

A PostgreSQL user with SUPERUSER and all permissions to particular database can't read a specific column [closed]

I am trying to edit a website. I created a user for myself and granted all permissions and set up that user as SUPERUSER, using the user postgres. I still can't read a specific column (which is ...
0
votes
1answer
23 views

Behaviour of Bulk Insert in pgsql or any Database

I'm using PostgreSQL 9.2.4. I'm working with multiple tables with large number of data. I have two tables Table_One which has one of the field with varchar(250) and Table_Two with one of the field ...
6
votes
1answer
337 views

Why is a new user able to create tables in PostgreSQL?

I followed two tutorials to create a DB with: a fully privileged user link a read-only user link I then got a tip from CJ Estel's tutorial stating that "you may have inherited the ability to ...
1
vote
0answers
39 views

Query for a table with paging and filtering vs. CTE (common table expression)

Intro In PostgreSQL 9.3: I am building a query that fetches data for a table that supports sorting, filtering and paging. Think Customers for example, you want to show name, surname, some detailed ...
0
votes
1answer
59 views

no pg_hba.conf entry for replication connection from host

(Also posted to pgsql-general) I have been configuring a slave server that needs to connect to the host. Both the master and the standby servers have a pg_hba.conf that looks like this: # Allow ...
0
votes
0answers
26 views

How do I know Postgres streaming replication is working?

I have set up a standby server to follow a primary Postgres server following [this wiki page][1]. The issue is that I am not sure the primary server is sending anything to the replica because I don't ...
0
votes
1answer
37 views

pg_basebackup fails with “ too many connections for role ”replication“”

I am trying to set up a standby server and keep getting this error. My primary server has more than enough connections to handle the load: listen_addresses = '*' wal_level = hot_standby ...
0
votes
1answer
97 views

pg_basebackup: could not connect to server: fe_sendauth: no password supplied

I'm trying to set up streaming replication between two Postgres servers. The master server's pg_hba.conf file is configured this way: host replication ${REP_USER} 0.0.0.0/0 ...
3
votes
1answer
101 views

'ERROR: text search dictionary “unaccent” does not exist' during CREATE INDEX?

I'm running PostgreSQL 9.3 on Mac OS X Yosemite. I try to create an unaccent lowercase trigram index. To achieve it I did this: mydb=# CREATE EXTENSION pg_trgm SCHEMA public VERSION "1.1"; ...
-3
votes
1answer
21 views

Get currentmilliseconds in postgres

I'm working on timestamp/datetime in my application. I'm using postgres db. What is the way to get current milliseconds (something like 1412706599000) in postgres?
2
votes
1answer
176 views

Postgres 9.4.1 on Ubuntu 14.04.2 LTS fails to install properly

I'm trying to install PostgreSQL 9.4.1 on Ubuntu 14.04.2. I'm using the packages from apt.postgresql.org. The package installs but it fails running pg_createcluster. If I run manually, I get: ...
3
votes
1answer
53 views

How to correctly use PostgreSQL to limit multiple and/or concurrent executions of a task

Let's assume you have a task FOO that can be queued once every minute, and a pool of 50 workers that can be paused. The queue is paused for 10 minutes, and 10 FOO tasks are queued. When the queue is ...
1
vote
0answers
42 views

Replication Lag on Postgres AWS RDS Service

We have a single master/single streaming replica Postgres 9.3 db on AWS. The load is not terribly high - this is a development/staging environment. (The production shows similar metrics). Point is the ...
1
vote
2answers
74 views

How to select data in the same table on different rows that are empty?

I use this select on my SQLite database: SELECT attributeX FROM table GROUP BY attributeX HAVING COUNT(attributeY) = 0; My table: attributeX (type integer) | attributeY (type text) ...
-1
votes
1answer
57 views

Replace SQL Server with another DBMS [closed]

In our current application (C#, ASP.NET) we are using SQL Server database. My superiors are planning to move from SQL Server to another database considering the amount of investment involved with ever ...
0
votes
1answer
64 views

set session - custom variable to store user id

I want to store user id in custom session variable and use it (read) in trigger procedures to authorize user actions. I found something like this: set session "myapp.user" = '12345'; ... SELECT ...
4
votes
3answers
98 views

Scalable query for running counts of events within x previous days

I already posted this question on stackoverflow but I thought that I might get a better answer here. I have a table storing millions of events occurring to users: ...
1
vote
1answer
71 views

SQL query to sum a column prior to date and show all entries after that date

I have a database function that sum a column prior to a date and after that date show all entries one by one. that function return the data ordered by a common field and apply the sum by date every ...
2
votes
2answers
111 views

Check if contiguous date interval exists

I have a table in PostgreSQL which describes some events that have a start date and an end date: CREATE TABLE my_table ( event_id serial NOT NULL, start_date timestamp without time zone NOT ...
1
vote
1answer
26 views

purchasing item by client - how to ensure that user has enough funds?

I have to tables: users (id serial, coins integer not null default 0) purchases (id serial, price integer not null, user_id) When new row is inserted into purchases table I want to subtract amount ...
0
votes
1answer
81 views

How to temporarily disable foreign keys in Amazon RDS PostgreSQL?

I'm migrating existing test environment to Amazon RDS PostgreSQL. The test framework has a feature of reloading data in certain tables to an earlier state. For this it disables foreign keys, deletes ...
1
vote
1answer
37 views

How to create INOUT assignment cast on Amazon RDS PostgreSQL? If not possible, then why?

I'm migrating existing application to Amazon RDS PostgreSQL. The application has cross-database support and for compatibility reasons it includes the following SQL: CREATE CAST (varchar AS numeric) ...
0
votes
1answer
108 views

Postgresql 9.4.1 stuck all queries when making multi updates

Before i used 9.3.5 and servers with ubuntu 12 with 32GB memory. After upgrade to 9.4.1, with more power server 60GB memory on each in wall replication and ubuntu 14, started get db stucks when run ...
1
vote
1answer
25 views

Upgraded Postgres; how to access now-missing databases?

My local connection on my Mac (10.9.5) wasn't working to my Postgres (9.3) database (and therefore I couldn't actually export the data), so I tried upgrading it to 9.4. Luckily I can now connect to ...
-1
votes
1answer
24 views

PsQl data dump import [closed]

Psql databasename < file.db is the recommended method to import to a database. This doesn't work why?
1
vote
2answers
53 views

Slow Select over View

My simple SELECT statement on a view in Postgres 9.3.1 is pretty slow. The receipts table has 20 million entries and the other one has around 17k. But I still think it should be faster than ~16 ...
0
votes
0answers
68 views

Slow query over 10mil rows with group by on indexed column

I've got a slow query which I want to optimize. SELECT pand.bouwjaar AS construction_date, count(*) FROM pand AS pand WHERE pand.begindatumtijdvakgeldigheid <= 'now'::text::timestamp ...
0
votes
0answers
25 views

Postgres count of delimited items in bytea encoded string

How would you get a count for each of $ delimited Individuals in each of the group_names? (Example: SomeGroup has 1, This Is Another Group has 8) thedb=# select group_name,encode(group_data,'escape') ...
1
vote
1answer
57 views

PostgreSQL on RDS: How to bulk import data with FK constraints?

Is there an efficient way to bulk import data into a PostgreSQL 9.4 database on RDS that makes extensive use of foreign key constraints? The standard method of using pg_restore fails because RDS does ...
0
votes
0answers
44 views

Online Recovery for pgPool-II “cannot find xlog functions”

I am trying to set up Online Recovery for pgPool-II [Replication,Load Balancing, Connection Pooling] modes enabled. I followed the sample provided on the pgPool-II website for the PITR recovery ...
3
votes
2answers
196 views

How to get exception context for a manually raised exception in PL/pgSQL?

In Postgres, we get the "stack trace" of exceptions using this code: EXCEPTION WHEN others THEN GET STACKED DIAGNOSTICS v_error_stack = PG_EXCEPTION_CONTEXT; This works fine for "natural" ...