pg_table_size(‘index_name’) for individual index – but it only shows you the size on disk, not the number of entries. count(*) to get the exact current number of rows sum(pg_column_size(column_name)) from table_name for estimations on column data size. You can try something like: t=# \di+ tbl* List of relations Schema | Name | Type | Owner … Read more
I use the shorthand cast syntax almost everywhere: SELECT sum(seconds)::float / -1323 AS averag; More details: Postgres data type cast
Instead of playing with symlinks in system library dirs, set the $DYLD_FALLBACK_LIBRARY_PATH to include the anaconda libraries. eg: export DYLD_FALLBACK_LIBRARY_PATH=$HOME/anaconda/lib/:$DYLD_FALLBACK_LIBRARY_PATH
Now you have 7 fields. You need to map those 6 fields from the CSV into 6 fields into the table. You cannot map only 3 fields from csv when you have it 6 like you do in: \COPY agency (agency_name, agency_url, agency_timezone) FROM ‘myFile.txt’ CSV HEADER DELIMITER ‘,’; All fields from the csv file … Read more
The quoting rules are painful. I think you want: SELECT nextval(‘foo.”SQ_ID”‘); to prevent case-folding of SQ_ID.
Since postgres is a lot more sane than MySQL, there are not that many “tricks” to report on 😉 The manual has some nice performance tips. A few other performance related things to keep in mind: Make sure autovacuum is turned on Make sure you’ve gone through your postgres.conf (effective cache size, shared buffers, work … Read more
You should refresh the view in triggers after insert/update/delete/truncate for each statement on table1 and table2. create or replace function refresh_mat_view() returns trigger language plpgsql as $$ begin refresh materialized view mat_view; return null; end $$; create trigger refresh_mat_view after insert or update or delete or truncate on table1 for each statement execute procedure refresh_mat_view(); … Read more
Found: CREATE TABLE publishers(id INT, info JSON); CREATE INDEX ON publishers((info->>’name’)); As stated in the comments, the subtle difference here is ->> instead of ->. The former one returns the value as text, the latter as a JSON object.
As of PostgreSQL 9.4, you can use the ? operator: select info->>’name’ from rabbits where (info->’food’)::jsonb ? ‘carrots’; You can even index the ? query on the “food” key if you switch to the jsonb type instead: alter table rabbits alter info type jsonb using info::jsonb; create index on rabbits using gin ((info->’food’)); select info->>’name’ … Read more
select table_name, pg_relation_size(quote_ident(table_name)) from information_schema.tables where table_schema=”public” order by 2 This shows you the size of all tables in the schema public if you have multiple schemas, you might want to use: select table_schema, table_name, pg_relation_size(‘”‘||table_schema||'”.”‘||table_name||'”‘) from information_schema.tables order by 3 SQLFiddle example: http://sqlfiddle.com/#!15/13157/3 List of all object size functions in the manual.