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

learn more… | top users | synonyms (1)

0
votes
2answers
49 views

should I denormalize for user profiles?

Assuming the choice is to stay with relational model, how are tables structured in the case of large User profiles? All the examples I see consider only simple cases, such as a "profile" containing ...
1
vote
0answers
45 views

PostgreSQL doesn't use index on inherited tables

I have two tables in PostgreSQL 9.4.5 deployed on Amazon RDS: an append only, weekly partitioned, All Positions table. These store all position readings from about 100,000 sensors. There are about ...
1
vote
1answer
26 views

Postgres Access Roles

I'm trying to get my head around postgres roles. I want to create a database and have the owner role have all privileges on the database. I then want another role which cannot create tables but able ...
4
votes
1answer
31 views

Dynamic form builder database optimization Postgresql

Hi I have a project that create forms dynamically I use a model like the following: forms ----- id (PK) name (other fields) form_fields ------------- id (PK) form_id (FK to forms.id) ...
0
votes
0answers
16 views

Best approach to maintain a clone of a db in Postgres RDS

We'd like to maintain a separate database for our dev environment that closely resembles the current state of our production database. What is the best way to do this in RDS? Our current process is to ...
0
votes
1answer
49 views

pgbouncer 1.7 with TLS/SSL client and server connections

With settings borrowed from a similar server running Postgres 9.4.1 & pgbouncer 1.6.1, I have multiple users connecting to the database through pgbouncer on port 6543. I also have a second server ...
0
votes
0answers
25 views

Does Postgresql have a transaction log?

My question is pretty simple; does postgresql have something similar to the transaction log in SQL Server? If so, how do you access it to truncate dead transactions, or set what size it should be? ...
0
votes
1answer
25 views

psql variable holding text with multiple lines

This works as expected, \set x '''2\n3''' but this doesn't \set x '''2 3''' Is there a workaround for later example so I don't have to use \n instead of newline?
0
votes
1answer
66 views

how can we merge sum and count? [closed]

My result of the request of group by is : ref state quantity total PO3253 assinged 300.0 323400 PO3253 done 300.0 323400 When I delete state from ...
1
vote
1answer
7k views

Permission denied for relation <table>

I have ran the following SQL in psql: CREATE USER bspu LOGIN; CREATE DATABASE bsp OWNER bspu; GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu; \c bsp CREATE TABLE users ( id SERIAL PRIMARY KEY, ...
7
votes
1answer
111 views

Postgres with pgpool architecture

Below is an example pgpool architecture: This implies that you only need to have pgpool on a single server; is this true? When I look at the configuration I also see that you configure backends ...
2
votes
1answer
175 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
vote
2answers
40 views

How to set roles and users with correct permissions on Postgres? [closed]

My current database uat has as owner postgres. But now I would like to set roles and users with the correct permissions. Something simple like: roles: dba, developer, application users: dba1, dev1, ...
1
vote
1answer
27 views

What determines whether `pg_dump` includes a varchar length?

I work on a project where we regularly dump our Postgres database schema and commit it in the project repository. However, depending on which developer dumps the schema (via pg_dump), varchar columns ...
0
votes
1answer
86 views

PostgreSQL error moving data directory to home folder

I have a problem with the moving of my data directory of PostgreSQL. I found many different ways, but none worked. The most sensible way for me was: Stop PostgreSQL Copy data from old folder to new ...
2
votes
1answer
56 views

What are the consequences of dropping or changing the user name for the postgres DB account?

I feel confident I understand what's involved at the db level: If dropping, I'd need to create users for each sys admin, give them SUPERUSER, and then drop the postgres user. If changing the name, ...
3
votes
1answer
117 views

Problems while building plpgsql_check on Windows

I try to build plpgsql_check extension by @PavelStehule (Erwin Brandstetter recommended it to me in this answer) on Windows XP 32bit with Visual Studio 2010 (I used this article as a manual). However, ...
1
vote
2answers
45 views

What is table bloating in databases

Can someone explain what bloating means in database terms. For example what does it mean to say an index is bloated. I tried searching for it but there is no explanation of what bloating is . Only ...
-4
votes
1answer
34 views

How to return a single column value using a PostgreSQL function? [closed]

I want to return a single column value in PostgreSQL function from a PostgreSQL database. How can I achieve that?
3
votes
1answer
38 views

Find current owner of custom data type?

In Postgresql, when I try to run ALTER TYPE x ADD VALUE y; on a custom datatype I get an error stating PG::Error: ERROR: must be owner of type x. I know this can be solved by running ALTER TYPE x ...
3
votes
2answers
37 views

Restore PostgreSQL database (or db names) to new version, from files?

Scenario in short: a development laptop broke old HDD is still readable new laptop has PostgreSQL 9.4 instead of 9.1 both laptops use a flavor of Ubuntu Linux Question 1: Is my understanding ...
4
votes
1answer
1k 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 ...
3
votes
2answers
3k views

remove password requirement for user postgres

I understand that, upon installation, PostgreSQL has no password for its db root user (postgres): postgres=# select usename, passwd is null from pg_shadow; usename | ?column? ----------+---------- ...
0
votes
0answers
43 views

Updated Financial Database Design

I took the design that @Daniel Hutmacher suggested in question below: Financial database design I amended it as to what I need below: -- Companies CREATE TABLE companies_list ( [code] int NOT ...
2
votes
1answer
2k views

When will PostgreSQL execute archive_command to archive wal files?

I use the archive_command below to archive WAL files. Suppose I have touched the file archive_active already. When will PostgreSQL execute the supplied archive_command to archive WAL files? I know we ...
43
votes
5answers
42k views

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

I had to write a simple query where I go looking for people's name that start with a B or a D : SELECT s.name FROM spelers s WHERE s.name LIKE 'B%' OR s.name LIKE 'D%' ORDER BY 1 I was wondering ...
-4
votes
0answers
75 views

how to get one of the values in group by?

For the following tables stock_moves product_id purchase_line_id state product_uom_qty ----------------------------------------------------------- 4018 1230 assigned ...
2
votes
2answers
48 views

How to unite 2 subqueries to reduce retrieving data from cumbersome tables?

There are 3 tables: user, conversation, message. Table user: id, login, last_login_time (time of last user's login), ... Table conversation: id, user1_id, user2_id, ...
2
votes
1answer
46 views

PostGIS - indexing - speed up query

I have this query (called from PHP, hence the $ variables): SELECT a.name, St_AsText(a.geom), b.name AS name_b, b.id FROM a LEFT JOIN b ON b.id = a.b_id WHERE ...
2
votes
2answers
47 views

select/union output from two columns into three

I'm sure there is an easy answer to this, but my PostgreSQL isn't up to it at the moment. I have a table hostname_facts with three columns: hostname name value hostname is a list of ...
0
votes
0answers
23 views

Postgres: Recreate TRIGGER after replacing function?

Given a function like this: CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$ BEGIN -- do something fancy END; $$ LANGUAGE plpgsql; which is used in a TRIGGER: CREATE TRIGGER bar ...
1
vote
1answer
42 views

Performance issue using view vs. direct statement

I have a table with structure similar to this: table values ( id1 int, id2 int, value real, value_at timestamp, constraint primary key (id1,id2,value_at) ) I need to get the "current value" for a ...
3
votes
1answer
134 views

Error while restoring Postgres DB Dump

Using below command to restore the database Restoration command: cat my_db_dump.sql.gz | gunzip | psql -d new_database -U my_username Note: my_db_dump.sql.gz size is 4.5 GB, (extracted size will ...
1
vote
1answer
51 views

Index performance for CHAR vs VARCHAR (Postgres)

In this answer (http://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye: Also keep in mind that there's often a very big difference between ...
0
votes
0answers
6 views

Using start and end event logs to create a table/view containing spans between the times of each log [duplicate]

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | ...
2
votes
1answer
33 views

How to optimize this query on Postgres

I'm trying to make a relatively simple query, but it's taking much longer than I'd expect. I have an index in place, but it doesn't seem to be helping much. Here's the query. It sometimes takes up to ...
28
votes
5answers
55k views

How do I use currval() in PostgreSQL to get the last inserted id?

I have a table: CREATE TABLE names (id serial, name varchar(20)) I want the "last inserted id" from that table, without using RETURNING id on insert. There seem to be a function CURRVAL(), but I ...
1
vote
1answer
271 views

Why postgresql pg_dump exports a view as a table?

I am using postgresql 9.3 pg_dump tool for extracting only the public schema definition using: pg_dump -s -n public -h host -U postgres --dbname=db > ./schema.sql but when I check schema.sql one ...
1
vote
1answer
41 views

In PostgreSQL, will VACUUM reset the dead tuple count (n_dead_tup in pg_stat_all_tables)?

In PostgreSQL, will VACUUM reset the dead tuple count (n_dead_tup in pg_stat_all_tables)? The situation I'm seeing is that vacuum is apparently being run manually, once daily, and the n_dead_tup / ...
1
vote
1answer
56 views

PostgreSql : flatten json array data

From my current query, I obtain this jsonb data : values: "a1", ["b1", "b2"] And I want to flatten it on one level only, like this : values: "a1", "b1", "b2" Here is a simplfied way to get ...
3
votes
0answers
102 views

What's the point of prefixing each element in the column by its column number in multicolumn GiN indexes?

According to these slides which, I believe, are incorporated into the current implementation, each column element in multicolumn GiN indexes is prefixed by its column number and is stored separately ...
2
votes
1answer
26 views

OFFSET and LIMIT on complex query

I am doing a pretty complex query in Postgres 9.3.4: SELECT p.* FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600 , 17804, 20717, 27598, 27599}'::int[]) ...
0
votes
3answers
52 views

Using start and end event logs to create a table/view containing spans between the times of each log

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | ...
0
votes
2answers
117 views

Rows into Column

I want to make the above view vConsolPrchOrd like below: Database: PostgreSQL 9.2 I want to create a Crystal Report with this view (I asked about this on Stack Overflow).
9
votes
1answer
386 views

What is the overhead for varchar(n)?

I wanted to ask for the meaning of this fragment from Postgres doc regarding varchar(n) type: The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which ...
0
votes
1answer
30 views

Will this SQL snippet REINDEX my GIN index? Why is it renaming an old index onto another?

I have a large PostgreSQL 9.3 database. One index on one table is quite large and appears to be bloated. I am using pgcompact from pgtoolkit to reduce database bloat without heavy table locks. However ...
4
votes
1answer
114 views

Hash Join vs Hash Semi Join

PostgreSQL 9.2 I'm trying to understand the difference between Hash Semi Join and just Hash Join. Here're two queries: I EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid ...
1
vote
1answer
33 views

PostgreSQL: Restoring 1 file from a directory format dump

I've got a large'ish (> 700GB) PostgreSQL database. I'm working on importing it into AWS RDS. I realize there are modules that will cause problems, we're dealing with that separately, so for right ...
0
votes
0answers
47 views

How can a select max query return no rows?

I have a process that selects the maximum date from a Greenplum (PostgreSQL 8.2) table, and coalesces it to a default value. Once, and once only, this query returned 0 rows. How is this possible? Even ...
0
votes
1answer
29 views

Migrate Oracle BLOB to Postgres BYTEA or OID [duplicate]

After the BLOB columns of Oracle (11g r2) tables are migrated into a Postgres (9.4.5) table such as using Ora2Pg, I have two questions: 1) Which data type will the new column assume, is it BYTEA or ...