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.
0
votes
0answers
8 views
How to return multiple columns (each a composite type) in plpythonu
I have created a plpythonu function that should return a table with multiple columns. At present, it returns a single column with multiple components.
I came across this answer which is A) a little ...
0
votes
1answer
16 views
queying from two tables
I'm teaching myself SQL and I ran into a question that may be related to joining tables. I'm just a bit unsure.
Say, you had two tables, one listing customer's creditcard_number, and one listing ...
0
votes
1answer
10 views
Looks like a postgres backup/dump structure (A sort of a forensic/data archeology question)
I was given a compressed folder that contains number of subdirectories, and, among others, directories named .pgdir with number of numerically sequenced files in a <9999>.dat.gz format and one in a ...
0
votes
1answer
25 views
Trigger function using current row and current table name as variables
I have multiple tables that have the exact same column names, they only vary in their column values:
tbl_log_a
tbl_log_b
tbl_log_c
...
26 tables (from a to z). Each table has a trigger that calls a ...
0
votes
0answers
10 views
Store Procedure for adding cascade
I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add ...
-6
votes
1answer
33 views
Postgres Dump and Restore on Linux Terminal
I need to take a dump in postgres db and to be restored on some other server. please tell me the command in Linux with compressed and encrypted format.
1
vote
2answers
40 views
PostgreSQL trigger function that selects from %current% table
I have multiple tables that have the same column names, they only vary in their column values, like:
tbl_log_a
tbl_log_b
tbl_log_c
...
26 tables from a to z. Each table has a trigger that calls a ...
2
votes
2answers
29 views
Grouping in long tables
My application is running over Postgresql 9.4 and I found a problem and I don't
know how can I fix it.
I'm runing VoIp application, and we have a few tables that are too long (>60M of
rows) with all ...
1
vote
0answers
24 views
PostgreSQL trigram GIST vs. GIN indexes
I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes.
Initially, I started to use GIN ...
5
votes
3answers
58 views
Slow query performance due to temporary file?
Here is the query:
SELECT "products".*
FROM "products"
WHERE (status > 100)
AND "products"."above_revenue_average" = 't'
AND ("products"."category_id" NOT IN (5))
ORDER BY ...
7
votes
3answers
97 views
Constraint to enforce “at least one” or “exactly one” in a database
Say we have users and each user can have multiple email addresses
CREATE TABLE emails (
user_id integer,
email_address text,
is_active boolean
)
Some sample rows
user_id | ...
0
votes
0answers
15 views
pg_ctl promote is not working
I'm working on setting up a two-node PostgreSQL 9.4 environment. Right now I have test instances running on port 5532 on two identical Centos servers. node1 is correctly sending logs to a shared ...
0
votes
1answer
16 views
Convert two columns to json
I have a json column with a json array in the form of
[{key : "key", value : "value"}, {key : "key2", value : "value2"}]
that I wish to convert to a new form
{"key" : "value", "key2" : ...
1
vote
1answer
15 views
Passing array from one plpgsql function to another
I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
0
votes
0answers
16 views
Using pipelined order by while fetching only first ten rows
PostgreSQL 9.2
I have the table table:
id user_id amount
PK integer integer
and I need to fetch the first 10 rows as follows:
SELECT user_id, SUM(amount) s
FROM table
GROUP BY ...
2
votes
1answer
27 views
What exactly is Psycopg2?
This question is for a report I'm writing for school. Technically speaking, what is Psycopg2? In this tutorial they refer to it both as an "adapter" and "driver"
Psycopg2 is a DB API 2.0 compliant ...
2
votes
0answers
30 views
How to deal with large offsets in select?
Table jtest with 200k rows, each row contains jsonb { id: "<uuid>", key: <index> } (<index> is integer 1-200k incremented per each row). There is also btree index on data->'key'.
...
2
votes
1answer
27 views
PostgreSQL: ERROR: cannot execute CREATE DATABASE in a read-only transaction
I installed PostgreSQL and and its GUI, pgAdmin III.
Setup already had a default database postgres.
When I am trying to create a new table or database, it is showing an error:
ERROR: cannot ...
1
vote
1answer
32 views
Calculate column running total
Given the following schema and dummy data:
CREATE TABLE transactions (
id SERIAL PRIMARY KEY,
memo VARCHAR(255) NOT NULL,
amount INT NOT NULL,
tx_date DATE NOT NULL
);
INSERT INTO ...
1
vote
1answer
34 views
pg_restore: [archiver] did not find magic string in file header
I'm using PostgreSQL 9.1 and want to restore backup files generated with pg_dump:
sudo pg_dump -h 127.0.0.1 -U postgres --clean --inserts -E UTF8 -f out.sql database_name
This command generates a ...
0
votes
1answer
33 views
Postgresql pg_basebackup from slave didnt save WAL's
Good day
please tell me whether it is possible to shoot a backup with utility pg_basebackup from the slave server (where of streaming replication with the help of this utility pg_basebackup)
I have ...
1
vote
1answer
23 views
Calculate Difference in Overlapping Time in PostgreSQL / SSRS
I'm using PostgreSQL 9.2 and SSRS 3.0. I'm trying to calculate the difference in non-overlapping time entries in a timesheet.
My data looks like this:
I would like this to return:
I can get ...
2
votes
1answer
39 views
Why does PostgreSQL require a database connection?
A more precise version of my question might be, does the fact that PostgreSQL requires a database connection (as opposed to some sort of generic database cluster connection) follow directly from some ...
4
votes
1answer
49 views
Compacting a sequence in PostgreSQL
I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row.
Now I want to "compact" the table by restarting the sequence and ...
1
vote
1answer
13 views
Are toast columns compressed also in shared_buffers?
Reading here I cannot find a clear answer:
http://www.postgresql.org/docs/9.1/static/storage-toast.html
I need to know if setting storage to EXTERNAL for a particular column, I can gain a little ...
0
votes
1answer
22 views
Returning rows based on time of day respective to their timezone [closed]
I also posted this on Stack Overflow. I got no answers there so I'm hoping someone here might be able to help.
I have a table in which every row represents a user, I am also storing the user's ...
1
vote
1answer
22 views
Create one row for every user in a Postgres table
I have a bunch of rows of users in a users table, for example:
| id | name |
|----|-------|
| 1 | Chris |
| 2 | Max |
| 3 | Steve |
For each one of these users, I'd like to create a row in ...
4
votes
1answer
113 views
How to display Postgres version in the CLI pompt?
I whish to display the version of the server I am connected to in the CLI's prompt. From what I read in the documentation, it is possible to execute a shell command, it is possible to display a psql ...
1
vote
0answers
15 views
Do you need external caching for a Postgres database?
I am currently building a server and have just written a database connection using JDBC, Jooq, and added a DAO layer, which also handles transaction management. I remember that databases can cache ...
1
vote
0answers
19 views
Postgres with no clients still chatty on network
This may be a non-issue but I was doing some network monitoring on my Mac and noticed that PostgreSQL (9.4.4) was still sending and receiving packets even when I'm not aware of any clients (localhost ...
0
votes
0answers
31 views
How to make a query from a database with schemas to a database without schemas, and vice versa, with DBLINK in postgres
I have a DB (DB1) in postgres 9.4 who use DBLINK to query another DB (DB2), and vice versa, using views and functions.
But now i have to add 2 schemes to DB1 (CLIENT1 And CLIENT2) and i want when i ...
1
vote
0answers
24 views
Optimize PostgreSQL server setting for extremely wide tables
We do have a dedicated server where only PostgreSQL 9.4.4 server is running, 2 physical cores, 16 logical cores, 128GB RAM.
On this server we have quite atypical data - there are several tables with ...
-1
votes
0answers
13 views
which option is wise to choose as a back end service mongodb or postgresSQL [closed]
which will be better to user mongodb or postgres sql?
I just want to say that postgres does not support nosqlm but it supports RDBS.
while mongodb does not support RDBMS.
But if RDBMS in not there ...
1
vote
0answers
26 views
Query planner slow to use newly created index on database under heavy load
We have some heavily loaded databases with more than 1k requests / sec and have started to have problem with index maintenance.
Sometimes we need to update/recreate one of the indexes we have, for ...
0
votes
0answers
10 views
Get all the numbers between two numbers [migrated]
I want to display all the numbers (even/odd/mixed) between two numbers (1-9; 2-10; 11-20) in one (or two) column:
Example initial data:
| rang | | r1 | r2 |
--------- ...
2
votes
2answers
34 views
What's the quickest way to periodically export PostgreSQL data to a local file on Windows?
I have access to a PostgreSQL database that I need to get data from for another system. The other system is running Windows 7 and does not (yet?) have PostgreSQL installed.
Ideally, I'd like to have ...
0
votes
1answer
50 views
How does Postgres make its B-tree index?
I want to estimate how many reads the Partial index of PostgreSQL with the method B-tree requires, since I have not been able directly to alter the block size.
PostgreSQL manual about the index here ...
2
votes
2answers
28 views
postgresql trigram search gets much slower as search string gets longer
In a postgresql-9.1 database, I have a table table1 with ~1.5M rows and a colum label that is trigram-indexed (actually, the trgm is on lower(unaccent(label)) (unaccent is made immutable for the index ...
4
votes
1answer
26 views
Dynamic casting from text within plpgsql
Many tables in my database share a common layout, in particular they have a serial primary key named after the relation name. Updating these tables from within a web application often involves a query ...
0
votes
0answers
44 views
Indexes are not created exactly as like mysql
I have connected ofbiz with postgres. It will create tables automatically. But when i compare mysql and postgres tables, i can see that some indexes are missing in postgres. If i migrate it from mysql ...
1
vote
2answers
14 views
Amazon RDS Postgresql adding new extensions
I want to download jsonbx to RDS instance. RDS PostgreSQL Features Supported shows only built-in features. Does this mean there is no way we can install an extension to RDS which is not in the Feature ...
4
votes
1answer
177 views
How to model complex systems like networks in Postgres
I need help with modelling of a network involving switches (24 port, 48 port, POE, and a combination of those) and edge devices (computers, IP cameras, telephones). From an object oriented ...
0
votes
1answer
11 views
postGIS schema qualifier - best practice
There are a number of cases where the EXTENSION for postGIS is created iwth a schema qualifier.
CREATE SCHEMA postgis;
CREATE EXTENSION PostGIS WITH SCHEMA postgis;
why is that? what are the ...
3
votes
1answer
49 views
Step through multiple arrays in parallel with FOREACH
Is it possible to loop over multiple array with FOREACH in PL/pgSQL? In my case, 3 arrays with identical dimensions. How can I pass the elements like:
for(int i = 0 ; i < array1.length ; i++){
...
1
vote
0answers
16 views
Pass additional data in connection for use by PGSQL Trigger
I would like to add some auditing triggers to PG, to log all changes to tables. I have found an example trigger ( wiki.postgresql.com - 9.1+ audit trigger ), which gets me started.
What I would ...
2
votes
1answer
32 views
Extend a single week's win/loss ratio calculation to a whole season
I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project.
The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be ...
4
votes
1answer
45 views
How to deduplicate SELECT statements in relational division?
I have a query that does a lot of duplicate work:
SELECT visitor_id, '1'::text AS filter
FROM events
WHERE id IN (SELECT event_id FROM params
WHERE key = 'utm_campaign' AND value ...
1
vote
1answer
20 views
How to merge partitions in Postgres?
I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
1
vote
0answers
39 views
Splitting logging into a separate database
In a postgresql database for a asp.net mvc website I have 3 tables (visits, uservisits, hits) that have update/insert query's running on them on every pageview. Rows in visits (for all visitors) and ...
2
votes
0answers
32 views
How to implement search for world-wide Wikipedia/OSM places with alternative-names
I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages.
Thus, if you search for ...
