All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

1
vote
2answers
586 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
2answers
2k views

How to restore postgreSQL schema dump to schema with different name

I know I can alter schema name after restoring but the problem is the name might already exist and I don't want to touch that existing schema. The dump type is "custom". So effectively I want ...
1
vote
3answers
4k views

get row count of last select query

I am using a scheduled script which exports the (new) rows of the PostgreSQL database into a textfile. There are also several instances of this script running for several DBs: COPY (SELECT ... FROM .....
1
vote
2answers
2k views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
1
vote
1answer
241 views

Improve performance on concurrent UPDATEs for a timestamp column in Postgres

I am using a timestamp column called updated_at for cache invalidation. More information on this particular technique here. The queries all have the same format UPDATE "managers" SET "updated_at" = '...
1
vote
2answers
2k views

How to make a table name an attribute in PostgreSQL?

I have received hundreds of tables in PostgreSQL with identical structure and each containing similar data of a different day, so that the table name is the date of the data. I would like to Union ...
1
vote
2answers
56 views

Can I find errors without using testing data for all the branches of each function?

In PostgreSQL, syntax errors (except for trivial syntax errors) are reported while running the code in question; unlike Oracle, PostgreSQL can't find errors without running the part of the code. This ...
1
vote
2answers
956 views

Dynamic UPDATE fails due to unwanted parenthesis around string in plpgsql

Using PostgreSQL 9.3.4 on Mac OSX 10.9.2 with Postgres.app. I want to apply a function to all columns in a table, specifically I want to trim white space. So far I've got this in a file test.sql: (...
1
vote
1answer
6k views

Installing pgcrypto Extension

I have this password hash function that hashes a password to sha256. I've already got it running in pgadmin3 in a local database. I've done it by importing the extension, a feature that has been added ...
1
vote
2answers
373 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...
1
vote
2answers
890 views

Extract all tags from hstore field

I need to extract (copy) all hstore tags by object id from a postgres 9.2+postgis 2.1 database into an external file for post-processing. Since the keys and values vary per record, and I don't know ...
1
vote
1answer
568 views

PostgreSQL - Create view with autoincremental column

I have a PostgreSQL table, and I need to create a view with a new column. This column needs to be an auto-incremental column starting at 1 and going to N. Is this possible to do without effecting the ...
1
vote
2answers
3k views

PostgreSQL - pgAdmin error creating a server

I have the latest versions of PostgreSQL and its GUI-frontend, pgAdmin, installed. When I try to register a new server a fatal error appears when pgAdmin tries to connect to the newly created server - ...
1
vote
1answer
248 views

numbering rows consecutively for a number of tables

I have a number of tables, all containing attributes aid,bid,cid, and xid of type integer, other attributes may differ. For every (given) table T, I would like to order the rows according to aid,bid,...
1
vote
1answer
698 views

PostgreSQL 9.2 number of days in a tstzrange?

How can the number of days contained within a range be found? For example, with these timestamp ranges, get these (integer) number of days: tstzrange('2013-10-01 07:00', '2013-10-01 07:15') | 1 (day)...
1
vote
1answer
790 views

Have aggregate function group results in one row

I have a table that has the following columns: debit credit payment_method class Now I will perform this query: select sum(debit - credit), payment_method, class from my_table group by ...
1
vote
2answers
130 views

Using an index for both uniqueness and fast lookup?

Right now I have a postgresql 8.4 database set up for players in a multiplayer game. I want the username column to be unique. I also want to be able to lookup a player by username quickly. Here's the ...
1
vote
2answers
317 views

Prune unused joins

I have expected that selecting from a view would not join the tables from which I don't query any values, but it doesn't seem to be the case. I have a bunch of tables with the following structure: ...
1
vote
2answers
3k views

Join on different types

In a database I have two tables: The first has a field named taxonomy_id that is an integer The latter has a field named ID that is a character varying The two tables are related: if it exists a ...
1
vote
1answer
2k views

Pass a table array as a parameter to an upsert function in postgresql

i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will ...
1
vote
1answer
486 views

Depending on Foreign Key Constraints to avoid deletion

In PostgreSQL, I have a 2 tables with father/parent relationship. I wish to delete the father record if no records of child are associated with it (using after triggers) Currently, I have been doing ...
1
vote
2answers
1k views

list tables that are missing primary/foreign key in postgresql database

I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing primary and foreign keys? PostgreSQL 8.4 and 9.x versions
1
vote
1answer
529 views

Upgrade production database during software development lifecycle

Background Using Oracle, there are a number of tools that help with migrating and applying development changes into the production environment (such as Embarcadero Change Manager). These tools can be ...
1
vote
2answers
131 views

Difficult categorising of rows

First off, I'm not a DBA at all, I'm more of a front-end guy, but I'm currently hitting a brick wall and can't figure out how to tackle it. A database I am working on has a table with several columns. ...
1
vote
1answer
179 views

how to query for rows by specifying the range of row numbers in postgresql

I have an application where in the user can see the history of all questions he has posted. These are have to be displayed in the order newest to oldest. I am implementing this in Android. I have two ...
1
vote
2answers
325 views

PostgreSQL : Is this because of locks(row level)?

I have a table with approximately 2.1 million tuples. Which has latitude and longitude columns. I am trying to convert this into a geographic type (point with SRID). The function(procedure) I have ...
1
vote
1answer
10k views

How to export a table on postgresql command line

Anyone can do tell me how to do that? (I know maybe is a dumb question but I'm trying to learn how to use command line of postgresql) ~$ psql mybase psql (9.1.3) Type "help" for help. and then ...
1
vote
2answers
273 views

PostgreSQL Database Migration

How can I migrate data from on PostgreSQL database to another PostgreSQL database? Is there any technique to migrate data with SQL scripting?
1
vote
1answer
221 views

How to design databases? [closed]

Describe the process in steps, please. What tools do you use for it?
1
vote
1answer
34 views

Select rows and add their previous rows

I have a table structure that holds historical data for another table. Something like id | name | date ---------------------------------------- 1 | John | 2016-01-01 14:38....
1
vote
2answers
31 views

Repeat total count value

I have a table called paths that looks like this: id parent_id -- --------- 1 null 2 1 3 2 4 3 5 4 6 5 7 5 8 5 9 ...
1
vote
1answer
67 views

Automatic index creation for primary vs. foreign keys in Postgresql

For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that A foreign key must reference columns that either are a primary key or form a unique ...
1
vote
2answers
49 views

UPDATE from subquery updates rows that shouldn't be updated

Using PostgreSQL, I want to update a column. The information how to update is in another table. My try was this, but it also updates rows I don't want to update: update table_1 set column_x = ( ...
1
vote
2answers
70 views

PostgreSQL: Calculate balance working backwards from current balance

I have a balances table that stores the current_balance for bank accounts; and a transfers table that contains a row for every transfer to- and from an account (deposits are positive numbers, ...
1
vote
3answers
119 views

Select all before first break

I have a table with three columns: id, sort, name in PostgreSQL 9.5 database. I need to select all rows starting from sort=1 and before the first break (ordered by sort). For example I have: id | ...
1
vote
1answer
82 views

Getting the next bigserial value in PostgreSQL [closed]

Say I have a table with an id BIGSERIAL column. I just started a new session. I want to know what the id of the next row I insert will be. How can I tell this? I can't simply SELECT MAX(id) FROM ...
1
vote
1answer
61 views

Postgresql uuid syntax error

I am trying to insert/update a foreign key field which is also a uuid to use the correct fk id for this set of data: zuri=# ALTER TABLE newarts ALTER COLUMN jurisdiction_id SET DEFAULT a82857b6-e336-...
1
vote
1answer
99 views

PostgreSQL optimistic reads (equivalent to SQL Server's Read Committed Snapshot)

Does PostgreSQL provide a built-in optimistic concurrency control mechanism such as SQL Server's Read Committed Snapshot Isolation? Readers can read all the rows without blocking, but writes will ...
1
vote
1answer
129 views

Index performance for CHAR vs VARCHAR (Postgres)

In this answer (http://stackoverflow.com/questions/517579/strings-as-primary-keys-in-sql-database) a single remark caught my eye: Also keep in mind that there's often a very big difference between ...
1
vote
1answer
31 views

What will happen with cached index or table when it is modified, will it stay in cache?

if PostgreSQL loads something (for example an index) into cache, what happens if it (cached index) gets modified? Is it going to be removed from cache or not?
1
vote
1answer
163 views

How to use values from table column as field names in SELECT query?

Scenario: Field names of Table1 named as testtable id,name,size,width,height Field names of Table2 named as errortable id,desc,field1,field2,operator Values of errortable +----+----------------...
1
vote
2answers
106 views

Select query not giving result for inner query values

Here is my schema and data create table mytable (id numeric, val text); create table mytable1 (id1 numeric, id text); insert into mytable values (123, 'aaa'); insert into mytable values (124, 'bbb');...
1
vote
1answer
216 views

Deleting many rows on a live table in chunks - without locking for too long (PostgreSQL 9.3)

I am new to postgres, I have a 20 million row table that is on a live server - I need to remove most of the rows but not all. I want to do this without impact to other read/write processes accessing ...
1
vote
1answer
77 views

UPDATE id table on the columns with the same name

I may be a newbie question but I don't know how to resolve this. I have a table like: name | id | value A 1286487 1286333 B 1286489 1286403 C 1286495 1286455 C ...
1
vote
1answer
476 views

Query with limit and order by runs too slow

I have the following table: CREATE TABLE dpg2 ( account_id integer NOT NULL, tank_id integer NOT NULL, battles integer, dmg integer, frags integer, wins integer, recent_battles integer[]...
1
vote
1answer
155 views

Cannot read from /tmp with PostgreSQL COPY, but able to read the same file from another directory with the exact same permissions

I have a file /tmp/foo.csv that looks like this: 1,2 10,20 100,200 I have the same exact file in another directory /mydata/foo.csv. Both directories and files have the same exact permissions, owner,...
1
vote
1answer
286 views

Matching data in 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 have two tables, one listing the customer's creditcard_number, and one listing ...
1
vote
1answer
241 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 ...
1
vote
2answers
134 views

Retrieve additional columns in recursive CTE

This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep. I manage ...
1
vote
2answers
42 views

PostgreSQL Extension Multiple SQL Files

I'm creating a custom extension for PostgreSQL. I can get the extension to create successfully, but my foo--0.1.sql is getting rather large and complex. I'd like to break it out into multiple files ...