PostgreSQL is a powerful, enterprise class, open source RDBMS. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability and data integrity. It runs on all major operating systems, including Linux, UNIX and Windows. It prides ...
0
votes
0answers
7 views
How to order by Levenshtein distance?
I want to order a table according to a char field using Levenshtein distance or similar because some entries may contain underscores or grammar errors.
It is a table with all the books I plan to read ...
0
votes
1answer
23 views
Duplicate records on joining
I have two tables books and books_data. books can have multiple books_data, books_data can have only one book. books_data table has date column by which I want to sort the books table. When trying to ...
0
votes
0answers
28 views
How to select column names that contains specific value from other table's column
Suppose I have table items and logs. Both tables have col1, col2, col3, col4... col(n).
col1.. col(n) in table items simply specifies key name (col1 = "beef", col2 = "chicken", col3 = "bacon")
...
1
vote
0answers
24 views
ORA-00937 when calculating a sub-value in an aggregate select statement
The problem described below applies to Oracle 11g databases. I also tested the statements on MySQL and PostgreSQL with no problems of the kind mentioned in this question.
I want to use an ...
0
votes
2answers
18 views
Best way to update the content of large lookups tables
In our application we have a about 20 tables that contain lookup information (read only data that the application doesn't modify).
Nightly we'd like to update this information by deleting all the ...
1
vote
0answers
32 views
SELECT query with DISTINCT on a table-structure for graphs is very slow
I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph.
CREATE TABLE edges
(
node_from bigint NOT NULL,
...
0
votes
1answer
8 views
PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid
I have created DB instance on RDS and used below function it gaves error while with same postgreSQL version on local development machine works fine.
create or replace function uuid() returns uuid as ...
2
votes
3answers
264 views
Updating every cell of of a table?
I have a table with m columns and n rows. The ETL tools I
was using wrote an empty ('') to every cell without a value and I would like
them to be NULL instead. This got me thinking if it would be
...
1
vote
1answer
26 views
Change existing column in PG to auto-incremental primary key
I have a database in Postgresql, which was migrated from SQL Server (only data).
On SQL Server, a table from this database has these columns:
measure_id
datum
measure
where measure_id is ...
2
votes
1answer
35 views
(How) can I return the most specific row on a join?
I'm trying to work out a query that can parse through a table of prices (and exceptions to those prices) to yield the applicable price for a product, ideally using a join. I'm on PostgreSQL. For ...
1
vote
0answers
24 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 ...
0
votes
1answer
28 views
Getting rid of partitioning in Postgresql 9
it turns out that our approach with list partitioning was not the best idea. We have way to much small partitions and a lot of time is wasted on partition choosing by query optimizer.
What's the ...
0
votes
1answer
41 views
Use if exists in postgresql [on hold]
I know about the exists feature, but that does not help me with the given situation. What I want is:
Check if some row exists, and if exists then check for an another row if row exists.
Here's ...
-1
votes
0answers
14 views
pg upgrade saving database definition taking time
I am in process of a pg_upgrade from 8.4 to 9.3. I am using this technique:
http://momjian.us/main/writings/pgsql/pg_upgrade.pdf
The upgrade has been running since 250 hours, and it has been on the ...
2
votes
1answer
26 views
Postgresql: How do full_page_writes help prevent data loss?
From the postgresql docs
[full page writes are] needed because a page write that is in process during an operating system crash might be only partially completed, leading to an on-disk page that ...
0
votes
0answers
9 views
PGBouncer configuration
Let's assume that, for a Django website, I have a PostgreSQL server with only one database in it. max_connections is set to 200.
Apart from serving the website, I run each day a local process that ...
0
votes
0answers
18 views
PostgreSQL SELECT and JOIN question [duplicate]
Trying to fix the previous post text formatting issue there was and I did not recognize:
I'm using PostgreSQL under ubuntu 14.04. I have two tables as below:
create table material_base_name (
...
0
votes
0answers
24 views
How to deal with finding nearby locations when given a zip code or city?
I am using PostgreSQL and PostGIS for this. I have downloaded dumps from Geoname, and built my tables from allCountries, alternateNames, and countryInfo. I've created my PostGIS geometry column, and ...
0
votes
0answers
25 views
SQL JOIN excluding duplicate
I'm using PostgreSQL under ubuntu 14.04. I have two tables as below:
create table material_base_name (
id bigserial,
base_name varchar(50),
constraint pk_materials_base_name primary key (id)
);
...
1
vote
1answer
39 views
joins with “could be blank” conditions
Question is most probably stupid but I am stuck on this and cant think anymore(Pissed Off).
I have these tables-> employee(has a column foreign to location),location and location_flag. And I need ...
2
votes
1answer
78 views
Why is CTE open to lost updates?
I don't understand what Craig Ringer meant when he commented:
This solution is subject to lost updates if the inserting transaction rolls back; there's no check to enforce that the UPDATE affected ...
0
votes
0answers
23 views
postgres: access to schema objects within a function
Here's my problem:
I have Schema A, owned by User 1, that owns all objects.
I've created schema B, owned by User 2.
I have granted User 2 all privileges to the objects owned by User 1.
When logged ...
1
vote
1answer
12 views
Removing a Key From HSTORE in Postgresql
At the moment I have an hstore that describes the properties of various items. When searching I need to remove values that are unique to a each item (such as serial number, vin, and so on). According ...
0
votes
1answer
28 views
When is the postgres auto vacuum executed
I am using an older version of postgres(8.4.20).
I know that the auto vaccum process is executed frequently to free disk space of queries that deleted or updated data in tables.
I have a database ...
1
vote
3answers
48 views
Longest matching suffix while using aggregate functions
Background
Looking to find the longest matching string suffix.
Setup
Consider the following fiddle:
CREATE TABLE noun
("label" varchar(10))
;
INSERT INTO noun
("label")
VALUES
...
1
vote
1answer
23 views
Tables names in a supertype/subtype schema
I have 3 types of users in my app. The users share some common attributes, but they also have quite a bit of stuff that pertains uniquely to each user type. I have a person table that contains limited ...
1
vote
2answers
30 views
SET ROLE via parameterized query
In the very helpful response I received to a previous question, I am trying to write some JDBC code that first sets the role to a specific user before executing subsequent queries. On the safe side I ...
1
vote
1answer
25 views
Upgrading 8.1 to 9.3 - using statement log and WAL to check compatibility?
We're migrating from postgres 8.1 to 9.3, since we need streaming replication badly. All the application developers have deserted the company a long time ago, way before my time here, so there is ...
0
votes
1answer
39 views
Check for blocked queries periodically and cancel them
I have series of update queries which are executed every 5-10 seconds. Sometimes it happens that one of these will block all the pending update queries as for some reason it just freezes.
Since I can ...
0
votes
2answers
38 views
PostgreSQL : dependencies on a schema
I need to delete a schema in my PostgreSQL database. I would like to make sure that the schema is not used in another schema (for instance : this schema is used for a view in another one). Is there a ...
1
vote
0answers
16 views
Unsupported languages in Postgres Full text search
My web application supports 3 languages, English, Traditional Chinese, Vietnamese. I understand that Traditional Chinese and Vietnamese is not supported in Postgres' Full Text Search.
If I were to go ...
0
votes
2answers
28 views
pg_stat_activity entries with no corresponding process on the client
If I see an entry in pg_stat_activity for a client_addr:client_port combination where there is no process bound to that port on the indicated client, what does that mean? Just that the client process ...
0
votes
2answers
43 views
Postgresql. Generate sets of random integers
With this command I can generate 16384 random integers between 1 AND 200,000.
SELECT generate_series (1,16384),(random()*200000)::int AS id
I want to generate 10 sets of such integers. Each set ...
1
vote
1answer
25 views
PostgreSQL Common Table Expressions vs a temporary table?
The PostgreSQL documentation on WITH shows the following example:
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), ...
2
votes
2answers
219 views
PostgreSQL maximize performance SSD
I will have a huge PostgreSQL 9.3 database with many tables with more than 100M entries per table. This database will be basically be read-only (once I fill all the necessary tables and build the ...
0
votes
2answers
20 views
Postgres: error message “does not exist” when dropping existing trigger
I have a trigger associated to a table. When i drop it via
DROP TRIGGER IF EXISTS myTrigger on dummytable;
postgres tells me
NOTICE: trigger "mytrigger" for table "dummytable" does not ...
0
votes
3answers
52 views
Can I perform a MAX over a pair of columns?
I would like to execute the following:
SELECT person, MAX( (date, priority) ) FROM table GROUP BY person;
It would return a person, date and priority row for each distinct value in the person ...
1
vote
2answers
94 views
Slow querys on billions-rows-table // index used
Since I'm a young developer and not realy skiled in using databases (PostgreSQL 9.3) i ran into some problems with a project, where i realy need help with.
My project is about collecting data of ...
2
votes
0answers
35 views
PL/pgSQL - Indexes vs Collation vs Pattern Ops
I'm using PostgreSQL 9.3.
Having already read articles and answers on DBA stackexchange:
Why would you index text pattern ops on a text column
PostgreSQL documentation: Indexes - Opclass
Pattern ...
2
votes
0answers
39 views
Checking for multiple identical values in a Posgresql array
I have a simple table in Postgresql:
CREATE TABLE data (id integer, values integer[]);
INSERT INTO data VALUES (1, '{1,2,3,4,5}');
INSERT INTO data VALUES (2, '{1,1,2,3,4,5}');
INSERT INTO data ...
1
vote
1answer
34 views
Problem with LISTEN - NOTIFY mechanism in PostgreSQL
I'm struggling with PostgreSQL LISTEN - NOTIFY mechanism.
I open two consoles and connect to my database using psql client:
psql -U postgres test_db
In the first console I subscribe to some ...
3
votes
1answer
34 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: ...
0
votes
0answers
13 views
What is the recommended pgpool mode?
I'm trying to determine which pgpool mode to use (http://www.pgpool.net/docs/latest/pgpool-en.html#config). In order of importance my requirements are:
All nodes accessible via shared public ...
1
vote
0answers
21 views
Select all foreign key relationship chains from any set of potentially gapped table names
I am using this query to find all foreign key relationships:
SELECT con.relname AS child_table,
att2.attname AS child_column,
cl.relname AS parent_table,
att.attname AS parent_column
...
0
votes
1answer
13 views
Getting information on users payments via subscriptions
My current table db design looks like this :
- User has one subscription
- One subscription has many payments
Table subscription has a user_id, which is FK to user table.
Table payment has a ...
3
votes
2answers
45 views
How to save an interval of years in postgresql
I have to save an interval of years, like:
id intervalYears
1 2014/2015
2 2015/2016
3 2016/2017
And so on. Those years are like accademic years, so the first year must be lesser then the second, ...
1
vote
1answer
22 views
use variable to determine the name of a column in the select in postgre
I'm trying create a update for a new structure of tables from a old table, but not using functions. I'm trying create a script to it.
The old table is like this:
-- old table (OldTable)
name| col_a | ...
0
votes
0answers
19 views
Importing CSV into multiple tables using PostgreSQL
I have a very big CSV file with the following format:
TAG,TIME,VALUE
as an example row:
footag,2014-06-25 08:00:00.0,3400.0
I used to import it easily inside PostgreSQL 9.3 using the following:
...
0
votes
0answers
9 views
when install pgpool is also needed install hearbeat and watchdog
I am trying to get the PostgreSQL services High Availability (HA) and then I am using pgpool-II. When I install and configure pgpool, the WATCHDOG section seems to doesn't works, then I have a ...
-1
votes
0answers
13 views
Error regarding SHMMNI parameter in your kernel durign the install of Postgres 9.3
During the install of Postgres 9.3 I get...
FATAL: could not create shared memory segment: No space left on device
DETAIL: Failed system call was shmget(key=1, size=36, 03600).
HINT: This error does ...
