Tag Info

New answers tagged

2

Possible with a single SELECT: SELECT name, count(*), to_char((count(*) * 100.0 / sum(count(*)) OVER ()), 'FM990.00" %"') AS percent FROM t GROUP BY 1 ORDER BY 1; count(*) is a separate form of the function and slightly faster than count(<expression>). Assuming all columns to be NOT NULL, else you may have to use the ...


1

SELECT name,COUNT(*), ROUND(100.0*COUNT(name)/(SELECT 100.0* count(name) FROM t),3) as percentage FROM t GROUP BY name FIDDLE


0

Thanks to Erwin Brandstetter on stackoverflow, I have my response: Using joins: SELECT u.*, t.team_organization_id AS organization_id FROM users u JOIN teams_users tu ON tu_user_id = u.user_id; JOIN teams t ON t.team_id = tu.tu_team_id WHERE tu_user_id = $user_id; To get that automatically, I could create a VIEW encapsulating the ...


0

The immediate cause of the infinite loop is this. Per documentation: Note in particular that EXECUTE changes the output of GET DIAGNOSTICS, but does not change FOUND. But there's more: CREATE FUNCTION upsert(_ins text, _upd text) RETURNS int AS $func$ DECLARE loop_ct int := 0; row_ct int; BEGIN LOOP IF loop_ct > 1 THEN -- terminate ...


2

You must group by all of the columns i the select clause: GROUP BY ticket.queue_id, queue.name, article.create_time or add aggregate functions such as MAX: SELECT ticket.queue_id, MAX(queue.name), MAX(article.create_time), COUNT(article.id) FROM article JOIN ticket ON article.ticket_id=ticket.id JOIN queue ON ticket.queue_id=queue.id GROUP ...


0

The answer is here: http://stackoverflow.com/questions/7975556/how-to-start-postgresql-server-on-mac-os-x Run this command to manually start the server: pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start


2

Use a data-modifying CTE to chain the inserts in a single statement. WITH inskey AS ( INSERT INTO key (key_name) VALUES ($$key_1$$) , ($$key_2$$) RETURNING * -- returns row including newly generated key_id ) INSERT INTO related_key (key_id, related_key_id) SELECT i.key_id, t.related_key_id FROM inskey i JOIN ( VALUES ...


2

Such a 1:1 mapping is sensible when part of the table is heavily accessed and part of it is lightly used. It's a good choice.


0

You already answered this yourself: No, there isn't in plain SQL. You can use PL/PgSQL if you want variables, in a function or a DO block. Most of the uses for query variables in MySQL are satisfied by CTEs (WITH queries), window functions, etc in PostgreSQL. Well, actually there is, but they're not suitable for general use within queries. You usually ...


0

For the second example you don't need a variable (neither in MySQL nor in Postgres): select id from client where platformID in (select id from platform where bios like '%INTEL%'); Don't be afraid of sub-queries, Postgres' query optimizer is much smarter than MySQL's. If the above is too slow, rewriting it ...


0

This is easy to do inside a PL/pgSQL function: create function myfunc() returns void language plpgsql as $$ declare aintconst constant int = -333; arealconst constant real = -9.99; pfid int; clientid int; begin select id from platform where bios like '%INTEL%' into pfid; select id from client where platformID = pfid into ...


1

The fragment you posted so far can be simplified to: INSERT INTO table2 (id, name, date) -- why "date" if you insert a timestamp? SELECT NEW.id, t1.name, NEW.timestamp FROM table1 t1 WHERE ST_DWithin(NEW.position , ST_SetSRID(ST_MakePoint(t1.longitudedecimal, t1.latitudedecimal), 4326) , 0.01447534783) AND t1.id > ...


0

If no delete or update happened, autovacuum shouldn't process the table, per autovacuum_analyze_threshold. See Automatic Vacuuming for the relevant configuration parameters. You may set log_autovacuum_min_duration to zero to help figure out what's being autovacuumed. In any case, it is possible to completely disable autovacuum for a particular table, with: ...


0

Information schema vs. system catalogs We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information. The information schema provides standardized views which help with ...


0

It should be int. The referencing and the refernced columns have to be of the same datatype but it doesn't make sense for a serial to reference another serial because serials are just int with auto incrementing values provided by a sequence. And when you will be inserting in the second, referencing table (client), you'll want to use the values already ...


0

You may try installing PostgreSQL 9.2 from repository then install PostGIS 2.0 from source using below commands: Install GEOS(for topology support) wget http://download.osgeo.org/geos/geos-3.3.9.tar.bz2 tar xfj geos-3.3.9.tar.bz2 cd geos-3.3.9 ./configure make sudo make install cd.. Install PostGIS 2.0 wget ...


0

If You use Linux you should list processes with ps aux and find Autovacuum process. Then just kill it(autovacuum should be working as another process if it's turned on).


0

I'm no Postgres expert so this might be wrong! Your primary key has 3 columns, sessionID as the first field. Does the file contain a decent spread of timestamps? you might consider making that the first field in the primary key or using a surrogate key as currently this is fairly wide. From your script I dont think you have a cluster. Different to SQL ...


1

SELECT INTO is usually used to select specific set of data into a table, esp., during scenarios when the data in the table is the priority and not the constraints. It automatically creates a table if there is no such table already. But, INSERT INTO is used when you already have a table that has specific defined constraints and need to add data from a ...


5

A partial unique index should do this: create unique index max_one_null on item (type_id) where manufactured_until is null; For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type Look into range types and exclusion constraints. They were specifically designed for this problem. Something ...


0

The bad news: Prepared statements do not work like that. Parameters can only be data, not code or identifiers. The good news: you do not need parameters for a ALTER TABLE statement at all. final String sqlps = "ALTER TABLE filen ADD COLUMN gn_id integer"; ... ps = con.prepareStatement(sqlps); ps.execute(); Your column needs a data type. I added ...


1

Use the information_schema views, they're SQL-standard and contain the information you want. You can also directly access pg_class, pg_attribute, etc, but that's unportable and often fiddlier; you may need helper functions like oidvectortypes, pg_get_function_arguments, etc for some things. If you want to see how psql executes something like \dt, run psql ...


1

You can use the psql command line client. \dt will show a list of tables \dv will show a list of views \d [object_name] will describe the schema of the table or view Not sure how you would describe a query though. More info: https://manikandanmv.wordpress.com/tag/basic-psql-commands/


0

Well, for this part of your question: I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so. I searched on Google, but I found only means to determine the actual space occupied by the database, and can not predict their growth. It's not clear why monitoring ...


1

I would say the most advanced database access layer these days is Spring Data JPA. It builds fast, correct DAOs for you at startup time. I would check that out, maybe read some of the source, and gain inspiration from there. As for connections, it depends on the app. For desktop to db apps, you can keep a connection open for the entire user session. For ...


0

You can't use ? parameters for identifiers. This is common across database brands. Use format() , like this: text sql = format('ALTER TABLE %I ADD COLUMN %I', table_name, column_name); %I means identifier. It will use identifier rules when substituting values for %I. For added security you can whitelist characters and identifier names and throw an ...


3

For now While stuck with your unfortunate solution: CHECK ((end_time AT TIME ZONE 'UTC' AT TIME ZONE 'US/Eastern')::time = '23:59:59'::time) That's right, AT TIME ZONE two times: The first instance transforms your timestamp without time zone into timestamp with time zone. that's assuming you are actually storing UTC times. The second instance converts ...


1

Some options: -- regexp_matches SELECT string_agg(arr[1], '') AS string FROM regexp_matches('The United States of America', '\y(?!(the|of)\y)\w', 'gi') arr; -- regexp_split_to_table SELECT string_agg(left(word, 1), '') FROM regexp_split_to_table('The United States of America', '\s+') t(word) WHERE NOT (word ILIKE ANY ('{the,of}'::text[])); -- Without ...


0

This is a simple adjacency list traversal problem. You're not even trying to build paths, just find nodes from a root node or set. That's pretty much a textbook example of what a recursive CTE is for. http://sqlfiddle.com/#!12/8c406/12 WITH RECURSIVE children(product_content_id, product_master_id, content_id) AS ( SELECT pc.product_content_id, ...


3

You may use to_char to get the time fields from a single function call: check (to_char(end_time at time zone 'UTC' at time zone 'US/Eastern','HH24:MI:SS') = '23:59:59') Seconds given by SS are not rounded up so that should be OK as an equivalent to floor


0

Try this. It maybe be faster, but still unreadable: alter table bananas add constraint ck_end_time_is_end_of_day check ( (end_time at time zone 'UTC' at time zone 'US/Eastern')::timestamp::date + interval '23:59:59' = date_trunc('second', end_time at time zone 'UTC' at time zone 'US/Eastern') ) ;


1

See this post, which links to a big query summarizing all the indexes which may not be pulling their weight.


1

SELECT REPLACE(array_to_string(array(select array_to_string(regexp_matches('The United States of America', '\y(?!(the|of)\y)\D', 'gi'),'')),''),' ',''); FIDDLE


1

You can monitor the index usage through pg_stat_user_indexes and pg_statio_user_indexes More details about the statistics collector can be found in the manual: http://www.postgresql.org/docs/current/static/monitoring-stats.html You should be careful with dropping unused unique indexes though. They might not be used for reading, but they are most probably ...


-1

SQLinForm (http://www.sqlinform.com) is based upon Java and thus can run in Linux environment. They have an API and command line version which you could easily use in your scripts.


2

Consistent rows The important question which does not seem to be on your radar yet: From each set of rows for the same seriesName, do you want the columns of one row, or just any values from multiple rows (which may or may not go together)? Your answer does the latter, you combine the maximum dbid with the maximum retreivaltime, which may come from a ...


2

So this is almost certainly a bad configuration in ip address resolution. As you mention, you have a line resolving localhost to your external ipv6 address. Yes, this should be removed, or changed from fe80::1 to ::1


1

Ok, I've read the docs more, and now I understand the issue at least a bit better. Basically, what's going on is there are multiple possible values for dbid as a result of the GROUP BY seriesName aggregation. With SQLite and MySQL, apparently the DB engine just choses one at random (which is absolutely fine in my application). However, PostgreSQL is much ...


1

PostgreSQL's CTE's materialize results - they run the CTE term once and cache the output for the duration of the query. So it's already doing what you want. CTE results cannot be cached between queries. If you want that, you should instead CREATE TEMPORARY TABLE AS SELECT ....


1

With only 5 values the index isn't honing to be very selective for joining so it'll pretty much always index scan unless looking for just one value. That query plan/anal output suggests that it thinks half the work is in matching the rows there with the CTE, could you try run the group+aggregate over the whole lot. If it is index scanning anyway you might ...


1

Assuming you took a copy of the whole data directory including pg_xlog, pg_clog, global, base, etc, then you can simply ensure that PostgreSQL 9.3 is installed and: postgres -D /path/to/data/directory -c "port=5433" then in another terminal use pg_dump to dump the database(s) from the database running on port 5433 with (eg) pg_dump "dbname=mydb ...


3

Going out on a limb here (basic information is missing), partial indexes will probably be your best bet. Much easier to handle than partitioning the whole table, it offers similar performance for the split case and allows much better performance for queries on the whole table: CREATE INDEX tbl_nodelay_idx ON tbl (tbl_id, ??) WHERE delay <= 0; CREATE ...


1

A few thoughts. Consider range partitioning on "timestamp", it will reduce the amount of work your query have to do. Further optimization might be to calculate and store the agg for "closed" partitions. You will of course have to recalculate this when you modify historical information. As a bonus it will be much easier to roll-out historical data that is ...


1

This is a case of relational division. If I understood correctly, that's the task: "Find all posts where none of the given categories is missing." SELECT p.* -- "Find all posts ..." FROM post p WHERE NOT EXISTS ( -- "where none of the ..." SELECT 1 FROM (VALUES (1),(2),(3)) c(category_id) ...


0

Given the information in your comments there's not much you can do index-wise beside the index that you proposed. This index will have poor selectivity. Dependent of the size of the table and the update frequency of delay you might consider range partitioning, one partition for delay > 0 and one partition for delay <= 0. Instead of scanning the index and ...


3

Most importantly, this is a special case of relational division. Once you know the name of the beast you'll find plenty of query techniques. Like the arsenal we assembled on SO: How to filter SQL results in a has-many-through relation Building on this test case (which you should have provided): CREATE TABLE post ( post_id serial PRIMARY KEY , post ...


2

IN is like writing = ANY, e.g. regress=> SELECT 1 = ANY (ARRAY[1, 2, 3]); ?column? ---------- t (1 row) A simplistic interpretation of your question would be to say that you're asking for = ALL, but that rarely makes sense: regress=> SELECT 1 = ALL (ARRAY[1, 2, 3]); ?column? ---------- f (1 row) What you really want is to find all posts ...


0

Bingo!! I did exactly what Craig said and that worked :) Although I showed just two check constraints above, there were more actually. And that was a blunder me trying to create unique constraints rather than check constraints. Thx for rectifying Craig. What I did Imported the raster data using raster2pgsql command from the terminal. Made a copy of ...


0

repmgr is a nice place to go indeed. If you use pacemaker/corosync you shoud check this as well, its a Resource Agent handling repmgr : https://github.com/xmm/repmgr. Pelase note you might need to either patch the provided Resource Agent or patch repmgr.



Top 50 recent answers are included