A UNIQUE constraint allows multiple rows with null values, because two null values are not considered to be the same. (Except when using NULLS NOT DISTINCT in Postgres 15 or later.) Similar considerations apply to CHECK constraints. They allow the expression to be true or null (just not false). Again, null values get past the … Read more
You should use UNION. select * from table1 union select * from table2 To insert into table 1: INSERT INTO TABLE1 select * from table2 where not exists( select * from table1 where name=TABLE2.Name and count=TABLE2.Count )
That is expected and documented behaviour: From the manual: Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the “current” time, so that multiple modifications within … Read more
pg_dump defaults to plain SQL export. both data and structure. open command prompt and run pg_dump -U username -h localhost databasename >> sqlfile.sql Above command is preferable as most of the times there will be an error which will be something like – …FATAL: Peer authentication failed for user …
In all schemas: => \dt *.* In a particular schema: => \dt public.* It is possible to use regular expressions with some restrictions \dt (public|s).(s|t) List of relations Schema | Name | Type | Owner ——–+——+——-+——- public | s | table | cpn public | t | table | cpn s | t | table … Read more
You will need to use the PL/PgSQL EXECUTE statement, via a DO block or PL/PgSQL function (CREATE OR REPLACE FUNCTION … LANGUAGE plpgsql). Dynamic SQL is not supported in the ordinary SQL dialect used by PostgreSQL, only in the procedural PL/PgSQL variant. DO $$ BEGIN EXECUTE format(‘CREATE TABLE %I AS SELECT * FROM backup’, ‘backup_’ … Read more
DO will do the trick: DO $$ DECLARE row record; BEGIN FOR row IN SELECT tablename FROM pg_tables WHERE schemaname=”public” — and other conditions, if needed LOOP EXECUTE format(‘ALTER TABLE public.%I SET SCHEMA [new_schema];’, row.tablename); END LOOP; END; $$;
use pg_getfunctiondef; see system information functions. pg_getfunctiondef was added in PostgreSQL 8.4. SELECT pg_get_functiondef(‘proc_name’::regproc); To dump all functions in a schema you can query the system tables in pg_catalog; say if you wanted everything from public: SELECT pg_get_functiondef(f.oid) FROM pg_catalog.pg_proc f INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid) WHERE n.nspname=”public”; it’s trivial to change … Read more
Try CREATE TEMP TABLE mytable AS SELECT * FROM orig_table; Per http://www.postgresql.org/docs/current/static/sql-selectinto.html CREATE TABLE AS is functionally similar to SELECT INTO. CREATE TABLE AS is the recommended syntax, since this form of SELECT INTO is not available in ECPG or PL/pgSQL, because they interpret the INTO clause differently. Furthermore, CREATE TABLE AS offers a superset … Read more
I’ve recently discovered DBeaver. It automatically detects relations between tables in a visual manner. You can move tables around, change colors, see foreign keys, etc. It’s extremely good. Fully compatible with PostgreSQL Querying and manipulating data using the GUI is extremely easy. Not only that you can use it with all major SQL and noSQL … Read more