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
3k views

Official PostgreSQL Capitalization conventions [closed]

Is there an official PostreSQL convention regarding capitalization in DB, Table and field names? The examples on the official site suggest a lowercase and _ word separation, and I wonder whether this ...
1
vote
1answer
23 views

array_length if array is empty behaviour

Please explain to me this behavior. This SQL finds elements where ids (type is array::bigint) is empty. SELECT * FROM rises WHERE ids = '{}' -- finds, e.g., 9 rows This SQL finds no rows: SELECT ...
1
vote
0answers
15 views

How do I configure Postgresql stop words to reduce to -ism and -ist?

I want to reduce communism and communist to the same stop words. Currently, that's not the way it's working. # select to_tsvector('communism communist'); to_tsvector ...
1
vote
1answer
31 views

Auto start pgAgent

When my windows server 2008 start, both services postgres and pgAgentstart because are in automatic. But if my db is frozen and I need to restart the postgres service, the pgAgent is also stoped, ...
0
votes
0answers
13 views

Diagram conventions and standards for Database modeling pgmodeler [duplicate]

I am using pgmodeler for data modeling in postgres.. Apart from naming conventions are there any other standards for drawing the diagram... Like UML modeling
1
vote
1answer
141 views

How to measure or estimate the streaming replication lag in Postgres 9.2

I've been asked to reverse engineer an app that uses postgresql in the back end. I can see that there is some database replica tion going on... based on my reading, I *think it's called streaming ...
32
votes
2answers
34k views

Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB. How can I ...
3
votes
1answer
517 views

PostgreSQL server problem in Windows XP

2014-10-01 09:22:49 IST LOG: database system was interrupted; last known up at 2014-09-30 18:30:24 IST 2014-10-01 09:22:49 IST FATAL: the database system is starting up 2014-10-01 09:22:49 IST LOG: ...
2
votes
1answer
2k views

How to create Postgres DB with case insensitive collation

I am using the postgres version 9.4.1 64-bit on windows 7 64-bit. I need to create DB with the setting "case sensitive = OFF" but couldn't make this work. I refereed already many question on SO and ...
1
vote
2answers
36 views

Aggregate function to return any (first) encountered value

How do I tell PostgreSQL to return first encountered value instead of an aggregate column? Table "public.cache" Column | Type | Modifiers | Storage | Stats target | ...
17
votes
2answers
11k views

Configuring PostgreSQL for read performance

Our system write a lots of data (kind of Big Data system). The write performance is good enough for our needs but the read performance is really too slow. The primary key (constraint) structure is ...
3
votes
0answers
45 views

Postgresql Filter Slows Query

I've got a query like this: SELECT count(*) FROM acs2014_5yr.geoheader as g JOIN tractcache as c ON (c.tract_id = g.state||g.county||g.tract) JOIN tiger2014.tract AS t ON (t.geoid = c.tract_id) ...
1
vote
0answers
22 views

optimize a window aggregate function

First, the query SELECT tc.numreg, com.numdep, tc.numcom, tc.numiris, sum(tc.conso_climat_reel)/sum(sum(tc.conso_climat_reel)) OVER (PARTITION BY tc.numreg) AS ratio, ...
0
votes
0answers
11 views

Different insert operations with the same data and cache

I want to perform several different insert operations into a Postgres database. PostgreSQL Version 9.5. Operating system: SLES 12. I have 200000 rows of data. It is the same for every different ...
25
votes
1answer
45k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
3
votes
2answers
44 views

Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT (*) AS "count" FROM "Posts" AS "Post" WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' AND "Post"."new" = TRUE; Index: CREATE ...
0
votes
1answer
10 views

postgresql fulltext slow

I am trying PostgreSQL for the first time, but am finding the fulltext search very slow. My database has a table with 5 columns and 5 million rows CREATE TABLE table_test ( id integer NOT NULL, ...
3
votes
3answers
2k views

How do I use an Ispell dictionary with Postgres text search?

Postgres can use Ispell-compatible dictionaries in text search, but does not provide the required files.
2
votes
1answer
13 views

Backup a hot standby database (streaming replication) whilst it is in recovery

Synchronous Streaming Replicating is set up between a master and a hot standby database. It seems like a good idea to perform daily backups on the standby database to avoid any performance issues ...
1
vote
1answer
35 views

Duplicate rows - how to remove one? [duplicate]

I have a pretty large table (~114 million rows) containing OS MasterMap data. This is freshly loaded data in a new table. When trying to set the primary key, I get this error: ERROR: could not ...
0
votes
0answers
6 views

Export data from Hive to Postgres

I would like to save data that I fetch via Hive on a remote Hadoop cluster to my local machine in a Postgres database. I am currently downloading the data as files but I have no idea on how to ...
3
votes
2answers
315 views

What function to use with this timestamp?

I have timestamp (stored as BIGINT) format I cannot turn into a human-readable format: 1462975819250 It points to a date in 2016-may-11th. However function to_timestamp() turns this into: ...
1
vote
1answer
39 views

Store table names to reference other tables

I'm designing a webapp where users can add widgets to their pages. Right now I have only two types of widgets that are different in nature, so they have different attributes, but both of them are used ...
2
votes
1answer
353 views

Postgres query plans and tuning parameters

I have a query for which the explain plan indicates that the cost for using a seq scan is lower than the cost for using an index that I have set up. However the actual time for the query is 20 times ...
4
votes
2answers
206 views

Optimize query to find top N users who commented on a post

Problem Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
2
votes
1answer
22 views

Table design for ticket sales

I'm designing a database for a ticket sales system in PostgreSQL, where the admin user can create a batch of tickets for a specific event. All tickets for a given event have the same attributes (like ...
5
votes
2answers
9k views

Very slow DELETE in PostgreSQL, workaround?

I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign ...
2
votes
1answer
24 views

Limited utility in RECURSIVE VIEWS?

In the docs for recursive queries with WITH, you'll find this Recursive queries are typically used to deal with hierarchical or tree-structured data. This seems to be true, but how does this ...
0
votes
1answer
16 views

Finding time ranges in multiple rows

I have a table with (suppose) just 2 columns: - fecha (a date) - ignicion (0/1 representing boolean state) The goal is to retrieve which periods of time was ignicion held at '1', for such task I ...
1
vote
0answers
22 views

What are the pros and cons of storing two values in an array instead of two columns?

I have a project that plans to store an away team and a home team. I'm wondering if it is better to store the teams in a single array column like this: {Harvard,Colorado} Or create away_team and ...
0
votes
0answers
11 views

SQL query for setting value

I found this example for configuration databse tables: http://stackoverflow.com/questions/10204902/database-design-for-user-settings Can you show me some example for SQL query for getting ...
2
votes
0answers
33 views

Postgres update query complaining about text value when only NULL values are passed

I am using Postgres 9.4.6. Is there any reason why the below update query would execute successfully here:- UPDATE "Predictions" SET "userid" = c."userid", "Fixture_No" = ...
0
votes
0answers
20 views

Backup options for database with large objects

I have a database that includes a table with references (as oids) to large objects that have been stored in the database via the JDBC Large Object manager. I need to make regular backups of the ...
0
votes
0answers
40 views

Postgres wont install on windows 7 64 bit (Failed to load SQL modules into table)

I have been trying to install standard postgres 9.3 onto a windows 7 Machine. every single time it gives the same error: Failed to load SQL modules into table I have tried (from this question ...
5
votes
2answers
4k views

How to partition existing table in postgres?

I would like to partition a table with 1M+ rows by date range. How is this commonly done without requiring much downtime or risking losing data? Here are the strategies I am considering, but open to ...
0
votes
1answer
30 views

Adding and populating column in Postgres with data from existing column

I have a TEXT column in one of my existing database tables that holds both domain names and IP addresses. I'd like to add a new column to the existing table that's of type INET and populate it with ...
7
votes
2answers
154 views

Normalizing a table with a field that generally uniquely identifies a row, but is sometimes null

forgive me if this has been asked and answered before. I'm roughing out a schema for an inventory management system, to be implemented in PostgreSQL. All of our products and services have a sku. ...
0
votes
1answer
22 views

Postgresql maintainenace queries fail with foreignkey violation

I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables ...
0
votes
1answer
39 views

How do I find the day of week of a date-time field in postgres?

How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While SELECT EXTRACT(DOW FROM '2011-07-07') works, the ...
5
votes
2answers
9k views

Is Data Warehousing possible in MySQL and PostgresSQL [closed]

I am fairly new to DB design and development. My requirement is simple drilldown/slicing based on time and language of words (in a language on a particular day). However, my db is mysql. But so far I ...
3
votes
1answer
32 views

What permissions are required to return rows from information_schema.schemata?

In postgres (I'm on 9.3.4) what permissions are required to return rows for select * from information_schema.schemata The 9.3 documentation says "are owned by a currently enabled role". Does that ...
1
vote
0answers
43 views

My simple FK constraints appear to have been ignored. How?

I have two tables, device and platform where device.platform_id enforces the following constraint: Foreign-key constraints: "device_platform_id_fkey" FOREIGN KEY (platform_id) REFERENCES ...
0
votes
0answers
20 views

Postgresql: find out all columns that have particular string and count the number

I want to create a function like this: enter table name and a particular text, then return a table that have 3 columns the : table name , column name, the number of that column have the particular ...
5
votes
3answers
2k views

Transactions within a Transaction

What behaviour would PostgreSQL display if for example the script below were called BEGIN; SELECT * FROM foo; INSERT INTO foo(name) VALUES ('bar'); BEGIN; <- The point of interest END; Would ...
0
votes
0answers
18 views

PostgreSQL 9.3 overlapping pg_dump commands

I'm wanting to do a pg_dump or pg_dumpall of a database every 10 minutes. Unfortunately the database takes longer then 10 minutes to write out. This post confirms that database dumps produce ...
1
vote
1answer
27 views

Show Dollar Amount By Name On A Month To Month Basis

I want to show dollar amount by name on a month to month basis, but my syntax gives me an entry for each different dollar amount. Below is what I get from my query (subset of data) Name Value ...
3
votes
1answer
195 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(' ', ...
-4
votes
2answers
31 views

Postgres relationship statement [closed]

Lets say I have the table parents. A parent has many kids and a kid has many toys What would be the best way to do a SELECT statement for the parent and grab all of kids associated with that parent ...
4
votes
1answer
74 views

Persistent inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
0
votes
2answers
26 views

Group By Month With Overlapping years

I want my query to show all months grouped together, regardless of the year. For example, I would want this to be the output: 01/13/2015 01/01/2016 02/07/2015 02/01/2016 This is sample DDL - what ...