97
Your syntax is almost good, needs some parenthesis around the subqueries and it will work:
INSERT INTO bar (description, foo_id) VALUES
( 'testing', (SELECT id from foo WHERE type='blue') ),
( 'another row', (SELECT id from foo WHERE type='red' ) );
Tested at DB-Fiddle
Another way, with shorter syntax if you have a lot of values to insert:
...
81
Whether a password is required or not has nothing to do with pg_shadow and whether a password is actually defined for the user. Yes, I know, that's weird.
pg_hba.conf controls the authentication method. If you want to request a password, use md5 authentication. If you want to allow login with no password to anyone, use trust. If you want to require the same ...
51
Connect to the psql command --> psql --u {userName} {DBName} then you can type the below command to check how many schemas are present in the DB
DBName=# \dn
Else you can check the syntax by the below steps easily-
After connecting the the DB, press
DBName=# help
You will get the below options:
You are using psql, the command-line interface to ...
50
Plain INSERT
INSERT INTO bar (description, foo_id)
SELECT val.description, f.id
FROM (
VALUES
(text 'testing', text 'blue') -- explicit type declaration; see below
, ('another row' , 'red' )
, ('new row1' , 'purple') -- purple does not exist in foo, yet
, ('new row2' , 'purple')
) val (description, type)
LEFT JOIN foo f ...
answered Jul 16 '13 at 20:40
Erwin Brandstetter
138k17 gold badges327 silver badges474 bronze badges
35
In PostgreSQL 9.6 there will be a new version of pg_trgm, 1.2, which will be much better about this. With a little effort, you can also get this new version to work under PostgreSQL 9.4 (you have to apply the patch, and compile the extension module yourself and install it).
What the oldest version does is search for each trigram in the query and take the ...
32
Granting all privileges to all tables within the database is achieved with
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>;
31
If you created an SQL-format dump, all you can use is psql.
If you created a custom-format (pg_dump -Fc) or directory-format (pg_dump -Fd) dump, you can and must use pg_restore.
Custom and directory format dumps offer a lot of advantages over plain SQL script dumps, and I use them exclusively. You can selectively restore only some tables/schema, can choose ...
29
You can query pg_catalog.pg_views for your desired information:
select viewname from pg_catalog.pg_views;
Refined query to get schema name also - just in case you have multiple views with the same name in different schemas - and left out those system views:
select schemaname, viewname from pg_catalog.pg_views
where schemaname NOT IN ('pg_catalog', '...
29
You can select which schemas to dump with the -n option of pg_dump. Create a dump of schema B:
pg_dump ...other...options... -Fc -n B >dump.dmp
Restore the dump file:
pg_restore -d somedb dump.dmp
The target database does not have to have the same name as the original one.
Note that you will have problems if schema B has dependencies on schema C. Then ...
28
There are basically three ways of upgrading PostgreSQL from different major versions (e.g. 9.1 to 9.3).
Upgrading with pg_dump
The first one, and recommended if possible, is to do a dump of the old (9.1) version using the binary of the newer (9.3) version and restore it on a new cluster created of the newer version.
This approach is, generally, the slower ...
25
You were close. Your last idea is actually the way to go:
log_statement = none
log_min_duration_statement = 10000
Then no statement will be logged, except those running longer than 10 seconds - including the query string itself. Logging may have seemed to stop because 10 seconds is a high threshold. I am using 2 seconds normally, but YMMV.
This related ...
24
You're restoring with pg_restore --format=c ... but the pg_dump was not done with --format=c, it was done with the default, plain format.
From pg_dump manpage:
-F format, --format=format
Selects the format of the output. format can be one of the
following:
p, plain
Output a plain-text SQL script file (the default).
A ...
22
It may be that I was doing something wrong here since I am very new to PostgreSQL. But this only solved the first part of the problem for me - setting the privileges on all existing tables.
In order for permissions to be correctly set for my user on new tables, that are created I has to set default permissions for the user:
ALTER DEFAULT PRIVILEGES IN ...
22
So you look up other tables in a CHECK constraint.
CHECK constraints are supposed to run IMMUTABLE checks. What passes OK for a row at one time should pass OK at any time. That's how CHECK constraints are defined in the SQL standard. That's also the reason for this restriction in the manual:
Currently, CHECK expressions cannot contain subqueries nor refer ...
21
The short answer to the question would be:
SELECT *
FROM pg_tables t
WHERE t.tableowner = current_user;
19
Beginning On postgres 9.3, One trick you can use in postgres to get the exact sql of informational command (such as \d, \du, \dp, etc) in psql is by using a transaction.
Here's how the trick goes.
Open one postgres session, then type your command :
begin;
\dn+
While the transaction still running, open another postgres session, and query the ...
19
You have corrupted your database by manually deleting files from within the data directory. Never delete files from within the data directory manually.
Safely removing WAL
If you want to remove WAL, either let the server do it at CHECKPOINT time, or use pg_archivecleanup. Note that the server will remove WAL is no longer needs automatically, unless:
...
19
The postgres user by default has no password. To remove a user password (in this case for the postgres user/role):
alter role postgres password null;
We also need to set authentication to trust in pg_hba.conf - view details
16
No, it's easy (now anyway).
Grant the connect permission on a new user
GRANT CONNECT ON DATABASE mydb TO myReadolyUser;
Grant the permissions on all the current database objects. This is schema-specific, and you'll have to run one copy for every schema you wish for your user to use,
GRANT SELECT ON ALL TABLES IN SCHEMA mySchema TO myReadonlyUser;
From ...
16
PostgreSQL maintains a list of reserved and non-reserved terms in the appendix. ALIAS is absent from that list. You can verify PostgreSQL does not use ALIAS by checking out the YACC grammar. Even as far back as Postgres95 ALIAS was not a reserved word (the first version in the migration from QUEL to SQL)
👍 MySQL does NOT reserve ALIAS
👍 MariaDB does NOT ...
15
This is some kind of misunderstanding. The query in your question already returns what you are asking for. I only changed minor details:
SELECT text 'Inspections' AS data_label
, count(i.close_case_date) AS daily_count
, d.day AS date_column
FROM (
SELECT generate_series(timestamp '2013-01-01'
, ...
answered Jul 28 '14 at 21:37
Erwin Brandstetter
138k17 gold badges327 silver badges474 bronze badges
15
You can run that, no problem:
VACUUM FULL ANALYZE pg_largeobject;
Might even remove some dead rows. Details:
VACUUM returning disk space to operating system
But it's probably not going to solve your actual problem.
When using the large object facility of Postgres, large objects ("blob": binary large object) themselves are broken up in chuncks of binary ...
answered Aug 29 '15 at 23:36
Erwin Brandstetter
138k17 gold badges327 silver badges474 bronze badges
13
You can add parameter -n [schema name]
The comment of this parameter is said:
-n schema
--schema=schema
Dump only schemas matching schema; this selects both the schema
itself, and all its contained objects. When this option is not
specified, all non-system schemas in the target database will be
dumped.
11
To check what CLUSTER does, I took a table fo mine from an earlier experiment which basically contained the first 10 million positive integers. I already deleted some rows and there is an other column as well but these only affect the actual table size, so it is not that interesting.
First, having run VACUUM FULL on the table fka, I took its size:
\dt+ ...
11
Basic INSERT
INSERT INTO item_tags (item_id, tag_id)
SELECT p.item_id, t.id
FROM (SELECT item_id FROM properties WHERE name LIKE 'body') p
, (SELECT id FROM tags WHERE name ILIKE '%hoax%') t
Where the comma (,) in the FROM clause is short notation for CROSS JOIN.
That's building on your assertion that the second query on tags ...
returns 1 row of ...
9
VACUUM FULL rewrites the entire contents of the table into a new
disk file with no extra space, allowing unused space to be returned to
the operating system. This method also requires extra disk space,
since it writes a new copy of the table and doesn't release the old
copy until the operation is complete. Usually this should only be used
when a ...
9
I just ran into the same issue on Postgres 8.3.11. Although I could not identify the root cause, the fix was simple enough:
REINDEX INDEX tbl_cust_id_idx;
This page contains hints as to what may have caused the error, although they are vague:
An indicated table index was corrupted (may be a result of recent postgres or system failure, there was a lack ...
9
The simple and nice way is to create a superuser with read only permission.
Login psql as postgres or other superuser.
Create the new superuser role and set it to read only :
CREATE USER backadm SUPERUSER password '<PASS>';
ALTER USER backadm set default_transaction_read_only = on;
Replace <PASS> by your choosen password.
You can replace ...
9
You can use DISTINCT ON for this type of queries:
SELECT DISTINCT ON (object_id) *
FROM data
ORDER BY object_id, dt DESC ;
9
PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either.
If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index.
I can't figure out what you're trying to say with:
If this ...
Only top voted, non community-wiki answers of a minimum length are eligible