Syntax for drop/recreate of constraints in PostgreSQL

1. Find the constraints belonging to a specific table:

SELECT conname
FROM pg_constraint
WHERE conrelid = 'mytable'::regclass;
    conname
----------------
 sys_c002733781
 sys_c002733782
(2 rows)

2. Drop them:

ALTER TABLE mytable DROP CONSTRAINT sys_c002733781;
ALTER TABLE
ALTER TABLE mytable DROP CONSTRAINT sys_c002733782;
ALTER TABLE

3. Load your data using your preferred method

4. Recreate the FK constraints but skip validating existing data (only new inserts/updates are checked):

alter table mytable add constraint sys_c002733781 foreign key(job_id) references jobs(id) on delete cascade not valid;
ALTER TABLE

alter table mytable add constraint sys_c002733782 foreign key(task_id) references tasks(id) on delete cascade not valid;
ALTER TABLE

5. Validate constraints:

alter table mytable validate constraint sys_c002733781;
ALTER TABLE
alter table mytable validate constraint sys_c002733782;
ALTER TABLE

Query equivalent to SELECT * FROM USER_OBJECTS in PostgreSQL

SELECT schema_name, object_name, object_type
FROM (
    -- Tables, Views, Indexes, etc.
    SELECT
        n.nspname AS schema_name,
        c.relname AS object_name,
        CASE c.relkind
            WHEN 'r' THEN 'TABLE'
            WHEN 'v' THEN 'VIEW'
            WHEN 'm' THEN 'MATERIALIZED VIEW'
            WHEN 'i' THEN 'INDEX'
            WHEN 'S' THEN 'SEQUENCE'
            WHEN 'f' THEN 'FOREIGN TABLE'
            WHEN 'p' THEN 'PARTITIONED TABLE'
            WHEN 'I' THEN 'PARTITIONED INDEX'
            ELSE c.relkind::text
        END AS object_type
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = current_schema()

    UNION ALL

    -- Functions
    SELECT
        n.nspname AS schema_name,
        p.proname AS object_name,
        'FUNCTION' AS object_type
    FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = current_schema()
) AS objects
ORDER BY object_type, object_name;

It only shows objects in the current schema (like Oracle USER_OBJECTS).

If you want all objects the user owns (across schemas), replace

WHERE n.nspname = current_schema()

with

WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')