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

how to update new parent node in the middle of the postgresql ltree by some simple update SQL statement? [duplicate]

I follow this URL to make some SOP for my colleague. How to update the parent/child of all rows in the tree (ltree)? However, my colleague hope to use some simple statement about "UPDATE" for adding ...
0
votes
1answer
21 views

Partition elimination and date variables

I have a table that is partitioned on a date column, with a default partition. In our deletion process, we drop partitions that are older than the retention date and then delete records that are older ...
1
vote
4answers
65 views

Build JSON object from one-to-many relationship data in a single query?

I have a PostgreSQL 9.5.3 DB with tables like this: container id: uuid (pk) ... other data thing id: uuid (pk) ... other data container_thing container_id: uuid (fk) ...
0
votes
1answer
31 views

PostgreSQL use NEW in query for INSTEAD OF trigger

I'm having trouble getting an INSTEAD OF trigger to work correctly, and I think I've misunderstood how to use NEW. Consider the following simplified scenario: CREATE TABLE Product ( product_id ...
0
votes
1answer
16 views

Using varchar_pattern_ops in a multi-column index in Postgres

I'm using postgres 9.5 If I have a table with 2 columns like so: CREATE TABLE mystuff ( somestring character varying(256), timestamp_ timestamp without time zone NOT NULL ) Will this multi-...
0
votes
0answers
18 views

Super polymorphic event schema

I want to be able to track every action a user takes on my site. An action can originate from a visitor or a user (both of which are human). An action can affect a subject (visitor or a user) An ...
1
vote
1answer
31 views

Speed and size with hidden index in postgresql

I have a table with 1M rows without index, and i don't understand why when i go for : SELECT * from table WHERE id = 954000 i get the result instantly ! I have no index how its possible to get me ...
3
votes
1answer
44 views

Slow query on primary server runs fast on hot standby

I have a primary PostgreSQL 9.3.4 server with 64GB of RAM that is replicated using streaming replication to a hot standby server with 32GB of RAM. My problem is as follows: I've detected a query that ...
0
votes
0answers
23 views

Postgresql promoting slave server to master

I am trying to setup Postgresql(9.4) master-slave cluster. I am using the following tutorial: http://linux.xvx.cz/2014/10/loadbalancing-of-postgresql-databases.html The issue I am facing at the ...
3
votes
0answers
36 views

Postgres 9.1 query fast second time

Postgres version: PostgreSQL 9.1.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Open psql and run query. Takes 80ms. Run same query again. Takes 11ms ...
0
votes
2answers
43 views

Slow UPDATE FROM Query in Large Table

I'm currently trying to merge two very large (11 million rows) tables, and my query has been running for over two days with no end in sight. My basic query is: UPDATE us_demand SET ... FROM ...
1
vote
0answers
15 views

#deleted in all fields using ODBC driver between PostgreSQL and Access 2013

I planning to use MS Access (2013) as a front end to a PostgreSQL database server (version 9.5.2). However, when updating a table in Access the new row appears with “#deleted” in every column until I ...
-4
votes
0answers
38 views

Retrieving the data based on id from table contains 2 million rows [closed]

I have one table/collection contains 2 million rows/document (id(20 alphanumerical digits), name, ... etc). From that table I want to retrieve data based on the id within 1 sec Which database ...
0
votes
0answers
29 views

Subquery or Inner Join?

I have this simple query that finds all transactions from accounts belonging to a specific login, with some extra conditions on the transactions. SELECT t.id, t.date, t.amount, t.description FROM ...
3
votes
1answer
566 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: ...
1
vote
1answer
259 views

Postgresql - Minor version upgrade 9.3.5 to 9.3.6 on Ubuntu

I am two servers: A & B. My master database is running on A and the version is 9.3.5. It's replica is running on B and the version is 9.3.6. I am trying to use B as UPSTREAM slave, and trying to ...
0
votes
1answer
121 views

Microsoft SQL Server -> Postgres Data Migration

I have data in a Microsoft SQL Server that needs to be migrated to Postgres. The problem is that the SQL Server gets continuous updates and is hosted on our local net, while the Postgres instance is ...
1
vote
0answers
9 views

Raspberry as data aggregator node, transfer to database if Raspberry is online

I have never used SQL and currently I do read and watch various turorials and the manual of PostgreSQL. I would appreciate if someone could give me a brief overview how an experienced user would set ...
1
vote
1answer
171 views

PostgreSQL 8.4 replication of more than 100 tables

I am facing a problem with PostgreSQL version 8.4. I need to create an slave machine but when I look to all tutorials of slony or londsite its saying I will need to specify the tables that I want to ...
0
votes
1answer
18 views

Convert timestamp type during INSERT

I have a table of raw data as timestamp with timezone, as well as other extraneous columns. I want to convert each timestamp to the interval difference between it and the earliest (smallest) timestamp ...
1
vote
1answer
32 views

Conditional WHERE in plpgsql function

I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not ...
1
vote
1answer
822 views

pgpool-II doesn't execute my failover script

I have been installed pgpool-II 3.3.4 from source. I have two pgpool nodes and I also used the watchdog configuration to get a pgpool services with High Availability. When I stop one, the other wake ...
0
votes
0answers
10 views

Security of SQL Trigger Using dynamically stored table name

I'm working with PostgreSQL 9.0 and am learning how to use Triggers to enable some useful functionality. I have an insert trigger on a table that looks like this: feature_id | layer_name| latitude | ...
0
votes
0answers
13 views

How does postgresql handle tablespace locations in multiple clusters?

I have a server running a single postgresql cluster with a tablespace defined like: CREATE TABLESPACE secondary LOCATION '/data_secondary/main'; I have a second server where I have two postgresql ...
0
votes
1answer
36 views

Possible to use first CTE as filter for second CTE?

I'm trying to optimize a query on a large database that essentially fetches 50 images for a site and the URL's for the pages they appear on. The current query works okay for smaller sites but larger ...
0
votes
3answers
42 views

What data type for a column which can have three states: yes, no, unknown

I have an application where the user can decide between: yes no unknown I started to use a boolean column which is nullable. But things get quite complicated since my framework (django) does not ...
7
votes
1answer
408 views

Delete from table rows where any of the column field is null

Is there a way to delete a row from a table where any of the column field is null without specifying explicitly which column is null? I am using postgreSQL. Here's my relation schema: Column |...
3
votes
2answers
33 views

How do I get postgres to log the stored procedures it is running?

I'm not asking how to log what the stored procedure does, I simply want a list of all the stored procedures that are being triggered and run dumped into the statement log. I've got it logging all the ...
4
votes
2answers
215 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(' ', album....
0
votes
1answer
25 views

How can I DRY up a series of upserts of the same fields from multiple tables?

I have a bunch of tables that all have a storenumber, extracteddate and extractedtime column. I'm doing ETL on these tables and I want to put the last extracted date and time for each store in a ...
0
votes
1answer
23 views

Disable trigger effects in PostgreSQL

I have a function where there is: alter table a disable trigger trig1; some code alter table a enable trigger trig1; It basicaly disables the trigger for the purpose of specific operations. My ...
3
votes
1answer
119 views

How to get max value of a column from one table with its matching record in second table in PostgreSQL?

I have two tables as follows: create table two(grade_id int, edu varchar(20),sortby int); alter table two add constraint pk_one primary key(grade_id); create table one(id int, name varchar(20),...
2
votes
1answer
386 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 ...
3
votes
2answers
55 views

Does it make sense to store a couple of Boolean values as array?

I have a table with five Boolean columns. In 90% plus of the rows, all the columns are null. (False is equivalent to null for me.) Instead of having Boolean columns, I could have a single array ...
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.
0
votes
2answers
47 views

Add database-level collation to PostgreSQL

So I have added a en_US collation to my system: # locale -a C en_US.utf8 POSIX sk_SK.utf8 Based on Postgres' documentation I have added it to my Postgres instance: postgres=# select * from ...
1
vote
2answers
32 views

Postgresql Log message without its statement

If I want to write a message to the Postgres log I can do this: DO $$ BEGIN RAISE LOG 'Justin Is Awesome'; END $$; However, it logs the statement as well so I get 5 lines of output instead of one:...
4
votes
1answer
118 views

Using SELECT to call a function in a jdbc migration

I'm looking to set up some Postgres/PostGIS migrations with clojure/jdbc. running side-effect functions with SELECT is proving to be an issue, with most migration libs eventually throwing the A ...
1
vote
1answer
19 views

Persisting Prepared Statements

I know that the documentation states: Prepared statements only last for the duration of the current database session. Is there any way way to either save a prepared statement create code which ...
1
vote
1answer
31 views

Postgres - MultiXactId error

I have a table which has just started giving me the following error when trying to set a primary key, create an index or take a backup: ERROR: MultiXactId 1092514672 has not been created yet -- ...
0
votes
0answers
32 views

Postgres: Understanding Hash Left Join?

Using Postgres 9.4, I'm trying to a query by conditions ==> no hash left join. EXPLAIN ANALYSE SELECT CEAR.RESULT_ENUM_TYPE_ID, CEAR.CE_CE_COMMUNICATION_EVENT_ID, MC.CAMPAIGN_NAME, MC....
0
votes
1answer
46 views

How to update value in inserted row in PostgreSQL?

I have a table t1 create table t1 ( id int primary key not null, name varchar(64), str2 varchar(64) ); I want to get the row ID and update a field str2 in this row. I tried this: WITH ...
0
votes
2answers
30 views

Are there any Database functions which allow access to other network services in PostgreSQL

I have set up a new postgres installation and have to meet the security requirements from my company. There is one requirement were i'm not quite sure: Database functions which allow access to ...
1
vote
1answer
23 views

How to insert a record returned by a function into a table

I have a plpgsql function that takes one input parameter and returns multiple columns of data as a record. CREATE FUNCTION my_func( IN id bigint, OUT foo bigint, OUT bar double precision ...
1
vote
1answer
117 views

Is content of the pg_autovacuum saved when pg_dumpall is used to backup the database?

From the docs: pg_dumpall also dumps global objects that are common to all databases. (pg_dump does not save these objects.) This currently includes information about database users and groups, ...
0
votes
0answers
19 views

How to delete/disable Extended SQL functions or turn off extended stored procedures in postgres

I have set up a new postgres installation and have to meet the security requirements from my company. There is one requirement were i'm not quite sure how to implement: Extended SQL functions ...
1
vote
0answers
45 views

SQL Server: How to select a snapshot for a transaction? [closed]

PostgreSQL has a feature to select a snapshot for a transaction using: SET TRANSACTION SNAPSHOT snapshot_id Does SQL Server have a similar feature? Edit: Answer to: What is the use case? In an ...
5
votes
2answers
140 views

Optimizing “WHERE x BETWEEN a AND b GROUP BY y” query

CREATE TABLE test_table ( id uuid NOT NULL, "RefId" uuid NOT NULL, "timestampCol" timestamp without time zone NOT NULL, "bigint1" bigint NOT NULL, "bigint2" bigint NOT NULL, "int1" integer ...
0
votes
0answers
18 views

Postgres dump fails on higher version

I'm trying to replace the current production database with staging. The production database version is 9.3.9 and staging is 9.3.10. Importing the dump on my local machine (which has version 9.3.4) ...
0
votes
1answer
200 views

psql: could not connect to server

when I'm executing U postgres -d "phoenix" -a -f "/northstar/phoenix/runtime/Daily-script/query.sql" this command i got the following errore psql: could not connect to server: No such file or ...