Tag Info

Hot answers tagged

4

except can be re-written as not exists query: select t1.id, t1.amount, t1.email, t1.date from t1 where not exists (select 1 from t2 where t2.amount = t1.amount and t2.email = t1.email and t2.date = t1.date); But your statement seems to indicate you do not actually want except but ...


4

The documentation says about INCLUDING CONSTRAINTS (emphasis mine): Not-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied. Also, no distinction is made between column constraints and table constraints — when constraints are ...


3

I would like to know if it is possible to share the structure and data between the two systems without duplicating and/or having to copy everything each time I load a new OS. Effectively, I would like all the data to be available to Ubuntu when it is created in Windows, and vice versa. You can't. Just not supported, not possible. No can do. PostgreSQL ...


3

As you have multiple rows inside the JSON column, you need a function that returns a set. This can be done using the json_to_recordset() function: select j.* from json_test, json_to_recordset(json_data) as j(name text, country text, hobby text, address text, sex text); Because this is an anonymous record type, you must explicitly define each column. ...


3

Just run pg_dump. The documentation says: pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). In turn, the state of the database that you see on the slave is also consistent. If ...


3

create table tree_data ( id integer primary key, code text, name text, parent_id integer, constraint fk_parent foreign key (parent_id) references tree_data(id) on delete set null ); SQLFiddle example: http://sqlfiddle.com/#!15/b9a62/1


3

When you create a foreign key in postgres, set ON DELETE SET NULL. ALTER TABLE public.tree_data ADD CONSTRAINT tree_data_id_fkey FOREIGN KEY (parent_id) REFERENCES tree_data(id) ON DELETE SET NULL;


3

Use an UPDATE statement. update student set total = social + math + science; But that column is totally useless. In general you should not store data in a database that can easily be derived from existing values. In your, adding the values of the three columns adds no (noticeable) overhead to a select statement, so there is no point in storing the ...


3

1. Subquery expression You can fix it with parentheses like @a_horse commented: SELECT * FROM test_function((SELECT customerid FROM tableX where id = 1)); But this form is rather error-prone. Nothing in the code guarantees that the sub-select only returns a single row. We don't know whether id is unique and neither does Postgres (unless it looks up system ...


2

That's a typical case of case misuse. When creating the view, notice that "objectifsBoutique" is written with a uppercase B plus double quotes. This implies that any future reference to this object must include the uppercase B. Yet in the error message « objectifsboutique » relation is not found it's a lowercase b so that name does not refer to the view, ...


2

According to the README included in the dmg file, postgres binaries are installed in '/usr/local/pgsql/bin/' You should add these lines in your ~/.bashrc : export PSQL_DIR=/usr/local/pgsql/bin/ export PATH="$PSQL_DIR:$PATH" Update your environment with 'source ~/.bashrc' or run a new shell and psql should be available.


2

A dump-restore cycle removes all bloat and dead tuples from your tables and restores with the minimum possible size - except if you have a fillfactor setting below 100 that reserves some wiggle-room per data page. Immediately after the migration, you get a lot more "extensions" (added pages at the physical end of the table (the file on disk). The ...


2

To get all unique pairs of elements from an array of arbitrary length: WITH a(a) AS (SELECT '{A,B,C,D}'::text[]) -- provide array here , i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i) SELECT ARRAY[a[i1.i], a[i2.i]] AS pair FROM i i1 JOIN i i2 ON i2 > i1 , a; You can then join to the message table. Without knowing any ...


2

This is how I solved my problem. Upgrade Postgresql 8.4 to 9.4 in Centos 1. Yum Install PG9.4 2. wget http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-1.noarch.rpm 3. yum install pgdg-redhat94-9.4-1.noarch.rpm 4. yum install postgresql94-server 5. service postgresql-9.4 initdb 6. chkconfig postgresql-9.4 on Backup Data 7. su ...


1

If you already have the PK value of a certain row in A, a simple SELECT is the fastest possible way: SELECT * FROM b WHERE a_id = ? If you need to retrieve a_id first, a join is your best options. Make that a LEFT [OUTER] JOIN only if there can be cases where there are no matching rows in B and you still want a_id in that case (or whatever, you didn't ...


1

Query You UPDATE statement looks good, mostly. I re-formatted and made minor improvements: UPDATE line_items li SET product_id = d.latest_product_id FROM products p JOIN vendors v ON v.id = p.vendor_id JOIN vendorgroups vg ON vg.id = v.vendorgroup_id JOIN duplicate_product_sets d ON d.invtid = ...


1

You're misunderstanding how REVOKE works. You can't REVOKE a right that was never GRANTed. It doesn't create a deny ACL (a negative right), it removes a granted right if it exist. You didn't GRANT rights to user1 on the schema; instead that user inherited them from the public role everyone's a member of. So you can't revoke it. You want to revoke the right ...


1

If I understand you requirements, this consists of selecting all the users that have a project in step 3, and using that list to select all the projects for just those users. Something like: SELECT user_groups.id , STRING_AGG( DISTINCT(projects.step_number), ', ') AS summary FROM user_groups g INNER JOIN ( SELECT users.user_group_id ...


1

Your question is a bit light on definitions. Assuming: You define the "least amount of information" with how many of the relevant columns are NULL. Primary key is adr_id. Duplicates are marked with a column dupe_id to indicate groups of duplicates. Since it's also vague what to do exactly, I create a separate table with the dupe ranking: CRATE TABLE ...


1

You could define your own functions range_add/range_sub (intersection doesn't need special handling): select range_add('[1,5)'::int4range, '[8,10)'::int4range); range_add -------------------- {"[1,5)","[8,10)"} select range_sub('[1,10)'::int4range, '[5,8)'::int4range); range_sub -------------------- {"[1,5)","[8,10)"} You could ...



Only top voted, non community-wiki answers of a minimum length are eligible