Skip to main content

Questions tagged [postgresql-9.3]

PostgreSQL version 9.3

Filter by
Sorted by
Tagged with
1 vote
1 answer
163 views

How do I restrict PostGIS database with user specific access?

I am using open source tools and working on QGIS 2.8 and Postgres/PostGIS 9.3 . Working on urban application so we have single database and many users .I want to create department wise user group so ...
Sunil's user avatar
  • 125
4 votes
1 answer
2k views

Placing a uniqueness constraint on a date range

I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate). How do I place a constraint on the table such that bookings are not ...
srip's user avatar
  • 143
1 vote
1 answer
64 views

What run-time configuration parameters are used for concurrent index creation

Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value. mhildreth=# show maintenance_work_mem; maintenance_work_mem ---------------------- 16MB (1 ...
Mark Hildreth's user avatar
1 vote
0 answers
118 views

How can I upgrade PostgreSQL while using PgQ queue system?

I'm using PostgreSQL 9.3 and PgQ 3.2.6 for about two years and now it's time to upgrade. I found out that upgrading PostgreSQL is fairy simple. Just install new Postgres version and then: sudo ...
Ivan Velichko's user avatar
7 votes
2 answers
10k views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of ...
jph's user avatar
  • 183
1 vote
0 answers
64 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
Jack Talbot's user avatar
7 votes
1 answer
3k 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 ...
Steph Locke's user avatar
1 vote
1 answer
4k 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 ...
Hassan Baig's user avatar
  • 1,979
1 vote
0 answers
65 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 platform(...
msanford's user avatar
  • 164
9 votes
1 answer
12k views

PostgreSQL 9.3: Dump a database excluding the data of some tables

Consider a production database with a few dozens of normal-size tables, and a handful of huge tables. I am looking for a convenient way to pg_dump the database content, excluding the few huge tables, ...
Adam Matan's user avatar
  • 11.7k
4 votes
1 answer
904 views

In PostgreSQL, can you read from an index during a ALTER INDEX ... SET TABLESPACE operation?

I have a PostgreSQL 9.3 server, and there's a large index that I want to move to another tablespace on another disk, because I want to free up space on the original disk. However this is a production ...
Amandasaurus's user avatar
1 vote
1 answer
159 views

Tricky condition for PostgreSQL UPDATE query

I need help with a PostgreSQL query. I have a Django app + pg backend, where users post interesting URLs (or links) and can then comment under such links (called replies). There are two pg tables to ...
Hassan Baig's user avatar
  • 1,979
3 votes
1 answer
3k views

PostgreSQL 9.3: Aggregate collection of JSON arrays

I have a table inventory with a value column that contains JSON strings, with the following data structure: { "category": { "item": ["price", "quantity"] }, "widgets": { "foo": [300, 15]...
Shaun Scovil's user avatar
4 votes
1 answer
8k views

Postgresql error: failed to re-find parent key in index

I have a Django app with a postgresql 9.3 DB (hosted on Ubuntu VM), and very recently the VM ran out of disk space (resulting in no space left on device errors showing up in my logs). I cleared the ...
Hassan Baig's user avatar
  • 1,979
1 vote
1 answer
459 views

postgresql: PANIC: cannot operate with inconsistent data

I'm trying to setup a replica using repmgr: repmgr -D /var/lib/postgresql/9.3/main -p 5432 -U repmgr -R postgres \ --verbose standby clone psql.master.example.com repmgr --verbose standby register ...
Tombart's user avatar
  • 1,070
0 votes
1 answer
2k views

Run script on multiple schemas on PostgreSQL DB

I need to run a huge migration script on multiple schemas on PostgreSQL 9.3 server. I want output to be in a .txt format if this failed on any schema. Would you be able to help me on this? Details: ...
Vinod's user avatar
  • 1
8 votes
3 answers
6k views

Create unique sequence per set of rows sharing the same foreign key value

In my system, I want each user to have an own id sequence for his articles. So, I have created a database that has a table that depicts a correspondence between users and their articles. Here is the ...
serhiizem's user avatar
2 votes
0 answers
84 views

Order by index unexpectedly slowing query down

I have two tables: houses has ~415,000 records, and account_houses has ~525,000 records. I've changed the table names for security reasons, but basically I am using this query: SELECT "houses".* FROM ...
nzifnab's user avatar
  • 121
35 votes
1 answer
39k views

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in ...
Hassan Baig's user avatar
  • 1,979
1 vote
1 answer
205 views

Deleting aged postgresql rows with tricky condition

I have two postgresql tables I need to clean up as part of maintenance. The tables, as described in my Django front-end, are: class Group(models.Model): owner = models.ForeignKey(User) unique ...
Hassan Baig's user avatar
  • 1,979
3 votes
2 answers
2k views

Database model about libraries

I need to make database model about libraries. In that model I'm tracking more libraries and every library has it's own moderators. Every moderator can make categories of the books and than add books ...
Superbrain_bug's user avatar
11 votes
2 answers
31k views

Deleting old postgresql log files (Ubuntu)

In a Ubuntu machine hosting a postgresql database, I have 16G space taken up at /var/lib/postgresql/9.3/main/pg_log. Apparently, log files from many months ago are being stored as well. I manually ...
Hassan Baig's user avatar
  • 1,979
1 vote
1 answer
442 views

Is there a limit to the number of database connections from a single machine in PostgreSQL? [closed]

We are planning on setting up multiple front ends behind a load balancer. I think our database can handle the load because I'm currently measuring a database TPS of about 450 for approximately ...
John Carlson's user avatar
5 votes
1 answer
9k views

Export roles *with* privileges under PostgreSQL 9.3

New to backups under PostgreSQL. I've been backing up my database with the general commands, and it works well. Except no users or privileges are stored with it. I understand that I need to do that ...
E.E's user avatar
  • 111
5 votes
1 answer
1k views

Sub-select takes "ages" - EXCEPT is much faster

Script to create the tables DROP TABLE IF EXISTS history; CREATE TABLE history ( id integer NOT NULL, ticket_id integer NOT NULL); ALTER TABLE ONLY history ADD CONSTRAINT history_pkey PRIMARY ...
guettli's user avatar
  • 1,521
2 votes
1 answer
2k views

Cannot log in with default user on Mac 10.10

I am running PostgreSQL on a Mac 10.10 PostgreSQL 9.3.5 on x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.1 (clang-503.0.40) (based on LLVM 3.4svn), 64-bit I am having trouble ...
four-eyes's user avatar
  • 177
3 votes
1 answer
4k views

Store same sequence value in two columns

I have a table in which one column is auto increment sequence number. I want the same value to be stored in another column as well (so it can be changed later). My table structure is like this: ...
Saddam Khan's user avatar
0 votes
1 answer
137 views

Extract date and time separately from a time with time zone column [closed]

I have a table in PostgreSQL 9.3 with the following column ...., created_at time with time zone NOT NULL DEFAULT now(), ..., When I do a select created_at from trails; I get this "02:32:44.62054-...
indago's user avatar
  • 535
0 votes
1 answer
72 views

Deleting aged rows from postgresql table which don't have related entries in another table

I maintain a website where users post interesting links, and other users leave replies under the said links. I want to delete aged link entries under which no one replied. The postgresql database is ...
Hassan Baig's user avatar
  • 1,979
1 vote
1 answer
8k views

Deleting aged postgresql rows with foreignkey constraints

I have a postgresql table where I want to delete rows beyond a certain age. But the catch is that some foreign key constraints would be violated For instance, if I run DELETE FROM links_reply WHERE ...
Hassan Baig's user avatar
  • 1,979
2 votes
0 answers
678 views

Manual postgres migration for Django (python) app

I have a Django (python) data model called Message: class Message(models.Model): text = models.CharField(max_length=50) sending_time = models.DateTimeField(auto_now_add=True) I added sender =...
Hassan Baig's user avatar
  • 1,979
3 votes
1 answer
2k views

Array of template type in PL/pgSQL function using %TYPE

I have a PostgreSQL database and a PL/pgSQL function that currently has arguments with copied types (for example IN arg_1 table_1.column_1%TYPE). I want to update it so one of those arguments is ...
Felipe Ruiz's user avatar
1 vote
0 answers
979 views

Foreign key violations detected upon UPDATE on another column

I have a table country that is referenced by many tables like so: db=# \d client Table "public.client" Column | Type | Modifiers --...
pyb's user avatar
  • 231
5 votes
1 answer
3k views

Change AWS's Read Replica's streaming replication to trigger based replication

We have a Postgres RDS instance for which we have created a read replica instance. The problem we are facing is that, that whenever there is a long running query(about 30-40 mins) on Read Replica, it ...
Mitaksh Gupta's user avatar
15 votes
1 answer
17k views

How do I move a PostgreSQL tablespace?

Is there a way to physically move a PostgreSQL 9.3 tablespace from /old/dir to /new/dir? I'd like to just mv the directory and tell PostgreSQL that the tablespace is now located at /new/dir. Looks ...
kontextify's user avatar
0 votes
1 answer
155 views

Source of error on psql data load

Upon importing a file derived from rails establishment, psql is hitting the error of foreign key constraints: ERROR: insert or update on table "documents" violates foreign key constraint "...
Jerome's user avatar
  • 279
2 votes
1 answer
200 views

How do I recover my database after losing a tablespace with indexes?

I'm thinking of moving all the indexes in my PostgreSQL 9.3 database to a tablespace on an SSD for improved performance. This SSD is not in a RAID array and I need to consider a scenario where it ...
kontextify's user avatar
1 vote
1 answer
1k views

Can we use subquery in GRANT statement in postgres

I want to grant select privilege on few selected tables. Can I use sub-query in GRANT statement???? I have tried the following query: GRANT SELECT ON TABLE (SELECT array_to_string( array_agg(...
Nik Rav's user avatar
  • 13
0 votes
1 answer
601 views

postgis installation gone awry

[cross-posted from stack ove On a Ubuntu 14.04 staging server a MySQL connection protocol had to be installed for full-text searching for a separate application. This led to installing, uninstalling ...
Jerome's user avatar
  • 279
2 votes
1 answer
8k views

Getting records for yesterday, but per hour

I am currently getting all data for yesterday from my database but I want to get data for each hour. This is my query: select camera_id, count(*) as snapshot_count from snapshots where ...
Junaid Farooq's user avatar
6 votes
1 answer
5k views

dumping database with "compressed data is corrupt" errors

I'm using pg_dump to dump a large (396 GB) PostgreSQL database: pg_dump --clean --create mydatabase After running for a day, a statement fails with ERROR: compressed data is corrupt and pg_dump ...
kontextify's user avatar
2 votes
2 answers
145 views

Retrieving changed rows only from a table with 10^7 rows

I have a table with about 300 columns and about 107 rows and I have to retrieve changed rows only, ordered by time (not distinct, but changed). The result set may be limited to 100 rows as well. I've ...
awesoon's user avatar
  • 123
5 votes
1 answer
696 views

PostgreSQL seems to create inefficient plans in simple conditional joins

Consider these two queries: SELECT t1.id, * FROM t1 INNER JOIN t2 ON t1.id = t2.id where t1.id > -9223372036513411363; And: SELECT t1.id, * FROM t1 INNER JOIN t2 ON t1....
hedayat's user avatar
  • 53
9 votes
1 answer
17k views

PostgreSQL not using index during count(*)

I have a COUNT(*) query in PostgreSQL that runs often, and looks like: SELECT COUNT(*) FROM customer WHERE source_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16); This query takes ...
Cerin's user avatar
  • 1,365
4 votes
2 answers
15k views

getting records for yesterday day starting till ending PostgreSQL

Currently my application is running over 2 dbs one is snapshotDB one is evercamDB. In snapshotDB we are saving records now it want to retrieve them , currently am using this query select camera_id,...
Junaid Farooq's user avatar
2 votes
1 answer
4k views

Specifying a username while running initdb causes error in log on start of PostgreSQL process

I have been forced to use a different default username for PostgreSQL. Before you ask: no, this is not something I can avoid. It's dumb, I know. Let's move on: In order to do this in an existing Cent ...
Chris Williams's user avatar
1 vote
0 answers
3k views

date/time field value out of range: "21/1/2016" [duplicate]

My PostgreSQL database version is PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit In both databases, datestyle is set to"ISO, DMY". ATTEMPT 1 ...
vijay's user avatar
  • 171
1 vote
1 answer
3k views

After renaming postgres user, getting "role 'postgres' does not exist" in logs on PostgreSQL startup

For security reasons, we've been asked to rename our postgres user in our database. We've made the change in our staging environment and bounced PostgreSQL. We're now seeing an error in the log on ...
Chris Williams's user avatar
18 votes
1 answer
14k views

Queries show data but not column names

I'm getting through the postgresql tutorial, I've created my db, 2 tables, weather and cities and added a few lines of data. When I query my db with this sql statement for example: SELECT city, ...
Alpagut's user avatar
  • 283
1 vote
1 answer
1k views

Postgres/pgloader: setting a delimiter for multiple line text records

I wish to import a 5 gigabyte text file with pgloader. The individual records are paragraphs of html. The records include carriage returns and white space. So this not an import where it is one record ...
Paulb's user avatar
  • 183

1 2 3
4
5
12