I’m coming to Postgres from Oracle and looking for a way to find the table and index size in terms of bytes/MB/GB/etc, or even better the size for all tables. In Oracle I had a nasty long query that looked at user_lobs and user_segments to give back an answer. I assume in Postgres there’s something … Read more
Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers. We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations. Closed 7 years ago. Improve this question Are there any free tools to … Read more
It seems many others have had problems installing the pg gem. None of the solutions posed for others have worked for me. I have tried to install the pg gem and postgres.app. The pg gem won’t install. The first error I get is: An error occurred while installing pg (0.17.0), and Bundler cannot continue. Make … Read more
Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed last year. This post was edited and submitted for review 5 months ago and failed to reopen the post: Original close … Read more
I have a question about the ALTER TABLE command on a really large table (almost 30 millions rows). One of its columns is a varchar(255) and I would like to resize it to a varchar(40). Basically, I would like to change my column by running the following command: ALTER TABLE mytable ALTER COLUMN mycolumn TYPE … Read more
I want the code to be able to automatically update the time stamp when a new row is inserted as I can do in MySQL using CURRENT_TIMESTAMP. How will I be able to achieve this in PostgreSQL? CREATE TABLE users ( id serial not null, firstname varchar(100), middlename varchar(100), lastname varchar(100), email varchar(200), timestamp timestamp … Read more
I have a table to store information about my rabbits. It looks like this: create table rabbits (rabbit_id bigserial primary key, info json not null); insert into rabbits (info) values (‘{“name”:”Henry”, “food”:[“lettuce”,”carrots”]}’), (‘{“name”:”Herald”,”food”:[“carrots”,”zucchini”]}’), (‘{“name”:”Helen”, “food”:[“lettuce”,”cheese”]}’); How should I find the rabbits who like carrots? I came up with this: select info->>’name’ from rabbits where exists … Read more
How do I find the maximum (or minimum) of two integers in Postgres/SQL? One of the integers is not a column value. I will give an example scenario: I would like to subtract an integer from a column (in all rows), but the result should not be less than zero. So, to begin with, I … Read more
Postgres 8.4 and greater databases contain common tables in public schema and company specific tables in company schema. company schema names always start with ‘company’ and end with the company number. So there may be schemas like: public company1 company2 company3 … companynn An application always works with a single company. The search_path is specified … Read more
I would like to have PostgreSQL return the result of a query as one JSON array. Given create table t (a int primary key, b text); insert into t values (1, ‘value1’); insert into t values (2, ‘value2’); insert into t values (3, ‘value3’); I would like something similar to [{“a”:1,”b”:”value1″},{“a”:2,”b”:”value2″},{“a”:3,”b”:”value3″}] or {“a”:[1,2,3], “b”:[“value1″,”value2″,”value3”]} (actually … Read more