Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
4
votes
2answers
4k 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 ...
4
votes
1answer
2k views

Why are query results shortened in pgAdmin 1.18.1?

I've recently installed pgAdmin 1.18.1 and noticed a strange thing: Long json query results are shortened to 256 symbols and then ' (...)' is added. Could someone help me disable this shortening?
3
votes
1answer
1k views

Why does a JSON element return text when it's not text?

When I try to access a JSON element using ->>'elementName' I get a texttype. SELECT pg_typeof(x1->>'a'), jsonb_typeof(x2) FROM ( VALUES ('{"a":5}'::jsonb, '5'::jsonb) ) AS t(x1,x2); ...
3
votes
2answers
6k views

Pushing or appending to JSON array in PostgreSQL 9.4

I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values. Something like that: CREATE OR REPLACE FUNCTION add_array(...
3
votes
2answers
2k views

How to `vacuumlo` an RDS PostgreSQL database?

As you may have noticed, as a fully managed data base as a service product, AWS Relational Data base Service (RDS) restricts the execution of userland commands. I was staging a production database, ...
2
votes
1answer
1k 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 ...
2
votes
2answers
626 views

In what case is a count(x or null) needed in Gaps and Islands?

In this answer, Erwin Brandstetter says: count(step OR NULL) OVER (ORDER BY date) is the shortest syntax that also works in Postgres 9.3 or older. count() only counts non-null values. In modern ...
2
votes
1answer
10k 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 ...
2
votes
1answer
469 views

OFFSET and LIMIT on complex query

I am doing a pretty complex query in Postgres 9.3.4: SELECT p.* FROM unnest('{19082, 19075, 20705, 18328, 19110, 24965, 18329, 27600 , 17804, 20717, 27598, 27599}'::int[]) s(source_id)...
1
vote
1answer
2k views

Slow queries related to subqueries using aggregation

Here's a simplified version of the queries I am running: select ... from editions join edition_events on edition_events.edition_id = editions.id join events on events.id = ...
238
votes
3answers
132k 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?
91
votes
2answers
154k 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 ...
32
votes
14answers
126k views

PostgreSQL is running locally but I cannot connect. Why?

Recently updated my machine from Mac OS X Lion (10.7.4) to Mountain Lion (10.8) and I think it borked my PostgreSQL installation. It was installed originally via Homebrew. I'm not a DBA, but hoping ...
33
votes
1answer
23k views

How can I benchmark a PostgreSQL query?

I want to benchmark a query containing a user-defined function I have written for PostgreSQL. Are there any standard ways to run such a benchmark? I know that timing can be turned on with \timing at ...
42
votes
3answers
64k views

What is the search_path for a given database and user?

I can see the current search_path with: show search_path ; And I can set the search_path for the current session with: set search_path = "$user", public, postgis; As well, I can permanently set ...
36
votes
6answers
33k views

What are the differences between “Stored Procedures” and “Stored Functions”?

So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL. The comment links to a wikipedia article but some of this don't ...
27
votes
3answers
24k views

upgrading from postgres 9.1 to 9.3 on ubuntu server

I have my production server(ubuntu 13.10) running with postgresql 9.1. I want to use few features of 9.3, hence want to upgrade. Could someone help me with upgrading from 9.1 to 9.3 so that there is ...
41
votes
3answers
39k views

Why is a new user allowed to create a table?

I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core: postgres=# \l List of ...
28
votes
4answers
69k views

Checking whether two tables have identical content in PostgreSQL

This has already been asked on Stack Overflow, but only for MySQL. I'm using PostgreSQL. Unfortunately (and surprisingly) PostgreSQL does not seem to have something like CHECKSUM table. A PostgreSQL ...
20
votes
1answer
43k views

Performance difference between MySQL and PostgreSQL for the same schema/queries [closed]

I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS. I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests ...
39
votes
5answers
31k views

What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?

There is a long and quite elucidating answer on the differences between TIMESTAMP WITH TIME ZONE -vs- TIMESTAMP WITHOUT TIME ZONE available in this SO post. What I would like to know is: are there ...
6
votes
2answers
15k views

Moving a Postgres database from standalone local DB to Amazon RDS

I have a working Ruby on Rails 4 app running locally on my Mac. I'm running Postgres locally. I want to move the database (and only the database, not the whole app) to Amazon RDS Postgres. What is ...
45
votes
3answers
80k views

remove password requirement for user postgres

I understand that, upon installation, PostgreSQL has no password for its db root user (postgres): postgres=# select usename, passwd is null from pg_shadow; usename | ?column? ----------+---------- ...
18
votes
1answer
14k views

Why would you index text_pattern_ops on a text column?

Today Seven Databases in Seven Weeks introduced me to per-operator indexes. You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
16
votes
2answers
8k views

PostgreSQL Index Caching

I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions: PostgreSQL indexes, like rows, live on disk ...
12
votes
2answers
8k views

How to speed up ORDER BY sorting when using GIN index in PostgreSQL?

I have a table like this: CREATE TABLE products ( id serial PRIMARY KEY, category_ids integer[], published boolean NOT NULL, score integer NOT NULL, title varchar NOT NULL); A product can ...
10
votes
2answers
83k views

Update column with data from another table

I am working on complicated problem, but I will simplify it to this problem. I have two tables A [ID, column1, column2, column3] B [ID, column1, column2, column3, column4] and I want to update the ...
7
votes
1answer
9k views

Can I do VACUUM FULL to pg_largeobject table?

I have two tables (table1, table2) in a Postgres 9.1 database. Both having oid type. Each table 1 million records. And pg_largeobject table size is around 40GB. I have removed 0.9 million records from ...
27
votes
3answers
18k views

Foreign key constraint on array member?

Suppose I have a table containing job roles: CREATE TABLE roles ( "role" character varying(80) NOT NULL, CONSTRAINT "role" PRIMARY KEY (role) ); Suppose I further have a table, users, and each ...
19
votes
2answers
10k views

Is a Postgres long-running query aborted if the connection is lost/broken?

If I open a connection to Postgres and issue a long-running query, and then break the connection (e.g., kill the client process that opened the connection), will the long-running query continue to run,...
18
votes
2answers
6k views

Custom unique column constraint, only enforced if one column has a specific value

Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter). If type is "true", then ...
14
votes
1answer
10k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
14
votes
2answers
9k views

Conditional Foreign Key Relationship

I currently have a foreign key between two entities, and I would like to make that relation conditional to the entityType of one of the tables. Here's the hierachy of tables, this is done via FK ...
13
votes
2answers
29k views

Efficient query to get greatest value per group from big table

Given the table: Column | Type id | integer latitude | numeric(9,6) longitude | numeric(9,6) ...
13
votes
1answer
12k views

How do I store phone numbers in PostgreSQL?

Let's assume I want to store phone numbers in a database. I may accept phone numbers from outside of the United States. How would I go about storing these phone numbers?
23
votes
1answer
2k views

Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I see frequent references to WITH queries (common table expressions, or CTEs) acting as an optimisation fence, where the server isn't permitted to push filters down into the CTE queries, pull common ...
17
votes
4answers
28k views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My ...
14
votes
2answers
48k views

Permission denied in file trying to import

When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and ...
13
votes
2answers
61k views

Postgres incomplete startup packet error

I am attempting to install Postgres 9.3 on Ubuntu 14.04 and I am getting an irksome error on startup. Here's what I'm seeing: $ sudo service postgresql restart * Restarting PostgreSQL 9.3 database ...
10
votes
2answers
5k views

How to make DISTINCT ON faster in PostgreSQL?

I have a table station_logs in a PostgreSQL 9.6 database: Column | Type | ---------------+-----------------------------+ id | bigint ...
7
votes
3answers
11k views

Duplicate row with Primary Key in PostgreSQL

Assume I have a table as follows named people, where id is a Primary Key: +-----------+---------+---------+ | id | fname | lname | | (integer) | (text) | (text) | +===========+=========+=...
7
votes
3answers
5k views

VACUUM VERBOSE outputs, nonremovable “dead row versions cannot be removed yet”?

I have a Postgres 9.2 DB where a certain table has lots of nonremovable dead rows: # SELECT * FROM public.pgstattuple('mytable'); table_len | tuple_count | tuple_len | tuple_percent | ...
6
votes
2answers
1k views

Better database for “keep always the 5 latest entries per ID and delete older”?

I have a PostgreSQL database with a history table where I store an fooID (not a primary key serial but a text), an attribute target and the current timestamp whenever the target for this fooID changes:...
4
votes
1answer
8k views

cannot insert multiple commands into a prepared statement

This query works for me when I tried in PG admin query tool: UPDATE bankdetails SET bank_details = 'mysore' ,"PAN"= 123,bank_acc = 456, "UAN"=789,tax = 'myy' WHERE user_id = ...
3
votes
2answers
8k views

Creating High Availability Cluster with PostgreSQL

I am new to database design. I have a task to create a high-availability cluster. I Googled a lot, however I haven't found any good way to do this on PostgreSQL. Where should I start and what are ...
20
votes
2answers
56k views

PgAdmin III - How to connect to database when password is empty?

I have installed PostgreSQL 9.1 on my PC (Win 7). I have a small Java application connecting successfully to it with login=sa and password="". The connection works. However, it is refused from ...
19
votes
2answers
19k views

Query the definition of a materialized view in Postgres

I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view: SELECT * FROM information_schema....
12
votes
1answer
9k views

How to index a query with `WHERE field IS NULL`?

I have a table with lots of inserts, setting one of the fields (uploaded_at) to NULL. Then a periodic task selects all the tuples WHERE uploaded_at IS NULL, processes them and updates, setting ...
12
votes
3answers
31k views

Compare two similar Postgres databases for differences

I occasionally download publicly available data sets in the form of Postgres dBs. These datasets are updated/modified/expanded over time by the repository host. Is there a Postgres command or ...
11
votes
1answer
7k views

What are the privileges required to execute a trigger function in PostgreSQL 8.4?

What are the privileges required to execute a trigger function in PostgreSQL 8.4? It seems that the privileges set to a role does not matter to execute a trigger function. I think I have seen some ...