New answers tagged

3

You don't need a subquery at all. But you have to decide what ordering you want. After the group by there is no tldid values any more. And every group will be made by collapsing many rows (with different tldid values) into one row. Which of them should be used for the ordering? Is it the maximum, the mimimum or some other? You can write for example: ...


1

How much improvement are you expecting from the SSD? It is surprising how little physical I/O actually happens on busy indexes and I don't think I've ever solved an actual performance problem with physical I/O devices. I don't know enough about PostgresSQL to comment on buffering or recoverability and the like but I will say that choosing performance over ...


0

For lack of information, assuming created_at is data type timestamp, saving UTC timestamps: SELECT camera_id, count(*) AS snapshot_count FROM snapshots WHERE created_at >= timestamp '2015-01-01' -- From date: 2015/01/01 AND created_at < timestamp '2015-12-31' -- to date: 2015/12/30 -- chop off ...


1

Table definition A low hanging fruit first: The UNIQUE constraint details_id_key is a total waste of resources. It adds nothing useful over the existing PK details_pkey. Where did you get these noisy DDL statements? All the redundant default clauses cloud the view. After trimming the noise: CREATE TABLE public.details ( value numeric, created_at ...


-1

Reference taken from this article. You can use this script. SELECT pg_get_userbyid(roleid) AS GroupRoleName ,pg_get_userbyid(member) AS GrantedUserName ,pg_get_userbyid(grantor) AS GrantorUserName ,admin_option AS HasAdminOption FROM pg_auth_members WHERE pg_get_userbyid(roleid) = 'User_Name';


1

Some clarifications: REFERENCES is the key word used for a FOREIGN KEY constraint (which allows to cascade DELETE or UPDATE). Your database design seems to have logical flaws. rating seems like a detail of the main table restaurant. Since you have a 1:1 relationship, you could just include the "rating" columns in the main table. If you need a separate ...


2

You can assign any numbers with a derived table. generate_series() is instrumental for a simple solution. With a table like this: CREATE TABLE things_to_do AS SELECT * FROM generate_series (1,6) AS thing_id; A simple solution for the given example SELECT * FROM things_to_do JOIN ( -- derived table with prepared timestamps SELECT row_number() OVER ...


1

I find the most reliable way to import CSV data into PostgreSQL is to create a table just for the import (you can delete it after). Often there are wonky values in some columns, so I make all the column types text and then convert them on insert into the "real" table. The \copy command in psql (different than copy) makes it easy to copy files to a remote ...


-1

Way 1 : I would suggest to have case embedded in your select. Way 2 : Have a function which does all the maths and call that.


2

If those two databases are on the same cluster (beware of the PostgreSQL terminology, it's a name for an instance or installation or whatever you want), then you cannot prevent the users from logging in. All databases on a single cluster share the same users: Database roles are conceptually completely separate from operating system users. In practice it ...


-4

The best way to import data from multiple tables is to use COPY: COPY (write your query here) FROM '/home/foo/my.csv' DELIMITER ',' CSV;


4

I want to know what the id of the next row I insert will be. How can I tell this, without inserting a row? This is exactly what nextval() does. But I can't think of any useful thing to do with this value, if you are not inserting into the table. It will be the id value of a row that may never be inserted: Important: To avoid blocking concurrent ...


-3

select isnull(column,1) from table


0

Postgres does not save information about who wrote what exactly. If you need this, you have to do it yourself. To save the current user or the session user (you decide) for every inserted row, I like to have a tiny meta table of eligible users with a tiny PK column and only safe that value with every row. To keep it simple and fast I don't even add a FK ...


0

Reference taken from this article. Find blocking sessions: SELECT pl.pid as blocked_pid ,psa.usename as blocked_user ,pl2.pid as blocking_pid ,psa2.usename as blocking_user ,psa.query as blocked_statement FROM pg_catalog.pg_locks pl JOIN pg_catalog.pg_stat_activity psa ON pl.pid = psa.pid ...


3

First rename the schemas in the current databases to have the names you want to end up with: alter schema public rename to schema_a; And the same for other databases. Whether you need to change your apps, SQL functions, etc. depends on what kind of coding standards you used in the first place. This can probably be done with the system online, if your ...


2

Empirically, I've found GIN indexes need to be re-indexed after I've inserted a large number of rows into the table. I had a table with 15 million rows that had a GIN index that let me do a keyword search in under 3 seconds. After I inserted another million rows, that same search took 30 seconds. I waited for a day after my inserts, but the search was still ...


5

The issue is probably due to this line: host all all 192.168.0.0/24 md5 This (the 24) basically tells Postgres to allow connections from any machine that has IP of the form: 192.168.0.x. So, your 192.168.10.195 does not match the criteria. You can replace it with: host all all 192.168.0.0/16 md5 -- any 192.168.x.x or host ...


3

RDS doesn't let you install arbitrary C functions. It's a restricted environment. crosstab is part of the tablefunc extension. You may be able to CREATE EXTENSION tablefunc if it's on the whitelist but you won't be able to add new variants, even if the underlying C function is already loaded and approved.


3

Superusers are always exempt from lowly permission checks. The documentation: superuser status A database superuser bypasses all permission checks, except the right to log in. This is a dangerous privilege and should not be used carelessly; ... To see whether your current user is a superuser: SHOW IS_SUPERUSER; To list all superuser roles: ...


3

For Postgres and Oracle: create unique index unique_combinations on my_table (least(sender_id, recipient_id), greatest(sender_id, recipient_id));


0

If you install from an rpm then it's installed as a service. If you start postgresql from a custom script as user postgres then the process will catch the environment variables but if you start postgresql as a service it won't. You should look for how to pass environment variables to systemd (or sysv) services. I used this example for systemd services. ...


0

Looks like Aqua Data Studio could be what I'm after. To get multiple query results you have to separate your queries with a slash. It's not automatic but it's a step in the right direction for me. e.g. select travelerId, name from travelers; / select dateCreated from carDealerships; /


2

Are you looking to use the existing methods already implemented in PostgreSQL, or to implement your own new indexing methods? PostgreSQL's implementation of b-tree indexes cannot index values longer than about 2712 (although it will compress the value if it is highly compressible, before failing due to size) unless you recompile PostgreSQL with a ...


1

All these questions are basically answered in the documentation. To be specific: A role is an entity that can own database objects and have database privileges; a role can be considered a "user", a "group", or both depending on how it is used. That implies that internally there is no difference between these, except the LOGIN option. If you specify ...


3

A collation is an SQL schema object that maps an SQL name to operating system locales. Whatever you shall setup as a default collation settings for the database. By default implicitly that collation will choose your database. In PostgreSQL nevertheless, the initial set of collation names is platform-dependent. There are no case insensitive ...


3

The extract from pgadmin.log shows that for each output column of the result, pgadmin executes two queries against the catalog, one to obtain the formatted name of the type from its oid, another to obtain a potential base type. In your extract, if I'm counting right, the total number of such queries appears to be 106. From the ping time of 148 ms, let's ...


2

That can be done with a simple SELECT statement. SELECT count(*) AS count_ , favorite_color_ FROM person_ GROUP BY favorite_color_ HAVING count(*) > 1 ORDER BY count_ DESC ; The count(*) command is an aggregate function supported by Postgres and gives the number of rows with duplicate values in a column specified by the GROUP BY clause. To omit the ...


2

If you want to see the impact of a single query on I/O, you can also use EXPLAIN (ANALYZE, BUFFERS). Since disk reads are heavily dependent on what is already cached, results are obviously going to vary across runs. Example on a table without index, where you can see 32 blocks were cached and 65562 blocks were read: explain (analyze, verbose, buffers) ...


1

It's a one-way street. You can use the GQB to generate SQL code but not the other way round. I have been involved with pgAdmin development for a couple of years and I don't know of any features in the opposite direction. So I am pretty sure.


1

If I interpret your added information correctly, you have 21 distinct source_id with roughly 1 million rows each (divided pretty evenly among each source_id). That means your query counts roughly 3/4 of the whole table. An index typically can't buy much either way. Test with EXPLAIN (just EXPLAIN is enough for this purpose) to see the estimated cost for ...


4

As documented in the manual, string constants (or in general: anything that is not a number) need to be enclosed in single quotes: ALTER TABLE newarts ALTER COLUMN jurisdiction_id SET DEFAULT 'a82857b6-e336-4c6c-8499-4242';


1

Removing the explicit cast to tstrange worked. The new CSV file looks like t_range|field1 '[2014-06-02 07:00:00,infinity)'|'sometext1' '[2014-06-02 07:00:00,infinity)'|'sometext2' It seems to me that \copy is fine with automatically attempting to cast to the appropriate datatypes, but the explicit cast causes it to choke. One can also simplify the CSV ...


2

As you were hinted in comments above, your RBAR approach might be very inefficient. Consider the suggestions there. Also, I am not going into details about the different approaches of UPSERT, as it is a very broad topic, especially when one wants to do concurrency-safe. PostgreSQL 9.5 helps a lot in this regard. So, to your actual question: it is not ...


0

Even though you write this works: select data->'name' as name from persons where cast(data->'age' as int) > 25 This cannot work, because you are trying to cast a jsonb value to integer. This would actually work: SELECT data->'name' AS name FROM persons WHERE cast(data->>'age' AS int) > 25; Or shorter: SELECT data->'name' AS name FROM persons WHERE ...


1

Personally, I would prefer to create the index on a column which we frequently use in the where clause. Also, you might be aware that index based scans will be beneficial in cases where the query is going to fetch less than 5% of the total amount of data in a particular table. Creating an index on all the columns in a table would not be beneficial, as it ...


0

Okay, the performance here is being limited by the index of "urlShort" even though the field isn't being updated. Another reason to normalise the schema so that this is no longer necessary.


3

The numbers reported by log_statement_stats come directly from the getrusage system call (assuming your OS has one). The numbers in square brackets are total for the session so far, the other numbers are deltas between the start and stop of the statement. The filesystem block size is not normalized to be the same as the PostgresSQL block size. It is in ...


3

You don't need to do anything to get statement level consistency A query always sees a consistent state of the database regardless of the isolation level you use. Quote from the manual: This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. ...


2

Read committed (especially section 13.2.1) is the default read level in PostgreSQL. This read level will give you a snapshot of what has been committed before your transaction starts. It will allow other transactions to read and write to your table, you just won't be able to see any writes made after the start of your transaction. Does this only apply ...


5

The index is a sorted structure - if you need only a sufficiently small portion of the data in the table, it could be fetched from the index more efficiently. This needs a few prerequisites, though: PostgreSQL version 9.2 or newer, as index-only scans appeared in this version the index supports the query (the order of the columns of the index decides ...


1

createIndex := 'CREATE INDEX idx_' || Fulltable_name || '_properties_crosspr_gist ON public.' || Fulltable_name || ' USING gist (properties) WHERE properties @> $$"block_level_0"=>"cerber-head"$$::hstore AND properties @> $$"block_level_1"=>"head"$$::hstore AND properties @> ...


2

If you just need to look at something relative to $PGDATA you can use pg_ls_data SELECT pg_ls_dir('pg_xlog'); Otherwise, a simple function like this: CREATE OR REPLACE FUNCTION ls(location text) RETURNS text AS $BODY$ use warnings; use strict; my $location = $_[0]; my $output = `ls -l $location`; return($output); $BODY$ LANGUAGE ...


2

You can easily do what @a_horse_with_no_name suggests in his comment. But there is also an interesting way to do it, using PL/pgSQL as the function language. This uses a feature of the COPY command, introduced in PostgreSQL 9.3. It can now take a command as target/source, exactly where you'd use a filename or STDIN/STDOUT in normal cases: COPY ...


3

Mark all your existing rows as old: ALTER TABLE integrations.billables ADD COLUMN is_old BOOLEAN NOT NULL DEFAULT false; UPDATE integrations.billables SET is_old = true; And set up the constraint to ignore old rows: ALTER TABLE integrations.billables ADD CONSTRAINT cc_at_least_one_mapping_needed_billables CHECK ( NOT(("qb_id", "xero_id", ...


3

The error message you show would be the result of: VALUES ('3915105', E'\x1A', 44), -- and not '44' ('3915135', E'\x1A', 'fe88ff8f-6b4d-4e3d-8020-3475a101d25e') (Trimming some of the irrelevant columns.) Data types of columns in a free-floating VALUES expression are determined by the first row. String literals default to text, numeric literals ...


2

If you don't specify FOR EACH ROW in your CREATE TRIGGER statement, it will default to FOR EACH STATEMENT. In this case, the OLD and NEW records will never be assigned - in the end, which row should they refer to, if you change, for example, a hundred of them? So, create your trigger as follows: CREATE TRIGGER TR1 AFTER DELETE ON ...


2

It seems you just need the query: UPDATE sensors SET seconds=(SELECT sensors.starttime-"timestamp" FROM secure_sanity WHERE id=*id*) WHERE seconds=0; For that, it's overkill to write a plpgsql function. Or if you really want one, just replace this *id* by a function parameter put the above UPDATE query inside a CREATE FUNCTION / BEGIN ...


2

Many variants have been posted, but the simplest check to find violating rows that have NULL in every column of the set is: SELECT * FROM integrations.accounts WHERE (qb_id,xero_id,freshbooks_id,myob_id,ppy_id) IS NULL; The WHERE expression evaluates to TRUE if and only if every single column IS NULL. Details: NOT NULL constraint over a set of columns ...



Top 50 recent answers are included