New answers tagged

2

If using psql via bash command line, you can simply do: psql -c "COPY (select query) To '/tmp/test_`date +"%Y-%m-%d_%H%M%S"`.csv' DELIMITER ',' CSV HEADER;"


1

Your strategy for getting information from full_path can be useful for a one-off, but for ongoing queries to it, especially over millions of records and expecting quick results, it is far from optimal. Considering the sheer size of your tables, you'll probably benefit from datawarehousing. If your tables are constantly updated, you'll need a trigger to keep ...


2

There are many ways for doing this. When measurements can be trusted to be sequential (time), then one can use a simple self left join, which should perform very fast: SELECT * FROM data d LEFT JOIN data prev ON (prev.time = d.time - 1) WHERE (d.foo, d.bar) IS DISTINCT FROM (prev.foo, prev.bar) Another one is using window functions (PG version >= 8.4): ...


1

You have to define the aliases for the columns outside the subqueries: with d as ( select distinct emp.ahrims_id, rank.name as rank from hr_employee as emp, ana_rank as rank, unit_identification_code as uic, record_record as rec where emp.unit_identificatin_code_id = uic.id and rec.hr_employee_id = emp.id and rank.id = ...


1

Perhaps this will help. If you'll rely on the account_id from full_path often, then you'll benefit from a function and a functional index for it: CREATE OR REPLACE FUNCTION gorfs.f_get_account_from_full_path(p_full_path text) RETURNS int AS $body$ SELECT (regexp_matches($1, '^/userfiles/account/([0-9]+)/[a-z]+/[0-9]+'))[1]::int $body$ LANGUAGE SQL ...


0

The information in the question is incomplete. If you always need the last 3 characters in the string, then use right(): SELECT right(description, 3) AS reserved_for FROM def WHERE description LIKE 'Reserved for %'; The first column would be a boolean where reserved = true or false. And then if it's true, the second column called reserved_for would ...


0

This would work: create table res ( id character varying(40) not null primary key references def, reserved_for character varying(20)); insert into res select id, substr(description, 14) from def where description like 'Reserved for %'; Then you can select all your records where there's no reservation: select * from def where not exists (select 1 ...


0

Seems like a decent approach. Of course, one should apply some human verification to this before automatically dropping everything that seems unused. For example, it's conceivable that the statistics were recently reset and/or an index is only used for some occasional batch tasks.


1

PostgreSQL can be built to either use the operating system's time zone database or its own. Check with the command pg_config --configure whether the option --with-system-tzdata was used. If not, it is using its own time zone data, and that hasn't been updated yet. If so, to fix, either wait for the next releases, or install the packages the way you have ...


1

pg_indexes.tablename only contains the table name, not the schema name. The schema name is available in the column schemaname. So you need to use select * from pg_indexes where tablename ='asignacion' and schemaname = 'distribucion';


0

You can use a deferred constraint trigger, or (what I'd do in this case) replace assignment table with an array column in set and a normal before update/insert per row trigger to sort the array, and and using a unique key on the array to ensure uniqueness.


0

Triggers will not work on some scenarios. Let set1(A,B), set2(A,B,C) Now one cann't move to set2(A,B,D) by dropping set2-C and adding set2-D assignments because DB must go through prohibited set1(A,B), set2(A,B) state. You should implement custom API, stored procedures or something with proper transactions which will check only final target state. Also those ...


1

In Oracle a schema and a user is the same thing. In Postgres it isn't. So there is no direct "mapping" on what you did in Oracle in "Postgres land". A table is always owned by the user who created it initially there is no way to change that. If you do not want to give the user ink the privilege to create tables, the "Postgres" way would be to give that ...


0

Give ink the right to create tables in the database and connect yourself as ink to the database. Then all the tables that you create there are owned by ink. PostgreSQL has a different approach on this then Oracle. The schema and owner are 2 different 'things'.


0

As you describe, first do the distinct in a common-table-expression. Then query that for the sums: create table me(id int, name text, type char(1)); insert into me values (1, 'Ali', 'a'), (1, 'Sami', 'a'), (3, 'Kamil', 'c'), (3, 'Imran', 'c'), (2, 'Wali', 'b'), (4, 'Yousuf', 'd'), (5, 'Kamran', 'e'), (6, 'Asad', 'f'), (6, 'Dawood', 'f'), (7, 'Asid', 'g'), ...


-1

I think only way to get sum from given data is use where clause. select sum(type) sum from xxx where type in(a,b,d) ; select sum(type) sum from xxx where type in(c,e,f,g) ; You need to have something to 'group by' to get sum , it does not have to be id This one would calculate same type values together select sum(type) sum from xxx group by type ...


0

pg_dump -U db_user -p5432 database_name > dump.pgsql and then you can gzip it and copy it to the other server , then unzip it there and use psql -U db_user -p5432 database_name < dump.pgsql to restore the database


0

DROP SCHEMA would attempt obtaining exclusive use of the schema first, so it would only actually manage to drop the schema when PostgreSQL is done retrieving data from ongoing queries. Further queries would likely be locked until the end of the transaction in your question. Queries accessing tables in the prod schema may fail after this transaction is ...


0

For this particular query, the following index would work better: create index index1 on lineorder(lo_suppkey, lo_partkey, lo_orderdate); Or possibly better two separate indexes for supplier+partkey and for orderdate: create index index1 on lineorder(lo_suppkey, lo_partkey); create index index2 on lineorder(lo_orderdate); That's because your query is ...


0

I found a better index. I think I was reading the query plan results, which caused me to determine the wrong index order.. Now I'm reading it from bottom to top, and it gives me this index: create index index1 on lineorder(lo_partkey,lo_suppkey,lo_orderdate); However, is there anyway of making it even better??


2

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.: AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+' Alternatively, create the index without typecasting: CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); Note also that the character / ...


2

Here's a shell script that can do what you want: SCHEMA="myschema" DB="mydb" psql -Atc "select tablename from pg_tables where schemaname='$SCHEMA'" $DB |\ while read TBL; do psql -c "COPY $SCHEMA.$TBL TO STDOUT WITH CSV" $DB > $TBL.csv done Make sure you set the DB and SCHEMA variables to your particular database and schema. The wrapping psql ...


1

The id field in your ny_stations table does not seem to be defined as a serial, so it is expected that pg_get_serial_sequence will return nothing. The duplicate you get relates to one of the records in your SELECT DISTINCT ... FROM ny_raw_trips ... is returning two rows with the same id: SELECT start_station_id, COUNT(*) FROM ( SELECT DISTINCT ...


2

You could just alter the table and convert the column in one step: alter table ogrtable alter column arrcolumn type varchar using (array_to_string(arrcolumn, ', '));


1

Just add an ON DELETE CASCADE option to your foreign key: ALTER TABLE links_link DROP CONSTRAINT constraint_name, ADD CONSTRAINT constraint_name FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON DELETE CASCADE; Apparently in django the above is translated as: on_delete=models.CASCADE


1

The results from these views do not overlap and together cover 100% of the table. What keeps you from just querying the underlying table? Should be fastest: SELECT x.* FROM cases x JOIN case_clients cacl ON cacl.case_id = x.main_id WHERE cacl.client_id = 12046 ORDER BY x.sort_nr, x.id;


3

As @NeilMcGuigan suggested, an Exclusion Constraint is the key here. The part I was unfamiliar with was the ability to use the <> operator. As available post Postgres 9.0: CREATE TABLE accrual ( add_date date NOT NULL, user_id integer NOT NULL, rate integer NOT NULL, amount numeric(7,3), EXCLUDE USING gist (date WITH =, ...


0

Your problem are the "idle in transaction" sessions. You should fix your application to avoid that. The queries in pg_stat_activity in those cases are the last query run, not the current one (since there isn't a current one).


0

It depends on where you ran your pg_upgrade, but when it has completed the process it will drop log files as well as two shell scripts in that same current working directory (not the postgres directory). delete_old_cluster.sh analyze_new_cluster.sh You want to start up your new postgres cluster with pg_ctl and then run the analyze_new_cluster.sh. The ...


0

Turned out to be a minor corruption of the SQL backup file. After re-running the entire process again, the restore worked fine with no errors. You would think that Postgres would have in-built checking to ensure integrity of exported data!


0

You can also use scp. Ensure that your local postgres user can ssh passwordlessly to your remote host (ssh-keygen -t dsa) and that its public key (~postgres/.ssh/id_dsa.pub) is included in the remote hosts authorized_keys files (~postgres/.ssh/authorized_keys). Once you (as postgres user) can ssh to the remote host, update the following in your ...


0

For large volumes of data, perhaps you can cut down on the cost of fulltext search by adding a fulltext GIN index, either for the whole json (to_tsvector(jsonb_column::text)) or the output of a function that would extract and concatenates all of its relevant values. Then you could use the @@ ts_query() operator to obtain a reduced subset of likely matches, ...


0

This is due to relation attributes (defined in pg_class and pg_attribute, or defined dynamically from a select statement) supporting modifiers (via pg_attribute.atttypmod), whilst function parameters do not. Modifiers are lost when processed through functions, and since all operators are handled via functions, modifiers are lost when processed by operators ...


1

Yes, you can refer to rows of higher levels in a subquery. But that would not solve your problem since you need to consider the whole table, not just the current row. It's not completely clear what the query is trying to achieve and data types (table definition!) are also missing. Be aware that your invocation of generate_series() returns timestamptz, which ...


1

To get all members of all roles: SELECT r.rolname as username,r1.rolname as "role" FROM pg_catalog.pg_roles r JOIN pg_catalog.pg_auth_members m ON (m.member = r.oid) JOIN pg_roles r1 ON (m.roleid=r1.oid) WHERE r.rolcanlogin ORDER BY 1; Add r1.rolname='council_stuff' to filter on only that one. Be aware that users ...


1

Streaming replication in PostgreSQL uses the same security mechanisms as a normal frontend-backend connection. So in order to encrypt the traffic, SSL should be used, and access control should be set up. Alternatively, or additionally, consider a VPN of some kind. PostgreSQL instances aren't really meant to be run facing the open internet.


2

If it's one time setup, I think dump would be easiest. But if You need your update be available on customer, then I think streaming replication would be easier, set up once and no need to update in the future as the replication will handle the data updates. http://www.postgresql.org/docs/current/static/high-availability.html


1

You can also set it as a default for any user or role (takes effect after a new connection is established): ALTER ROLE <rolename> SET client_encoding = 'UTF8';


0

You can either set the environment variable PGCLIENTENCODING in the operating system or include the set command in .psqlrc if you only need it for psql


1

If a milestone can have multiple tasks (which is highly likely), then your statement will only update the taskcompleted for the "last" task processed in the query (the "last" is undefined here though). If you want to check all tasks for a milestone to be completed you need consider all taskcompleted combined with an AND condition. This can be done using ...


5

If the account id is always at the third position use split_part() select * from gorfs.seg WHERE split_part(full_path, '/', 4) = '4865' Note that due to the leading / the actual index is 4, not 3. The following query: with seg (id, full_path) as ( values (1, '/userfiles/account/4556/attachment/1234'), (2, ...


0

You can use the following, which relies on DISTINCT ON, and turns calculates version -1 as 9999999: select DISTINCT ON (field_id) * from field_mappings where COALESCE(NULLIF(version, -1), 9999999) < 4 ORDER BY field_id, COALESCE(NULLIF(version, -1), 9999999) DESC The query retrieves all the rows with unique fields (by field_id), prioritising the ...


1

...or you can simply use parenthesis to isolate the components and cast as usual: select ((regexp_matches(full_path, '\d+'))[1])::int from gorfs.seg where account_id = 4865 The question is: why do you need to extract the account_id from the full_path if you already have it in the where clause?


2

If your attribute item_number is unique, you can leave it in your original table even in case it can have null values. In fact the PostgreSQL manual says: For the purpose of a unique constraint, null values are not considered equal. So this could be right solution: CREATE TABLE product ( sku text PRIMARY KEY, name text ...


2

What you're asking the DB to do in Query one is: Give me ALL from table A FILTERED Give me ALL from table B FILTERED Give me ALL from table C FILTERED Give me ALL from table D FILTERED And then Union. In the second query you first get all the data, and only after that you do the join and the filter. JOIN and WHERE on a UNION query, which doesn't really ...


4

This turned out to be a tricky puzzle which took up far more time than I thought it would - I guess for you too? :-). I (finally) managed to solve it thus: First I created (using DDL and DML for those who wish to try this at home) a table called stringtest: CREATE TABLE stringtest (my_string VARCHAR(255)); and populated it as below INSERT INTO ...


8

1. Assuming that sku and item_number will always hold unique values I consider that you have come up to the answer by yourself, since an item_number is an optional attribute (or column at the implementation phase), i.e., it does not apply to all the product occurrences, therefore, from a logical point of view, it decidedly cannot (should not) be declared as ...


1

From this post: ;WITH CTE AS ( SELECT table_name , ( SELECT MAX(pg_ls_dir::int)::text FROM pg_ls_dir('./base') WHERE pg_ls_dir <> 'pgsql_tmp' AND pg_ls_dir::int <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name) ) as folder ...


0

When you Partition a table, physical tables are created and when you query table with where predicates on partition only partition tables queried, you can also query a particular partition.


2

csd's answer really saved me. One minor issue is that the last "getSubjectX500Principal()" should have been a "getIssuerX500Principal()". I didn't have enough rep to comment, so I ended up writing a complete answer with a more detailed walkthrough for those of us who don't know or have forgotten java. Make sure your server can compile java. Try the command ...



Top 50 recent answers are included