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

learn more… | top users | synonyms (1)

6
votes
1answer
488 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 ...
4
votes
2answers
4k views

How to conditionally stop a psql script (based on a variable value)?

Let's consider the following example (from the start of a psql script): \c :db_to_run_on TRUNCATE the_most_important_table; -- tried to avoid similarities to anything that exists out there Now if ...
4
votes
1answer
86 views

Persistent inserts in a UDF even if the function aborts

I have a rather complicated UDF (moves around a bunch of tables as well as creates a bunch of new ones) where multiple aborts may occur. Before every operation I would like to record the time when an ...
4
votes
1answer
10k views

Declare variable of table type in PL/pgSQL

I am wondering if there is a way to declare a variable of type table in PL/pgSQL to hold query results? For instance how can I express something like: q1 = select * from foo; q2 = select * from bar; ...
4
votes
1answer
92 views

DISTINCT ON vs ROW_NUMBER() for getting a row with a maximum value of a group

I have the following table in PostgreSQL 9.4: CREATE TABLE dpa( id serial NOT NULL, currency_id integer, amount numeric(14,3), date timestamp without time zone, plat_id integer, pl_id ...
4
votes
1answer
2k views

Creating UUID extension as the non super privileged user

I need to load the uuid extension on my database, which is being created in runtime. The user that created the db is not superuser, she only has the "CREATEDB" permissions. Is there a way to do this? ...
3
votes
1answer
123 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 ...
3
votes
1answer
44 views

Why does the behavior of array syntax differ from '(?,?)' syntax when updating a point field and that field is NULL?

I'm using PostgreSQL 9.3.5. Suppose I have the following table: CREATE TEMPORARY TABLE point_test ("name" varchar(255), "pt" point); I then insert a row, leaving pt NULL: INSERT INTO point_test (...
3
votes
1answer
1k views

SELECT in trigger function in two tables

My table and trigger in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit: CREATE TABLE measurements ( measurement_id SERIAL PRIMARY KEY NOT NULL, ...
3
votes
1answer
7k views

Select first row (grouping) + add aggregate function

First have a look at this question on StackOverflow. I'm looking to accomplish the same task, except I also need to add an aggregate function (PostGIS's ST_Union) to my query. How can I combine the ...
3
votes
2answers
4k views

Get all partition names for a table

I want to list all the partitions created by dynamic triggers in PostgreSQL 9.1. I was able to generate a count of partitions using this related answer by Frank Heikens. I have a table foo with an ...
3
votes
1answer
405 views

Postgres Backup queries on AWS after reading Postgres docs

I am reading backup section of postgresql docs before heading over to create backup strategy on my EC2 instance. So there are 2 ways to do it. Create a dump using pg_dump. File level backup with ...
3
votes
4answers
4k views

migrating tables from Postgres to SQL Server 2008

(I fear what I wish to accomplish shall not be easy, but a lot of my googling around about it yielded rather dated results, so here it goes...) I have a Postgres 8.4.x database with over 100 tables. ...
3
votes
2answers
4k views

Postgres 9.2 select multiple specific rows in one query

I have a table with three columns, Id Serial. Value real. timein Timestamp. I want to select the values based on a range of days ie. from two days ago until now. The table may contain one or two ...
3
votes
1answer
99 views

How do I automatically set meta commands when starting psql?

When I start a psql session, I would like certain meta commands to already be set. For instance: \x auto \set ON_ERROR_STOP on I was hoping to find an rc or config file but couldn't. The command ...
3
votes
1answer
567 views

Can't list users - “select * from user” returns only one row with one column

I have a heroku postgres add-on database with a user table. However, when I attempt to select * from user, all I get is: current_user ---------------- rtsjlhdfptlaqd (1 row) The table name is ...
3
votes
3answers
178 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 ...
3
votes
3answers
14k views

Bulk update of all columns

I am wondering if PostgreSQL has an update query somewhat like their insert values syntax. I have an updated set of data in this form currently: INSERT INTO bought_in_control_panel(ID,PARENT_ID,...
3
votes
1answer
143 views

Subquery magic in PostgreSQL

I have a query: update product_product set (write_date, default_code) = (LOCALTIMESTAMP, 'update') where product_tmpl_id in ( select distinct product_tmpl_id from product_template ...
3
votes
2answers
2k views

Running a CTE query in a loop using PL/pgSQL

I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude ...
3
votes
1answer
566 views

Proxy authentication for PostgeSQL

The following seems likely to be a fairly common situation, but I haven't seen any solutions. I have a PostgreSQL database containing views that are specific to the $user, in order to restrict data ...
3
votes
2answers
535 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
2
votes
1answer
3k views

PostgreSQL update and delete property from JSONB column

From this article, I tried to update or delete property of a JSONB column: CREATE TABLE xxx (id BIGSERIAL, data JSONB); INSERT INTO xxx(data) VALUES( '{"a":1,"b":2}' ); SELECT * FROM data; id | ...
2
votes
1answer
162 views

How to estimate reindexing time?

How do you estimate the total time reindexing an entire table will take in PostgreSQL? I have a large PostgreSQL database, the primary table of which has 15 million rows with 100 columns and 200 ...
2
votes
1answer
59 views

How to make unique check without creating heavy index in postgres

I need to implement unique check for INSERT and UPDATE operations, but I would prefer to avoid creating heavy unique index (which is approx 12Gb now) on my table. Now I have unique partial index and ...
2
votes
1answer
121 views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT ...
2
votes
1answer
564 views

Can't alter default privileges

I followed the steps below. I installed PostgreSQL. I logged in as postgres via (sudo -i -u postgres) I created new user new_user via shell script createuser --interactive I granted that user ...
2
votes
1answer
4k views

How to pass a parameter into a function

At the moment I have a view created, see it here in the answer. How can I create a function based on that crosstab() query , so I can pass a date, and get data for the specific date? Also is it good ...
2
votes
2answers
1k views

How to do WHERE x IN (val1, val2,…) in plpgsql

I would like to update a selection of rows in a table; this works... UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5); But how can I do the same in a plpgsql function? The following gives a ...
2
votes
1answer
165 views

Foreign key for array column [duplicate]

I have a table: CREATE TABLE methods ( method_id serial PRIMARY KEY, method_name varchar(100) ); I now want to create a table with the following columns: CREATE TABLE experiments ( ...
2
votes
2answers
299 views

Index for numeric field is not used

I have PG server (9.0.4) installed on Debian x86. In one of my tables I use numeric(6,1): CREATE TABLE mytable(name text, numfield numeric(6,1)) Also index created for this field: CREATE INDEX ...
1
vote
1answer
34 views

Insert multiples rows/columns - PostgreSQL 9.2

I've got a table that has several columns, But I need to insert data into two columns of that table. I need to add data into those columns, from a select statement. Would it be something like: ...
1
vote
1answer
70 views

How to view tuples changed in a PostgreSQL transaction?

BEGIN; INSERT INTO foo_table(foo_column) VALUES('a'),('b'),('c'); -- SELECT * FROM pg_stat_activity WHERE ... -- shows records with a flag if they have been added/deleted/updated END; Is ...
1
vote
0answers
318 views

Reset postgresql [closed]

I have trouble: I tried to restore PG backup from CentOS on my Ubuntu computer. But had a trouble: after placing backuped Centos /var/lib/pgsql/9.1/data/ to my /var/lib/postgresql/9.1/main at Ubuntu, ...
1
vote
2answers
1k views

Can I select data inserted in the same uncommited transaction?

Maybe this is a dumb beginner question, but I cannot find an answer anywhere. Everywhere I read about the Transaction Isolation which solves the visibility of data within the concurrent transactions. ...
1
vote
3answers
108 views

Make column default a query

Is there a way I can set up a column in PostgreSQL 9.2 such that the default value is a query? I am having to track down query statements from different sources that don't respect the "default" ...
1
vote
4answers
92 views

PostgreSQL execute a default query when no row are returned

I need to calculate the average score of a list of userId. I have a table user_place_score: (user_id, place_id, score) Sometimes I do not have the score (no row for (user_id,place_id) in the table)...
1
vote
2answers
553 views

Restoring the superuser account on the postgres server

I have a complete curious situation with a postgres dbms. I have installed the database and due to a misconfiguration of my installation scripts I made the superuser role to be a regular user. Now I ...
1
vote
3answers
283 views

Remove duplicate values from query result

I am using Postgres 9.3.9. I have 2 tables : f_agent : CREATE TABLE f_agent ( f_agent__id bigserial NOT NULL, f_agent__total_users integer NOT NULL, f_agent__dim_time_id integer NOT NULL, ......
1
vote
2answers
2k views

Is there a faster way to get UNION ALL behavior in Postgres?

I have a table (t0), in my Postgres DB, with data that looks something like this: t1_id t2_id 1 1 2 1 2 1 4 null 4 null 5 null And I have a query to return my ...
0
votes
2answers
119 views

Query that was working on MySQL is not working on PostgreSQL

I had the following query (with bin_number being a string) which was working on MySQL 5.5: Postgres - was working on MySQL and doesn't work on PostgreSQL SELECT "menu_items".* FROM "menu_items" ...
0
votes
2answers
2k views

sum of double precision gives weird results

I've been making a sum of a column containing about 500.000 records of double precision numbers. All numbers in the database should normally be of two ciphers behind the period. However when making ...
0
votes
0answers
144 views

PostgreSQL crash when connection failed

Error log is: 016-02-21 21:22:34 GET DETAIL: Connection matched pg_hba.conf line 83: "host all all ::1/128 md5" .... .... 2016-02-21 21:22:37 GET DETAIL: ...
0
votes
0answers
46 views

Reducing the time when retrieving all records from large databases

I have to retrieve most of the records from large table, I'm talking about approximately 3 millions rows. I'm even using this site as a guide for tuning the database. It is not enough, it takes an ...
0
votes
1answer
222 views

Trigger function using current row and current table name as variables

Like detailed in my first question I have multiple tables with identical layout in a Postgres 9.1 DB. They only vary in their column values: tbl_log_a tbl_log_b tbl_log_c ... 26 tables (from a to z)...
59
votes
8answers
75k views

How do I find PostgreSQL's data directory?

I forgot how I last time started postgres (it was months ago) and I don't remember where the data directory is located at. The postgres command seems to require to location. I'm on MacOsX if that ...
102
votes
2answers
47k views

How can I time SQL-queries using psql?

I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?
44
votes
2answers
68k views

How do I get the current unix timestamp from PostgreSQL?

Unix timestamp is the number of seconds since midnight UTC January 1, 1970. How do I get the correct unix timestamp from PostgreSQL? When comparing to currenttimestamp.com and timestamp.1e5b.de I ...
24
votes
1answer
16k views

Scalability limitations of PostgreSQL and MySQL

I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB. I suspect that limits as such do exist as one would not come up with Netezza, ...
16
votes
4answers
26k views

tuning postgresql for large amounts of ram

I have two identical servers (in terms of hardware), they are both standard installations of windows server 2008 r2, with minimal software installed (basically my code and required stuff like jvm etc)....